Jump to content

SELECT query help please


realjumper

Recommended Posts

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

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.
      }
    }
  }

?>

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 *****

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.