# Searching MYSQL with multiple fields in PHP

**URL:** <https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718>\
**Category:** programming\
**Created:** [March 20, 2006, 11:04pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718 "2006-03-20T23:04:54Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:04pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/1 "2006-03-20T23:04:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![morse](https://avatars.discourse-cdn.com/v4/letter/m/bbce88/32.png) [@morse](https://forum.kirupa.com/u/morse)\
**Post date:** [March 20, 2006, 11:06pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/2 "2006-03-20T23:06:45Z")

</div>

Post SQL query?

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [March 20, 2006, 11:09pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/3 "2006-03-20T23:09:00Z")

</div>

sounds like you need an associative array…

```php

$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.

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:12pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/4 "2006-03-20T23:12:27Z")

</div>

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

this returns fine

but if I add

OR pickup LIKE ‘%$embark%’

it fails.

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:18pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/5 "2006-03-20T23:18:46Z")

</div>

This is the actual code from my page

```php

 $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)) 
 { 

```

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [March 20, 2006, 11:28pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/6 "2006-03-20T23:28:08Z")

</div>

> [@onemhunki](#):
>
> "SELECT \* FROM members WHERE river LIKE ‘%$waterway%’ OR canal LIKE ‘%$waterway%’
> 
> this returns fine
> 
> but if I add
> 
> OR pickup LIKE ‘%$embark%’
> 
> it fails.

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%’

---

<div class="post-metadata">

**Author:** ![JoshuaJonah](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/joshuajonah/32/1775_2.png) [@JoshuaJonah](https://forum.kirupa.com/u/JoshuaJonah)\
**Post date:** [March 20, 2006, 11:31pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/7 "2006-03-20T23:31:07Z")

</div>

lol

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:43pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/8 "2006-03-20T23:43:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JoshuaJonah](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/joshuajonah/32/1775_2.png) [@JoshuaJonah](https://forum.kirupa.com/u/JoshuaJonah)\
**Post date:** [March 20, 2006, 11:45pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/9 "2006-03-20T23:45:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:49pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/10 "2006-03-20T23:49:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [March 20, 2006, 11:50pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/11 "2006-03-20T23:50:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:57pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/12 "2006-03-20T23:57:56Z")

</div>

```php

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 
 ?>

```

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 20, 2006, 11:59pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/13 "2006-03-20T23:59:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [March 21, 2006, 12:02am UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/14 "2006-03-21T00:02:52Z")

</div>

> [@onemhunki](#):
>
> Currently this is working, it’s searching my database fields ‘river’ ‘canal’ ‘lakeloch’ and ‘sea’ with the search field ‘waterway’

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?

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 21, 2006, 12:13am UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/15 "2006-03-21T00:13:00Z")

</div>

The next field in the search form ‘embark’ has to search ‘from\_one’ on the database, also ‘from\_two’ etc

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 21, 2006, 12:17am UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/16 "2006-03-21T00:17:47Z")

</div>

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.

```php

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

```

---

<div class="post-metadata">

**Author:** ![onemhunki](https://avatars.discourse-cdn.com/v4/letter/o/c57346/32.png) [@onemhunki](https://forum.kirupa.com/u/onemhunki)\
**Post date:** [March 21, 2006, 12:21am UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/17 "2006-03-21T00:21:49Z")

</div>

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

```auto

<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>

```

---

<div class="post-metadata">

**Author:** ![teiz77](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/teiz77/32/771_2.png) [@teiz77](https://forum.kirupa.com/u/teiz77)\
**Post date:** [March 21, 2006, 1:31pm UTC](https://forum.kirupa.com/t/searching-mysql-with-multiple-fields-in-php/182718/18 "2006-03-21T13:31:26Z")

</div>

or use full text search

First alter your database:

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

```

Then use the following SQL statement:

```php
$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";

```