lbh2011 Posted February 27, 2011 Share Posted 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> "; } } ?> Quote Link to comment Share on other sites More sharing options...
denno020 Posted February 27, 2011 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 27, 2011 Share Posted 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> "; } } ?> Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted February 27, 2011 Any ideas? Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 27, 2011 Share Posted 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'])) Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted February 27, 2011 do you have single quotes around the values of the columns? i. e. .... WHERE column = 'value' Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted February 27, 2011 I'm not sure, I used the code mjdamato suggested above... can you work out whether this has what you mentioned? Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted February 27, 2011 could you echo the $query variable just before or after executing the sql and post the result? Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted February 27, 2011 I inserted echo "$query"; before echo "$num results found!<br>"; in the code above. It seems to generate the same error. Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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' Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted 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' Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted February 27, 2011 that definitely is the issue <?php $where_parts[] = str_replace("search", "user", $name)." = '".mysql_real_escape_string(trim($_POST[$name]))."'"; ?> Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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! Quote Link to comment Share on other sites More sharing options...
flolam Posted February 27, 2011 Share Posted February 27, 2011 if ($num != 0) You're welcome, though you could have thought of that one yourself ... Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 27, 2011 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 28, 2011 Author Share Posted 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"; } } ?> Quote Link to comment Share on other sites More sharing options...
flolam Posted February 28, 2011 Share Posted 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 <= Quote Link to comment Share on other sites More sharing options...
lbh2011 Posted February 28, 2011 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.