Category Archives: MySQL

Finding duplicate records in MySQL


I have added a new function to the add bookmarks & category form to look for duplicate URL’s in the database, and stop them being added into the bookmarks table

First step is to move if(empty($errors)) from above the main query to insert the bookmark into the table to below the input validation section. We then add the test for unique URL just below the if(empty($errors))

if(empty($errors)) {

// checks for duplicate URL

$q = "SELECT bmarks_id FROM b_marks WHERE baddy='$ba'";
$stmt = mysqli_query ($dbc, $q);

if (mysqli_num_rows($stmt) == 0) {

//check if new category is being entered.

This will check the database table for a match of URL’s, if one is found we echo the below which is inserted as the last item when confirming the results of the query, otherwise the query will run.

} else {
echo '<p>URL has already been entered</p>';
}

As you can see I have changed the layout & formated the forms (you can only see this if you login which is just me at the min), I will post all the new files soon once I have cleaned it all up.

Bookmarks PHP Pagination update

I have updated the view_bookmarks.php. script so that the pagination now works correctly once you have selected a category. This will of course only show once you have more than 10 records within a category as the $display is hard coded in at 10.

1) After the $display insert the below, this will check for a category id within the URL.


// check for category in URL
if (isset($_GET['cid']) && is_numeric($_GET['cid']) ) {
$cid = (int) $_GET['cid'];
}

2) Then after the // already determined.
if (isset($_GET[‘p’]) && is_numeric($_GET[‘p’])) {
$pages = $_GET[‘p’]; we need to include an else.


} else { // need to determine

if ($cid > 0) {

and a variable to check that the category id is greater than zero.

3) We need to write another mysql query to count the number of rows per category selected, then another else if no category is selected.


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

} else {

4) Finally we need to edit the echo() statements that create the links so that the category value is passed. We add name=value ( ‘&cid=’ . $cid .) pair at the end of each statement. (PS: I had to remove some of the HTML from the below code for it to show, not sure why?)


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

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

// make all the numbered pages:

for ($i = 1; $i <= $pages; $i++) {
if ($i !=$current_page) {
echo ' herf="view_bookmarks.php?s='. (($display*($i - 1))). '&p=' . $pages .
'&cid=' . $cid . '">' .$i. ';
} else {
echo '<li>' .$i .'</li>';
}
} // end of FOR loop.

// If it's not the last page, make a Next button:
if ($current_page != $pages) {
echo'
href="view_bookmarks.php?s='. ($start + $display) . '&p=' . $pages .
'&cid=' . $cid . '">Next

I have a few more things in the pipeline to upgrade the bookmark app, so more coming soon ish!!!

Getting into PHP

Why did I get into learning PHP? Because I wanted to understand how a website was built correctly. I had a old website with blogging software from b2evolution, gallery from coppermine and couple of static pages put together not knowing how the code worked. So to upgrade things I started to learn how a website was built & developed, and what technologies were required.

The first part was to understand (X)HTML & CSS and then from that point you can go on to learn PHP with MySQL. You can find many tutorials online as an example of a resource of PHP tutorials at Nettuts. I also find buying an old book was very good as it meant that I was learning in a more structured manor then if I used online tutorials. So the book I brought was PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide and also PHP 5 Advanced: Visual QuickPro Guide (2nd Edition).

I have now finished the first book and will be finally starting work on my first PHP & Mysql application

MySQL University

Came across this the other day, it’s the MySQL University that offers free educational online program for engineers/developers. It’s open to everyone to just join in by using DimDim, a web based conference tool build in Flash.

The session room opens 15 minutes before the live sessions starts, but you can also catch up on previous shows if like me you probably will not be able to catch the live session. Go here for list of previous sessions

For more information and please visit the MySQL University homepage

Cannot modify header information

I have been working on making a login page as part of learning php. This method also included using cookies, not everyones favorite but I had to learn about it. The thing with cookies is that they must be sent from the server to the client prior to any other information.

This was the area that was creating my problem, every time I logged in I would get this error.

Cannot modify header information – headers already sent by (output started at /var/mysqli_connect.php:18)

This is a common error when creating cookies. So pay attention to the error message, which is something I did not do straight away. All I had to do was delete any white space after my closing ?> in mysql_connect file.

PhP & Mysql update

Been making slow but steady progress with php & mysql learning this month. I feel that things are starting to fall into place and soon I’m going to start work on my first project.

It’s going to be php & mysql based links/bookmarks page for the main basshead website. Currently it’s an old static page that was put together a few years ago before I had a clue about basic structure of HTML etc. I could use scuttle as used by weloveit on they bookmarks site, but it’s something I would like to write from start to finish as it would be a good learning platform. Also this will start life as a page for basshead links/bookmarks that can only be updated by myself as I will not be allowing anyone else to input bookmarks as it is not a social networking bookmark system like scuttle. If this project works out ok then anyone can use it, and change it so it becomes social networking based.