|
PHP - Searching Multiple Columns For Multiple Words
Hello all! I'm having some issues with a snippet that I found online and have edited to how I want it.
Basically the code works at the moment, except if I were to search for 2 or 3 terms, it would search the columns username/action/result/changes for any result that have any of the words rather than results that include both terms, instead of either. I want the query to be like (assuming 2 search terms): Quote username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' AND username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' and not: Quote username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' OR username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' It needs an AND instead of an OR, but I'm not sure how to go around doing it. Here's the code as it is at the moment: $filter = ($_POST['filter']); $query = "SELECT * FROM activity WHERE"; $searchresult = explode(" ", $filter); foreach ($searchresult as $key => $filter) { $query .= " username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%'"; if ($key != (sizeof($searchresult) - 1)) $query .= " AND "; } $query .= "ORDER BY id DESC"; Sorry if this is a little confusing to understand, hope someone understands what I need. Similar TutorialsI have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories". $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting. [100] => Array ( [id] => 100 [username] => John [quote] => new test quote blah blah [by] => John [voteup] => 0 [votedown] => 0 [servtime] => 2010-12-02 @ 16:27:03 [label] => Array ( [0] => Historic [1] => Serious [2] => Funny ) ) Here is the code in full. //// $sword = explode(" ",$search); foreach($sword as $sterm){ $srch="%".$sterm."%"; echo"$srch<br />"; $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; $result=mysql_query($sql); while($row=mysql_fetch_object($result)){ $quote[$row->id]['id'] = $row->id; $quote[$row->id]['username'] = $row->username; $quote[$row->id]['quote'] = $row->quote; $quote[$row->id]['by'] = $row->by; $quote[$row->id]['voteup'] = $row->voteup; $quote[$row->id]['votedown'] = $row->votedown; $quote[$row->id]['servtime'] = $row->servtime; $quote[$row->id]['label'][] = $row->label; } echo"<pre>"; print_r($quote); echo"</pre>"; I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search. Any help would be great!! -BaSk First off, hello all! I want to improve our website's search but unfortunately don't know enough to get there! I'm not a PHP programmer but I'm generally able to tweak things... Our search page has 3 options - All words, Any word, Exact. I want to concentrate on the default "All words" which is the default parameter. The code is essentially as follows - Code: [Select] $gotcriteria=TRUE; $Xstext = mysql_escape_string($stext); $aText = split(" ",$Xstext); $aFields[0]="products.pId"; $aFields[1]="products.pName"; $aFields[2]="products.pDescription"; $aFields[3]="products.pLongDescription"; if($stype=="exact") $sSQL .= "AND (products.pId LIKE '%" . $Xstext . "%' OR ".getlangid("pName",1)." LIKE '%" . $Xstext . "%' OR ".getlangid("pDescription",2)." LIKE '%" . $Xstext . "%' OR ".getlangid("pLongDescription",4)." LIKE '%" . $Xstext . "%') "; else{ $sJoin="AND "; if($stype=="any") $sJoin="OR "; $sSQL .= "AND ("; for($index=0;$index<=3;$index++){ //$sSQL .= "("; $rowcounter=0; $arrelms=count($aText); foreach($aText as $theopt){ if(is_array($theopt))$theopt=$theopt[0]; $sSQL .= $aFields[$index] . " LIKE '%" . $theopt . "%' "; if(++$rowcounter < $arrelms) $sSQL .= $sJoin; } //$sSQL .= ") "; if($index < 3) $sSQL .= "OR "; } $sSQL .= ") "; } Here's the important bit - as far as I can see, the search looks at each of the 4 fields in the array individually? So if the search text is "RED APPLE", to get a result for the "All Words" option will require that both "RED" AND "APPLE" will need to be present in one of the searched fields...? What I want to achieve is that if the product name is "RED FRUIT" and the description mentions "APPLE", then an "All words" search for "RED APPLE" will pick this up. I'd guess that the easiest way would be to string the table fields together for searching but I don't know how to do this as a query... now I'm thinking I'll get flamed for not posting this in the MySQL forum too!! Thanks in advance for any help! Hi,
How can I select multiple columns from two tables and run a search through multiple fields?
My tables a
t_persons (holds information about persons)
t_incidents (holds foreign keys from other tables including t_persons table)
What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:
$query = "SELECT p.PersonID ,p.ImagePath ,p.FamilyName ,p.FirstName ,p.OtherNames ,p.Gender ,p.CountryID ,i.IncidentDate ,i.KeywordID ,i.IncidentCountryID ,i.StatusID FROM t_incidents AS i LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID WHERE FamilyName LIKE '%" . $likes . "%' AND FirstName LIKE '%" . $likes . "%' AND OtherNames LIKE '%" . $likes . "%' AND Gender LIKE '%" . $likes . "%' AND IncidentDate LIKE '%" . $likes . "%' AND KeywordID LIKE '%" . $likes . "%' AND IncidentCountryID LIKE '%" . $likes . "%' AND StatusID LIKE '%" . $likes . "%' ORDER BY PersonID DESC $pages->limit";Errors a Column 'IncidentDate' in where clause is ambiguous Column 'KeywordID' in where clause is ambiguous Column 'IncidentCountryID' in where clause is ambiguous Column 'StatusID' in where clause is ambiguousThese columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps. I will appreciate any better way to do this. Thanx. Joseph Attached Files Model - 3.png 76.6KB 0 downloads does anyone have a code todo this? a snippet or soemthing ive tried a few diffrent ones ive seen but nothing that works.. it takes whats submited in search, then takes the words, breaks them up and creates a query string with all the diffrent feilds... here is what phpadmin produces when i search SELECT * FROM `breedclads`.`activeamahorse` WHERE (`id_entered` LIKE '%green%' OR `uuid` LIKE '%green%' OR `ama_hr_coat` LIKE '%green%' OR `breedable` LIKE '%green%' OR `ama_hr_sex` LIKE '%green%' OR `ama_hr_age` LIKE '%green%' OR `date_entered` LIKE '%green%' OR `ama_hr_eyes` LIKE '%green%' OR `ama_hr_eyes_type` LIKE '%green%' OR `ama_hr_mane` LIKE '%green%' OR `ama_hr_tail` LIKE '%green%' OR `ama_hr_luster_coat` LIKE '%green%' OR `ama_hr_luster_hair` LIKE '%green%' OR `ama_hr_gleam_coat` LIKE '%green%' OR `ama_hr_gleam_hair` LIKE '%green%' OR `ama_hr_parents` LIKE '%green%' OR `ama_hr_sale_type` LIKE '%green%' OR `ama_hr_sale_price` LIKE '%green%' OR `ama_hr_location` LIKE '%green%' OR `auction_date` LIKE '%green%' OR `picture_type` LIKE '%green%' OR `picture_name` LIKE '%green%' OR `picture_size` LIKE '%green%' OR `picture_content` LIKE '%green%' OR `ama_hr_coat_type` LIKE '%green%' OR `ama_hr_coat_gloom` LIKE '%green%' OR `ama_hr_hair_gloom` LIKE '%green%' OR `ama_hr_slurl` LIKE '%green%' OR `class_link_key` LIKE '%green%') AND (`id_entered` LIKE '%albino%' OR `uuid` LIKE '%albino%' OR `ama_hr_coat` LIKE '%albino%' OR `breedable` LIKE '%albino%' OR `ama_hr_sex` LIKE '%albino%' OR `ama_hr_age` LIKE '%albino%' OR `date_entered` LIKE '%albino%' OR `ama_hr_eyes` LIKE '%albino%' OR `ama_hr_eyes_type` LIKE '%albino%' OR `ama_hr_mane` LIKE '%albino%' OR `ama_hr_tail` LIKE '%albino%' OR `ama_hr_luster_coat` LIKE '%albino%' OR `ama_hr_luster_hair` LIKE '%albino%' OR `ama_hr_gleam_coat` LIKE '%albino%' OR `ama_hr_gleam_hair` LIKE '%albino%' OR `ama_hr_parents` LIKE '%albino%' OR `ama_hr_sale_type` LIKE '%albino%' OR `ama_hr_sale_price` LIKE '%albino%' OR `ama_hr_location` LIKE '%albino%' OR `auction_date` LIKE '%albino%' OR `picture_type` LIKE '%albino%' OR `picture_name` LIKE '%albino%' OR `picture_size` LIKE '%albino%' OR `picture_content` LIKE '%albino%' OR `ama_hr_coat_type` LIKE '%albino%' OR `ama_hr_coat_gloom` LIKE '%albino%' OR `ama_hr_hair_gloom` LIKE '%albino%' OR `ama_hr_slurl` LIKE '%albino%' OR `class_link_key` LIKE '%albino%') AND (`id_entered` LIKE '%69%' OR `uuid` LIKE '%69%' OR `ama_hr_coat` LIKE '%69%' OR `breedable` LIKE '%69%' OR `ama_hr_sex` LIKE '%69%' OR `ama_hr_age` LIKE '%69%' OR `date_entered` LIKE '%69%' OR `ama_hr_eyes` LIKE '%69%' OR `ama_hr_eyes_type` LIKE '%69%' OR `ama_hr_mane` LIKE '%69%' OR `ama_hr_tail` LIKE '%69%' OR `ama_hr_luster_coat` LIKE '%69%' OR `ama_hr_luster_hair` LIKE '%69%' OR `ama_hr_gleam_coat` LIKE '%69%' OR `ama_hr_gleam_hair` LIKE '%69%' OR `ama_hr_parents` LIKE '%69%' OR `ama_hr_sale_type` LIKE '%69%' OR `ama_hr_sale_price` LIKE '%69%' OR `ama_hr_location` LIKE '%69%' OR `auction_date` LIKE '%69%' OR `picture_type` LIKE '%69%' OR `picture_name` LIKE '%69%' OR `picture_size` LIKE '%69%' OR `picture_content` LIKE '%69%' OR `ama_hr_coat_type` LIKE '%69%' OR `ama_hr_coat_gloom` LIKE '%69%' OR `ama_hr_hair_gloom` LIKE '%69%' OR `ama_hr_slurl` LIKE '%69%' OR `class_link_key` LIKE '%69%') i need this to be made when people search with mutliple words as you can see it searchs each feilds for the green then each feild for albino, so on.. but i wont ever know what there searching for Please help me, thanks This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=351561.0 Hello all, I am trying to learn OOP in PHP so please forgive my ignorance. I seem to be having difficulty inserting data into my database from checkbox items. My database has a column for each each fruit. I would like for each column to be populated with either a one(1) for checked or a zero(0) for not checked. Currently, my attempts have failed. I can either get all checkboxes to insert all zeros or I can get the first column to insert a one(1) while the others as zeros. Is there anybody here that can help me figure out what I am doing wrong and help me to re-work my code so I can get this to work? If anybody can help me using OOP methods that would be fantastic. Thank you all in advance.
$preffruit->create_preffruit(array(
'fruit_apple' => escape(Input::get(['fruit_selection'][0]) ? 1 : 0),
'fruit_orange' => escape(Input::get(['fruit_selection'][1]) ? 1 : 0),
'fruit_banana' => escape(Input::get(['fruit_selection'][2]) ? 1 : 0),
'fruit_pear' => escape(Input::get(['fruit_selection'][3]) ? 1 : 0),
'fruit_kiwi' => escape(Input::get(['fruit_selection'][4]) ? 1 : 0)
));
<input type="checkbox" name="fruit_selection[]" value="fruit_apple">
<input type="checkbox" name="fruit_selection[]" value="fruit_orange">
<input type="checkbox" name="fruit_selection[]" value="fruit_banana">
<input type="checkbox" name="fruit_selection[]" value="fruit_pear">
<input type="checkbox" name="fruit_selection[]" value="fruit_kiwi">
public function insert_preffruit($table, $fields = array()) {
$keys = array_keys($fields);
$values = '';
$x = 1;
foreach($fields as $field) {
$values .= '?';
if($x < count($fields)) {
$values .= ', ';
}
$x++;
}
$sql = "INSERT INTO preffruit (`user_id`, `" . implode('`, `', $keys) . "`) VALUES (LAST_INSERT_ID(), {$values})";
if(!$this->query($sql, $fields)->error()) {
return true;
}
return false;
}
Edited September 23, 2020 by ke-jo I want to echo out a few subcategories per main category. It should be a maximum of 20 entries per column At this stage I have the following... It echo's the results out in 4 columns and not what I want. Any suggestions? $query = "SELECT adsubcat.name AS subname, adsubcat.linkname AS sublink, adcat.clinkname AS clink FROM adsubcat JOIN adcat ON adcat.id=adsubcat.catid WHERE adsubcat.catid='$catid' ORDER BY adsubcat.name ASC"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)) { $count = 0; print '<table style="text-align:left;margin-left:0px;width:943px;">'."\n"; while ($row = mysql_fetch_assoc($result)){ $gal = '<a href="'.$root.'/'.$row['clink'].'/'.$row['sublink'].'">'.$row['subname'].'</a>'; if ($count == 0){ print '<tr>'; } ++$count; print '<td>'.$gal.'</td>'; if ($count == 4){ $count = 0; print'</tr>'."\n"; } } print'</table>'."\n"; } I found this regex, which is what I want, here:
(?=.*\bdog\b)(?=.*\bpuppet\b)(?=.*\bfuzzy\b)It is suppose to 'succeed' if it can find all the given words in any order. Yet, when I try to test it against this sequence of words (or any other order of these words): need fuzzy hand puppet in dogpen for dogI get no indication of a match. What am I not understanding? I've been trying to go around with str_replace to replace numbers with words. I have a database which has table actions. The actions are saved in numbers from 0 to 20. I'd need to get something to replace the numbers with words to explain what actions these are. I've tried using this. $action1 = str_replace("5", "Command", "action1"); But that doesn't work. Not even 100% sure if that is even correct, because I am new to the str_replace thing anyway. Could someone help me with this, please? Hi there I am beginning to learn regex in php. I was wondering why the following regex pattern wasn't finding the two words in the string and replacing them with welcome? '/(Hello).*?(Hi)/is' Code: [Select] $patterns = array(); $patterns[0] = '/(Hello).*?(Hi)/is'; $replacements = array(); $replacements[0] = 'Welcome'; ksort($patterns); ksort($replacements); echo preg_replace($patterns, $replacements, $string); Any help would be much appreciated. Thanks a million! Hello 1) I am reading two good tutorials for building a search page with pagination: http://www.phpfreaks.com/tutorial/basic-pagination http://php.about.com/od/phpwithmysql/ss/php_pagination.htm Before I ask the big question, I would like to know how does a query look like when searching multiple tables (e.g three) in the same time. Table1/Table2/Table3. May you kindly give me example? 2) The problem is that I have multiple tables with the same structure to search e.g. (title, body) a) how I will know the ROWS NUMBER returned by the search query? it doesn't look a smart way to query each table and them count the results, and this basically takes much time right? b) when displaying the result, how I will be able to set the correct hyperlink for each result? you know, each table is a different subject, each one is located in different folder. Thank you SO MUCH Hi I'm trying to find the max value in 3 columns, new_date, reply_date and finalized_date in my table: Code: [Select] $test = safe_query("SELECT MAX(new_date) FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges UNION SELECT reply_date FROM ".PREFIX."cup_challenges UNION SELECT finalized_date FROM ".PREFIX."cup_challenges) AS maxval"); Did some research and have no idea what is wrong/if the above is correct? The page breaks below this code. I have this code which works fine. I want to improve it by displaying it into multiple columns. Current output: TITLE 1 JOB1 --------- TITLE 2 JOB 2 --------- ETC... I want it to display like this instead: TITLE 1 | TITLE 2 JOB 1 | JOB 2 Here is my code: $sql = "SELECT * FROM coaches WHERE position = 'Junior Coach'"; $result = mysql_query ($sql); while ($row = mysql_fetch_array($result)){ $id = $row["id"]; $firstname = $row["firstname"]; $lastname = $row["lastname"]; $position = $row["position"]; $image = $row["image"]; ?> <table width="183" border="1"> <tr> <td align="center" height="18"> <?php echo "<img id='image_shadowCoaches' src=" . $image . ">"; ?><br /> <?php echo "<div id='Coaches_Detail'>".$firstname." ".$lastname."</div>" ?> <?php echo "<div id='Coaches_DetailLower'>".$position."</div>" ?> </td> </tr> </table> <?php } ?> I have some code that selects one column from mysql table. However when I try to add more it doesn't work.
First the working code....
$query = "SELECT `profile_privacy` FROM `users` WHERE `user_username` = '$username'"; $result = mysql_query($query) or die($query."<br/><br/>".mysql_error()); $profile_privacy = mysql_result($result, 0);And here is where I have added another column.... $query = "SELECT `profile_privacy`, `profile_pictures` FROM `users` WHERE `user_username` = '$username'"; $result = mysql_query($query) or die($query."<br/><br/>".mysql_error()); $profile_privacy = mysql_result($result, 0); $profile_pictures = mysql_result($result, 1);What have I done wrong? Many thanks, Hi there, This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself. 1. Pull all the records from my database. 2. Assign their unique user id's, callsigns and destinations. 3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'. For example if three people are going to Heathrow, then it would return 3 results. 4. Then do a mysql_num_rows of the result. 5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier. All in a loop of course. // Selects all of the ATC Records. $atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS"); // Starts the loop for fetching the records of the previous query. while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){ // Sets the users cid, planned_destairport and callsign. $atc_cid = $atc_arrivals_row['cid']; $atc_destinations = $atc_arrivals_row['planned_destaiport']; $atc_callysign = $atc_arrivals_row['callsign']; // Searches the database and selects all records where the callsign is LIKE the destination airport. $atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'"); // Counts previous query results. $atc_arrivals_count = mysql_num_rows($atc_arrivals_check); // Updates the atcarrivals column with the count.. $atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'"); // End loop. } Concerned columns are these: "cid" - Unique client id. "atcarrivals" - Number of arrivals from count. (This is the problem.) "planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow. Thanks. I'm wanting to output data into two columns. I've searched for this in a few places. A vast majority of the search results deal with getting data from multiple columns, and the the ones that deal with what I'm looking for don't really have any solutions attached to them. I'm not fully worried about the columns being equal length. The data is broken up into a certain number of rows in a given year. I have about 12 years, probably 6-8 rows per year. I just don't know how to get it into different columns. I don't care if it goes left to right then down or down then left to right. If there has been a solution to that here already, feel free to point me in that direction. Point me in any applicable direction, I'd fine with. I just can't locate anywhere what trigger would cause that. Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+). I have written a PHP script that uploads individual entries. Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually? Thanks for any observations and/or help. Im having problems with this code in my search button script. I get the following error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource Here's the code I use on my search form. Code: [Select] <form method="post" action="srch_all.php"><input type=text name='search' size=15 maxlength=255><br><input type=submit></form> and here's the code I'm using to perform the search query: Code: [Select] if ($search) // perform search only if a string was entered. { mysql_connect($host, $user, $pass) or die ("Problem connecting to Database"); $srch="%".mysql_real_escape_string($search)."%"; $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC"; $result = mysql_db_query("database_name", $query); if(mysql_num_rows($result)==0) { print "<h2>Your search returned 0 Results</h2>"; } else if ($result) { <restult data stuff here>.. Cheers Hi There, I have an SQL query that returns 10 rows, which I want to echo over 2 columns and 5 rows, however, I want rows 1-5 on the left hand column and rows 6-10 on the right hand side. Is there an easy way to do this? Normally I would do a fetch_array but, that would place the rows in order of, left, right, left, right - if that makes sense? Table is a standard table with 2 columns and 5 rows. Thanks Matt |
|||||||