mysql & php bookmark application – tutorial – Part 1


In this tutorial we are going to create a basic bookmark/links application using php & mysql. It’s aimed and beginners but does require a good understanding of html, css, php & mysql, website structure and that you have access to localhost running a web server or external service.

Now the first part is to create the database, we are going to create three tables. 1) b_marks 2) category 3) user.

Log into your preferred mysql interface.
1) Create the database

CREATE DATABASE bookmarks;
USE bookmarks;

2) Create the b_marks table to store the bookmarks/URL links.

CREATE TABLE `bookmarks`.`b_marks`(
`bmarks_id` int(10) unsigned NOT NULL auto_increment,
`bname` varchar(100) NOT NULL,
`baddy` varchar(500) NOT NULL,
`bmdesc` longtext NOT NULL,
`cat_id` int(10) NOT NULL,
`user_id` int(10) default NULL,
PRIMARY KEY (`bmarks_id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;

3) Create category table to store the category list.

CREATE TABLE `bookmarks`.`category` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(50) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `cat_name` (`cat_name`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

4) Create users table as you will need to log in to enter & edit the bookmarks.

CREATE TABLE `bookmarks`.`users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`username` varchar(30) NOT NULL,
`pass` char(40) NOT NULL,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(40) NOT NULL,
`email` varchar(80) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Once the above has been created you will need to add a user to the users database,

INSERT INTO users (username, pass, first_name, last_name, email)
VALUES (‘username’, SHA1(‘password’),’yourfn’, ‘yourlast’, ’email@email.com’);

Now the above database has been completed we can start with building the application, the list below is the order of files that need to be created. You do not have to follow the order but that’s how I did it.

1) mysqli_connect_bm.php // To enable connection to your bookmarks database.

<?php # mysqli_connect_bm.php

// This file contains the database access information.
// This file also establishes a connection to MySQL
// and selects the database.
// Set the database access information as constants:

DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'pass');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'bookmarks');

// Make the connection:

$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );

?>

Once the above is created save as mysqli_connect_bm.php and place it outside of the web document directory.

2) Create Header


<?php # script 0.5 - header.html

session_start();

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />

<title><?php echo (isset($page_title)) ? $page_title : 'Bookmarks!'; ?></title>

<link rel="stylesheet" href="/phpdev/includes/style.css" type="text/css" media="screen" />

</head>

<body>

<div id="header">

<h3>Bookmarks of Basshead</h3>
<h4>List of handy bookmarks</h4>

</div>

<div id="navigation">
<ul>

// using sessions to enable different links based on whether a user is logged in or not
<?php
if ( (isset($_SESSION['user_id'])) && (!strpos($_SERVER['PHP_SELF'], 'logout.php')) ) {
echo '<li><a href="/phpdev/edit_view_bookmarks.php">Edit/View Bookmarks</a></li>';
echo '<li><a href="/phpdev/add_bm_cat.php">Add New Bookmark</a></li>';
echo '<li><a href="/phpdev/logout.php">Logout</a></li>';

} else {

echo '<li><a href="/phpdev/view_bookmarks.php">View Bookmarks</a></li>';
echo '<li><a href="/phpdev/login.php">Login</a></li>';

}

?>
</ul>
</div>
<div id="content">
</code markup="none">
</div>
Save the file as header.html and place it in your includes directory

<strong>3) Create the footer</strong>
<div class="box">
<code>
</div>

<div id="footer">

<p>Copyright &copy; <a href="#">Dev.Basshead</a> 2008 | Designed by <a href="http://www.basshead.info/">basshead</a> Valid <a href="http://jigsaw.w3.org/css-validator/">
<img style="border:0;width:44px;height:15px"
src="http://jigsaw.w3.org/css-validator/images/vcss-blue"
alt="Valid CSS!" /></a>
</div>

</body>

</html>

Save the file as footer.html and place it in your includes directory

4) Create login functions page that will be used in the login & logout process

<?php # login_functions.inc.php

// This page defines two functions used by the login/logout process.

/* This function determines and returns an absolute URL.
* It takes one argument: the page that concludes the URL.
* The argument defaults to view_bookmarks.php.
*/

function absolute_url ($page = 'view_bookmarks.php') {

//URL is http:// plus the host name plus the current directory

$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);

// remove any trailing slashes:

$url = rtrim($url, '/\\');

// Add the page

$url .= '/' . $page;

// Return the URL

return $url;

} // End of absolute_url() function.

/*This function validates the form data (the email address and password).
* If both are present, the database is queried.
* The function requires a database connection.
* The functions returns an array of information,
*/

function check_login($dbc, $email = '', $pass = '') {

$errors = array(); // Initialize error array.

// Validates the email address and password:

if (empty($email)) {

$errors[] = 'You forgot to enter your email address dude. ';

} else {

$e = mysqli_real_escape_string ($dbc, trim($email));

}

if (empty($pass)) { $errors[] = 'You forgot to enter your password. ';

} else {

$p = mysqli_real_escape_string($dbc, trim($pass));

}

// no errors run the db query

if (empty($errors)) {
$q = "SELECT user_id, first_name FROM users WHERE email='$e' AND
pass=SHA1('$p')";

$r = @mysqli_query ($dbc, $q);

// Check results of query

if (mysqli_num_rows($r) == 1) {

// fetch the record:

$row = mysqli_fetch_array ($r, MYSQLI_ASSOC);

// return true and the record

return array(true, $row);

} else {

$errors[] = 'The email address and password entered do not match those on file.';

}

// Complete conditional & functions

} // End of empty($errors) IF.

return array(false, $errors);

} // End of check_login() function

