Two many records showing when h_id is identified

Question

I am trying to filter a mysql table using PHP, My aim is when the url is History.php?h_id=1 it only shows the rows that have one in the h_id (H_id is not a unique number)

My code is as below.

    <html>
<head>
<title></title>
</head>
<body >

<?php
mysql_connect('localhost', 'root', 'matl0ck') or die(mysql_error());
mysql_select_db("kedb") or die(mysql_error());

$h_id = (int)$_GET['h_id'];
$query = mysql_query("SELECT * FROM Hist WHERE H_ID = '$h_id'") or die(mysql_error());

if(mysql_num_rows($query)=1){
    while($row = mysql_fetch_array($query)) {
        $id = $row['ID'];
        $name = $row['Name'];
        $datemod = $row['DateMod'];
        $h_id = $row['H_ID'];
    }
?>
<?php
    $con=mysqli_connect("localhost","root","matl0ck","kedb");
    // Check connection
    if (mysqli_connect_errno()){
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $result = mysqli_query($con,"SELECT * FROM Hist");

    echo "<table border='1'>
            <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Date</th>
            <th>H_ID</th>
            </tr>";

    while($row = mysqli_fetch_array($result))
    {
        echo "<tr>";
        echo "<td>" . $row['ID'] . "</td>";
        echo "<td>" . $row['Name'] . "</td>";
        echo "<td>" . $row['DateMod'] . "</td>";
        echo "<td>" . $row['H_ID'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

    mysqli_close($con);
?>
<?php
}else{
    echo 'No entry found. <a href="javascript:history.back()">Go back</a>';
}
?>
</body>
</html>

When I try to use this it shows all records that has a number in the h_id when I delete a number in this column it shows an error.

My table layout is as below.

Thank you

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Nov 12 '16 at 04:03
**WHY are you using both the MYSQL_ and MYSQLI_ database extension???** — RiggsFolly, Nov 12 '16 at 04:05
@Dagon Maybe that is why my cat has a BB gun pellet embedded in between her shoulder blades — RiggsFolly, Nov 12 '16 at 04:13

score 2 · Accepted Answer · edited May 23 '17 at 12:01

This is your syntactically incorrect statement

if(mysql_num_rows($query)=1){

A test is done using == and = is a value assignment

if(mysql_num_rows($query) == 1){
//------------------------^^
    while($row = mysql_fetch_array($query)) {
        $id = $row['ID'];
        $name = $row['Name'];
        $datemod = $row['DateMod'];
        $h_id = $row['H_ID'];
    }

Also

Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements and therefore stick to the mysqli_ or PDO database extensions

Your general code seemed to get a bit confused, and you were getting data from a query "SELECT * FROM Hist" that you never seemed to use.

Also the while loop was being terminated before you actually consumed and output the results of the first query.

I also amended the code to use parameterized and prepared queries, and removed the use of the mysql_ which no longer exists in PHP7

<?php
// Use one connection for all script, and make it MYSQLI or PDO
$con=mysqli_connect("localhost","root","matl0ck","kedb");
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    // if connection fails there is no point doing anything else
    exit;
}

//$h_id = (int)$_GET['h_id'];

// prepare and bind values to make the code safe from SQL Injection
// also only select the rows you want
$sql = "SELECT ID, Name, DateMod, H_ID FROM Hist WHERE H_ID = ?";
$stmt = $con->prepare($sql);
if ( ! $stmt ) {
    echo $con->error;
    exit;
}

$stmt->bind_param("i", $_GET['h_id']);

$stmt->execute();

if ( ! $stmt ) {
    echo $con->error;
    exit;
}

// bind the query results 4 columns to local variable
$stmt->bind_result($ID, $Name, $DateMod, $H_ID);

echo "<table border='1'>
      <tr><th>ID</th><th>Name</th><th>Date</th><th>H_ID</th></tr>";

if($con->affected_rows > 0){

    echo "<table border='1'>
          <tr><th>ID</th><th>Name</th><th>Date</th><th>H_ID</th></tr>";

    while($stmt->fetch()) {

        while($row = $stmt->fetch_array()) {
            echo "<tr>";
            echo "<td>$ID</td>";
            echo "<td>$Name</td>";
            echo "<td>$DateMod</td>";
            echo "<td>$H_ID</td>";
            echo "</tr>";
    }
    echo "</table>";

}else{
    echo 'No entry found. <a href="javascript:history.back()">Go back</a>';
}
?>

Two many records showing when h_id is identified

1 Answers1