blackdogupya Posted March 13, 2012 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
scootstah Posted March 13, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 13, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
scootstah Posted March 13, 2012 Share Posted 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. :/ Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 13, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 13, 2012 Share Posted March 13, 2012 MySQL field names do not get enclosed in quotes. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 13, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Drummin Posted March 13, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 13, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Drummin Posted March 13, 2012 Share Posted 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']. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 14, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Drummin Posted March 14, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 14, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 14, 2012 Share Posted March 14, 2012 lets see your updated code please. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 14, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 14, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
cpd Posted March 14, 2012 Share Posted 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> <p>This is a paragraph.</p> <p>This is a paragraph.</p> <p>This is a paragraph.</p> </body> </html> HTMLBLOCK; Quote Link to comment Share on other sites More sharing options...
cpd Posted March 14, 2012 Share Posted 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; Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 15, 2012 Author Share Posted 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> <p>This is a paragraph.</p> <p>This is a paragraph.</p> <p>This is a paragraph.</p> </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 Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 15, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 15, 2012 Share Posted March 15, 2012 ok, as you chose to completly ignore my last post, im done with this thread good luck. Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 15, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
cpd Posted March 15, 2012 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 15, 2012 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
blackdogupya Posted March 16, 2012 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.