如何使用PHP在MySQL数据库中执行搜索操作?

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?

我有一个包含字段名、电子邮件地址、我想使用名称、电子邮件地址、资格的数据库表，需要在我的web页面中显示用户详细信息，谁能告诉我该怎么做?

<?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 = '[e-mail]' 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);

3 个解决方案

#1

 $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);    

#2

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

´使用PDO,不使用mysql甚至mysqli。在最新的PHP版本中甚至不再支持它。

$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.

还有其他方法可以用PDO来做，但这就是我怎么做的。

#3

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.

我希望这能满足你的需要。