PHP MYSQL Search with multiple WHERE conditions

[jattsurma]

Hello Everyone,

 

I'm new here so "Hi" to all the php freaks out there. I'm new to php as well but I'm getting hang of things pretty quickly. I have one issue that I'm struggling with so your help will be greatly appreciated. Here is my problem:

 

I'm trying a create a simple boolean search function with multiple WHERE conditions. Here is the code:

if($_GET['terms']){
$terms = $_GET['terms'];
$myAgents = "SELECT * FROM userdata WHERE MATCH(fName,lName) AGAINST('$terms' IN BOOLEAN MODE) AND WHERE account_type='agent' AND WHERE manager_id='".$_SESSION['manager_id']."' ORDER BY fName ASC";
//echo $myAgents;
mysql_query("$myAgents") or die(mysql_error());
}

 

But for some reason it is not working and I'm getting this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE account_type='agent' AND WHERE manager_id='222222' ORDER BY fName ASC' at line 1

 

Can someone please tell me what am I doing wrong? Thanks in advance for your suggestions and help!

 

Jatt Surma

 

[Maq]

"AND WHERE" should just be "AND".  Same with the other clause.

[jattsurma]

Hello Maq,

 

Thanks a lot! That took care of the error part.

 

for some reason, it is not working. I'm not getting any errors neither any results. I know all the variables have the right values as well as the MYSQL table.

 

Any other suggestions? Thanks again for your help.

 

Jatt Surma

[Maq]

Show us the code that is displaying the results.

 

Have you echoed out '$myAgents' to ensure the values are correct and the DB to make sure there is at least 1 match?

[jattsurma]

Hello Maq,

 

Thanks again! I have 2 records in MYSQL table and they both match with the query. Here is result of $myAgents echo:

 

SELECT * FROM userdata WHERE MATCH(fName,lName) AGAINST('John' IN BOOLEAN MODE) AND account_type='agent' AND manager_id='222222' ORDER BY fName ASC

 

Here is rest of the code:

 

<?php
echo'<div align="left"><form name="search" method="get" action="'.$site_url.'portal/my-agents.php?do=search'.'">
<input type="text" name="terms" size="20" value="'.$_GET['terms'].'"><input type="submit" value="Find">
</form></div>
';
if($_GET['terms']){
$terms = $_GET['terms'];
$myAgents = "SELECT * FROM userdata WHERE MATCH(fName,lName) AGAINST('$terms' IN BOOLEAN MODE) AND account_type='agent' AND manager_id='".$_SESSION['manager_id']."' ORDER BY fName ASC";
//echo $myAgents;
mysql_query($myAgents) or die(mysql_error());
}
else{
$myAgents = mysql_query("SELECT * FROM userdata WHERE account_type='agent' AND manager_id=".$_SESSION['manager_id']);
}
$tMyAgents = @mysql_num_rows($myAgents);
echo'
<table width="100%" border="0" cellspacing="0" cellpadding="6">
<tr>
<td valign="top" align="left">Name:</td>
<td valign="top" align="left">Leads:</td>
<td valign="top" align="left">Contracts:</td>
<td valign="top" align="left">Status:</td>
</tr>';
for($i=1;$i<=$tMyAgents;$i++){
$agent = mysql_fetch_array($myAgents);
$leads = mysql_num_rows(mysql_query("SELECT project_id FROM projects WHERE agent_id=".$agent['agent_id']." AND status='LEAD'"));
$contracts = mysql_num_rows(mysql_query("SELECT project_id FROM projects WHERE agent_id=".$agent['agent_id']." AND status='CONTRACT'"));
echo'<tr>
<td valign="top" align="left">'.$agent['fName'].' '.$agent['lName'].'</td>
<td valign="top" align="left">'.$leads.'</td>
<td valign="top" align="left">'.$contracts.'</td>
<td valign="top" align="left">'.$agent['status'].'</td>
</tr>';
}
echo'</table>';

?>

 

As you can see that there are 2 queries 1 with search and 1 without search. One without search works perfectly. So its something with the search query.

 

Thanks again! I appreciate your help.

 

Jatt Surma