Help with PHP and MySQL query

lbh2011 · February 27, 2011

Hello!

Please help... I am trying to use the script below to get results from a mysql database based on a query of the form fields (the names of which are displayed near the top of the script as POST items)

When a location or age etc. is entered into the form, I want the script to search for records which meet those criteria. At the moment the script works but only does so if all the values are entered to match what is in the database. e.g. if the location england and the age 22 was entered into the form, and that matched the value in the database, then at the moment, the script will display the result, but if only the location is entered in the form without any value for age/genre etc. then no results are displayed.

Any help would be very welcome as I have search high and low for a solution on google... which doesn't seem to exist...

I'm not that experienced with php/mysql but am learning on the job so any helpful prompts as to terms etc. would help!

Thanks!

Lewis

<?php 

if($_POST) 
{ 
$searchage = $_POST['searchage'];
$searchlocation = $_POST['searchlocation'];
$searchgenre = $_POST['searchgenre']; 
$searchinstrument = $_POST['searchinstrument']; 
$searchexperience = $_POST['searchexperience']; 

// Connects to your Database 
mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
mysql_select_db("DB") or die(mysql_error()); 

$query = mysql_query("SELECT * FROM table_user WHERE userage = '".$searchage."' AND userlocation = '".$searchlocation."' AND usergenre = '".$searchgenre."' AND userinstrument = '".$searchinstrument."' AND userexperience = '".$searchexperience."'") or die(mysql_error()); 

$num = mysql_num_rows($query); 

echo "$num results found!<br>"; 

while($result = mysql_fetch_assoc($query)) 
{ 
$username = $result['username']; 
$useremail = $result['useremail'];
$userage = $result['userage'];
$userlocation = $result['userlocation'];
$usergenre = $result['usergenre'];
$userinstrument = $result['userinstrument']; 
$userexperience = $result['userexperience']; 
$userbiography = $result['userbiography']; 

echo "
Name: $username<br> 
Email: $useremail<br>
Age: $userage<br>
Location: $userlocation<br>
Genre: $usergenre<br>
Instrument: $userinstrument<br>
Experience: $userexperience<br>
Biography: $userbiography<br><br>       
"; 
} 


} 

?>

denno020 · February 27, 2011

In your query, you need to say WHERE this OR that OR somethingElse.

Saying AND, means that each of the items needs to be met, using OR means one, or multiple can be met.

Denno

Psycho · February 27, 2011

<?php 

if(isset($_POST['username'])) 
{ 
    // Connects to your Database 
    mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
    mysql_select_db("DB") or die(mysql_error()); 
    
    //Create where clause
    $postNames = array('searchage', 'searchlocation', 'searchgenre',
                       'searchinstrument', 'searchexperience');
    $where_parts = array();
    $where_clause = '';
    foreach($postNames as $name)
    {
        if(isset($_POST[$name]) && trim($_POST[$name])!='')
        {
            $where_parts[] = mysql_real_escape_string(trim($_POST[$name]));
        }
    }
    if(count($where_parts)>0)
    {
        $where_clause = " WHERE " . implode(' AND ', $where_parts);
    }
    $query = "SELECT * FROM table_user {$where_clause}";

    $result = mysql_query($query) or die(mysql_error()); 

    $num = mysql_num_rows($result); 
    echo "$num results found!<br>"; 

    while($row = mysql_fetch_assoc($query)) 
    { 
        echo "
        Name: {$row['username']}<br> 
        Email: {$row['useremail']}<br>
        Age: {$row['userage']}<br>
        Location: {$row['userlocation']}<br>
        Genre: {$row['usergenre']}<br>
        Instrument: {$row['userinstrument']}<br>
        Experience: {$row['userexperience']}<br>
        Biography: {$row['userbiography']}<br><br>       
        "; 
    } 
} 

?>

lbh2011 · February 27, 2011