?>

Save the file as login_functions.inc.php and place it in your includes directory

5) Create the login page that will enable you to login & include the login form.

<?php # login_page_inc.php

$page_title = 'Login';

include ('includes/header.html');

// print error messages
if (!empty($errors)) { echo '<h2>Error!</h2>
<p class="error">The following error(s) occurred:<br />';
foreach ($error as $msg) { echo " - $msg<br />\n";
}
echo '</p><p>Please try again.</p>';
}

?>

<h2>Login</h2>

<form action="login.php" method="post">
<p>Email Address: <input type="text" name="email" size="20" maxlength="80" /></p>

<p>Password: <input type="password" name="pass" size="20" maxlength="20" /></p>

<p><input type="submit" name="submit" value="Login" /></p>

<input type="hidden" name="submitted" value="TRUE" />

</form>

<?php
// Include the footer
include('includes/footer.html');
?>

Save the file as login_page.inc.php and place it in your includes directory

6) Create the CSS file, this you can edit to your delight as this is basic and still work in progress.

/* general */
a {
color:#777;
text-decoration:none;
}

a:hover {
color:#333;
text-decoration:none;
}

/* body */
body {
background:#fafafa;
color:#003300;
font:0.8em Arial, Helvetica, "bitstream vera sans", sans-serif;
}

/* header */
#header {
border-bottom:1px solid #999;
height:80px;
margin:0 auto;
width:751px;
}
#header h1 {
color:#888;
font-size:300%;
letter-spacing:-3px;
text-align:right;
padding:5px;
margin-bottom:-20px;
}
#header h2 {
color:#CCC;
font-size:200%;
letter-spacing:-2px;
text-align:right;
}

/* navigation */
#navigation {
background:#fafafa;
border-right:1px solid #999;
margin:0 auto;
width:750px;
height:40px;
list-style:none;
}
#navigation li {
border-left:1px solid #999;
float:left;
width:149px;
list-style:none;
}
#navigation a {
color:#555;
display:block;
line-height:40px;
text-align:center;
}
#navigation a:hover {
background:#e3e3e3;
color:#555;
}
#navigation .active {
background:#e3e3e3;
color:#777;
}

/* content */
#content {
height:auto;
margin:0 auto;
padding:0 0 20px;
width:751px;
}
#content h1 {
border-bottom:1px dashed #999;
font-size:1.8em;
padding:20px 0 0;
}
#content p {
padding:20px 20px 0;
}

/* footer */
#footer {
border-top:1px solid #999;
height:50px;
margin:0 auto;
padding:10px;
text-align:center;
width:751px;
}

/* error */

.error {
font-weight: bold;
color: #C00
}

Save the file as style.css and place it in your includes directory

Now the next steps are to create view bookmarks page & the final process to enable login & logout functions. This will require four more files.

7) View bookmarks – This script will enable you to view all the bookmarks by name, category and with description. You will also will be able to click on the URL and send you to your website. Added to this script is pagination so that you are direct to another page if rows listed are greater than 10 (This will be updated in future tutorial to include a drop down menu to enable you to select how many rows are listed on the page). The final feature is to sort by category, by clicking on a category name you will list all bookmarks for that category (Again more updates to this function in future tutorials).

<?php # view_bookmarks.php

//Set the page title and include the html header:

$page_title = 'View the Bookmarks';

include ('includes/header.html');

require_once ('../mysqli_connect_bm.php');

// Number of records to display

$display = 10;

// Determine how many pages

if (isset($_GET['p']) && is_numeric($_GET['p'])) { // already determined.

$pages = $_GET['p'];

} else { // need to determine

// Count number of records
$q = "SELECT COUNT(bmarks_id) FROM b_marks";
$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];

// Calculate the number of pages
if ($records > $display) { //more than one page
$pages = ceil ($records/$display);
} else {
$pages =1;
}
} // end of p if

// determine where in the database to start results.

