Pagination and dropdown menu used in searching mysql

[intriguedone]

I am relativly new to PHP and MySQL, only been working with it for about a month other than modding forums and pre made scripts.

I am working on building a database made for searching. I have a text search and a dropdown box used for searching different table collumns (lets people search for certan criteria). I have that working correctly.

I also only want to show 25 results per page. I have that working correctly.

My problem is that when I combine the two it breaks the code. The pagination works fine without the dropdown and vice versa. I think it is due to the variable that is being used to choose the collumn in the sql query.

Can someone help me out with this one?

<?
echo '<form action='.$_SERVER['PHP_SELF'].' method="get">
<input type="text" name="search">
<select name="search_type">

<option value="tut_writer">Tutorial Writer Name</option>
<option value="art_name">Artist Name</option>
</select>
<input type="submit" name="Submit" value="search">
</form>';

if ($_GET['search']== "") {
echo "Please enter a search term.";
die;
}
$search_type=$_GET['search_type'];
$search=$_GET['search'];

echo $search_type;
if($search)
{
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 25;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
$sql = mysql_query("SELECT * FROM Links WHERE $search_type LIKE '%$search%' ORDER by id DESC LIMIT $from, $max_results ");

// set up table for displaying results

// Create Table headers for output
echo "<table border='1'>
<tr>
<th>Tutorial Writer</th>

<th>Tutorial Name</th>
<th>Tutorial Type</th>
<th>Artists Name</th>
<th>Artist Type</th>
<th>Artist Company</th>
<th>Notes</th>
<th>Preview</th>
</tr>";

while($row = mysql_fetch_array($sql)){
// Build your formatted results here.
// replace title with desired output
$tut_notes=$row['tut_notes'];
$preview_popup=$row['preview'];
$path="<img src=\"images/$preview_popup\">";

// Show Results
echo "<tr>";
echo "<td>" . $row['tut_writer'] . "</td>";
echo "<td>" . "<a target=\"blank\" href=" . $row['tut_url'] . ">" . $row['tut_name'] . "</a>" . "</td>";

echo "<td>" . $row['tut_type'] . "</td>";
echo "<td>" . $row['art_name'] . "</td>";
echo "<td>" . $row['art_type'] . "</td>";
echo "<td>" . $row['art_comp'] . "</td>";
//echo "<td>" . "<a target=\"blank\" onmouseover=\"" . $tt->show($row['tut_notes']) . "\"href=\"# \">Notes </a>" . "</td>";

?>
<td> <a onmouseover="<?=$tt->show("$tut_notes")?>" href="#">Notes</a></td>
<td> <a onmouseover="<?=$tt->show("$path")?>" href="#">Preview</a></td>

<?php
echo "</tr>";
}
echo "</table>";

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM Links WHERE $search_type LIKE '%$search%'"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
echo "<center>$total_results Total Results<br />";
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&search=$search\">Previous</a>&nbsp;";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "[$i]&nbsp;";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&search=$search\">$i</a>&nbsp;";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&search=$search\">Next</a>";
}
echo "</center>";
}
?>

[intriguedone]

I forgot to add the error part in the first post.

The search works fine, but when you click to go to the next page I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /***/***/***/***/search.php on line 94

line 94 is this while($row = mysql_fetch_array($sql)){

and this error:
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /***/***/***/***/search.php on line 122

Line 122 is: $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM Links WHERE $search_type LIKE '%$search%'"),0);

My guess is that the vairable is being erased when refreshing the page to get the next results?

[jatar_k]

Welcome to WebmasterWorld intriguedone,

try breaking your wuery line up into a couple lines, this makes it much easier to see your query. You also need to add an or die statement to get the error from mysql as the error you are receiving just means your query returned an error

$q = "SELECT * FROM Links WHERE $search_type LIKE '%$search%' ORDER by id DESC LIMIT $from, $max_results ";
echo '<p>query is: ',$q;
$sql = mysql_query($q) or die ('<p>select died: ' . mysql_error());

try that and see what it returns

[intriguedone]

Ahhh.. yes thank you. I forgot the die statement
And thanks for the welcome. I have found alot of useful information on this fourm.

I know I have alot of cleaning up of code to do.

I actually figured out the problem. The next pages did not work because I did not put the variable into the pagination code.

By changing this....
echo "<a href=\"".$_SERVER['PHP_SELF']."?
page=$prev&search=$search\">Previous</a>&nbsp;";

To this...
echo "<a href=\"".$_SERVER['PHP_SELF']."?
page=$prev&search=$search&search_type=$search_type\">Previo
us</a>&nbsp;";

I figured this out by accident, I saw that the url's were not the same from the first result to the other pages.

[jatar_k]

nice work