Search multiple sql fields

**Zindin** · 26-02-2009

I am looking for my script to fix, it could first find a word in different tables. But in combination they did not. Now someone help me and said I had to implement this script:

<?
$searchQuery = '%$_POST[search]%' ;
$chunks = preg_split ( '#\s+#' , $searchQuery );
$ columns = Array ( 'label', 'type', 'price', 'doors',' color ',' year ',' weight ',' kmstand ',' fuel ',' MOT ',' engine content ',' transmission ',' guarantee ',' info ')

$clausule = '' ;

foreach( $chunks as $chunk ){
$searchType = 0 ;
if( $chunk [ 0 ] == '-' ){
$searchType = 1 ;
$chunk = substr ( $chunk , 1 );
}
$fields = Array();
foreach( $columns as $column ){
$fields [] = '(' . $column .( $searchType ? ' NOT' : '' ). ' LIKE \'%' . mysql_real_escape_string ( $chunk ). '%\')' ;
}
$clausule .= ( strlen ( $clausule )? ' AND' : ' ' ). ' (' . implode (( $seachType ? ' AND ' : ' OR ' ), $fields ). ') ' ;
}
?>

Now I have no idea how I now get to work.
The rest of my existing script like this:

<?
/ / - Execute the SQL code and put it in a variable so we are about to be
/ / - Check if there is a result
$ res = mysql_query ($ sql);

/ / - Check now or a result, the keyword is found or not
if (mysql_num_rows ($ res)> = 1)
(
/ / - A result, show the results via a while () loop
while ($ row = mysql_fetch_array ($ res))
(
echo " bgcolor=#999999 <table width=500 hight=auto> <tr> <td ALIGN=LEFT> Note: </ td> <td ALIGN=LEFT> ". $ row [ 'mark']. " </ td> </ tr> <tr> <td ALIGN=LEFT> Type: </ td> <td ALIGN=LEFT>. $ row [ 'type']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Price: </ td> <td ALIGN=LEFT>. $ row [ 'price']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Image: </ td> <td ALIGN=LEFT> <HR color=gray> <a target = _blank href = img /". $ row [ 'image']. "> <img border = 0 src = img / thumbs /". $ row [ 'image']. "> </ a> <HR color=gray> </ td> </ tr> <tr> <td ALIGN=LEFT> Number of doors: </ td> <td ALIGN=LEFT>. $ row [ 'doors']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Color: </ td> <td ALIGN=LEFT>. $ row [ 'color']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Year: </ td> <td ALIGN=LEFT>. $ row [ 'age']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Weight: </ td> <td ALIGN=LEFT>. $ row [ 'weight']. "</ td> </ tr> <tr> <td ALIGN=LEFT> KMs: </ td> <td ALIGN=LEFT>. $ row [ 'kmstand']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Fuel: </ td> <td ALIGN=LEFT>. $ row [ 'fuel']. "</ td> </ tr> <tr> <td ALIGN=LEFT> MOT: </ td> <td ALIGN=LEFT>. $ row [ 'MOT']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Motor contents: </ td> <td ALIGN=LEFT>. $ row [ 'content engine']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Transmission: </ td> <td ALIGN=LEFT>. $ row [ 'transmission']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Warranty: </ td> <td ALIGN=LEFT>. $ row [ 'guarantee']. "</ td> </ tr> <tr> <td ALIGN=LEFT> Info: </ td> <td ALIGN=LEFT>. $ row [ 'info']. "</ td> </ tr> </ table> <img src=img\streep.jpg>";
)

echo " <center> <a href=\"?p=auto\" title=\"zoe opnie\"> search again </ a> </ center>";
)
/ / - If no result is found, so if the keyword is not found:
else
(
echo " <center> Nothing found in your search: $ _POST [query] </ center>";

echo " <CENTER> href=\"?p=auto\" title=\"zoe opnie\"> search again </ a> </ center>";
)
>
>

Can someone help me thank you

**MindSpace** · 26-02-2009

What the above script does is a WHERE clause in your SQL query building. That is something you need to add to an existing query. Briefly through the curve, you need to do something

**MindSpace** · 26-02-2009

<? php
/ / This script expects the POST variable in the search query is typed
$ Searchquery = '% $ _POST [search]%'
$ chunks = preg_split ( '# \ s + #', $ search Query);
/ / In this array you just put all the database columns which could be
$ columns = Array ( 'label', 'type', 'price', 'doors',' color ',' year ',' weight ',' kmstand ',' fuel ',' MOT ',' engine content ',' transmission ',' guarantee ',' info ')

$ clause ='';

foreach ($ chunks as $ chunk) (
$ search = Type 0;
if ($ chunk [0] == '-') (
$ search = Type 1;
$ chunk = substr ($ chunk, 1);
)
$ fields = array ();
foreach ($ columns as $ column) (
$ fields [] = '('. $ column. ($ search type? 'NOT':''). "LIKE \ '%'. mysql_real_escape_string ($ chunk). '% \') ';
)
$ clause .= (strlen ($ clause)? 'AND': '). '('. Implode (($ seachType? 'AND' 'OR'), $ fields). ");
)

$ sql = "SELECT * FROM` table `WHERE". $ clause. "ORDER BY` name `";

/ / Here your query and the results show
>

As you can see, the script you got ensures that the columns searched are set to the value that you enter on your form. What you actually get is the following query:

Code:

SELECT *
FROM `table name`
"` WHERE field1 LIKE '% search%'
OR `field2` LIKE '% search%'
ORDER BY `name` ASC

You can use the above script, you can also based on this query itself cobble something together. Keep in mind: If you in this way by a large database searches can be quite long lasting. If you big texts or big databases to search, you should look at the FULL TEXT search of MySQL.

**Reegan** · 26-02-2009

Debugging is easier with some output.
If you just let the $ sql ECHO' you see what the script sends to the database

**Zecho** · 26-02-2009

Incidentally, the above script is not really secure. A user does a search using the word directly in the query stream. Even google search for articles on sql injection and you will soon find out that one of the most dangerous and most made security errors.