if (isset($_GET['s']) && is_numeric ($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Define the query

$q = "SELECT category.cat_id,
cat_name, bname, baddy, bmdesc FROM category, b_marks
WHERE
category.cat_id = b_marks.cat_id
ORDER BY
category.cat_name ASC,
b_marks.bname ASC
LIMIT $start, $display";

// check for category in URL

if (isset($_GET['cid']) && is_numeric($_GET['cid']) ) {
$cid = (int) $_GET['cid'];

// overwrite the query
if ($cid > 0) {

$q = "SELECT category.cat_id,
cat_name, bname, baddy, bmdesc FROM category, b_marks
WHERE
category.cat_id = b_marks.cat_id
AND b_marks.cat_id = $cid
ORDER BY
category.cat_name ASC,
b_marks.bname ASC
LIMIT $start, $display";

}
}

$r = @mysqli_query ($dbc, $q); // Run the query.

// Count the number of returned rows:
$num = mysqli_num_rows($r);

if ($num > 0) { // If it ran OK, display the records.

// Print how many bookmarks there are:
echo "<p>There are currently $num Bookmarks Listed.</p>\n";

// Table header.
echo '<table align="center" cellspacing="3" cellpadding="3" width="100%">
<tr><td align="left"><b>Category</b></td>
<td align="left"><b>URL Link</b></td>
<td align="left"><b>Description</b></td>
</tr>
';

// Fetch and print all the records:

$bg = '#eeeeee'; // set the initial background colour

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.

echo "\t
<tr bgcolor={$bg}>
<td align=\"left\"><a href=\"view_bookmarks.php?cid={$row['cat_id']}
\">{$row['cat_name']}</a></td>
<td align=\"left\"><a href={$row['baddy']}>{$row['bname']}</td>
<td align=\"left\">{$row['bmdesc']}</td>
</tr>\n";
}

echo '</table>'; // Close the table.

mysqli_free_result ($r); // Free up the resources.

} else { // If no records were returned.

echo '<p class="error">There are currently no listed bookmarks.</p>';

}

mysqli_close($dbc); // Close the database connection.

// make the links to other pages, if required.
if ($pages > 1) {
// add some spacing and start a paragraph
echo '<br /><p>';
// determine what page the script is on:
$current_page = ($start/$display) +1;

// if it's not first page make a previous button:

if ($current_page !=1) {
echo '<a href="view_bookmarks.php?s=' . ($start - $display) . '&p=' . $pages .
'">Previous</a> ';
}

// make all the numbered pages:

for ($i = 1; $i <= $pages; $i++) {
if ($i !=$current_page) {

echo '<a href="view_bookmarks.php?s=' . ($start + $display) . '&p=' . $pages .
'">Next</a>';
} else {

echo $i . ' ';

}
} // end of FOR loop.

echo '</p>'; // close the paragraph.

} // End of links section.

include ('includes/footer.html');
?>

Save the file in your root web directory as view_bookmarks.php

8) Login function, this script will call for the login_functions.inc.php file and then redirect to loggedin.php if logged in.

<?php # login.php

if (isset($_POST['submitted'])) {

require_once ('includes/login_functions.inc.php');
require_once ('../mysqli_connect_bm.php');
list ($check, $data) = check_login($dbc, $_POST['email'], $_POST['pass']);

if ($check) { // OK!

// Set the session data:.
session_start();
$_SESSION['user_id'] = $data['user_id'];
$_SESSION['first_name'] = $data['first_name'];

// Redirect:
$url = absolute_url ('loggedin.php');
header("Location: $url");
exit();

} else { // Unsuccessful!
$errors = $data;
}

mysqli_close($dbc);

} // End of the main submit conditional.

include ('includes/login_page.inc.php');
?>

Save in root directory as login.php

9) The loggdein script is used to redirect you from login.php file with a message and also redirect if accessed in error.

<?php # loggedin.php

// The user is redirected here from login.php.

session_start(); // Start the session.

// If no session value is present, redirect the user:
if (!isset($_SESSION['user_id'])) {
require_once ('includes/login_functions.inc.php');
$url = absolute_url();
header("Location: $url");
exit();
}

$page_title = 'Logged In!';
include ('includes/header.html');

// Print a customized message:
echo "<h2>Logged In!</h2>
<p>You are now logged in, {$_SESSION['first_name']}!</p>";

include ('includes/footer.html');
?>

Save in root directory as loggedin.php

10) Now for the logout script. this will log you out and clear the session data

<?php - logout.php
// This page lets the user logout.

session_start(); // Access the existing session.

// If no session variable exists, redirect the user:
if (!isset($_SESSION['user_id'])) {

require_once ('includes/login_functions.inc.php');
$url = absolute_url();
header("Location: $url");
exit();

} else { // Cancel the session.

$_SESSION = array(); // Clear the variables.
session_destroy(); // Destroy the session itself.
setcookie ('PHPSESSID', '', time()-3600, '/', '', 0, 0); // Destroy the cookie.

}

// Set the page title and include the HTML header:
$page_title = 'Logged Out!';
include ('includes/header.html');

// Print a customized message:
echo "<h2>Logged Out!</h2>
<p>Goodbye you are now logged out!</p>";

include ('includes/footer.html');
?>

Save in root directory as logout.php

Now you should be able test all this out and login/logout. You cannot view any bookmarks as we have yet to do that script, but I think this will do it for this tutorial. So hopefully this will all work and check back soon for the final part. Once the final tutorial is complete you should have a complete bookmark/URL application. I will then add more tutorials as I develop it myself with more functions and more pleasing to the eye.

If you notice any errors, problems then please let me know as I’m a beginner at all this.

Leave a Reply

Your email address will not be published. Required fields are marked *

To submit your comment, click the image below where it asks you to...
Clickcha - The One-Click Captcha