CODEWITHSUNDEEP
phpmysql
Borgy ES
Borgy ES

Reputation: 9

SQL syntax error; check the manual that corresponds to your MariaDB server

I would like to search in my database using multiple input fields but it gives me an error.

error Message: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1"

As you can see I have 3 input fields to search in the respective columns. Not all fields are mandatory. User can search for 1 or 2 fields only.

Scenarios; 1. When I enter any string in the first field it will search in the database.

  1. When I enter any string in the first and second field it will search again.

  2. Now, I entered any string in the second field only. Then the error comes.

please help guys.

Thanks in advance.d 

<html>
<body>
<form name="form" action="index1.php" method="get">
Profile Number: <input id="profile_number" name="profile_number">
Nice Name:      <input id="nice_name" name="nice_name">
ISO 3:          <input id="iso_3" name="iso_3">

<input type="submit" name="Submit" value="Search" />
</form>

<?php

    echo @$_GET['profile_number'];
    echo @$_GET['nice_name'];
    echo @$_GET['iso_3'];

    $conn = mysqli_connect("localhost","root","","autocomplete");

    if (mysqli_connect_errno()) {
        echo "Failed to connect: " . mysqli_connect_error();
    }

if(isset($_GET['Submit']))

{
    echo'
<table width=100% style="border: 1px solid #a8c562;" cellpadding=3><tr>
    <td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">Profile Number</td>
    <td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">Country Name</td>
    <td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">ISO3</td>
';

    $usearchprofile = @$_GET['profile_number'];
    $usearchnicename = @$_GET['nice_name'];
    $usearchiso3 = @$_GET['iso_3'];

    $qrystring = "SELECT * FROM country where ";  

    if($usearchprofile)
        $qrystring .= " name like '%$usearchprofile%'  ";

    if($usearchnicename)
        $qrystring .= " OR nicename like '%$usearchnicename%'  ";

    if($usearchiso3)
        $qrystring .= " OR iso3 like '%$usearchiso3%'  ";


    $userarray = mysqli_query($conn, $qrystring) or die(mysqli_error($conn));

    while ($usrow = mysqli_fetch_array($userarray))
    {
        echo'
<tr>
<td style="border: 1px solid #a8c562;" align=center>'.$usrow['name'].'</td>
<td style="border: 1px solid #a8c562;" width=10% align=center>'.$usrow['nicename'].'</td>
<td style="border: 1px solid #a8c562;" width=10% align=center>'.$usrow['iso3'].'</td>
</tr>
';
    }

    echo'</table>';
}?>
    </body>
</html>

Upvotes: 1

Views: 1536

Answers (4)

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

I think the issue is here:

$qrystring = "SELECT * FROM country where ";  

    if($usearchprofile)
        $qrystring .= " name like '%$usearchprofile%'  ";

    if($usearchnicename)
        $qrystring .= " OR nicename like '%$usearchnicename%'  ";

    if($usearchiso3)
        $qrystring .= " OR iso3 like '%$usearchiso3%'  ";

In the above query, if 

if($usearchprofile)
            $qrystring .= " name like '%$usearchprofile%'  ";

this condition do not satisfy than your query is something like:

SELECT * FROM country where OR LIKE ...

which is wrong. You can try:

$qrystring = "SELECT * FROM country where 1 = 1";  // 1 = 1 will always true. Now all the below conditions are independent of each other  

        if($usearchprofile)
            $qrystring .= " and name like '%$usearchprofile%'  ";

        if($usearchnicename)
            $qrystring .= " OR nicename like '%$usearchnicename%'  ";

        if($usearchiso3)
            $qrystring .= " OR iso3 like '%$usearchiso3%'  ";

Upvotes: 2

Adam Hull
Adam Hull

Reputation: 214

$qrystring = "SELECT * FROM country where "; 
if($usearchprofile)
  $qrystring .= " name like '%$usearchprofile%' ";     
 if($usearchnicename) {
 if($usearchprofile){
   $qrystring .= " OR nicename like '%$usearchnicename%' "; 
  }else{
   $qrystring .= "nicename like '%$usearchnicename%' "; 
 }
}
if($usearchiso3) {
 if($usearchprofile || $usearchnicename){
 $qrystring .= " OR iso3 like '%$usearchiso3%' ";
 }else{
 $qrystring .= " iso3 like '%$usearchiso3%' ";
}
}

this will stop the issue as it checks if there is only one or more set

Upvotes: 0

Nigel Ren
Nigel Ren

Reputation: 57121

As Mayank pointed out, there is a problem if the first condition isn't met, a way round this is...

    $qrystring = "SELECT * FROM country where 1=1 ";  

    if($usearchprofile) {
        $qrystring .= " OR name like '%$usearchprofile%'  ";
    }

    if($usearchnicename) {
        $qrystring .= " OR nicename like '%$usearchnicename%'  ";
    }
    if($usearchiso3) {
        $qrystring .= " OR iso3 like '%$usearchiso3%'  ";
    }

The 1=1 is a dummy condition to satisfy the fact the OR must follow some other condition.

Upvotes: 1

B. Desai
B. Desai

Reputation: 16436

change your condition as below then try y code with different fields

$qrystring_arry = array();
if($usearchprofile)
        $qrystring_arry[] = " name like '%$usearchprofile%'  ";

    if($usearchnicename)
        $qrystring_arry[] = " nicename like '%$usearchnicename%'  ";

    if($usearchiso3)
        $qrystring_arry[] = " iso3 like '%$usearchiso3%'  ";

$qrystring .= implode(" OR ",$qrystring_arry);

Upvotes: 0

Related Questions