Another Hair Tearer Outa!

blackdogupya · March 13, 2012

Hey Guys,

Struggling again with some full text searching etc.

I have a MySQL statement in my code but it's not working. Now, it's probably something extremely simple, but I just can't see it.

//Now we search for our search term, in the field the user specified  so we know how many pages to link to at the bottom
$data = mysql_query("SELECT * , 
MATCH ('status', 'date', 'species', 'breed', 'sex', 'primary_colour', 'colour', 'distinctive_traits', 'fur_length', 'age', 'desexed', 'microchipped', 'suburb', 'pound_area', 'contact', 'link')
AGAINST ('%$find%') AS score
FROM animal_info
WHERE MATCH ('status', 'date', 'species', 'breed', 'sex', 'primary_colour', 'colour', 'distinctive_traits', 'fur_length', 'age', 'desexed', 'microchipped', 'suburb', 'pound_area', 'contact', 'link')
AGAINST ('%$find%') $max");

//Make sure we have some results to pass

$result = mysql_query($data) or die("No Results Here");

//And we display the results of the search query as long as there is some
while($row = mysql_fetch_array($result))
{ 
//Build the HTML for the results in the table

Now, my second question is that I've made a quick template for this, but I'm not sure how to format the "or die()" statement so it looks pretty with the header, footer etc.

$result = mysql_query($data) or die("**SOME HTML FORMATTING IN HERE**");

Any help, once again, would be greatly appreciated!

Cheers,

Dave

scootstah · March 13, 2012

Putting mysql_error() in your die() will show any query errors, helping to pinpoint the error (if any) in the query.

Also, you really shouldn't use or die() on queries in a production environment. Ideally, you shouldn't use it at all, but especially not in production. All it's going to do is help hackers break your app, and not alert you of a problem in the slightest. So unless someone reports it, you'll never know if a query breaks.

litebearer · March 13, 2012

Also, it helps when debugging, if you create your queries as strings so you can echo them out to ensure they contain what you anticipate.

scootstah · March 13, 2012

Putting mysql_error in your die() will show any query errors, helping to pinpoint the error (if any) in the query.

Also, you really shouldn't use or die() on queries in a production environment. Ideally, you shouldn't use it at all, but especially not in production. All it's going to do is help hackers break your app, and not alert you of a problem in the slightest. So unless someone reports it, you'll never know if a query breaks.

EDIT: Whoops, thought that was the edit button. :/

blackdogupya · March 13, 2012

So this is the error I'm getting, both in the browser and in PHPMyAdmin:

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 ''status', 'date' , 'species', 'breed', 'sex', 'primary_colour', 'colour', 'dist' at line 2

Pikachu2000 · March 13, 2012

MySQL field names do not get enclosed in quotes.

blackdogupya · March 13, 2012

Okay, so I've come to the realisation that my entire code sucks!

Would anyone be willing to have a look over it and see where I'm going wrong? Understand if it's too hard, or you don't want to do it for free. Happy to chuck some $$ into a paypal account if you can help!

Here's the code:

<link href="css/style.css" rel="stylesheet" type="text/css" />
<!--
Copyright Crap Here!
-->
<?php

//Get the page number for later
$pagenum = $_GET['pagenum'];

//Here we display stuff if they have submitted the form
if ( $_GET['searching'] == 'yes' )
{ 
//If they stuffed up and didn't search for anything, we show them this 
if ($_GET['find'] == "") 
{ 
//Build the HTML display that will output the page when no search is entered
  	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"css/style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"../index.html\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo "<br />";
echo "<p>Oh, Bianca, you need to enter SOMETHING to search!"; 
  echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"web address here\" target=\"_blank\">Company Name here</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
exit; 
} 

//If everything is all good, we connect to the database
mysql_connect("localhost", "******", "******") or die(mysql_error()); 
mysql_select_db("******") or die(mysql_error()); 

//Let's not forget the register globals off crap
$status = $_GET['status'];
$date = $_GET['date'];
$species = $_GET['species'];
$breed = $_GET['breed'];
$sex = $_GET['sex'];
$primary_colour = $_GET['primary_colour'];
$colour = $_GET['colour'];
$distinctive_traits = $_GET['distinctive_traits'];
$fur_length = $_GET['fur_length'];
$age = $_GET['age'];
$desexed = $_GET['desexed'];
$microchipped = $_GET['microchipped'];
$suburb = $_GET['suburb'];
$pound_area = $_GET['pound_area'];
$contact = $_GET['contact'];
$link = $_GET['link'];
$columns = $_GET['columns'];
$find = $_GET['find'];
$searching = $_GET['searching'];


// We perform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Check to see if there's a page number, if not, set it to 1
if (!(isset($pagenum))) 

{ 

$pagenum = 1; 

} 

//Count the number of results based on the search term

$numresults = mysql_query("SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%')") or die(mysql_error()); 

$rows = mysql_num_rows($numresults);

//How many results should be per page?

$page_rows = 1;

//What is the last page number?
$last = ceil($rows/$page_rows); 

//Make sure the page number isn't a negative number or more than the maximum page required
if ($pagenum < 1) 

{ 

$pagenum = 1; 

} 

elseif ($pagenum > $last) 

{ 

$pagenum = $last; 

} 

//Set the range to display in the query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 



//Now we search for our search term, in the field the user specified  so we know how many pages to link to at the bottom
$data = mysql_query("SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') $max");

//Make sure we have some results to pass

$result = mysql_query($data);

//And we display the results of the search query as long as there is some
while($row = mysql_fetch_array($result))
{ 
//Build the HTML for the results in the table
	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"../style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"../favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"../index.html\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo " --Page $pagenum of $last-- <p>";
echo "<br />";
//Now we actually build the table
echo "<table width=\"100%\" border=\"0\" cellspacing=\"5\" cellpadding=\"2\">";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Status</th>";
echo "<td>".$row['status']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Date</th>";
echo "<td>".$row['date']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Species</th>";
echo "<td>".$row['species']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Breed</th>";
echo "<td>".$row['breed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Sex</th>";
echo "<td>".$row['sex']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Primary Colour</th>";
echo "<td>".$row['primary_colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Colour</th>";
echo "<td>".$row['Colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Distinctive Traits</th>";
echo "<td>".$row['distinctive_traits']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Fur Length</th>";
echo "<td>".$row['fur_length']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Age</th>";
echo "<td>".$row['age']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Desexed?</th>";
echo "<td>".$row['desexed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Microchipped?</th>";
echo "<td>".$row['microchipped']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Suburb</th>";
echo "<td>".$row['suburb']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Pound Area</th>";
echo "<td>".$row['pound_area']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Contact</th>";
echo "<td>".$row['contact']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Link</th>";
echo "<td><a href='".$row['link']."' target=_blank>".$row['link']."</a></td>";
echo "</tr>";
echo "</table>";
 echo "<BR><BR>";
//Build the navigation
if ($pagenum == 1) 

{

} 

else 

{

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

echo " ";

$previous = $pagenum-1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

} 


//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

if ($pagenum == $last) 

{

} 

else {

$next = $pagenum+1;

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$next&find=$find&columns=$columns&searching=$searching'>Next -></a> ";

echo " ";

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$last&find=$find&columns=$columns&searching=$searching'>Last ->></a> ";

} 
echo "<br><BR>";	
//And we remind them what they searched for 
echo "<b>You searched for:</b> " .$find; 
  echo "</div>";
//Build the footer part of the page
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"Web Address Here\" target=\"_blank\">Company Name Here</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";

} 


} 

//If there wasn't any results, we say thanks for searching, but we couldn't find anything that was worth showing and ask them to try again
if ($numresults == 0) 
{ 
//Once again, build the HTML output of the page for the "No Search Results" page
  	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"../style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"../favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"../index.html\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo "<br />";
echo "Nope, couldn't find anything here!  Maybe refine your search criteria?<br><br>"; 
echo "</div>";
//Build the footer part of the page again
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"Web Address Here\" target=\"_blank\">Company Name Here</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";

}
//Okay, we're done here.  Finish all the pages with the HTML closing tags that we need to end the page
echo "</body>";
echo "</html>";
?>

Basically, I have one box that the user is typing a search string into, for example "lost dog perth" and I'm trying to format the results into a table of one record per page, with the most accurate results first.

Should be simple right? Well, I'm buggered if I can get it working!

If anyone can help, I'd be forever in your debt! Beers are on me!

Cheers,

Dave

Drummin · March 13, 2012

I had never seen this style of coding before so I looked it up and made a simple example. What I found was all fields need to type TEXT and you need to make them FULLTEXT with an index name. Here's an example.

mysql_query("ALTER TABLE `animal_info` ADD FULLTEXT `search_index` ('status', 'date', 'species', 'breed', 'sex', 'primary_colour', 'colour', 'distinctive_traits', 'fur_length', 'age', 'desexed', 'microchipped', 'suburb', 'pound_area', 'contact', 'link')");

As I'd hate to mess anything up you've got going, so look this up on the internet or wait for the big boys to respond, but this is what I found in a few minutes and setting up a sample DB table in this fashion worked fine for me.

blackdogupya · March 13, 2012

Thanks Drummin,

I already had a FULLTEXT search index. Removing the '' allowed me to successfully run the query agains the DB.

It's just my shitty code that's stopping the rest!

Cheers,

Dave

Drummin · March 13, 2012

Curious how much data you have in your table, and if you can search for something unique where results should be say one out of ten records. Found that if results are over 50% it returns no results found.

Just a glance at your code, I don't spot where find is defined as a GET value. i.e no form. I see you have it in your paging but no starting point for the value $_GET['find'].

blackdogupya · March 14, 2012

Not sure where you're seeing this part:

Curious how much data you have in your table, and if you can search for something unique where results should be say one out of ten records. Found that if results are over 50% it returns no results found.

This code is the processing script to the form. The form itself parses information to this script.

Just a glance at your code, I don't spot where find is defined as a GET value. i.e no form. I see you have it in your paging but no starting point for the value $_GET['find'].

Thanks for the observations so far!

Cheers,

Dave

Drummin · March 14, 2012

Not sure where you're seeing this part:

Curious how much data you have in your table, and if you can search for something unique where results should be say one out of ten records. Found that if results are over 50% it returns no results found.

This code is the processing script to the form. The form itself parses information to this script.

Well I had read (when searching for this type of code processing) that if results are higher than 50% of total records queried, the result will be "No records found". I also found this true when testing on my test form. That's why I mentioned it.

I haven't had time to look into this more.

I would suggest you take just the query section (and DB connection) and run some stand-alone tests to get that working. At least this way you can rule out any "page" issues.

blackdogupya · March 14, 2012

Not sure where you're seeing this part:

Curious how much data you have in your table, and if you can search for something unique where results should be say one out of ten records. Found that if results are over 50% it returns no results found.

This code is the processing script to the form. The form itself parses information to this script.

Well I had read (when searching for this type of code processing) that if results are higher than 50% of total records queried, the result will be "No records found". I also found this true when testing on my test form. That's why I mentioned it.

I haven't had time to look into this more.

I would suggest you take just the query section (and DB connection) and run some stand-alone tests to get that working. At least this way you can rule out any "page" issues.

Okay, so the DB connection is fine, and the query works with PHPMyAdmin and returns the results as expected.

The problem I'm getting now is:

Warning: mysql_query() expects parameter 1 to be string, resource given in /home/loveani/public_html/poplaf/process_search.php on line 206

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /home/loveani/public_html/poplaf/process_search.php on line 209

Muddy_Funster · March 14, 2012

lets see your updated code please.

blackdogupya · March 14, 2012

lets see your updated code please.

Okay, I'm getting the search results that I want now! FINALLY! However, I seem to be having an issue with entering in something that DOESN'T appear in the database. SO if I search for "Homer Simpson" (which I know isn't in the DB) then I receive the following:

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 '-1,1' at line 1

Here's my full code again:

<!--
Blah!
-->
<?php

//Get the page number for later
$pagenum = $_GET['pagenum'];

//Here we display stuff if they have submitted the form
if ( $_GET['searching'] == 'yes' )
{ 
//If they stuffed up and didn't search for anything, we show them this 
if ($_GET['find'] == "") 
{ 
//Build the HTML display that will output the page when no search is entered
  	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"css/style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"../index.html\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo "<br />";
echo "<p>Oh, Bianca, you need to enter SOMETHING to search!"; 
  echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"link\" target=\"_blank\">Company</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
exit; 
} 

//If everything is all good, we connect to the database
mysql_connect("localhost", "******", "******") or die(mysql_error()); 
mysql_select_db("******") or die(mysql_error()); 

//Let's not forget the register globals off crap
$status = $_GET['status'];
$date = $_GET['date'];
$species = $_GET['species'];
$breed = $_GET['breed'];
$sex = $_GET['sex'];
$primary_colour = $_GET['primary_colour'];
$colour = $_GET['colour'];
$distinctive_traits = $_GET['distinctive_traits'];
$fur_length = $_GET['fur_length'];
$age = $_GET['age'];
$desexed = $_GET['desexed'];
$microchipped = $_GET['microchipped'];
$suburb = $_GET['suburb'];
$pound_area = $_GET['pound_area'];
$contact = $_GET['contact'];
$link = $_GET['link'];
$columns = $_GET['columns'];
$find = $_GET['find'];
$searching = $_GET['searching'];


// We perform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Check to see if there's a page number, if not, set it to 1
if (!(isset($pagenum))) 

{ 

$pagenum = 1; 

} 

//Count the number of results based on the search term

$numresultsSQL = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%')" or die(mysql_error()); 

$numresults = mysql_query($numresultsSQL);

$rows = mysql_num_rows($numresults);

//How many results should be per page?

$page_rows = 1;

//What is the last page number?
$last = ceil($rows/$page_rows); 

//Make sure the page number isn't a negative number or more than the maximum page required
if ($pagenum < 1) 

{ 

$pagenum = 1; 

} 

elseif ($pagenum > $last) 

{ 

$pagenum = $last; 

} 

//Set the range to display in the query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 



//Now we search for our search term, in the field the user specified  so we know how many pages to link to at the bottom
$data = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') $max" or die(mysql_error());

$results = mysql_query($data) or die(mysql_error());
//edited here

  if (mysql_num_rows($results) < 1) {
echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"css/style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"index.php\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo " --Page $pagenum of $last-- <p>";
echo "<br />";
echo "<p>Couldn't find anything matching your search terms.  Please try again"; 
  echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"link\" target=\"_blank\">Company</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
  }

      elseif(MySQL_num_rows($results) > 0) {


//And we display the results of the search query as long as there is some
while($row = mysql_fetch_array($results))
{ 
//Build the HTML for the results in the table
	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"css/style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"index.php\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo " --Page $pagenum of $last-- <p>";
echo "<br />";
//Now we actually build the table
echo "<table width=\"100%\" border=\"0\" cellspacing=\"5\" cellpadding=\"2\">";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Status</th>";
echo "<td>".$row['status']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Date</th>";
echo "<td>".$row['date']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Species</th>";
echo "<td>".$row['species']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Breed</th>";
echo "<td>".$row['breed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Sex</th>";
echo "<td>".$row['sex']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Primary Colour</th>";
echo "<td>".$row['primary_colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Colour</th>";
echo "<td>".$row['Colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Distinctive Traits</th>";
echo "<td>".$row['distinctive_traits']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Fur Length</th>";
echo "<td>".$row['fur_length']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Age</th>";
echo "<td>".$row['age']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Desexed?</th>";
echo "<td>".$row['desexed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Microchipped?</th>";
echo "<td>".$row['microchipped']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Suburb</th>";
echo "<td>".$row['suburb']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Pound Area</th>";
echo "<td>".$row['pound_area']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Contact</th>";
echo "<td>".$row['contact']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Link</th>";
echo "<td><a href='".$row['link']."' target=_blank>".$row['link']."</a></td>";
echo "</tr>";
echo "</table>";
 echo "<BR><BR>";
//Build the navigation
if ($pagenum == 1) 

{

} 

else 

{

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

echo " ";

$previous = $pagenum-1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

} 


//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

if ($pagenum == $last) 

{

} 

else {

$next = $pagenum+1;

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$next&find=$find&columns=$columns&searching=$searching'>Next -></a> ";

echo " ";

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$last&find=$find&columns=$columns&searching=$searching'>Last ->></a> ";

} 
echo "<br><BR>";	
//And we remind them what they searched for 
echo "<b>You searched for:</b> " .$find; 
  echo "</div>";
//Build the footer part of the page
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"link\" target=\"_blank\">Company</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";

} 


} 

//If there wasn't any results, we say thanks for searching, but we couldn't find anything that was worth showing and ask them to try again
if ($numresults == 0) 
{ 
//Once again, build the HTML output of the page for the "No Search Results" page
  	echo "<html>";
echo "<body>";
echo "<title>Search for an Animal</title>";
echo "<link rel=\"stylesheet\" href=\"css/style.css\" type=\"text/css\" media=\"screen\" />";
echo "<link rel=\"shortcut icon\" href=\"favicon.ico\" type=\"image/x-icon\" />";
echo "</head>";
echo "<body>";
echo "<div id=\"art-page-background-glare-wrapper\">";
echo "<div id=\"art-page-background-glare\">";
echo "</div>";
echo "</div>";
echo "<div id=\"art-main\">";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-box art-sheet\">";
echo "<div class=\"art-box-body art-sheet-body\">";
echo "<div class=\"art-header\">";
echo "<div class=\"art-headerobject\">";
echo "</div>";
echo "<div class=\"art-logo\">";
echo "<h1 class=\"art-logo-name\">";
echo "<a href=\"index.php\">Pets of Perth</a>";
echo "</h1>";
echo "<h2 class=\"art-logo-text\">Lost and Found</h2></div>";
echo "</div>";
echo "<div class=\"cleared reset-box\">";
echo "</div>";
echo "<div class=\"art-layout-wrapper\">";
echo "<div class=\"art-content-layout\">";
echo "<div class=\"art-content-layout-row\">";
echo "<div class=\"art-layout-cell art-sidebar1\">";
echo "<div class=\"art-box art-vmenublock\">";
echo "<div class=\"art-box-body art-vmenublock-body\">";
echo "<div class=\"art-bar art-vmenublockheader\">";
echo "<h3 class=\"t\">Main Menu</h3>";
echo "</div>";
echo "<div class=\"art-box art-vmenublockcontent\">";
echo "<div class=\"art-box-body art-vmenublockcontent-body\">";
echo "<ul class=\"art-vmenu\">";
echo "<li>";
echo "<a href=\"index.php\" class=\"\">Welcome!</a>";
echo "</li>";
echo "<li><a href=\"add.php\">Add an Animal</a></li>";
echo "<li> <a href=\"search.php\" class=\"active\">Search for an Animal</a>";
echo "</li>";
echo "</ul>";
echo "</li>";	
echo "</ul>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"art-layout-cell art-content\">";
echo "<div class=\"art-box art-post\">";
echo "<div class=\"art-box-body art-post-body\">";
echo "<div class=\"art-post-inner art-article\">";
echo "<h2 class=\"art-postheader\">Results</h2>";
echo "<div class=\"art-postcontent\">";
echo "<br />";
echo "<br />";
echo "Nope, couldn't find anything here!  Maybe refine your search criteria?<br><br>"; 
echo "</div>";
//Build the footer part of the page again
echo "<div class=\"cleared\">";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\">";
echo "</div>";
echo "<div class=\"art-footer\">";
echo "<div class=\"art-footer-body\">";
echo "<a href=\"#\" class=\"art-rss-tag-icon\" title=\"RSS\"></a>";
echo "<div class=\"art-footer-text\">";
echo "<p>Copyright © 2012 Pets of Perth, Lost and Found. All Rights Reserved.</p>";
echo "<p>Website by <a href=\"link\" target=\"_blank\">Company</a>";
echo "</p>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"cleared\"></div>";
echo "<p class=\"art-page-footer\"></p>";
echo "<div class=\"cleared\"></div>";
echo "</div>";
}
}
//Okay, we're done here.  Finish all the pages with the HTML closing tags that we need to end the page
echo "</body>";
echo "</html>";

?>

Thanks!

Dave

Muddy_Funster · March 14, 2012

ok, your or die should be on the mysql_query() line, not the one that builds your SQL statement. if you could also output the SQL query in the or die that would help too:

$numresults = mysql_query($numresultsSQL) or die (mysql_error()."<br><br>Encountered when atemptempting to run the following query:<br>$numresultsSQL");

Let me know what comes back from that.

cpd · March 14, 2012

Without knowing which line is erroring I'm going to hazard a guess from your previous posts as to where the error is occuring.

$data = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') $max" or die(mysql_error());

Judging by your query and the error you've received I believe its coming from $max variable which suggests, and I'll once again assume, your trying to make a LIMIT, except your missing the word LIMIT . Furthermore, you must have some form of error when working the limit out because your trying to get rows -1 to 1 which doesn't make sense. Review your code which assigns the $max variable as well as adding the LIMIT syntax.

I arrived at this conclusion because you receive an error from mysql_fetch_array which is usually generated when no resource ID is returned from a query. I then scanned through your code to find a single mysql_fetch_array statement using the $results variable as it's parameter. The $results variable was set using the above query hence why I believe this is the query causing the error.

I have however, made two assumptions and only scanned through the code; I could be wrong... only way to find out is do what I've recommended and test it

And a little tip

$thisWillAvoidLoadsOfEchoStatements = <<<HTMLBLOCK

<html>

<body>

This is a paragraph.

</body>

</html>

HTMLBLOCK;

cpd · March 14, 2012

Sorry, made a mistake and can't edit anymore

$thisWillAvoidLoadsOfEchoStatements = <<<HTMLBLOCK
<html>
<body>

<p>This is a paragraph.</p>
<p>This is a paragraph.</p>
<p>This is a paragraph.</p>

</body>
</html>
HTMLBLOCK;

blackdogupya · March 15, 2012

Without knowing which line is erroring I'm going to hazard a guess from your previous posts as to where the error is occuring.
$data = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') $max" or die(mysql_error());
Judging by your query and the error you've received I believe its coming from $max variable which suggests, and I'll once again assume, your trying to make a LIMIT, except your missing the word LIMIT . Furthermore, you must have some form of error when working the limit out because your trying to get rows -1 to 1 which doesn't make sense. Review your code which assigns the $max variable as well as adding the LIMIT syntax.

I arrived at this conclusion because you receive an error from mysql_fetch_array which is usually generated when no resource ID is returned from a query. I then scanned through your code to find a single mysql_fetch_array statement using the $results variable as it's parameter. The $results variable was set using the above query hence why I believe this is the query causing the error.

I have however, made two assumptions and only scanned through the code; I could be wrong... only way to find out is do what I've recommended and test it

And a little tip
$thisWillAvoidLoadsOfEchoStatements = <<<HTMLBLOCK
<html>
<body>

This is a paragraph.
This is a paragraph.
This is a paragraph.

</body>
</html>
HTMLBLOCK;
[/quote]

Hey CPD! 

Thanks for your observations!  I didn't know about the HTMLBLOCK in a variable!  That's really great!  Thanks!

For your other observation, I have this defined as $max for the end of the SQL statement:

[code]
//Set the range to display in the query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 
Wouldn't that apply the limit tag to the end of the sql query?

I also define $pagenum and $page_rows here:
//How many results should be per page?

$page_rows = 1;

//What is the last page number?
$last = ceil($rows/$page_rows); 

//Make sure the page number isn't a negative number or more than the maximum page required
if ($pagenum < 1) 

{ 

$pagenum = 1; 

} 

elseif ($pagenum > $last) 

{ 

$pagenum = $last; 

} 
I thought that this would help with the navigation with each record, putting NEXT, PREVIOUS, LAST and FIRST as links at the bottom. Well, that's the reasoning behind it.

Cheers,

Dave

blackdogupya · March 15, 2012

Okay, so I've updated my code to implement the $ variables with the HTMLBLOCK stuff.

Here's my new code, but it's still giving me the same error:

<?php

require("format.php");

//Get the page number for later
$pagenum = $_GET['pagenum'];

//Here we display stuff if they have submitted the form
if ( $_GET['searching'] == 'yes' )
{ 
//If they stuffed up and didn't search for anything, we show them this 
if ($_GET['find'] == "") 
{ 
//Build the HTML display that will output the page when no search is entered
echo "$noSearch";

} 

//If everything is all good, we connect to the database
mysql_connect("localhost", "******", "******") or die(mysql_error()); 
mysql_select_db("******") or die(mysql_error()); 

//Let's not forget the register globals off crap
$status = $_GET['status'];
$date = $_GET['date'];
$species = $_GET['species'];
$breed = $_GET['breed'];
$sex = $_GET['sex'];
$primary_colour = $_GET['primary_colour'];
$colour = $_GET['colour'];
$distinctive_traits = $_GET['distinctive_traits'];
$fur_length = $_GET['fur_length'];
$age = $_GET['age'];
$desexed = $_GET['desexed'];
$microchipped = $_GET['microchipped'];
$suburb = $_GET['suburb'];
$pound_area = $_GET['pound_area'];
$contact = $_GET['contact'];
$link = $_GET['link'];
$columns = $_GET['columns'];
$find = $_GET['find'];
$searching = $_GET['searching'];


// We perform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Check to see if there's a page number, if not, set it to 1
if (!(isset($pagenum))) 

{ 

$pagenum = 1; 

} 

//Count the number of results based on the search term

$numresultsSQL = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%')" or die(mysql_error()); 

$numresults = mysql_query($numresultsSQL);

$rows = mysql_num_rows($numresults);

//How many results should be per page?

$page_rows = 1;

//What is the last page number?
$last = ceil($rows/$page_rows); 

//Make sure the page number isn't a negative number or more than the maximum page required
if ($pagenum < 1) 

{ 

$pagenum = 1; 

} 

elseif ($pagenum > $last) 

{ 

$pagenum = $last; 

} 

//Set the range to display in the query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 



//Now we search for our search term, in the field the user specified  so we know how many pages to link to at the bottom
$data = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') $max" or die(mysql_error());

$results = mysql_query($data) or die(mysql_error());
//edited here

  if (mysql_num_rows($results) < 1) {
echo "$noResults";
  }

      elseif(MySQL_num_rows($results) > 0) {


//And we display the results of the search query as long as there is some
while($row = mysql_fetch_array($results))
{ 
//Build the HTML for the results in the table
echo "$resultsHTMLabove";
echo " --Page $pagenum of $last-- <p>";
echo "<br><BR>";
//Here's the table
echo "<table width=\"100%\" border=\"0\" cellspacing=\"5\" cellpadding=\"2\">";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Status</th>";
echo "<td>".$row['status']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Date</th>";
echo "<td>".$row['date']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Species</th>";
echo "<td>".$row['species']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Breed</th>";
echo "<td>".$row['breed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Sex</th>";
echo "<td>".$row['sex']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Primary Colour</th>";
echo "<td>".$row['primary_colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Colour</th>";
echo "<td>".$row['Colour']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Distinctive Traits</th>";
echo "<td>".$row['distinctive_traits']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Fur Length</th>";
echo "<td>".$row['fur_length']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Age</th>";
echo "<td>".$row['age']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Desexed?</th>";
echo "<td>".$row['desexed']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Microchipped?</th>";
echo "<td>".$row['microchipped']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Suburb</th>";
echo "<td>".$row['suburb']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Pound Area</th>";
echo "<td>".$row['pound_area']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Contact</th>";
echo "<td>".$row['contact']."</td>";
echo "</tr>";
echo "<tr>";
echo "<th scope=\"row\" align=\"left\">Link</th>";
echo "<td><a href='".$row['link']."' target=_blank>".$row['link']."</a></td>";
echo "</tr>";
echo "</table>";
 echo "<BR><BR>";
//Build the navigation
if ($pagenum == 1) 

{

} 

else 

{

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

echo " ";

$previous = $pagenum-1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

} 


//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

if ($pagenum == $last) 

{

} 

else {

$next = $pagenum+1;

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$next&find=$find&columns=$columns&searching=$searching'>Next -></a> ";

echo " ";

echo " <a href='{$_GET['PHP_SELF']}?pagenum=$last&find=$find&columns=$columns&searching=$searching'>Last ->></a> ";

} 
echo "<br><BR>";	
//And we remind them what they searched for 
echo "<b>You searched for:</b> " .$find; 
  echo "</div>";
//Build the footer part of the page
echo "$resultsHTMLbelow";

} 

//If there wasn't any results, we say thanks for searching, but we couldn't find anything that was worth showing and ask them to try again
if ($numresults == 0) 
{ 
//Once again, build the HTML output of the page for the "No Search Results" page
echo "$noResultsSearch";
}
  }
}
?>

I tried this:

//Now we search for our search term, in the field the user specified  so we know how many pages to link to at the bottom
$data = "SELECT *, MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST ('%$find%') AS score FROM animal_info WHERE MATCH(status, date, species, breed, sex, primary_colour, colour, distinctive_traits, fur_length, age, desexed, microchipped, suburb, pound_area, contact, link) AGAINST('%$find%') [b]LIMIT[/b] $max" or die(mysql_error());

But it gave me this 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 'limit 0,1' at line 1

Taking out the LIMIT statement, the search results work again. But if I enter something that's not in the DB, I get this error again:

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 '-1,1' at line 1

Cheers,

Dave

Muddy_Funster · March 15, 2012

ok, as you chose to completly ignore my last post, im done with this thread good luck.

blackdogupya · March 15, 2012

ok, as you chose to completly ignore my last post, im done with this thread good luck.

Now that you're done with your little hissy fit, I apologise, I thought I had responded!

I changed that line as suggested, and I get THIS:

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 '-1,1' at line 1

So, was none the wiser!

Cheers,

Dave

cpd · March 15, 2012

You definitely needed the limit statement. A typical example of the syntax would be:

"SELECT * FROM tblName ORDER BY id ASC LIMIT 0,30"

So you need to add the limit statement back into your query. I would personally include it in the main part of your query making it easier to read, for me anyway.

Like I previously said, you need to review your code which sets the $max variable however, I'm not entirely convinced this is where the error is. Usually the errornisnjust before whatever the SQL server spits out at you so perhaps they an error with your BETWEEN statement.

Can we also try to keep our toys in our own prams please.

Muddy_Funster · March 15, 2012

ok, as you chose to completly ignore my last post, im done with this thread good luck.

Now that you're done with your little hissy fit, I apologise, I thought I had responded!

I changed that line as suggested, and I get THIS:
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 '-1,1' at line 1
So, was none the wiser!

Cheers,

Dave

lol that's not me having a hissy fit, that's just fact. and from your error mesage there, and from the code revision you posted just before that, you still havn't made the change or you would have the full query at the end of your error exactly how it is being sent to the server - thus helping everyone get a better understanding of what exactly is causing the problem.

So seriously, if you go somewhere and ask for help, then ignore that help, how should people react?

blackdogupya · March 16, 2012

You definitely needed the limit statement. A typical example of the syntax would be:

"SELECT * FROM tblName ORDER BY id ASC LIMIT 0,30"

So you need to add the limit statement back into your query. I would personally include it in the main part of your query making it easier to read, for me anyway.

Like I previously said, you need to review your code which sets the $max variable however, I'm not entirely convinced this is where the error is. Usually the errornisnjust before whatever the SQL server spits out at you so perhaps they an error with your BETWEEN statement.

Can we also try to keep our toys in our own prams please.

We have a winner!

It was the limit in the $max variable that was doing it. I declared the limit in the statement as 0,1 and it's working perfectly!

Muddy_Funster, thank you for your help, there really was no malice intended with "ignoring" you. It was an honest mistake.

CPD, thanks heaps for your help with this also.

I really do appreciate the help that I'm provided in this forum and it certainly isn't my intention to piss people off.

Again, my sincere apologies if I did that!

Cheers,

Dave

Sign In

Another Hair Tearer Outa!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information