PHP/mySQL Search Script

[TeenBite]

On my website (in progress), I have a search script, which allows you to search like on www.deviantart.com (lots of different options can be searched at once). However, I have just thrown together 115 lines of rubbish for my search script, and whilst it works in most areas, I know that there must be a easier way to process a form. I am quite good at PHP (this search script is about the last thing I have to do in a Content Management System I've made) and am simply looking for a PHP script that gets results from a mySQL database depending on input from more than one form field.

For example, you can enter a keyword (where like), choose how many results you want on a page (limit), how to display them (order by), what category to view results from (where), what author to view from (where). I want the visitor to be able to use as many or as little of these search areas as they want.
<?php
$limit = $_POST ['limit'];
$order = $_POST ['order'];
$category = $_POST ['category'];
$author = $_POST ['author'];
$textsearch = $_POST ['textsearch'];

if(isset($textsearch)){
$string= "title LIKE '%$textsearch%'";}

if($category != All){
$string1="category='$category'";}

if($author != All){
$string2="author='$author'";}

if($order == A){
$string3="ORDER BY title";}

if($order == Z){
$string3="ORDER BY title ASC";}

if($order == Plays){
$string3="ORDER BY hits DESC";}

if($order == Rating){
$string3="ORDER BY score DESC";}

if(isset($string2) && ($string1) && ($string)){
$string="WHERE title LIKE '%$textsearch%' ";
$string1="AND category='$category' ";
$string2="AND author='$author'";}

if(isset($string) && ($string1) && (!$string2)){
$string="WHERE title LIKE '%$textsearch%' ";
$string1="AND category='$category'";}

if(isset($string) && ($string2) && (!$string1)){
$string="WHERE title LIKE '%$textsearch%' ";
$string2="AND author='$author'";}

if(isset($string2) && ($string1) && (!$string)){
$string1="WHERE category='$category' ";
$string2="AND author='$author'";}

if(isset($string) && (!$string1) && (!$string2)){
$string="WHERE title LIKE '%$textsearch%'";}

if(isset($string1) && (!$string) && (!$string2)){
$string1="WHERE category='$category'";}

if(isset($string2) && (!$string1) && (!$string)){
$string1="WHERE author='$author'";}

if($category == All){
$string1="";}

if($author == All){
$string2="";}

$query_count = "SELECT count(*) FROM games $string $string1 $string2 $string3";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

if(empty($page)){
$page = 1;
}

$limitvalue = $page * $limit - ($limit);
$query = "SELECT * FROM games $string $string1 $string2 $string3 LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0){
echo("<p>No games found!</p>");
}

while($r = mysql_fetch_array($result)){
include("function.php");
echo("<br>");
}


if($page != 1){
$pageprev = $page--;

echo("<a href=\"?Page==Search&page=$pageprev\">&lt;&lt;Previous</a>&nbsp;");
}else{
echo("&lt;&lt;Previous &nbsp;");
}

$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i."&nbsp;");
}else{
echo("<a href=\"?Page==Search&page=$i\">$i</a>&nbsp;");
}
}


if(($totalrows % $limit) != 0){
if($i == $page){
echo($i."&nbsp;");
}else{
echo("<a href=\"?Page==Search&page=$i\">$i</a>&nbsp;");
}
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page++;

echo(" <a href=\"?Page=Search&page=$pagenext\">Next&gt;&gt;</a>");
}else{
echo(" Next&gt;&gt;");
}

mysql_free_result($result);

?>That is what I have, but there has to be a much simpler way than going through each possibility individually?

Of course, there is a working database connected to this file etc.

[dumpfi]

<?php
$string = '';
$text_search = FALSE;
$category_search = FALSE;
if(isset($_POST['textsearch'])) {
$string .= ' WHERE title LIKE "%'.$_POST['textsearch'].'%"';
$text_search = TRUE;
}
if(isset($_POST['category']) && $_POST['category'] != 'All') {
$string .= ($text_search) ? ' AND' : ' WHERE';
$string .= ' category = "'.$_POST['category'].'"';
$category_search = TRUE;
}
if(isset($_POST['author']) && $_POST['author'] != 'All') {
$string .= ($text_search || $category_search) ? ' AND' : ' WHERE';
$string .= ' author = "'.$_POST['author'].'"';
}
if(isset($_POST['order'])) {
switch($_POST['order']) {
case 'A': $string .= ' ORDER BY title'; break;
case 'Z': $string .= ' ORDER BY title DESC'; break;
case 'Plays': $string .= ' ORDER BY hits DESC'; break;
case 'Rating': $string .= ' ORDER BY score DESC'; break;
}
}
$page = (isset($_GET['page']) && intval($_GET['page'])) ? $_GET['page'] : 1;
$limitvalue = $page * $limit - $limit;
$result = mysql_query('SELECT * FROM games'.$string.' LIMIT '.$limitvalue.', '.$limit) or die('Error: '.mysql_error());
if(!mysql_num_rows($result)) echo '<p>No games found!</p>';
else {
while($r = mysql_fetch_array($result)) {
include('function.php'); // why to hell are you including function.php for every row returned?
echo '<br>';
}
echo ($page > 1) ? '<a href="?Page=Search&page='.($page - 1).'">&lt;&lt;Previous</a>&nbsp;' : '&lt;&lt;Previous &nbsp;';
$result_count = mysql_query('SELECT COUNT(*) FROM games'.$string) or die('Query problem: '.mysql_error());
$totalrows = mysql_num_rows($result_count);
$numofpages = ceil($totalrows / $limit);
for($i = 1; $i < $numofpages + 1; $i++) {
echo ($page == $i) ? $i.'&nbsp;' : '<a href="?Page=Search&page='.$i.'">'.$i.'</a>&nbsp;';
}
echo ($totalrows - $limit * $page > 0) ? ' <a href="?Page=Search&page='.($page + 1).'">Next&gt;&gt;</a>' : ' Next&gt;&gt;';
}
?>For being quite good at php your code is quite bad. ;)

dumpfi

[TeenBite]

Thankyou so much!

I have been doing it for a year, and have made a content management system, but I'm only 14, so therefore not able to grasp some of the harder aspects (they only start teaching it at 2nd year university - I'm 5 years away from that!).

[TeenBite]

It doesn't like the SQL:

Error: 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 "%wal%" ORDER BY title LIMIT 0, 10' at line 1

Where wal is what I searched for. I've tried several different combinations of searches, all give that error message.

[dumpfi]

Fixed the problem (hopefully :D) and edited my post above with the changes.

dumpfi

[TeenBite]

You, my friend, are the most helpful person I have ever met on an internet forum. Thankyou very very much.

[raf]

You, my friend, are the most helpful person I have ever met on an internet forum. Thankyou very very much.
Words are cheap :D Put your vote in if you realy mean it http://www.codingforums.com/showthread.php?t=42008 ;)