I have tried the suggestion you made, but just get a blank page? It might help if I show the form code below - thanks for your help so far!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form action="process.search.musician.php" method="post" >
<table width="200" border="1">
  <tr>
    <td>Search Form</td>
    <td> </td>
  </tr>
  <tr>
    <td>DOB</td>
    <td><label>
      <select name="searchage" id="searchage">
        <option value="%">Please Select</option>
        <option value="15 to 19">15 to 19</option>
        <option value="20 to 24">20 to 24</option>
        <option value="25 to 29">25 to 29</option>
        <option value="30 to 34">30 to 34</option>
        <option value="35 to 39">35 to 39</option>
        <option value="40 to 44">40 to 44</option>
        <option value="45 to 49">45 to 49</option>
        <option value="50 to 54">50 to 54</option>
        <option value="55 to 59">55 to 59</option>
        <option value="60 +">60 +</option>
              </select>
    </label></td>
  </tr>
  <tr>
    <td>Location</td>
    <td><label>
      <select name="searchlocation" id="searchlocation">
        <option value="%">Please Select</option>
        <option value="Aberdeenshire">Aberdeenshire</option>
        <option value="Anglesey">Anglesey</option>
        <option value="Angus">Angus</option>
        <option value="Argyll">Argyll</option>
        <option value="Ayrshire">Ayrshire</option>
        <option value="Banffshire">Banffshire</option>
        <option value="Bedfordshire">Bedfordshire</option>
        <option value="Berwickshire">Berwickshire</option>
        <option value="Breconshire">Breconshire</option>
        <option value="Buckinghamshire">Buckinghamshire</option>
        <option value="Bute">Bute</option>
        <option value="Caernarvonshire">Caernarvonshire</option>
        <option value="Caithness">Caithness</option>
        <option value="Cambridgeshire">Cambridgeshire</option>
        <option value="Cardiganshire">Cardiganshire</option>
        <option value="Carmarthenshire">Carmarthenshire</option>
        <option value="Cheshire">Cheshire</option>
        <option value="Clackmannanshire">Clackmannanshire</option>
        <option value="Cornwall and Isles of Scilly">Cornwall and Isles of Scilly</option>
        <option value="Cumbria">Cumbria</option>
        <option value="Denbighshire">Denbighshire</option>
        <option value="Derbyshire">Derbyshire</option>
        <option value="Devon">Devon</option>
        <option value="Dorset">Dorset</option>
        <option value="Dumbartonshire">Dumbartonshire</option>
        <option value="Dumfriesshire">Dumfriesshire</option>
        <option value="Durham">Durham</option>
        <option value="East Lothian">East Lothian</option>
        <option value="East Sussex">East Sussex</option>
        <option value="Essex">Essex</option>
        <option value="Fife">Fife</option>
        <option value="Flintshire">Flintshire</option>
        <option value="Glamorgan">Glamorgan</option>
        <option value="Gloucestershire">Gloucestershire</option>
        <option value="Greater London">Greater London</option>
        <option value="Greater Manchester">Greater Manchester</option>
        <option value="Hampshire">Hampshire</option>
        <option value="Hertfordshire">Hertfordshire</option>
        <option value="Inverness">Inverness</option>
        <option value="Kent">Kent</option>
        <option value="Kincardineshire">Kincardineshire</option>
        <option value="Kinross-shire">Kinross-shire</option>
        <option value="Kirkcudbrightshire">Kirkcudbrightshire</option>
        <option value="Lanarkshire">Lanarkshire</option>
        <option value="Lancashire">Lancashire</option>
        <option value="Leicestershire">Leicestershire</option>
        <option value="Lincolnshire">Lincolnshire</option>
        <option value="London">London</option>
        <option value="Merionethshire">Merionethshire</option>
        <option value="Merseyside">Merseyside</option>
        <option value="Midlothian">Midlothian</option>
        <option value="Monmouthshire">Monmouthshire</option>
        <option value="Montgomeryshire">Montgomeryshire</option>
        <option value="Moray">Moray</option>
        <option value="Nairnshire">Nairnshire</option>
        <option value="Norfolk">Norfolk</option>
        <option value="North Yorkshire">North Yorkshire</option>
        <option value="Northamptonshire">Northamptonshire</option>
        <option value="Northumberland">Northumberland</option>
        <option value="Nottinghamshire">Nottinghamshire</option>
        <option value="Orkney">Orkney</option>
        <option value="Oxfordshire">Oxfordshire</option>
        <option value="Peebleshire">Peebleshire</option>
        <option value="Pembrokeshire">Pembrokeshire</option>
        <option value="Perthshire">Perthshire</option>
        <option value="Radnorshire">Radnorshire</option>
        <option value="Renfrewshire">Renfrewshire</option>
        <option value="Ross & Cromarty">Ross & Cromarty</option>
        <option value="Roxburghshire">Roxburghshire</option>
        <option value="Selkirkshire">Selkirkshire</option>
        <option value="Shetland">Shetland</option>
        <option value="Shropshire">Shropshire</option>
        <option value="Somerset">Somerset</option>
        <option value="South Yorkshire">South Yorkshire</option>
        <option value="Staffordshire">Staffordshire</option>
        <option value="Stirlingshire">Stirlingshire</option>
        <option value="Suffolk">Suffolk</option>
        <option value="Surrey">Surrey</option>
        <option value="Sutherland">Sutherland</option>
        <option value="Tyne and Wear">Tyne and Wear</option>
        <option value="Warwickshire">Warwickshire</option>
        <option value="West Lothian">West Lothian</option>
        <option value="West Midlands">West Midlands</option>
        <option value="West Sussex">West Sussex</option>
        <option value="West Yorkshire">West Yorkshire</option>
        <option value="Wigtownshire">Wigtownshire</option>
        <option value="Wiltshire">Wiltshire</option>
        <option value="Worcestershire">Worcestershire</option>
              </select>
    </label></td>
  </tr>
  <tr>
    <td>Genre</td>
    <td><label>
      <select name="searchgenre" id="searchgenre">
        <option value="%">Please Select</option>
        <option value="Alternative & Punk">Alternative & Punk</option>
        <option value="Blues">Blues</option>
        <option value="Books & Spoken">Books & Spoken</option>
        <option value="Children?s Music">Children?s Music</option>
        <option value="Classical">Classical</option>
        <option value="Country">Country</option>
        <option value="Data">Data</option>
        <option value="Easy Listening">Easy Listening</option>
        <option value="Electronica/Dance">Electronica/Dance</option>
        <option value="Folk">Folk</option>
        <option value="Gospel & Religious">Gospel & Religious</option>
        <option value="Hip Hop/Rap">Hip Hop/Rap</option>
        <option value="Holiday">Holiday</option>
        <option value="Industrial">Industrial</option>
        <option value="Jazz">Jazz</option>
        <option value="Latin">Latin</option>
        <option value="Metal">Metal</option>
        <option value="New Age">New Age</option>
        <option value="Pop">Pop</option>
        <option value="R&B">R&B</option>
        <option value="Reggae">Reggae</option>
        <option value="Rock">Rock</option>
        <option value="Soundtrack">Soundtrack</option>
        <option value="Unclassifiable">Unclassifiable</option>
        <option value="World">World</option>
              </select>
    </label></td>
  </tr>
  <tr>
    <td>Instrument</td>
    <td><select name="searchinstrument" id="searchinstrument">
      <option value="%">Please Select</option>
      <option value="Bagpipes">Bagpipes</option>
        <option value="Banjo">Banjo</option>
        <option value="Bass drum">Bass drum</option>
        <option value="Bassoon">Bassoon</option>
        <option value="Bell">Bell</option>
        <option value="Bongo">Bongo</option>
        <option value="Castanets">Castanets</option>
        <option value="Cello">Cello</option>
        <option value="Clarinet">Clarinet</option>
        <option value="Clavichord">Clavichord</option>
        <option value="Conga drum">Conga drum</option>
        <option value="Contrabassoon">Contrabassoon</option>
        <option value="Cornet">Cornet</option>
        <option value="Cymbals">Cymbals</option>
        <option value="Double bass">Double bass</option>
        <option value="Dulcian">Dulcian</option>
        <option value="Dynamophone">Dynamophone</option>
        <option value="Flute">Flute</option>
        <option value="Flutophone">Flutophone</option>
        <option value="Glockenspiel">Glockenspiel</option>
        <option value="Gongs">Gongs</option>
        <option value="Guitar">Guitar</option>
        <option value="Harmonica">Harmonica</option>
        <option value="Harp">Harp</option>
        <option value="Harpsichord">Harpsichord</option>
        <option value="Lute">Lute</option>
        <option value="Mandolin">Mandolin</option>
        <option value="Maracas">Maracas</option>
        <option value="Metallophone">Metallophone</option>
        <option value="Musical box">Musical box</option>
        <option value="Oboe">Oboe</option>
        <option value="Ondes-Martenot">Ondes-Martenot</option>
        <option value="Piano">Piano</option>
        <option value="Recorder">Recorder</option>
        <option value="Saxophone">Saxophone</option>
        <option value="Shawm">Shawm</option>
        <option value="Snare drum">Snare drum</option>
        <option value="Steel drum">Steel drum</option>
        <option value="Tambourine">Tambourine</option>
        <option value="Theremin">Theremin</option>
        <option value="Triangle">Triangle</option>
        <option value="Trombone">Trombone</option>
        <option value="Trumpet">Trumpet</option>
        <option value="Tuba">Tuba</option>
        <option value="Ukulele">Ukulele</option>
        <option value="Viola">Viola</option>
        <option value="Violin">Violin</option>
        <option value="Xylophone">Xylophone</option>
        <option value="Zither">Zither</option>
                    </select></td>
  </tr>
  <tr>
    <td>Experience</td>
    <td><label>
    <select name="searchexperience" id="searchexperience">
      <option value="%">Please Select</option>
      <option value="Beginner">Beginner</option>
      <option value="Intermediate">Intermediate</option>
      <option value="Advanced">Advanced</option>
      <option value="Professional">Professional</option>
          </select>
    </label></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td><label>
      <input type="submit" name="submit" id="submit" value="search" />
    </label></td>
  </tr>
