How can I perform search operation in MySQL database with help of PHP?

Question

I have a database table with fields Name, EmailAddress, Qualification I want to perform the search using name, emailaddress, qualification and need to display the user details in my web page can anybody tell how can I do it?

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

   //$name=$_POST['name'];
  //$email=$_POST['email'];
  //$qualification=$_POST['qualify'];
  $sql =  "SELECT * FROM form WHERE Name ='kumar' OR EmailAddress = 'kumar@gmail.com' OR Qualification = 'BE' ";
 $result=$conn->query($sql);

    while($row = $result->fetch_assoc())
 {
   echo 'Name: '.$row['Name']; 
   echo '<br /> EmailAddress: ' .$row['EmailAddress'];
  echo '<br /> Qualification: '.$row['Qualification'];
   echo '<br /> DOB: '.$row['DOB'];
 }


mysql_close($con);

anything you have tried so far ? any code snippet on which we can help ? what issue are u facing ? — Jas, Dec 22 '15 at 09:22
There are plenty of tutorials out there, this is basic php / mysql interaction. Google something like _"php mysqli select"_ or even just _"php connect to database"_ — Epodax, Dec 22 '15 at 09:24
i have tried but when i search with name arun it is displaying all values in the database — , Dec 22 '15 at 09:28
The legacy mysql extension you are using has been removed from PHP (after being deprecated for years). Don't waste efforts learning it! — Álvaro González, Dec 22 '15 at 09:35
then what i have to use im new to php i dont know about such things — , Dec 22 '15 at 09:49
use mysqli instead. Something like `while($row = $result->fetch_assoc())` — Ikhlak S., Dec 22 '15 at 10:26
The information is right there at every manual page of the legacy extension, e.g. [mysql_query](http://php.net/mysql_query). Just check the big fat red box on top. — Álvaro González, Dec 22 '15 at 10:33
Check [this answer](http://stackoverflow.com/a/26476208/3899908) for a guide of migrating from `mysql_` to `mysqli_` — worldofjr, Dec 22 '15 at 11:19
while($row = mysql_fetch_array($sql, MYSQL_ASSOC)) who to write it with the help of mysqli — , Dec 22 '15 at 11:26
see the above code i have modified it but it is not working can you tel whats wrong in it — , Dec 22 '15 at 11:34
You can't `echo` everything and expect it to work! `echo while($row = $sql->fetch_assoc())` ... seriously? — worldofjr, Dec 22 '15 at 12:01
i just added to all see whether it is print or not i will remove it the problem is with while($row = $sql->fetch_assoc()) it is not working — , Dec 22 '15 at 12:10

Keyur Chavda-kc1994 · Answer 1 · 2015-12-22T12:24:34.973

3

 $con = mysql_connect ("localhost", "root", "");     
 mysql_select_db ("myDB", $con); 
 if (!$con) { die ("Could not connect: " . mysql_error()); }
 $sql = mysql_query("SELECT * FROM search WHERE name LIKE '%arun%' OR EmailAddress LIKE '%arun%' OR Qualification LIKE '%arun%' ");
 $con->query($sql);

 if(count($sql)>0 || $sql !=NULL){
  while ($row = mysql_fetch_array($sql, MYSQL_ASSOC))
   {
     echo 'Name: '.$row['name']; 
     echo '<br /> Email: ' .$row['email'];
     echo '<br /> Address: '.$row['address'];
   }
  }
  else{
    echo 'your error here';
  }

 mysql_close($con);

edited Dec 22 '15 at 12:24

answered Dec 22 '15 at 09:35

Keyur Chavda-kc1994

1,045
1
13
28

i have tired but the nothing is coming – Dec 22 '15 at 09:55
try this print_r($sql) – Keyur Chavda-kc1994 Dec 22 '15 at 10:01
if none of the records matches with the database i want to display error message can you tell what should i do for that – Dec 22 '15 at 10:54
you can also check condition in while loop. – Keyur Chavda-kc1994 Dec 22 '15 at 11:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98683/discussion-between-arun-kumaresh-and-kc1994). – Dec 22 '15 at 11:21

score 1 · Answer 2 · answered Dec 22 '15 at 13:20

Use PDO, don´t use mysql or even mysqli. Its not even supported anymore in the latest PHP versions.

$host = 'localhost';
$dbname = 'mydb';
$username = 'root';
$password = 'password';

try {
    // create the connection
    $conn = new PDO('mysql:host=' . $host . ';dbname=' . $dbname . ';charset=utf8', 'root', 'password');
    // set the errmode to exception, set this to ERRMODE_SILENT if you want to hide database errors
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage(); // catch any connection errors
    $conn = false;
}

$select_data = $conn->prepare("SELECT * FROM search WHERE name LIKE :name OR EmailAddress LIKE :email OR Qualification LIKE :qualification ");
$select_data->bindValue(':name', $name); // bind the values to  the paramaters
$select_data->bindValue(':email', $email);
$select_data->bindValue(':qualification', $qualification);
$select_data->execute();
if($select_data->rowcount() > 0){ // rowcount returns the amount of results
    // atleast 1 result
    $results = $select_data->fetchAll(PDO::FETCH_ASSOC); // fetch the results into an array
    foreach($results as $row){
        echo 'Name: ' . $row['name'];
        echo '<br /> Email: ' . $row['email'];
        echo '<br /> Address: ' . $row['address'];
    }
}

There are other ways to do this with PDO but this is how I tend to do it.

but most of the suggesting to mysqli is it supported in lastest version of php? — , Dec 23 '15 at 04:20

Nitesh Pawar · Answer 3 · 2015-12-22T11:25:31.953

Please try the below code:

<form id="form" action="" method="POST" >
    <fieldset>

                    <label>First Name </label>
                    <input type="text"  placeholder="Name" value="<?php if (isset($_REQUEST['name'])) echo $_REQUEST['name']; ?>" class="form-control required" id="name" name="name">

                    <label>EmailAddress</label>
                    <input type="text" placeholder="EmailAddress"  value="<?php if (isset($_REQUEST['EmailAddress'])) echo $_REQUEST['EmailAddress']; ?>"  class="form-control required" id="EmailAddress" name="EmailAddress">

                <label>Qualification </label>
                        <input type="text" class="form-control" placeholder="Qualification"  value="<?php if (isset($_REQUEST['qualification'])) echo $_REQUEST['qualification']; ?>"  id="Qualification" name="Qualification">
                    <br><input value="Search" name="Search" style="width:100%" type="submit"  class="btn btn-success">
   </fieldset>

</form>

<?php
if (isset($_POST['Search'])) {

    $con = mysql_connect("localhost", "root", "");
    mysql_select_db("myDB", $con);
    if (!$con) {
        die("Could not connect: " . mysql_error());
    }

    $where = '';
    if ($_POST['name']) {
        $where .="name like '%" . $_POST['name'] . "%'";
    }

    if ($_POST['EmailAddress']) {
        if (!empty($where))
            $where.=" or ";
        $where .="email like '%" . $_POST['EmailAddress'] . "%'";
    }

    if ($_POST['Qualification']) {
        if (!empty($where))
            $where.=" or ";
        $where .="qualification like '%" . $_POST['Qualification'] . "%'";
    }

    $sql = "select id, name, email, qualification from student where " . $where;

    echo $sql;


    $res = mysql_query($sql) or die("Error in query " . mysql_error());

    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

        echo "<br><br>";
        echo 'No. : ' . $row['id'];
        echo '<br />  Name: ' . $row['name'];
        echo '<br /> Email: ' . $row['email'];
        echo '<br /> Qualification: ' . $row['qualification'];
    }

    mysql_close($con);
}
?>

I hope this will work for you as you want.

How can I perform search operation in MySQL database with help of PHP?

3 Answers3