mysql database with HTML form?

Question

I'm trying to make it to where the user can input a name that is already in the database. Once they select "Go", I need it to show all of the results from the contact table that matches the inputted data by the user. Am I missing something? When I run the HTML, the PHP runs, but comes up with "0 results". I think it might be something in my SELECT statement, but could not find an answer. Code below...

HTML File

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Module 3 | Course Project</title>
</head>
<body>

<h4>Ancestry Data Query</h4>
  <form action="queryMyDatabase.php" method="post">
    First Name: <input name="dataItem1" type="text" size="30" maxlength="30"><br><br>
    Last Name: <input name="dataItem2" type="text" size="30" maxlength="30"><br><br>
    <input value="Go"  type="submit">
  </form>

</body>
</html>

PHP File

<?php
// Make a MySQL Connection
$servername = "localhost";
$username = "root";
$password = "bitnami admin password";
$dbname = "adventureworks";



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

$item1=$_POST["dataItem1"];
$item2=$_POST["dataItem2"];

$sql="SELECT * FROM `contact` WHERE FirstName = `$item1` AND LastName = `$item2`";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "First Name " . $row["FirstName"] . "<br>" . "Last Name " . $row["LastName"] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

table structure

The shell code I had was this....

<?php
// Make a MySQL Connection
$servername = "localhost";
$username = "root";
$password = "yourApplicationPassword";
$dbname = "desiredDatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$stmt = $conn->prepare("SELECT * FROM desiredTable WHERE desiredColumn1 = ? AND desiredColumn2 = ?");
$stmt->bind_param('ss', $_POST["dataItem1"] , $_POST["dataItem2"]);
$stmt->execute();
$result = $stmt->get_result();
$row_count= $result->num_rows;

echo "Query Results</br>-----------------------------<br><br>";
if($row_count>0){
while($row = mysqli_fetch_array($result))
{
    echo $row['desiredColumn1']." ".$row['desiredColumn2']." ".$row['desiredColumn3']."-".$row['desiredColumn4']."-".$row['desiredColumn5']."</br>";
}
} else {
    echo "0 results";
}
echo "<br>-----------------------------<br>";
$stmt->close();
$conn->close();
?>

But I still needed the variables for the "dataItem".

$item1=$_POST["dataItem1"];
$item2=$_POST["dataItem2"];

@Chris `$sql="SELECT * FROM contact WHERE FirstName = $item1 AND LastName = $item2"` Try removing **`** — Rathan Naik, Oct 21 '17 at 21:48
You should use prepared statement as mentionned in the answer of @mohammed-akhtar-zuberi however, Rathan, you are right that the ` cause error and should be removed, but since its probably strings, it should be ```'$item1'``` and ```'$item2'```. — olibiaz, Oct 21 '17 at 21:58

Mohammed Akhtar Zuberi · Answer 1 · 2017-10-21T22:39:30.003

2

It is STRONGLY RECOMMENDED to use PDO to avoid SQL Injection. Use the below PHP code to fix your error along with the code re-written with PDO.

<?php
    // Make a MySQL Connectio

    $servername = "localhost";
    $username = "root";
    $password = "bitnami admin password";
    $dbname = "adventureworks";

    $conn = new PDO("mysql:host=" . $servername . ";dbname=" . $dbname, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error); 
    }

    $item1=$_POST["dataItem1"];
    $item2=$_POST["dataItem2"];

    $stmt = $conn->prepare("SELECT * FROM contact WHERE FirstName = :item1 AND LastName = :item2");
    $stmt->execute(array(':item1'=>$item1, ':item2'=>$item2));

    $userRow=$stmt->fetchAll(PDO::FETCH_ASSOC);

    if($stmt->rowCount() >= 1) {
        foreach($userRow as $row){
            echo "First Name " . $row["FirstName"] . "<br>" . "Last Name " . $row["LastName"] . "<br>";
        }
    } else {
        echo "0 results";
    }

    $conn->close();
?>

edited Oct 21 '17 at 22:39

answered Oct 21 '17 at 21:52

Mohammed Akhtar Zuberi

663
8
25

Thanks to all of you guys. I'm giving all of this a try. I'm in my first semester, so I have to ask. Is PDO something that has been recommended for a long time? Or is something new? – Chris Oct 21 '17 at 21:58
This is nothing new. You can Google about SQL Injection to find out about what it actually is. To quickly tell you, the user input value once inserted directly into a SQL Query can change the query itself. So it is wise NOT to fill in the user input value directly into your SQL Query. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:00
Ok, the above code did not work. Instead it broke it and sent me to a page saying page is not working. – Chris Oct 21 '17 at 22:01
If it is a 500 error, check your error log. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:02
I will edit the answer for you. Wait. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:05
Yep, it is a HTTP 500 Error – Chris Oct 21 '17 at 22:06
In your response Mohammed, you use ```$this``` for the connection since in the question its just a local variable, using the code directly as you show it in your answer will break. May be you should instanciate the PDO connection in the function as it made in the question. – olibiaz Oct 21 '17 at 22:06
I have a habit of working with PDO and OOP so used $this in reflexes. Modifying the answer to work without OOP but with PDO. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:11
I still want to check that PDO format out, so go ahead and send it. @Nathan 's code worked for me. I appreciate all of the help guys. You are awesome!! – Chris Oct 21 '17 at 22:23
I have fixed the code. You were using MySQLi to connect to the DB whereas I prefer using PDO instead. Now I have edited the answer with the entire code converted to PDO. I have already tested it locally. Hope this helps. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:40
It is an advice that you MUST stick to the correct procedures right from the beginning. Using any query that is risky or vulnerable is not a good idea at all. – Mohammed Akhtar Zuberi Oct 21 '17 at 22:45

score 0 · Accepted Answer · answered Oct 21 '17 at 22:11

0

Replace ` with ' (selectors only).

<?php
// Make a MySQL Connection
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";



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

$item1=$_POST["dataItem1"];
$item2=$_POST["dataItem2"];

$sql="SELECT * FROM `contact` WHERE FirstName='$item1' AND LastName ='$item2'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "First Name " . $row["FirstName"] . "<br>" . "Last Name " . $row["LastName"] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

answered Oct 21 '17 at 22:11

code still subject to mysql injection which is quite dangerous: [mysql injection](http://php.net/manual/en/security.database.sql-injection.php) – olibiaz Oct 21 '17 at 22:28
@olibiaz true, would `stripslashes()` fix that? – Oct 21 '17 at 23:27
use prepared statement with pdo or mysqli. see [mysqli prepared statement](http://php.net/manual/en/mysqli.prepare.php) and [pdo prepared statement](http://php.net/manual/en/pdo.prepare.php) – olibiaz Oct 22 '17 at 22:58

mysql database with HTML form?

HTML File

PHP File

2 Answers2