</table>

</form>
</body>
</html>

lbh2011 · February 27, 2011

Any ideas?

Psycho · February 27, 2011

I changed your original IF statement because this

if($_POST)

will always return true. So, I changed it to check if one of the form inputs was set, but inadvertantly set it to the wrong name. So, it was always returning false and the search code wasn't run. Change it to this:

if(isset($_POST['searchage']))

lbh2011 · February 27, 2011

Hi mjdamato

thanks for the reply. I have changed the IF POST as you suggested, but now if I filter by instrument or any of the other dropdowns and in the form I submitted in the other post, then the script returns the result (when the instrument banjo is selected from the instrument drop down):

Unknown column 'Bagpipes' in 'where clause'

I'm no expert, but to me, Banjo should be a value in the user record not the column which should be "userinstrument"

If the script is submitted with no filters however, instead of all the resuts being displayed, the following is shown:

3 results found!

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/fluffyd1/public_html/online/muzomag/process.search.musician.php on line 26

flolam · February 27, 2011

do you have single quotes around the values of the columns? i. e.

 .... WHERE column = 'value'

lbh2011 · February 27, 2011

I'm not sure, I used the code mjdamato suggested above... can you work out whether this has what you mentioned?

flolam · February 27, 2011

could you echo the $query variable just before or after executing the sql and post the result?

