Hey i previosuly posted a problem i was encountering with displayin my query results over multiple pages, that problem has been sorted, however i am now faced with a new problem. the problem is i have a search form containing 2 form fields; a drop menu containing search categorys, i.e. first_name, surname, postcode etc. and a text box where the user enters the criteria.
when the user submits these values i have 2 global variables on m,y results page these are;
$category = $_POST['category'];
$criteria = $_POST['criteria'];
the results are split up into the correct number of pages however when i click next to view the second page, i am faced with 2 errors
1 . "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%%'' at line 1"
2 . "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"
from previous experiances i beleive this corresponds to an empty result set. so i decided to create 2 variables $category , $criteria and assign strings to these variables as follows;
$category = "surname";
$criteria = "d";
thais was to similute a user selecting a category from the drop menu and some criteria. when i tested it again the paging worked fine, so i beleive that the problem lies with the value of the 2 variables;
$category = $_POST['category'];
$criteria = $_POST['criteria'];
is resseting (contains no value) when i click to view the second page. does any one know of an efficient way of solving this problem i.e. preventing the values of the 2 variables being removed when i click next.
the code for my paging is below; any help would be much appriciated cheers 😉
john
<?php
$linkID = mysql_connect("server", "user", "pass")
or die("Error, database not accessing!");
mysql_select_db('database',$linkID);
$table = "customer_table";
$category = $_POST['category'];
$criteria = $_POST['criteria'];
///// NOW ADD YOUR QUERY STRING
$query = "SELECT * FROM $table WHERE $category LIKE '%$criteria%'";
if(!$result = mysql_query($query, $linkID))
echo mysql_error($linkID); //kill yourself if the query fails
else //else page results
{
//get number of rows returned in a variable $totalrows
$totalrows = mysql_num_rows($result);
//grab page number from the query string (in the address bar)
// This can be changed to anything, make shure you change all if you change num
$page = $_GET['num'];
if(!IsSet($page)) //if page has no value set it to one
$page = 1;
//if user did not specify number of results returned
if($_GET['results_returned'] == "")
/// This is the limit you can set to how many resulst per page
$limit = 2; //use a default value of ten (any positive number will do)
else
$limit = $_GET['results_returned'];
//calculates the range of the database query results (i.e. 1-10, 11-20 etc.)
$limitvalue = $page * $limit - ($limit);
if($_GET['numlinks_perpage'] == "") //no number of links specified
$max_links_shown = 3; //use default
else
$max_links_shown = $_GET['numlinks_perpage'];
//modify old query to return a certain range of the results
$query = $query." LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) == 0) //if no rows returned
echo 'No results returned.';
else
{
echo "<font color=\"#779a5e\">".$totalrows." Rows: | </font>";
//calculate number of pages needed, rounded up
$numofpages = ceil($totalrows / $limit);
//if more than one page returned, generate links to other pages
if($numofpages > 1)
{
//if number of pages needed is less than
//or equal to the max links per page
if($max_links_shown >= $numofpages)
{
for($i = 1; $i <= $numofpages; $i++)
{
//if page counter is equal to page number output page
if($i == $page)
echo "[$i]";
else //else generate link to other page number
echo("<a href=\"$PHP_SELF?num=$i\">$i</a> ");
}
}
else //more pages needed than max links shown
{
//here we figure what our first link is and our last page link is
if($max_links_shown%2 != 0) //if max links per page is odd
{
$first_page_link = $page - (($max_links_shown - 1)/2);
$last_page_link = $page + (($max_links_shown - 1)/2);
}
else //max links per page is even
{
$first_page_link = $page -($max_links_shown/2) + 1;
$last_page_link = $page + ($max_links_shown/2);
}
//if first link is less than zero, adjust the first and last links
if($first_page_link < 1)
{
for($i = $first_page_link; $i < 1; $i++)
$last_page_link++; //increment last link
$first_page_link = 1; //set first link to one
}
//if last link is greater than the number of pages needed
if($last_page_link > $numofpages)
{
for($i = $last_page_link; $i > $numofpages; $i--)
$first_page_link--; //decrement first link
//set last link to the number of pages needed
$last_page_link = $numofpages;
}
//if first link is not 1, make link to the first page
if($first_page_link != 1)
echo("$PHP_SELF?num=1".
$varstring."\">First</a> ... ");
//print each page link
for($i = $first_page_link; $i <= $last_page_link; $i++)
{
if($i == $page) //we don't need a link to the current page
//print the page number with brackets around it
echo "<font color=\"#779a5e\">[$i]</font>";
else //print links to other pages
echo("<a href=\"$PHP_SELF?num=$i".
$varstring."\">$i</a> ");
}
//if last link does not equal the number of pages needed,
//print link to last page
if($last_page_link != $numofpages)
echo("... <a href=\"$PHP_SELF?num=$numofpages".
$varstring."\">Last</a> ");
}//end else
}
echo ' ';
$color1 = "#535353";
$color2 = "#474545";
$row_count = 0;
}
}//end paginate results
?>
//display results
<?php
while($row = mysql_fetch_array($result)){
$row_class = ($row_count % 2) ? $color1 : $color2;
echo "<tr>
<td width='30' height='30' align='center'><input name='id' type='radio' value='$row[customer_id]'></td>
<td width='438' height='30'><font face='arial' size='2'> $row[first_name] $row[surname]</font></td>
</tr>";
$row_count++;
}
?>
