PHP Drop-Box/Search Field

[adamduncan]

Hey guys,

 

I'm working on a project and basically what I need is one dropbox (for a musical instrument) and a regular search field (for location) to allow users to find a given musician in a given location - ie. Bassist in Perth.

 

I have a database full of members, each has a field for 'city' and 'instrument' and I was just wondering what is the best way to go about getting search results that include only users that match both criteria?

 

I'm pretty new to PHP/MySQL so any help would be greatly appreciated.

Thanks.

 

 

 

 

[eZe616]

You could make both fields dropboxes. That way you make sure that only cities that have been submitted can be searched with a specific instrument also. That's how I would do it.

As for the query string you could use something like this: 

 

<?php 
    // Query string to search database
    $sql = "SELECT * FROM Musician WHERE instrument = 'Bassist' AND location = 'Perth'";
   
?>

 

But this depends on how your database is structured. I.E if the musician is allowed to insert more than 1 instrument, I'd assume you have a separate table to hold the instruments then.

 

If you'd still want the regular input text field the query would be something like:

 

<?php
   //Query string to search
   $sql = " SELECT * FROM Musician WHERE instrument = 'Bassist' AND location LIKE '%Perth%'"; 
?>

 

[adamduncan]

Cool. That makes sense.

 

So with the HTML, my search form is:

 

<form method="get" action="search.php">
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Search">
</form>

 

What would be the correct syntax for a drop-box menu so that it links up with my PHP query?

 

I need something like;

-Bass

-Guitar

-Piano

etc.

 

More importantly, is there a way of populating the drop-box with the only current listings in my database?

[zenag]

you can populate ur database with ur database list...

[zenag]

sorry ur dropdown with database listings

[adamduncan]

Yeah, I figured it should be possible.

How exactly is it done?

[zenag]

<? $sql=mysql_query("select * from tablename");

<select name="">

while($result=mysql_fetch_array($sql))

 

{

<option value=""><? echo $result["fieldname"];?></option>

 

}

</select>

[adamduncan]

Alright, so this is what I have for the single search box and the PHP (this is all working fine):

 

<b>Find a musician by City:</b>

<form method="get" action="search.php">
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Search">
</form>


<b>Results:</b><br /><br />


<?php 

mysql_connect ("localhost", "SERVER", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("DATABASE") or die ('I cannot connect to the database because: ' . mysql_error());



$search=$_GET["search"];

//get the mysql and store them in $result
$result = mysql_query("SELECT * FROM listings WHERE city LIKE '%$search%'");

//grab all the content
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   $full_name=$r["full_name"];
   $email=$r["email"];
   $city=$r["city"];
   $state=$r["state"];
   $instrument=$r["instrument"];
   $influences=$r["influences"];
   $available=$r["available"];
   
   //display the row
   echo "<b>Full Name:</b> $full_name <br /><b>Email:</b> $email <br /><b>City:</b> $city, $state<br /><b>Instrument:</b> $instrument <br /><b>Influences:</b> $influences <br /><b>Available:</b> $available <br /><br />";

}

?>

 

What I need to do is alter this code to include the Instrument dropbox parameter in the query... It's not happening for me. I'm guessing I need a bit of HTML and some tweaking of the '$result' but I really don't know exactly what. Any re-write or clear help would be greatly appreciated.

 

Thanks heaps.

[zenag]

while($r=mysql_fetch_array($result))
{	

echo "<select name=''>";
while($result=mysql_fetch_array($sql))

{
echo "<option value=''><? echo $r["instrument"];?></option>";

}
echo "</select>";

[adamduncan]

Sorry mate, I really appreciate your help with the bits of code, but being so new to this, could you maybe let me know which bit of code to substitute that for/what to leave in or out etc.?

[zenag]

in which place do u really need drop down ...in form or in sql query???& what ur drop down actually need to do??