lbh2011 · February 27, 2011

I inserted echo "$query"; before echo "$num results found!<br>"; in the code above. It seems to generate the same error.

flolam · February 27, 2011

I'm sorry, you need to insert it before executing the mysql of course. otherwise script execution is interrupted by the error before it is echoed

lbh2011 · February 27, 2011

I placed it just after the first mention of $query here:

$query = "SELECT * FROM musician_user {$where_clause}";	
echo "$query";
$result = mysql_query($query) or die(mysql_error()); 
$num = mysql_num_rows($result);

The result in the browser was SELECT * FROM musician_user WHERE ArgyllUnknown column 'Argyll' in 'where clause'

flolam · February 27, 2011

//replace this
$where_parts[] = mysql_real_escape_string(trim($_POST[$name]));

//with this
$where_parts[] = $name." = '".mysql_real_escape_string(trim($_POST[$name]))."'";

your $query will look like this: SELECT * FROM table_user WHERE searchage = '25' AND searchgenre = 'foo'

flolam · February 27, 2011

Are you using the correct table? I noticed that in your initial post you used table_user (and I copied that one), while in a later post you changed it to musician_user.

lbh2011 · February 27, 2011

Yes. The correct table is musician_user. I don't know if this could be the problem, but the field names in the form are called search??? and the column names in the table are called user??? could that be the issue?

