Hi,

 

I have a database with details for 2 customers - title, forename and surname x2. I have a search function to enable the user to search by surname....thing is - I want the user to be able to find a record that has 2 customers, in which case, both surnames need to be searched based on one name search i.e. the search will pull up a record based on either surname.

 

Is there a way to do this?

Do you mean the table in your database is like this...

 

 

because I'm rather confusled

The user is entering two different names to search for? Or there are multiple records of the same surname in the table?

Sorry!!

 

I have a table:

 

title1, firstname1, surname1, title2, firstname2, surname2...

 

The records are pulled from the database showing:

 

Customer 1, Customer 2, etc.... Where Customer1 is made up of combining title, firstname and surname.

 

I have a search function which I have added a drop down to i.e. search by 'customer ID', 'surname', 'date submitted', etc...

 

I'd like it so that if the user searches by surname - the search will check surname1 and surname2 and display the appropriate record.

cant you just do something like

WHERE `surname1` LIKE '%$surname%' OR `surname2` LIKE '%$surname%'

cant you just do something like

WHERE `surname1` LIKE '%$surname%' OR `surname2` LIKE '%$surname%'

 

Most likely! However, the search pulls through the value from the option...

 

So in my query it's where ". $option ." = \"%trimmed%\".

I'm still stuck on this.....

 

I've tried this to no avail:

 

HTML:

<form name="searchform" action="search.php" method="get">
<p><label id="search">Search by</label></p><select name="searchby">
   <option value="surnames">Surname</option>
<option value="id">Customer ID</option>
   <option value="date_submitted">Date submitted</option>
   <option value="adviser">Adviser</option>
</select> etc...

and php:

  $option = $_GET['searchby'];
 $var = @$_GET['q'];
 $trimmed = trim($var); 

if(isset($option) == 'surnames'){
$surnamequery = "select * from debtman where \"%trimmed%\" like ('surname1' or 'surname2')";
$numresults=mysql_query($surnamequery);
$numrows=mysql_num_rows($numresults);
if(!$numresults)die(mysql_error());
}

//If search option is not 'surnames'	
if(isset($option) == 'id' || 'date_submitted' || 'adviser'){
$searchquery = "select * from debtman where $option like \"%$trimmed%\" order by surname1"; 

$numresults=mysql_query($searchquery);
$numrows=mysql_num_rows($numresults);
 if(!$numresults)die(mysql_error());
 }

 

I get this error:

mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/wwwetc/.../.../ on line 113

Unknown column 'surnames' in 'where clause'

 

Can anyone help please?

why not have the names in a seperate table and then link that back to the main table. ie. a 'names' table that has the customer ID and 'name' number. Then you can have more than 2 names linked to a customer and be able to search that table instead.

why not have the names in a seperate table and then link that back to the main table. ie. a 'names' table that has the customer ID and 'name' number. Then you can have more than 2 names linked to a customer and be able to search that table instead.

 

I could do I guess...but I was hoping I could get it working this way. Logically, it should work.....but then sometimes php isn't logical!

Just realised my issue! In the second query - it's trying to find 'surnames' as it's an option.....

lol and just when you do, 3 months down the line someone will say "can we have 3 names?"

lol and just when you do, 3 months down the line someone will say "can we have 3 names?"

 

Hmmmm.....How do I fix it then?

 

Can I set 2 different variables depending on what the 'searchby' option is?

 

i.e. if it's 'surnames' = $option1

 

but if it's id, date_submitted or adviser = $option2??

The search completely stopped working, so I've tried this..everything works apart from the surname search!:

 

if(isset($option) == 'surnames'){
$surnamequery = "select * from debtman where ($option or surname2) like \"%trimmed%\"";
$numresults=mysql_query($surnamequery);
$numrows=mysql_num_rows($numresults);
if(!$numresults)die(mysql_error());
}

//If search option is not 'surnames'	
if(isset($option) == 'id' || 'date_submitted' || 'adviser'){
$searchquery = "select * from debtman where $option like \"%$trimmed%\" order by date_submitted"; 

$numresults=mysql_query($searchquery);
$numrows=mysql_num_rows($numresults);
 if(!$numresults)die(mysql_error());
 }

