user2071435 user2071435 - 23 days ago 4
HTML Question

Two many records showing when h_id is identified

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.

Layout of table

Thank you

Answer

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>';
}
?>
Comments