mysql & php bookmark application – tutorial – Part 3


We are carrying on with the bookmarks application. In this tutorial we shall be adding add & edit bookmarks. Just follow the example below and the comments will explain what each script does. Now let get started.

1 – Add New Bookmark

This will allow you add a new bookmark & new category if one is not avaiable.


<?php # Script 0.5 - add_bm_cat.php
session_start(); // Access the existing 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 = 'Add new Bookmarks';

include ('includes/header.html');

// connect to db
require_once ('../mysqli_connect_bm.php');

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

$errors = array();

// check for bookmark name

if (!empty($_POST['bname'])) { $bn =trim($_POST['bname']);
} else {
$errors[] = 'Please enter URL title';
}

// check for URL

if (!empty($_POST['baddy'])) { $ba =trim($_POST['baddy']);
} else {
$errors[] = 'Please enter URL address';
}

// check for description

if (!empty($_POST['bmdesc'])) { $bm =trim($_POST['bmdesc']);
} else {
$errors[] = 'Please enter description';
}

//check if new category is being entered.

if(isset($_POST['category']) && ($_POST['category'] == 'new') )
{

// validate category

if (!empty($_POST['cat_name'])) {
$cn = trim($_POST['cat_name']);

// add new category to db

$q = 'INSERT INTO category (cat_name) VALUES (?)';

$stmt = mysqli_prepare($dbc, $q);

mysqli_stmt_bind_param($stmt, 's', $cn);

mysqli_stmt_execute($stmt);

if(mysqli_stmt_affected_rows($stmt) == 1) {
echo '<p>new category added. </p>';
$ci = mysqli_stmt_insert_id($stmt);

} else {

$errors[]='New category could not be added!';
}
mysqli_stmt_close($stmt);

// Complete category conditional

} else { // no category name
$errors[] ='Please enter the category name';

}

} elseif ( isset($_POST['category'])&&($_POST['category'] == 'existing')
&&($_POST['existing'] > 0) )
{

$ci = (int) $_POST['existing'];
} else { // no catergory selected.

$errors[]= 'Please enter Category';

}

// insert into DB b_marks

if(empty($errors)) {
$q = 'INSERT INTO b_marks (bname, baddy, bmdesc, cat_id)
VALUES (?, ?, ?, ?)';

$stmt = mysqli_prepare($dbc, $q);

mysqli_stmt_bind_param($stmt, 'sssi', $bn, $ba, $bm, $ci);

mysqli_stmt_execute($stmt);

// confirm results of the query.

if (mysqli_stmt_affected_rows ($stmt) ==1)
{
echo '<p> The bmark is added. </p>';
}else{
echo '<p>error system overload aahh</p>';
}

// Complete the conditionals

mysqli_stmt_close($stmt);
} // end of $errors if
} // end of submission if

?>

<h2>Enter a new Bookmark</h2>
<form action="add_bm_cat.php" method="post">
<p>Bookmark Name: <input type="text" name="bname" size="15" maxlength="20" value="
<?php if (isset($_POST['bname'])) echo $_POST['bname']; ?>" /></p>

<p>URL Address: <input type="text" name="baddy" size="50" maxlength="80" value="
<?php if (isset($_POST['baddy'])) echo $_POST['baddy']; ?>" /></p>

<p>Description: <textarea name="bmdesc" cols="40" rows="5" >
<?php if (isset($_POST['bmdesc'])) echo $_POST['bmdesc']; ?> </textarea></p>

<div>Category:
<p><input type="radio" name="category" value ="existing"
<?php if (isset($_POST['category']) && ($_POST['category']=='existing'))
echo 'checked="checked"'; ?> />
Exisitng =>
<select name="existing"><option>Select One</option>

// Retrive Category

<?php $q = "SELECT cat_id, (cat_name) FROM category ORDER BY cat_name ASC";

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

if (mysqli_num_rows($r) > 0) {
while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {

echo "<option value=\"$row[0]\"";

if (isset($_POST['existing']) && ($_POST['existing'] == $row[0]) )
echo ' selected="selected"';
echo ">$row[1]</option>\n";

}

} else {

echo '<option>Please add a new category. </option>';

}

mysqli_close($dbc);

?>

</select></p>

<p><input type="radio" name="category" value ="new"
<?php if (isset($_POST['category']) && ($_POST['category']=='new'))
echo 'checked="checked"'; ?> />
New =>
Category Name: <input type="text" name="cat_name" size="15" maxlength="25"
value="<?php if(isset($_POST['cat_name']))
echo $_POST['cat_name']; ?>"
</p>
</div>

<div align="center"><input type="submit" name="submit" value="Submit" /></div>
<input type="hidden" name="submitted" value="TRUE" />

</form>

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

2 – Edit Bookmarks
In this script you will be able to edit current bookmarks or delete them, also added to this which has not been included within the previous tutorial in view_bookmarks.php is pagination of listed records.

<?php # script 0.5 - edit_view_bookmarks.php

session_start(); // Access the existing 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();
}

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

$page_title = 'Edit & 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, bmarks_id, 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, bmarks_id, 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.

// Table header.
echo '<table id="mytable" cellspacing="0" summary="The List of Bookmarks">
<tr>
<th scope="col" abbr="Edit">Edit</th>
<th scope="col" abbr="Category">Category</th>
<th scope="col" abbr="URL Link">URL Link</th>
<th scope="col" abbr="Description">Description</th>
<th scope="col" abbr="Delete">Delete</th>
</tr>
';

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

echo '<tr bgcolor="' . $bg . '">
<td>Edit</td>
<td>' . $row['cat_name'] . '</td>
<td>' . $row['bname'] . '</td>
<td>' . $row['bmdesc'] . '</td>
<td>Delete</td>
</tr>
';
}

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>';

}
// 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 'Previous ';
}

// make all the numbered pages:

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

echo '' .$i. '';
} else {

echo $i . ' ';

}
} // end of FOR loop.

// If it's not the last page, make a Next button:
if ($current_page != $pages) {
echo 'Next';
}

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

} // End of links section.
echo '</div>';

//*make category summary list*

// Define the query
echo '<div id="sidebar">';
$q = "SELECT category.cat_id, cat_name FROM category ORDER BY cat_name ASC";

// check for category in URL

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

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

// Table header.
echo '<table id="mytable2" cellspacing="0" summary="Select Category">
<tr>
<th scope="col" abbr="Category">Category</th>
</tr>
';

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

echo "<tr bgcolor={$bg}>
<td>{$row['cat_name']}</td>
</tr>";

}

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

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

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

The above also includes a sidebar to select links based on category, also I have changed the CSS layout so the change must be reflected in view_bookmarks.php. So to save the effort please download updated files from here. Right hand click and save as CSS FileFooterHeaderView Bookmarks also images from here. Please remember to change the URL links within the header so that the links work. Any problems, questions or bugs then leave a comment.

Next tutorial will be creating RSS feed. I have started work on this and is close to completion.

2 thoughts on “mysql & php bookmark application – tutorial – Part 3”

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