Specific syntax to search multiple columns in a MySQL Database?

Question

I am working on creating a searchable database of all the students and associated information (for a larger project involving managing a iPad deployment). The database has 7 columns:

• first_name
• last_name
• id_number
• grade
• grad_year
• student_email
• dob

What I need to do is return the entire row if the input matches any of the values in the row.

The PHP that I am working with is:

<?php 
$conn = mysql_connect ("localhost", "blmrvftl_ipdb", "ipad-db") or die ('I cannot connect to the database because: ' . mysql_error()); 
$selected = mysql_select_db ("blmrvftl_ipdb") 
or die ("Could not select database because: " . mysql_error()); 


// PHP Search Script 

$sql = "select * from phonebook, where first_name = '".$_GET['seek']."' or last_name = '". $_GET['seek'] ."' or id_number = '". $_GET['seek'] ."'";
//$sql = "select * from student_database, where first_name = '".$_GET['seek']."' or last_name = '". $_GET['seek'] ."' or id_number = '". $_GET['seek'] ."' or grade = '". $_GET['seek'] ."' or grad_year = '". $_GET['seek'] ."' or student_email = '". $_GET['seek'] ."' or dob = '". $_GET['seek'] ."'"; 
// $sql = "select * from student_database, where first_name = '".$_POST['seek']."' or last_name = '". $_POST['seek'] ."' or id_number = '". $_POST['seek'] ."' or grade = '". $_POST['seek'] ."' or grad_year = '". $_POST['seek'] ."' or student_email = '". $_POST['seek'] ."' or dob = '". $_POST['seek'] ."'";
$result = mysql_query($sql,$conn)or die (mysql_error()); 


if (mysql_num_rows($result)==0){ 
echo "No Match Found"; 
}else{ 
while ($row = mysql_fetch_array($result)){ 
echo "Name: " .$row['first_name']." ".$row["last_name"]."<br>"; 
echo "Student ID: ".$row['id_number']."<br>"; 
// echo "Department: ".$row['department_name']."<br>"; 
// echo "Directorate: ".$row['directorate_name']."<br>"; 
//echo "Site: ".$row['site_name']."<br>"; 
//echo "Phone #: ".$row['pb_tel_ext']."<br>"; 
//echo "Email Address: ".$row['pb_email_address']."<br>"; 
echo "<br>"; 
echo "---------------------------------------------------------------------"."<br>"; 
} 
} 

mysql_close(); 
?>

What i am having issues with is that I can't quite get the format right to get it to search all the columns. (the uncommented $sql line is a modified version of the original from the site i snatched this bit of code from.) The error i get is:

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 first_name = 'Elliott' or last_name = 'Elliott' or id_number = 'Elliott'' at line 1

Answer 1

  <?php
  $submit=$_GET['submit'];

  $q1= "SELECT * FROM rent_page WHERE (local LIKE '%$submit%')";
  $r1 = mysql_query($q1);
  $num=mysql_num_rows($r1);
  if($num>0){     

  while($row=mysql_fetch_array($r1)){

  //$row=mysql_fetch_array($r);

  $_SESSION['nid']=$row['nid'];


    ?>


       <div class="prod_box" >
    <div class="top_prod_box" style="float:right; width:100%;"></div>
    <div class="center_prod_box" style="float:right; width:100%;">
      <div class="product_title" style="padding-top:20px; padding-bottom:2px;"><a href style="text-decoration:none; "="index.php"><b style="font-size:16px; color:red; "><?php echo $row['select_city']; ?></b></a></div>


      <div class="product_img" style="padding-top:2px; padding-bottom:2px;>
      <a href="index.php?nid=<?php echo $row['nid']; ?>" >

      <img width="90%" height="150px" src="uploads/<?php echo $row['image']; ?>" alt="" border="0" />

      </a>
      </div>
      <div class="product_title" style="padding-top:1px; padding-bottom:1px;> <span class="price">Location:<?php echo $row['local']; ?></span></div>
      <div class="prod_price" style="padding-top:1px; padding-bottom:1px;>  <span class="price">Minimum Price: <?php echo $row['minimum']; ?></span></div>
    </div>
    <div class="bottom_prod_box" style="padding-top:1px; padding-bottom:1px;><span class="price">Maximum Price: <?php echo $row['maximum']; ?></span></div>
    <div class="prod_details_tab" style="padding-top:1px; padding-bottom:1px;><span class="price">Building Floor: <?php echo $row['name_of_floor']; ?></span></div>
  <div class="prod_details_tab" style="padding-top:1px; padding-bottom:1px;><span class="price">Posted By. <?php echo $row['opt']; ?></span></div>



  </div>


      <?php

    }
    }else{

        echo "Sorry invalid product";}


  ?>

Answer 2

I know that when you start developing you don't want things to get too complicated and the last thing you care about is security.

But please always keep in mind that

• Facebook started with pictures stolen from a easy to hack student database just like yours (fun fact)

• Never build a SQL script from unfiltered user input

• Don't use the mysql_* adapter any more, it is in a deprecation process.

Use mysqli or pdo_mysql instead. For a beginner prepared statements are the best choice concerning input escaping.

http://www.php.net/manual/de/book.mysqli.php

http://www.php.net/manual/de/ref.pdo-mysql.php

Now concerning your question the SQL syntax for a basic SELECT statement is

SELECT
     fields
FROM
     table
WHERE
     field1 = 'filter1'
     AND
     field2 = 'filter2'

No comma after table(s)

Answer 3

Remove your query and paste this one

$sql = "select * from phonebook
        where first_name = '".$_GET['seek']."' 
        or last_name = '". $_GET['seek'] ."' 
        or id_number = '". $_GET['seek'] ."'";

Answer 4

You've got a rogue comma:

select * from phonebook, where

should be

select * from phonebook where

Other than that, should work - it looks OK on first glance.