Search database with four select fields in php

Question

I am trying to search a mysql database in php: my motive is to search by 4 dropdown menu parameters namely: Option 1 Option 2 Option 3 Option 4. All in dynamically populated from a database.

Please see below the search form in image.

The problem is that I can't get it right. I'm not sure what's happening but the result returns

Please enter a search query.

Please take a look below:

<?php
if(isset($_POST['submit'])){
    if(isset($_GET['go'])){
        if(preg_match("/^[  a-zA-Z]+/", $_POST['bloodgroup'])){
           $name=$_POST['bloodgroup'];
           //connect  to the database
           $db=mysql_connect  ("localhost", "username",  "password") or die ('I cannot connect to the database  because: ' . mysql_error());
           //-select  the database to use
           $mydb=mysql_select_db("databasename");
           //-query  the database table
           $sql="SELECT  fullname, bloodgroup, Phone FROM donors WHERE bloodgroup LIKE '%" . $bloodgroup .  "%' AND city LIKE '%" . $city ."%'";
           //-run  the query against the mysql query function
           $result=mysql_query($sql);
           //-create  while loop and loop through result set
           while($row=mysql_fetch_array($result)){
               $fullname  =$row['$bloodgroup'];
               $bloodgroup=$row['state'];
               $city=$row['city'];
               $city=$row['donortype'];
               //-display the result of the array
               echo "<ul>\n";
               echo "<li>" . "<a  href=\"search.php?id=$ID\">" .$fullname . " " . $bloodgroup .  " " . $Phone .  "</a></li>\n";
               echo "</ul>";
           }
        } else {
            echo  "<p>Please enter a search query</p>";
        }
    }
}
?>

Pls help.

Answer 1

Several things here confusing me. First, why regex on your posted bloodgroup value? Why are you checking for a GET superglobal when you're already checking for POST submit? Is that not superfluous (a double check for a submitted form)? Then there is Mysql() (Already addressed above by Jay in his comments). This is also wrong by the look of things -

$fullname  =$row['$bloodgroup'];
$bloodgroup=$row['state'];
$city=$row['city'];
$city=$row['donortype'];

You might want to check it, as you're overwriting values, and assigning wrong values to variables (And $row[$bloodgroup] should be $row[bloodgroup]. Or $row[fullname]? Finally, why are you using like instead of = if you're providing database values into the dropdown menus? I can rewrite this in mysqli or PDO (preferably) if you are happy moving away from mysql().

Bah, did it anyway

function dbconnect() {
$db = new PDO('mysql:host=<server>; dbname=<database>', '<user>', '<pass>');
}

$db = dbconnect();

# Define a PDO connection class, see the PDO manual
if (isset($_POST['submit'])) {
$sql = "SELECT id, phone, fullname, bloodgroup, phone FROM donors "
     . "WHERE bloodgroup = :bloodgroup AND city = :city "
     . "ORDER BY fullname ASC";
$query = $db->prepare($sql);
$query->bindValue(':bloodgroup', $_POST['bloodgroup']);
$query->bindValue(':city', $_POST['city']);
$query->execute();
$result = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($result as $donor) {
echo "<a href=\"search.php?id=$donor->id\">$donor->fullname $donor->bloodgroup 
$donor->Phone</a><br>";
}
} else {
# Display form?
}

Answer 2

As stated by another user, you should be using prepared statements. Here's an untested example:

<?php

// Has the user posted the form?
if (isset($_POST['submit'])) {

    // Define a new mysqli instance
    $mysqli = new mysqli("localhost", "username", "password", "databasename");

    // Check the connection works
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }

    // Define the post variables
    $bloodgroup = $_POST['bloodgroup'];
    $state      = $_POST['state'];
    $city       = $_POST['city'];
    $donortype  = $_POST['donortype'];

    // Prepare the SQL query
    if ($stmt = $mysqli->prepare("
        SELECT
            `id`,
            `fullname`,
            `phone`
        FROM
            `donors`
        WHERE
            `bloodgroup` = ?
            AND `state` = ?
            AND `city` = ?
            AND `donortype` = ?
        ;
    ")) {

        // Attach the parameters
        $stmt->bind_param("ssss", $bloodgroup, $state, $city, $donortype);

        // Execute the query
        $stmt->execute();

        // Shove everything into the $results variable
        $results = $stmt->fetchAll(PDO::FETCH_OBJ);

        echo '<ul><li>';

        // Go through each result
        foreach ($results as $result) {

            echo '<a href="search.php?id=<?php echo $result->id; ?>"><?php echo $result->donortype; ?> - <?php echo $result->bloodgroup; ?> - <?php echo $result->city; ?> - <?php echo $result->state; ?></a>';

        }

        echo '</li></ul>';

        $stmt->close();

    }

    $mysqli->close();

} else {

    echo  '<p>Please enter a search query</p>';

}

I don't see a reason why you are using preg_match but ideally you would want the post variables to return IDs. That way your database query would run efficiently (remember to change the MySQLi parameter types, from s - for string, to i - for integer).