search multiple fields from one database via 1 qurey

[pbucc]

 

 

 

 

 

hi there, 

 

i'm new here and i have a question.  i have a mysql database that has 4 fields.

 

1. name

2. type

3. country

4. color

 

what i would like to do is give the user the option to search by any one of these or all of these...

 

example: "name" from "USA" that is the color "red".

 

right now i have 4 different forms and it will only return 1 of the 4 options. 

 

all fields would be a drop down menu except the "name" field.

 

any help would be great.  i would be willing to pay for some help.

 

thank you

 

 

 

 

 

 

 

[sharp.mac]

well I don't know about ONE glorious query string, but you can break it down into a function that would check all of them.

 

[pseudocode]

 

SELECT *  FROM `tableName` WHERE `name` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `type` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `country` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `color` LIKE '%searchString%';

 

[/pseudocode]

 

then just list them out

[Muddy_Funster]

well I don't know about ONE glorious query string, but you can break it down into a function that would check all of them.

 

[pseudocode]

 

SELECT *  FROM `tableName` WHERE `name` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `type` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `country` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `color` LIKE '%searchString%';

 

[/pseudocode]

 

then just list them out

 

If I could reach you I'd slap you up side the head for suggesting not just 1 SELECT *, but 4 for the one dataset!! and on a table with ony 4 columns!! 

 

what the OP wants to do is create a single form with the 4 search criteria:

<form action="search.php" type="submit" method="post">
<label> Name: </label>
<input type="text" name="name" value="" />
<label> Type: </label>
<input type="text" name="type" value="" />
<label> Country: </label>
<input type="text" name="country" value="" />
<label> Color: </label>
<input type="text" name="color" value="" />
<input type="submit" value="Search Now!" />
</form>

 

Now on the search.php page take each of your POST variables and assign them to page variables that you can use to build your SQL SELECT query.

[pbucc]

well I don't know about ONE glorious query string, but you can break it down into a function that would check all of them.

 

[pseudocode]

 

SELECT *  FROM `tableName` WHERE `name` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `type` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `country` LIKE '%searchString%';

SELECT *  FROM `tableName` WHERE `color` LIKE '%searchString%';

 

[/pseudocode]

 

then just list them out

 

If I could reach you I'd slap you up side the head for suggesting not just 1 SELECT *, but 4 for the one dataset!! and on a table with ony 4 columns!! 

 

what the OP wants to do is create a single form with the 4 search criteria:

<form action="search.php" type="submit" method="post">
<label> Name: </label>
<input type="text" name="name" value="" />
<label> Type: </label>
<input type="text" name="type" value="" />
<label> Country: </label>
<input type="text" name="country" value="" />
<label> Color: </label>
<input type="text" name="color" value="" />
<input type="submit" value="Search Now!" />
</form>

 

Now on the search.php page take each of your POST variables and assign them to page variables that you can use to build your SQL SELECT query.

 

 

well muddy your right but the options are drop down menus.  also i would know what to do on the search page.  i'm new to this stuff.

 

[Adam]

You'll need to progressively build up the query after checking if a value has been selected from each of the drop downs:

 

$sql = "select * from your_table_name where ";

$conditions = array();

if (!empty($_POST['name_select']))
{
    $conditions[] = "name = '" . mysql_real_escape_string($_POST['name_select']) . "'";
}

if (!empty($_POST['type_select']))
{
    $conditions[] = "type = '" . mysql_real_escape_string($_POST['type_select']) . "'";
}

// etc.

$sql .= implode(' and ', $conditions);

 

Edit: updated the code.

[pbucc]

so mr.adam,

 

do i put this in my search.php file?

[Adam]

Well yeah I guess, wherever your form points at. You'll need to modify it obviously; add the correct table name, add the rest of the fields, execute and process the query, etc.

[pbucc]

ok here is the code

 

----------------------------------------------------------------------------------------------------------------

<html>

    <head>

        <title>Search Test</title>

    </head>

    <body topmargin="0" leftmargin="0">

 

Name Search:<br>

 

    <form method="post" action="name_search.php">

<input type="text" name="search" size=25 maxlength=25>

 

<input type="Submit" name="Submit" value="Submit">

</form><br>

 

Country Search:<br>

<form method="post" action="country_search.php">

<select name="search">

<option value="" selected="selected"></option>

<option value="albania">ALBANIA</option>

<option value="algeria">ALGERIA</option>

