Searching MYSQL with multiple fields in PHP

programming
1

Folks,

I have a simple MYSQL database that I’m trying to search, I have four fields
waterway, embark, disembark, region, in a search form. I can get the php to return search results for one field, but when I add a second it just lists the whole database. Each field is named different in the form, I’ve used AND and OR in my MYSQL SELECT statement, but it doesn’t seem to work when I try to search with more than one field.

Any ideas, deadlines looming.

Cheers

2

Post SQL query?

3

sounds like you need an associative array…


$result = mysql_query( "SELECT waterway,embark,disembark,region FROM $tbl_name" );
while( $row = mysql_fetch_array($result) ) {
 echo '<p>waterway: '.$row[waterway].'</p>';
}

^will print waterway for that each row.

4

"SELECT * FROM members WHERE river LIKE ‘%$waterway%’ OR canal LIKE ‘%$waterway%’

this returns fine

but if I add

OR pickup LIKE ‘%$embark%’

it fails.

5

This is the actual code from my page



 $query = mysql_query("SELECT * FROM members WHERE river LIKE '%$waterway%' OR canal LIKE '%$waterway%' OR lakeloch LIKE '%$waterway%' OR sea LIKE '%$waterway%' LIMIT 0, 50"); 
 while ($row = @mysql_fetch_array($query)) 
 {
6

that’s because you don’t have a field named pickup. same goes for your other like’s (no fields named river, canal, lakeloch, or sea). looks to me like you have the LIKE statement backwards in your head. it goes like this…
field LIKE variable
waterway LIKE ‘%river%’

7

lol

8

Sorry, don’t get it. My search field in my form is called waterway, the database has fields named ‘river’ ‘canal’ etc.

9

“waterway” is not a php variable “$waterway” might do the job though:thumb2:

10

The search field waterway searches ‘river’ ‘canal’ etc fine, that’s the bit that works. It’s when I try to get my second field in my search form to work with the first.

11

how about you post all of your code and the table layout?

12 

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
 @mysql_select_db( "$dbName") or die( "Unable to select database"); 
 ?> 
 <? 
 //error message (not found message)begins 
 $XX = "No Record Found, please search again"; 
 //query details table begins
 $query = mysql_query("SELECT * FROM members WHERE river LIKE '%$waterway%' OR canal LIKE '%$waterway%' OR lakeloch LIKE '%$waterway%' OR sea LIKE '%$waterway%' LIMIT 0, 50"); 

 while ($row = @mysql_fetch_array($query))
 { 
 $variable1=$row["memberID"];
 $variable2=$row["name"]; 
 $variable3=$row["tel"]; 
 $variable4=$row["email"];
 //table layout for results 

 print ("<tr>");
 print ("<td>$variable2</td>"); 
 print ("<td>$variable3</td>"); 
 print ("<td><a href= $variable4>$variable4</a></td>"); 
 print ("</tr>"); 
 }
 //below this is the function for no record!!
 if (!$variable1)
 { 
 print ("$XX");
 } 
 //end 
 ?>
13

Currently this is working, it’s searching my database fields ‘river’ ‘canal’ ‘lakeloch’ and ‘sea’ with the search field ‘waterway’

14

sounds like $embark isn’t being passed to the page w/the query correctly. what happens if you try printing out $embark on that page?

15

The next field in the search form ‘embark’ has to search ‘from_one’ on the database, also ‘from_two’ etc

16

The weird thing is that if I completely replace the first query, with the second query, it works still, but just not when I have them both together.


 $query = mysql_query("SELECT * FROM members WHERE from_one LIKE '%$embark%'  LIMIT 0, 50");
17

This is the HTML code for the form, perhaps the problem lies there?


<form action="/html/search_results.php" method="post" name="" target="_blank" id="">
        <table width="153" height="74" border="0">
          <tr>
            <th width="33" scope="row"><div align="right">waterway</div></th>
            <td width="110"><div align="left">
                <input name="waterway" type="text" id="waterway" size="12">
            </div></td>
          </tr>
          <tr>
            <th scope="row"><div align="right">from</div></th>
            <td><div align="left">
                <input name="embark" type="text" id="embark" size="12">
            </div></td>
          </tr>
          <tr>
            <th scope="row"><div align="right">to</div></th>
            <td><div align="left">
                <input name="disembark" type="text" id="disembark" size="12">
            </div></td>
          </tr>
          <tr>
            <th colspan="2" scope="row"><select name="region">
                <option>Scotland</option>
                <option>North West</option>
                <option>North East</option>
                <option>Wales</option>
                <option>Midlands</option>
                <option>East Anglia</option>
                <option>Upper Thames</option>
                <option>London</option>
                <option>Bristol &amp; South West</option>
                <option>South East</option>
                <option selected>Select</option>
              </select></th>
          </tr>
          <tr>
            <th colspan="2" scope="row"><input type="submit" name="Submit" value="go"></th>
          </tr>
        </table>
        <p>&nbsp; </p>
        <p>&nbsp; </p>
      </form>
18

or use full text search

First alter your database:

ALTER TABLE `members` ADD FULLTEXT (
`river` ,`canal` ,`river` ,`lakeloch` ,`sea`
);

Then use the following SQL statement:

$query = "SELECT *, MATCH(`river` ,`canal` ,`river` ,`lakeloch` ,`sea`) AGAINST ('".$_POST['inputfield']."') AS score 
FROM `members` 
WHERE MATCH(`river` ,`canal` ,`river` ,`lakeloch` ,`sea`) AGAINST ('".$_POST['inputfield']."' IN BOOLEAN MODE) 
AND ((MATCH(`river` ,`canal` ,`river` ,`lakeloch` ,`sea`) AGAINST ('".$_POST['inputfield']."' IN BOOLEAN MODE)) > 0) 
ORDER BY SCORE DESC";