I won't need more. I'm positive.

 

My search doesn't work

Try

 

<?php

if(isset($option)) {
if($option == 'surnames') {
	$sql = "select * from debtman where ((`surname1` LIKE '%".$trimmed."%\') OR (`surname2` LIKE '%".$trimmed."%\'))";
} elseif ($option == 'id' || $option == 'date_submitted' || $option == 'adviser') { 
	$sql = "select * from debtman where $option like \"%$trimmed%\" order by date_submitted"; 
} else {
	$sql = "select * from debtman where 1==2";
}
$numresults=mysql_query($sql);
$numrows=mysql_num_rows($numresults);
if(!$numresults)die(mysql_error());
}

 

Notice I put that spare else in, you'll probably need to add another elseif for date submitted because IIRC you cant do a LIKE on a date.

Try

 

<?php

if(isset($option)) {
if($option == 'surnames') {
	$sql = "select * from debtman where ((`surname1` LIKE '%".$trimmed."%\') OR (`surname2` LIKE '%".$trimmed."%\'))";
} elseif ($option == 'id' || $option == 'date_submitted' || $option == 'adviser') { 
	$sql = "select * from debtman where $option like \"%$trimmed%\" order by date_submitted"; 
} else {
	$sql = "select * from debtman where 1==2";
}
$numresults=mysql_query($sql);
$numrows=mysql_num_rows($numresults);
if(!$numresults)die(mysql_error());
}

 

Notice I put that spare else in, you'll probably need to add another elseif for date submitted because IIRC you cant do a LIKE on a date.

 

Didn't work

 

So frustrating - I can't see any reason why this isn't working? It's a perfectly correct mysql statement.

okay, step back. Have you got cpanel and myphpadmin? Does your SQL statement get any results using it there?

If it is then that may suggest something wrong elsewhere in your code.

$sql = "select * from debtman where $option like \"%$trimmed%\" order by date_submitted";

 

 

Does not look right to me, $option is included in the string rather than added to the string as a variable. I am not a php developer so I maybe wrong but shouldnt it be something like:

 

 

$sql = "select * from debtman where " . $option . " like \"%" . $trimmed . "\"% order by date_submitted";

 

 

Not sure exactly where the percentage signs (%) go before or after the escaped quotes (\").

 

 

When I used to write sql in asp (do not need to any more) I often had issues with the sql string itself, if you can ehco it out to the page so you can check it is exactly what you expect.

I think its the sql syntax.

 

for the like queries, try this :

 

 

$sql = "SELECT * FROM debtman WHERE (`surname1` LIKE '%$trimmed%' OR `surname2` LIKE '%$trimmed%') ";

 

 

$sql = "SELECT * FROM debtman WHERE $option LIKE '%$trimmed%' ORDER BY date_submitted";

What about Jock's code didn't work? Modify it as below and post the output.

 

<?php
if(isset($option)) {
       if($option == 'surnames') {
               $sql = "select * from debtman where ((`surname1` LIKE '%".$trimmed."%\') OR (`surname2` LIKE '%".$trimmed."%\'))";
       } elseif ($option == 'id' || $option == 'date_submitted' || $option == 'adviser') { 
               $sql = "select * from debtman where $option like \"%$trimmed%\" order by date_submitted"; 
       } else {
               $sql = "select * from debtman where 1==2";
       }

       // Debug

       if(!$query = mysql_query($sql)) {
           echo "An error occured on line ".__LINE__.".<br /><br />\n"
           .    "MySQL said: ".mysql_error()."<br /><br />\n"
           .    "Query: \"".$sql."\"<br /><br />\n";
           exit;
       }
       $num_results = mysql_num_rows($query);
       if($num_results == "0") {
           echo "Query returned an empty results set.<br /><br />\n"
           .    "Query: \"".$sql."\"<br /><br />\n";
       } else {
           echo "Success! Query returned results.\n";
       }

       // End debugging

       // $numresults=mysql_query($sql);
       // $numrows=mysql_num_rows($numresults);
       // if(!$numresults)die(mysql_error());
}
?>

Adding simple snippets like this has saved me days of time over the years, and made it much easier for people to help me when I couldn't fix the problem myself.