Need help with a where clause

[zychos]

I am trying to create a where clause that loops through my foreach loop. The idea is to have a search with four possible values depending on which values the user fills in.

,<?php 
$fname = $_GET['fname'];
$lname = $_GET['lname'];
$nature =$_GET['nature'];
$ticket_id = $_GET['ticket_id'];
$nbsp = " ";

$fields = array('fname', 'lname', 'ticket_id', 'nature');

$whereClause = "WHERE" ;
foreach ($fields as $field)
    {
        if(isset($_GET[$field]) and !empty($_GET[$field]))
        {
        $whereClause .= $field . " LIKE '%" . $_GET[$field] . "%' OR "; 
        }


    //  else { return "no results found";
    //  }
    } 
substr($whereClause, 0, -4);    
$query = ("SELECT fname, lname, ticket_id, nature FROM trouble_ticket = '$whereClause' ");

$result = mysql_query($query)or die($query."<br/><br/>".mysql_error()); 

 while($row = mysql_fetch_array( $result )) // calls the information from the db places it in a table
 { 
 echo "<tr>"; 
 echo "<td bgcolor= white>".$row['fname']. " ".$nbsp. " ".$row['lname']. " </td>";
 echo "<td bgcolor= white>".$row['lname']. " </td>";
 echo "<td bgcolor= white>".$row['ticket_id']. "</td> ";
 echo "<td bgcolor= white>".$row['nature']. " </td>";
 echo"</tr>"; 
 } 
 echo "</table>"; 
 mysql_free_result($result);
 mysql_close($conn);
 ?> '

The error I am getting is the following: SELECT fname, lname, ticket_id, nature FROM trouble_ticket = 'WHERElname LIKE '%paulson%' OR '

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 ''WHERElname LIKE '%paulson%' OR '' at line 1

I thought I had the right syntax for the where clause but I guess I don't, help with this would be greatly appreciated. Thanks

[veledrom]

Did you try put a space after WHERE ? Also remove = mark and OR at the end of the query.

> WHERElname
> WHERE lname

Should read:

SELECT fname, lname, ticket_id, nature FROM trouble_ticket WHERE lname LIKE '%paulson%'

[zychos]

I did put a space of the WHERE but it didn't make a difference the same with removing the =. I need the OR in my query as the user doesn't need to fill out all the fields. To adjust a hanging OR I use the substr to delete the remaining OR.

[veledrom]

You're not actualy removing OR in your code.
Change line 22 to $whereClause = substr($whereClause, 0, -4);

[pritaeas]

You can try this code instead, then you don't need to remove the last OR:

$where = array ();
foreach ($fields as $field)
{
  if (isset($_GET[$field]) and !empty($_GET[$field]))
  {
    $where[] = $field . " LIKE '%" . $_GET[$field] . "%' "; 
  }
} 
$whereClause = ' WHERE ' . implode(' OR ', $where);

[zychos]

Both ways knock off the OR thanks for that, however, the problem still exists with the syntax of my whereClause. As I am still getting the error I mentioned above. I am a novice when it comes to the SQL syntax as I am learning this stuff on the fly for a web engineering course.

[pritaeas]

If this is what you are still getting:

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 ''WHERElname LIKE '%paulson%' OR '' at line 1

Then you are missing a space after the WHERE. Is the empty OR still at the end too? It would be helpful to show your latest code.

[zychos]

Here is the latest code I am using:

<?php 
$fname = $_GET['fname'];
$lname = $_GET['lname'];
$nature =$_GET['nature'];
$ticket_id = $_GET['ticket_id'];
$nbsp = " ";

$fields = array('fname', 'lname', 'ticket_id', 'nature');

$whereClause = " WHERE " ;
foreach ($fields as $field)
    {
        if(isset($_GET[$field]) and !empty($_GET[$field]))
        {
        $whereClause .= $field . " LIKE '%" . $_GET[$field] . "%' OR "; 
        }


    //  else { return "no results found";
    //  }
    } 
$whereClause =substr($whereClause, 0, -4);  
$query = ("SELECT fname, lname, ticket_id, nature FROM trouble_ticket .$whereClause ");

$result = mysql_query($query)or die($query."<br/><br/>".mysql_error()); 

 while($row = mysql_fetch_array( $result )) // calls the information from the db places it in a table
 { 
 echo "<tr>"; 
 echo "<td bgcolor= white>".$row['fname']. " ".$nbsp. " ".$row['lname']. " </td>";
 echo "<td bgcolor= white>".$row['lname']. " </td>";
 echo "<td bgcolor= white>".$row['ticket_id']. "</td> ";
 echo "<td bgcolor= white>".$row['nature']. " </td>";
 echo"</tr>"; 
 } 
 echo "</table>"; 
 mysql_free_result($result);
 mysql_close($conn);
 ?> 

The error message is:
SELECT fname, lname, ticket_id, nature FROM trouble_ticket . WHERE lname LIKE '%paulson%'

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 lname LIKE '%paulson%'' at line 1

[pritaeas]

SELECT fname, lname, ticket_id, nature FROM trouble_ticket . WHERE lname LIKE '%paulson%'

There is a dot before WHERE, remove it

Another issue, if all fields are empty, the query will also fail.

[zychos]

removed the dot before the whereClause and it works now. I understand if all fields are empty or if none of the fields match the query will fail and I am now working to resolve that issue. Thanks.