Alexhoward Posted March 19, 2008 Share Posted March 19, 2008 Hi, I have created a column of keywords next to the data people will be searching for. Then i am using the LIKE function. SELECT * FROM * WHERE * LIKE * Say you where searching for "car" If the keywords column only has one word in it, it works fine e.g. car but if it has more than one, it doesn't bring back anything e.g. car, van or car van Is this a common problem...? Cheers Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 19, 2008 Share Posted March 19, 2008 lol do you have any code? are you sure your query is in correct syntax? try echoing the exact problem query just before calling mysql_query. --- also just by guessing atm it could be... are you using % symbols? (wildcards), these are used in mysql to show you want to match "anything", this keyword row: "car,truck,bike,airplane", with this like query: "LIKE '%truck%'", should work. whereas this query: "LIKE 'truck'", would not. also just fyi this query: "LIKE 'car%'", would also work as well as: "LIKE '%airplane'" hope this helps, Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 19, 2008 Author Share Posted March 19, 2008 Nice one, thanks mate! it was the % i was missing as i was just testing the script i was inputting the search criteria manually into the code it's still got a long way to go, so i'll give you a shout back when i've got a little further Thanks again! Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 19, 2008 Author Share Posted March 19, 2008 hi the code i'm using uses $PHP_SELF? is this out of date with new php scripts...? also, say i have the key words 1) car van bus - in one cell 2) and car - in another (for example) if you search - "car van" It will only bring back (1), although (2) also includes the keyword car....? Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 19, 2008 Share Posted March 19, 2008 you need to split keywords, then build a where query with the array. Use $_SERVER['SCRIPT_NAME'] instead of $PHP_SELF; something similar to: <?php $Keywords = "car van"; // Split into array of keywords $kw = @explode(" ",$Keywords); // Get number of keywords $x = (!is_array($kw))? "1" : count($kw); // Loop for each keyword adding a where query each time. For($i=0;$i<$x;$i++){ // the current keyword (made safe from mysql_injection), depending on wether the explode() worked or not. $key = ($x==1)? mysql_escape_string($Keywords) : mysql_escape_string($kw[$i]); // Add the where item as an appended array. $where[] = "`name` LIKE '%$key%' OR `category` LIKE '%$key%'"; } // Get full where query using the array created $where = "WHERE ".implode(" OR ",$where); // Make the Query. $Query = "SELECT * FROM `table` $where"; ?> i hope this helps; Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 20, 2008 Author Share Posted March 20, 2008 Thanks mate! but... i'm not sure how to put that into my code....? <?php include ("banner.php") ?> <html> <body> <form name="form" action="searchscript.php" method="get"> <input type="text" name="q" /> <input type="submit" name="Submit" value="Search" /> </form> <?php // Get the search variable from URL $var = @$_GET['q'] ; $trimmed = trim($var); //trim whitespace from the stored variable // rows to return $limit=10; // check for an empty string and display a message. if ($trimmed == "") { echo "<p>Please enter a search...</p>"; exit; } // check for a search parameter if (!isset($var)) { echo "<p>We dont seem to have a search parameter!</p>"; exit; } //connect to your database ** EDIT REQUIRED HERE ** mysql_connect("host name","database username","password"); //specify database ** EDIT REQUIRED HERE ** mysql_select_db("database") or die("Unable to select database"); //select which database we're using // Build SQL Query $query = "select link from links where keywords like \"%$trimmed%\" order by company_name"; // EDIT HERE and specify your table and field names for the SQL query $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); if ($numrows == 0) { echo "<h4>Results</h4>"; echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>"; } // next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; } // get results $query .= " limit $s,$limit"; $result = mysql_query($query) or die("Couldn't execute query"); // display what the person searched for echo "<p>You searched for: "" . $var . ""</p>"; // begin to show results set echo "Results"; $count = 1 + $s ; // now you can display the results returned while ($row= mysql_fetch_array($result)) { $title = $row["link"]; echo "$count.) $title" ; $count++ ; } $currPage = (($s/$limit) + 1); //break before paging echo "<br />"; // next we need to do the links to other results if ($s>=1) { // bypass PREV link if s is 0 $prevs=($s-$limit); print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< Prev 10</a>  "; } // calculate number of pages needing links $pages=intval($numrows/$limit); // $pages now contains int of pages needed unless there is a remainder from division if ($numrows%$limit) { // has remainder so add one page $pages++; } // check to see if last page if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { // not last page so give NEXT link $news=$s+$limit; echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>"; } $a = $s + ($limit) ; if ($a > $numrows) { $a = $numrows ; } $b = $s + 1 ; echo "<p>Showing results $b to $a of $numrows</p>"; ?> </body> </html> Thanks Again!! Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 21, 2008 Share Posted March 21, 2008 without knowing your code theory i would guess: <?php include ("banner.php") ?> <html> <body> <form name="form" action="searchscript.php" method="get"> <input type="text" name="q" /> <input type="submit" name="Submit" value="Search" /> </form> <?php // Get the search variable from URL $var = @$_GET['q'] ; $trimmed = trim($var); //trim whitespace from the stored variable // rows to return $limit=10; // check for an empty string and display a message. if ($trimmed == "") { echo "<p>Please enter a search...</p>"; exit; } // check for a search parameter if (!isset($var)) { echo "<p>We dont seem to have a search parameter!</p>"; exit; } //connect to your database ** EDIT REQUIRED HERE ** mysql_connect("host name","database username","password"); //specify database ** EDIT REQUIRED HERE ** mysql_select_db("database") or die("Unable to select database"); //select which database we're using // WHERE QUERY BUILDER ///// // Split into array of keywords $kw = @explode(" ",$var); // Get number of keywords $x = (!is_array($kw))? "1" : count($kw); // Loop for each keyword adding a where query each time. For($i=0;$i<$x;$i++){ // the current keyword (made safe from mysql_injection), depending on wether the explode() worked or not. $key = ($x==1)? mysql_escape_string($var) : mysql_escape_string($kw[$i]); // Add the where item as an appended array. $where[] = "`keywords` LIKE '%$key%' OR `link` LIKE '%$key%'"; } // Get full where query using the array created $where = "WHERE ".implode(" OR ",$where); // --------------- END WHERE BUILDER // Build SQL Query $query = "select `link` from `links` $where order by `company_name`"; // EDIT HERE and specify your table and field names for the SQL query $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); if ($numrows == 0) { echo "<h4>Results</h4>"; echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>"; } // next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; } // get results $query .= " limit $s,$limit"; $result = mysql_query($query) or die("Couldn't execute query"); // display what the person searched for echo "<p>You searched for: "" . $var . ""</p>"; // begin to show results set echo "Results"; $count = 1 + $s ; // now you can display the results returned while ($row= mysql_fetch_array($result)) { $title = $row["link"]; echo "$count.) $title" ; $count++ ; } $currPage = (($s/$limit) + 1); //break before paging echo "<br />"; // next we need to do the links to other results if ($s>=1) { // bypass PREV link if s is 0 $prevs=($s-$limit); print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< Prev 10</a>  "; } // calculate number of pages needing links $pages=intval($numrows/$limit); // $pages now contains int of pages needed unless there is a remainder from division if ($numrows%$limit) { // has remainder so add one page $pages++; } // check to see if last page if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { // not last page so give NEXT link $news=$s+$limit; echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>"; } $a = $s + ($limit) ; if ($a > $numrows) { $a = $numrows ; } $b = $s + 1 ; echo "<p>Showing results $b to $a of $numrows</p>"; ?> </body> </html> the only thing i edited outside the comment tags i added was the quury string itself. hope this helps, Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 21, 2008 Author Share Posted March 21, 2008 Thankyou so much for taking the time to look at this, however, it's had no effect...? thanks again thou! Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 21, 2008 Share Posted March 21, 2008 what do you mean? no difference? echo $query tell me what it says Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 21, 2008 Author Share Posted March 21, 2008 hi, ok the echo is bringing back: select link from links where keywords like "%argos shop%" order by company_name limit 0,10 Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 21, 2008 Share Posted March 21, 2008 are you sure you used my code? my procedure doesnt seem to be being used... for a start my query uses backticks ( ` ). its not the same... make sure it reads: // WHERE QUERY BUILDER ///// // Split into array of keywords $kw = @explode(" ",$var); // Get number of keywords $x = (!is_array($kw))? "1" : count($kw); // Loop for each keyword adding a where query each time. For($i=0;$i<$x;$i++){ // the current keyword (made safe from mysql_injection), depending on wether the explode() worked or not. $key = ($x==1)? mysql_escape_string($var) : mysql_escape_string($kw[$i]); // Add the where item as an appended array. $where[] = "`keywords` LIKE '%$key%' OR `link` LIKE '%$key%'"; } // Get full where query using the array created $where = "WHERE ".implode(" OR ",$where); // --------------- END WHERE BUILDER // Build SQL Query $query = "select `link` from `links` $where order by `company_name`"; // EDIT HERE and specify your table and field names for the SQL query Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 21, 2008 Author Share Posted March 21, 2008 Excellent! sorry! what a doof! Thanks a load!!!! Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 21, 2008 Share Posted March 21, 2008 np Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted March 21, 2008 Author Share Posted March 21, 2008 Hi, I'm no good at this, i've come a long long way, but this code is hard to look at. basically when you click on the "next 10" link, it displays the same results, here's the code so far...? with help from uniflare (thanks again) <?php include ("banner.php") ?> <html> <body> <form name="form" action="searchscript.php" method="get"> <input type="text" name="q" /> <input type="submit" name="Submit" value="Search" /> </form> <?php $page = "searchscript.php"; // Get the search variable from URL $var = @$_GET['q'] ; $trimmed = trim($var); //trim whitespace from the stored variable // rows to return $limit=10; // check for an empty string and display a message. if ($trimmed == "") { echo "<p>Please enter a search...</p>"; exit; } // check for a search parameter if (!isset($var)) { echo "<p>We dont seem to have a search parameter!</p>"; exit; } //connect to your database ** EDIT REQUIRED HERE ** mysql_connect("host","username","password"); //specify database ** EDIT REQUIRED HERE ** mysql_select_db("database_name") or die("Unable to select database"); //select which database we're using // WHERE QUERY BUILDER ///// // Split into array of keywords $kw = @explode(" ",$var); // Get number of keywords $x = (!is_array($kw))? "1" : count($kw); // Loop for each keyword adding a where query each time. For($i=0;$i<$x;$i++){ // the current keyword (made safe from mysql_injection), depending on wether the explode() worked or not. $key = ($x==1)? mysql_escape_string($var) : mysql_escape_string($kw[$i]); // Add the where item as an appended array. $where[] = "`keywords` LIKE '%$key%' OR `link` LIKE '%$key%'"; } // Get full where query using the array created $where = "WHERE ".implode(" OR ",$where); // --------------- END WHERE BUILDER // Build SQL Query $query = "select `link` from `links` $where order by `company_name`"; // EDIT HERE and specify your table and field names for the SQL query $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); if ($numrows == 0) { echo "<h4>Results</h4>"; echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>"; } // next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; } // get results $query .= " limit $s,$limit"; $result = mysql_query($query) or die("Couldn't execute query"); // display what the person searched for echo "<p>You searched for: "" . $var . ""</p>"; // begin to show results set echo "Results"; $count = 1 + $s ; // now you can display the results returned while ($row= mysql_fetch_array($result)) { $title = $row["link"]; echo "$count.) $title" ; $count++ ; } $currPage = (($s/$limit) + 1); //break before paging echo "<br />"; // next we need to do the links to other results if ($s>=1) { // bypass PREV link if s is 0 $prevs=($s-$limit); print " <a href=\"$page?s=$prevs&q=$var\"><< Prev 10</a>  "; } // calculate number of pages needing links $pages=intval($numrows/$limit); // $pages now contains int of pages needed unless there is a remainder from division if ($numrows%$limit) { // has remainder so add one page $pages++; } // check to see if last page if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { // not last page so give NEXT link $news=$s+$limit; echo " <a href=\"$page?s=$news&q=$var\">Next 10 >></a>"; } $a = $s + ($limit) ; if ($a > $numrows) { $a = $numrows ; } $b = $s + 1 ; echo "<p>Showing results $b to $a of $numrows</p>"; ?> </body> </html> also, i'd like to display them like this (as seen on www.everyonlinestore.co.uk (not advertising... just for reference as of what the code does) ): <?php include("xxxxxxxxx.php"); // connect to the mysql server $link = mysql_connect($server, $db_user, $db_pass) or die ("Could not connect to mysql because ".mysql_error()); // Select the jokes database if (!@mysql_select_db('database_name')) { exit('<p>Unable to locate the links ' . 'database at this time.</p>'); } if (!isset($_GET['start'])) { $_GET['start'] = 0; } if (!isset($_GET['p_f'])) { $_GET['p_f'] = 0; } require "xxxxxxxxxx.php"; // All database details will be included here $page_name="members.php"; // If you use this code with a different page ( or file ) name then change this $start= $_GET['start']; // To take care global variable if OFF if(!($start > 0)) { // This variable is set to zero for the first page $start = 0; } $eu = ($start -0); $limit = 12; // No of records to be shown per page. $this1 = $eu + $limit; $back = $eu - $limit; $next = $eu + $limit; $mysite_username = $_COOKIE["mysite_username"]; /////////////// WE have to find out the number of records in our table. We will use this to break the pages/////// $query2=" SELECT link FROM memberlinks WHERE username = '$mysite_username'"; $result2=mysql_query($query2); echo mysql_error(); $nume=mysql_num_rows($result2); /////// The variable nume above will store the total number of records in the table//// ////////////// Now let us start executing the query with variables $eu and $limit set at the top of the page/////////// $query=" SELECT link FROM memberlinks WHERE username = '$mysite_username' limit $eu, $limit "; $result=mysql_query($query); echo mysql_error(); echo "<table width='100%' border='0'>"; // display the users in table $c = 0; while($row = mysql_fetch_array($result)) { $user2 = html_entity_decode($row['link']); if($c%3 == 0) echo "<tr>"; // If the counter has ticked 5 times, start a new row. echo "<td scope='row' height='150' width='25%' align='center' valign='middle' class='bkgrnd'>$user2</td>"; if($c%3 == 2) echo "</tr>"; // If we're drawing the 6th pic, end this row. $c++; } if($c%5 != 4) echo "</tr>"; // If there isn't a number of pics divisible by 6, end the row echo "</table>"; // end the table ////////////////////////////// End of displaying the table with records //////////////////////// ///// Variables set for advance paging/////////// $p_limit=12; // This should be more than $limit and set to a value for whick links to be breaked $p_f= $_GET['p_f']; // To take care global variable if OFF if(!($p_f > 0)) { // This variable is set to zero for the first page $p_f = 0; } $p_fwd=$p_f+$p_limit; $p_back=$p_f-$p_limit; //////////// End of variables for advance paging /////////////// /////////////// Start the buttom links with Prev and next link with page numbers ///////////////// echo "<table align = 'center' width='80%'><tr><td align='left' width='20%'>"; if($p_f<>0){print "<a href='$page_name?start=$p_back&p_f=$p_back'><font face='Verdana' size='2'>PREV</font></a>"; } echo "</td><td align='left' width='10%'>"; //// if our variable $back is equal to 0 or more then only we will display the link to move back //////// if($back >=0 and ($back >=$p_f)) { print "<a href='$page_name?start=$back&p_f=$p_f'><font face='Verdana' size='2'>PREV</font></a>"; } echo "</td><td align='right' width='10%'>"; ///////////// If we are not in the last page then Next link will be displayed. Here we check that ///// if($this1 < $nume and $this1 <($p_f+$p_limit)) { print "<a href='$page_name?start=$next&p_f=$p_f'><font face='Verdana' size='2'>NEXT</font></a>";} echo "</td><td align='right' width='20%'>"; if($p_fwd < $nume){ print "<a href='$page_name?start=$p_fwd&p_f=$p_fwd'><font face='Verdana' size='2'>NEXT</font></a>"; } echo "</td></tr></table>"; ?> Thanks for all your help!!! 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.