Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 26-02-2009
Member
 
Join Date: Feb 2009
Posts: 117
Search multiple sql fields

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:

Quote:
<?
$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:


Quote:
<?
/ / - 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 "<br> <br> bgcolor=#999999 <table width=500 hight=auto> <tr> <td ALIGN=LEFT> Note: </ td> <td ALIGN=LEFT> <b>". $ row [ 'mark']. "</ b> </ 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> <br> <br> <img src=img\streep.jpg>";
)

echo "<br> <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 "<br> <center> <p> Nothing found in your search: <b> $ _POST [query] </ b> </ p> </ center>";

echo "<br> <CENTER> <p> href=\"?p=auto\" title=\"zoe opnie\"> search again </ a> </ p> </ center>";
)
>
>
Can someone help me thank you
Reply With Quote
  #2  
Old 26-02-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Search multiple sql fields

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
Reply With Quote
  #3  
Old 26-02-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Search multiple sql fields

Quote:
<? 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:
Quote:
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.
Reply With Quote
  #4  
Old 26-02-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Search multiple sql fields

Debugging is easier with some output.
If you just let the $ sql ECHO' you see what the script sends to the database
Reply With Quote
  #5  
Old 26-02-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Search multiple sql fields

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Search multiple sql fields"
Thread Thread Starter Forum Replies Last Post
In Access 2007 I need to combine multiple tables into 1 having identical fields djbbenn Software Development 4 09-05-2012 09:21 PM
Zoom Search: Multiple values in Meta Search Fields Aashay Technology & Internet 4 20-08-2010 11:20 AM
How to parse comma separated text into Multiple Fields? Leonard Windows Software 5 12-03-2010 06:39 AM
Excel vertical search, Multiple results per id Theo Windows Software 3 21-03-2009 09:33 AM
How to perform Multiple Search? Demetrius Software Development 3 04-03-2009 08:26 AM


All times are GMT +5.5. The time now is 08:51 AM.