realjumper Posted April 12, 2007 Share Posted April 12, 2007 Hi, In my db I have id, first_name, last_name fields. From the php page I want to search the db and return the results that the user enters into a form field named 'name'. This much works fine...... $query = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '$name' OR last_name LIKE '$name' ORDER BY first_name"; $result = mysql_query($query); The above query works as expected if the user is searching by first_name or last_name. What I am having a problem with is how to construct the query if the user enters a first name and a last name into the form field, rather than a first name or a last name. I have tried playing with the IN condition but I think I'm looking at this wrong. Any help would be great. Thanks Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/ Share on other sites More sharing options...
trq Posted April 12, 2007 Share Posted April 12, 2007 If they are putting both names in the one textbox separated by a space.... <?php if (isset($_POST['name'])) { $namefield = mysql_real_escape_string($_POST['name']); $names = explode(' ', $namefield); if (count($names) > 1) { $sql = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '{$names[0]}' OR last_name LIKE '{$names[1]}' ORDER BY first_name"; } else { $sql = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '{$names[0]}' OR last_name LIKE '{$names[0]}' ORDER BY first_name"; } if ($result = mysql_query($sql)) { if (mysql_num_rows($result)) { $row = mysql_fetch_assoc($result); // display data. } } } ?> Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227351 Share on other sites More sharing options...
MadTechie Posted April 12, 2007 Share Posted April 12, 2007 What about <?php if( isset($_GET['last_name']) && isset($_GET['first_name']) ) { $OP = "AND" }else{ $OP = "OR" } $query = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '$name' $OP last_name LIKE '$name' ORDER BY first_name"; $result = mysql_query($query); ?> EDIT: thorpe just kicked my ***** Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227352 Share on other sites More sharing options...
realjumper Posted April 12, 2007 Author Share Posted April 12, 2007 Thank you both....I will work through this and see what I can learn and fit inot my code :-) Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227357 Share on other sites More sharing options...
realjumper Posted April 12, 2007 Author Share Posted April 12, 2007 I have just fitted the code kindly supplied by Thorp into my application and I can see how this works now. I'll have to modify my code which loops through the results of the search, but this is going to work out fine. Thank you (both) very much :-) Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227366 Share on other sites More sharing options...
trq Posted April 12, 2007 Share Posted April 12, 2007 Your welcome, I'll mark the thread resolved. Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227372 Share on other sites More sharing options...
realjumper Posted April 12, 2007 Author Share Posted April 12, 2007 Sorry, just one more thing.....when I echo out $num_rows, it show the number of records returned unless the number returned is zero. Obviously I need to let the user know if his search found no result, but if I echo $num_rows when I know for certain that there is no record, all I get is white space, but I expected '0'. Have I missed something obvious? Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227394 Share on other sites More sharing options...
Glyde Posted April 12, 2007 Share Posted April 12, 2007 0, as an integer, when output by PHP, is interpreted basically as nothing. The simple way of fixing this is by forcing a string data type: $num_rows = (string) mysql_num_rows($result); This should resolve your problem. Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227395 Share on other sites More sharing options...
realjumper Posted April 12, 2007 Author Share Posted April 12, 2007 Thanks....but still have the same problem. Any records returned are recorded by $num_rows, except when there are no records returned....I still get 'nothing!!' Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227396 Share on other sites More sharing options...
rcorlew Posted April 12, 2007 Share Posted April 12, 2007 Try this, I use it all the time. <?php if($num_rows > 0) { //Put your stuff here } if($num_rows == 0) { echo "We are sorry there were no results found"; } ?> Link to comment https://forums.phpfreaks.com/topic/46669-select-query-help-please/#findComment-227412 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.