SQL Error Line 49??

I dont understand why my program is breaking at line 49. The syntax is correct. I should be able to display the results only based on the category I select. The SQL string is correct in MySQL Query Browser.

All I want is my code to display Paginated data based on the category I select with the form.

If i remove the "WHERE category = '$category'"; the code works fine... except it returns EVERY row in the database. Thats a problem, I only want to return rows where category = the category I selected in the form.

Any Idea's from you fellow programmers out there?

<!-- FORM TO DISPLAY DATABASE TEMPLATES -->
<form name="form1" method="post" action="<?php echo $PHP_SELF?>">
  <select name="section" size="1" multiple>
    <option selected>layouts</option>
  </select>
  <select name="category" size="1">
    <option selected>animals</option>
    <option>Anime</option>
  </select>
  <br>
  <input type="submit" name="Submit" value="Submit">
</form>
<!-- END DISPLAY FORM -->

<?
//GET FORM DATA
$section = $_POST['section'];
$category = $_POST['category'];

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if

require("connectDB.php"); //DATABASE CONNECTION SEQUENCE

$query = "SELECT count(*) FROM upload WHERE category = '$category'";
$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 1;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query = "SELECT * FROM upload $limit";
$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if

echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if


//FETCH SQL DATA AND PRINT IT TO THE SCREEN
while($row = mysql_fetch_array($result)){
	$id = $row["id"];
	$codes = $row["codes"];
	// $codes = ereg_replace("INSERTURLHERE", , $codes); NOT CURRENTLY USING THIS FEATURE
	print '<table width="400" border="2" cellspacing="0" cellpadding="0">';
	
	//DISPLAY THUMBNAIL IMAGE FROM DATABASE
	print ("<tr><td><img src=\"download.php?id=$id\"></td></tr>"); 
	
	//POPULATE TEXTFIELD WITH CSS CODE FOR TEMPLATE
	print "<tr><td><textarea name='textfield' wrap='OFF' cols='50' rows='7'>".$codes."</textarea></td></tr>";
	print '</table><br /><br />';

}
require("disconnectDB.php");
?>

Code (markup):

 

Probably the $category that you get from user input contains a quote character , which breaks the SQL query.

In any case, you should always escape any user input to avoid SQL injection attack.

$query = "SELECT count(*) FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'";

 

Hot Dog man!!

This almost solved my problem completely! I can now browse categories no problem.

The only issue I run into now. Is when I select a new category in the dropdown box and click submit. The page refreshes and shows the information but the pagination gets really jacked up.

It shows e.g - "Page 1 of 7" but it actually isnt paginating correctly. It is spitting all rows on on the first page.

so if i add $limit for line 48:

$query = "SELECT * FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'$limit";

Code (markup):

it now displays the proper amount on the page. But when you click next page you get an error on line 49 which is:

$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);

Code (markup):

*******************

On a second note, I have to say I do find learning PHP rather fun. I was pretty good at C++ and grasp most of PHP but there is still a learning curve. Although I will say, it is easier in many ways compared to C++!

 

My first guess is, $limit isn't constructed properly, most probably because there is some minor mistakes in the paging links.

To help with debugging during development, would be good if you also print out the error message:
$result = mysql_query($query) or trigger_error("FATAL ERROR: " . mysql_error(), E_USER_ERROR);

Echo-ing the $query will definitely help, too.

If you're still stuck, post the 'href' of the link to the "next" page, the part of the code which assigns the value to $limit and the final value of $query.

 

yeah it looks like it may not be constructed properly.

Doing a print $query returned the following.

On Initial Load:

On Selection of Category and Submitting

On Clicking the "next" hyperlink. the links dissapear and the page displays.

 

I changed the code to check if the variable is empty before loading the page and that got rid of the query error. But pagination is still broke.

I uploaded my code and database to a live server so ppl could see. Figured it might be a little easier to see what my code is doing that way.

My Page is Here: http://www.anakin-skywalker.com/

As you can see you select a category and it displays the first 3 results like we want to. (for debugging i had it print out the SQL query it passes) And on clicking the next page it doesnt page ahead.

Here is my modified code.

<!-- FORM TO DISPLAY DATABASE TEMPLATES -->
<form name="form1" method="GET" action="<?php echo $PHP_SELF?>">
  <select name="section" size="1" multiple>
    <option selected>layouts</option>
  </select>
  <select name="category" size="1">
    <option selected>animals</option>
    <option>Anime</option>
  </select>
  <br>
  <input type="submit" name="Submit" value="Submit">
</form>
<!-- END DISPLAY FORM -->

<?php

// Check if the form was submitted
if ($_GET['Submit']) {

	//GET FORM DATA
	$section = $_GET['section'];
	$category = $_GET['category'];

	if (isset($_GET['pageno'])) {
	   $pageno = $_GET['pageno'];
	} else {
	   $pageno = 1;
	} // if

	require("connectDB.php"); //DATABASE CONNECTION SEQUENCE

	$query = "SELECT count(*) FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'";
	$result = mysql_query($query) or trigger_error("FATAL ERROR: " . mysql_error(), E_USER_ERROR);
	$query_data = mysql_fetch_row($result);
	$numrows = $query_data[0];
	
	// Check if any results were returned
	if ($numrows < 1){
		// No results returned, show a message
		echo "No Results Returned! Please try searching again!";
	} else {
		$rows_per_page = 3;
		$lastpage      = ceil($numrows/$rows_per_page);

		$pageno = (int)$pageno;
		if ($pageno < 1) {
		   $pageno = 1;
		} elseif ($pageno > $lastpage) {
		   $pageno = $lastpage;
		} // if

		$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
		$query = "SELECT * FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'$limit";
		print $query;
		$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);

		if ($pageno == 1) {
		   echo " FIRST PREV ";
		} else {
		   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&section=$section&category=$category'>FIRST</a> ";
		   $prevpage = $pageno-1;
		   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&section=$section&category=$category'>PREV</a> ";
		} // if

		echo " ( Page $pageno of $lastpage ) ";

		if ($pageno == $lastpage) {
		   echo " NEXT LAST ";
		} else {
		   $nextpage = $pageno+1;
		   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&section=$section&category=$category'>NEXT</a> ";
		   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&section=$section&category=$category'>LAST</a> ";
		} // if

		//FETCH SQL DATA AND PRINT IT TO THE SCREEN
		while($row = mysql_fetch_array($result)){
			$id = $row["id"];
			$codes = $row["codes"];
			// $codes = ereg_replace("INSERTURLHERE", , $codes); NOT CURRENTLY USING THIS FEATURE
			print '<table width="400" border="2" cellspacing="0" cellpadding="0">';

			//DISPLAY THUMBNAIL IMAGE FROM DATABASE
			print ("<tr><td><img src=\"download.php?id=$id\"></td></tr>"); 

			//POPULATE TEXTFIELD WITH CSS CODE FOR TEMPLATE
			print "<tr><td><textarea name='textfield' wrap='OFF' cols='50' rows='7'>".$codes."</textarea></td></tr>";
			print '</table><br /><br />';

		}
		
	}

	require("disconnectDB.php");

} else {
	// The form was not submitted yet
	echo "Please search to see results...";
}

?>

Code (markup):

 

Problem Solved