flolam · February 27, 2011

that definitely is the issue

<?php
$where_parts[] = str_replace("search", "user", $name)." = '".mysql_real_escape_string(trim($_POST[$name]))."'";
?>

lbh2011 · February 27, 2011

I have put that in but get this output:

SELECT * FROM musician_user

2 results found!

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/fluffyd1/public_html/online/muzomag/process.search.musician.php on line 28

flolam · February 27, 2011

I guess line 28 is this one?

while($row = mysql_fetch_assoc($query))

it needs to be while($row = mysql_fetch_assoc($result))

If this doesn't work, please post your complete code again so we see what it looks like after all the modifications

lbh2011 · February 27, 2011

Brilliant - It works!

The only issue I have is that when no results are available, the script still displays the echo name, location, age part. is there an if statement which can be used to only display the records when the number of records is greater than 0?

Thanks so much though!

flolam · February 27, 2011

if ($num != 0)

You're welcome, though you could have thought of that one yourself ...

lbh2011 · February 27, 2011

You're right I could! but got that working so all is well!

As I'm new here, do you know how I can mark this thread as solved?

Thanks again.

lbh2011 · February 28, 2011

I have had a chance to review the suggestions made, and have found that only one result is displayed when anything upto the number of records in the database should be. I assume this can be resolved using foreach() or similar function. The updated code is below, which has taken into account all the suggestions made in this forum. Any suggestion for how to get it working are welcome!

	<?php if(isset($_POST['searchage'])) {     
// Connects to your Database     
mysql_connect("host", "user", "pass") or die(mysql_error()); 
mysql_select_db("db") or die(mysql_error());        
//Create where clause    	
$postNames = array('searchage', 'searchlocation', 'searchgenre', 'searchinstrument', 'searchexperience');
$where_parts = array();
    $where_clause = '';
    foreach($postNames as $name)
	    {        
		if(isset($_POST[$name]) && trim($_POST[$name])!='')
		        {            
$where_parts[] = str_replace("search", "user", $name)." = '".mysql_real_escape_string(trim($_POST[$name]))."'";					        }
						    }
							    if(count($where_parts)>0)    
								{        
								$where_clause = " WHERE " . implode(' AND ', $where_parts);
								    }    
									$query = "SELECT * FROM musician_user {$where_clause}";	
										$result = mysql_query($query) or die(mysql_error());      

																				     
$num = mysql_num_rows($result);     


										echo "$num results found!<br>";     

  

($row = mysql_fetch_assoc($result)) ;{											
         
										if ($num != 0) echo "
										Name: {$row['username']}<br>
										Email: {$row['useremail']}<br>
										Age: {$row['userage']}<br>
										Location: {$row['userlocation']}<br>
										Genre: {$row['usergenre']}<br>
										Instrument: {$row['userinstrument']}<br>
										Experience: {$row['userexperience']}<br>
										Biography: {$row['userbiography']}<br><br>
										"; 
										else echo "No results found";    
										} 
										} 
										?>

flolam · February 28, 2011

The SQL you produce looks something like this: SELECT * FROM table WHERE searchage = 25;

Note the equal sign, which means that only rows with this exact age will be selected. if you want to select all rows with a lower value, use < or <=

lbh2011 · February 28, 2011

Just to clarify, the issue is that if multiple records have the same location, and only the location filter is set, then multiple records should be displayed, not just the first.

Sign In

Help with PHP and MySQL query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information