<option value="angola">ANGOLA</option>

<option value="argentina">ARGENTINA</option>

<option value="australia">AUSTRALIA</option>

 

<option value="belgium">BELGIUM</option>

<option value="bolivia">BOLIVIA</option>

<option value="brazil">BRAZIL</option>

<option value="canada">CANADA</option>

<option value="chile">CHILE</option>

<option value="china">CHINA</option>

<option value="croatia">CROATIA</option>

<option value="egypt">EGYPT</option>

<option value="finland">FINLAND</option>

 

<option value="france">FRANCE</option>

<option value="germany">GERMANY</option>

<option value="greece">GREECE</option>

<option value="india">INDIA</option>

<option value="iran">IRAN</option>

<option value="ireland">IRELAND</option>

<option value="israel">ISRAEL</option>

<option value="italy">ITALY</option>

<option value="lebanon">LEBANON</option>

 

<option value="macedonia">MACEDONIA</option>

<option value="madagascar">MADAGASCAR</option>

<option value="morocco">MOROCCO</option>

<option value="namibia">NAMIBIA</option>

<option value="nigeria">NIGERIA</option>

<option value="norway">NORWAY</option>

<option value="pakistan">PAKISTAN</option>

<option value="peru">PERU</option>

<option value="portugal">PORTUGAL</option>

 

<option value="russia">RUSSIA</option>

<option value="saudi arabia">SAUDI ARABIA</option>

<option value="south africa">SOUTH AFRICA</option>

<option value="spain">SPAIN</option>

<option value="sri lanka">SRI LANKA</option>

<option value="sweden">SWEDEN</option>

<option value="taiwan">TAIWAN</option>

<option value="turkey">TURKEY</option>

<option value="ukraine">UKRAINE</option>

 

<option value="usa">USA</option>

<input type="Submit" name="Submit" value="Submit">

</select>

</form><br>

 

Color Search:<br>

<form method="post" action="color_search.php">

<select name="search">

<option value="" selected=""></option>

<option value="beige">Beige</option>

<option value="black">Black</option>

<option value="blue">Blue</option>

 

<option value="brown">Brown</option>

<option value="gold">Gold</option>

<option value="green">Green</option>

<option value="grey">Grey</option>

<option value="pink">Pink</option>

<option value="purple">Purple</option>

<option value="red">Red</option>

<option value="white">White</option>

<option value="yellow">Yellow</option>

 

<input type="Submit" name="Submit" value="Submit">

</select>

</form>

<br>

 

Type of Stone Search:<br>

<form method="post" action="type_search.php">

<select name="search">

<option value="" selected=""></option>

<option value="caesarstone">Caesarstone</option>

<option value="granite">Granite</option>

<option value="limestone">Limestone</option>

<option value="marble">Marble</option>

 

<option value="onyx">Onyx</option>

<option value="quartzite">Quartzite</option>

<option value="silestone">Silestone</option>

<option value="slate">Slate</option>

<option value="soapstone">Soapstone</option>

<option value="travertine">Travertine</option>>

<input type="Submit" name="Submit" value="Submit">

</select>

</form>

   

   

   

    </body>

 

</html>

------------------------------------------------------------------------------------------------------------------

 

as you can see the user can only search one of them at a time.

 

here is the php code.

 

-----------------------------------------------------------------------------------------------------------------

<?

//connect to mysql

//change user and password to your mySQL name and password

mysql_connect("localhost","user","password");

 

//select which database you want to edit

mysql_select_db("database");

 

$search=$_POST["search"];

 

//get the mysql and store them in $result

//change whatevertable to the mysql table you're using

//change whatevercolumn to the column in the table you want to search

$result = mysql_query("SELECT * FROM stonelist WHERE country LIKE '%$search%'");

 

//grab all the content

while($r=mysql_fetch_array($result))

{

  //the format is $variable = $r["nameofmysqlcolumn"];

  //modify these to match your mysql table columns

 

  $thumb=$r["thumb"];

  $name=$r["name"];

  $type=$r["type"];

  $country=$r["country"];

  $slink=$r["slink"];

 

 

  //display the row

  echo '<IMG SRC="',$thumb,'"  ALT="',$name,'" /> | ',$name,' | ',$type,' | ',$country,' | <a href="',$slink,'">View Larger Image</a> <br>';

 

}

?>

-----------------------------------------------------------------------------------------------------------------------

 

i have one file for each field that i want searched.