How to fetch a single row from a MySQL DB using MySQLi with PHP?

Question

I am using PHP with MySQli and I want to fetch a single row from the whole SQL DB, which fits in my condition. Just for a note, this is what my current database looks like :

I want to get that single row where, eg. txnid column's value == $txnid (a variable). I tried to build the SQL Query which would fit my requirements, and here's how it looks like : $sql = "SELECT * FROM 'table1' WHERE 'txnid' = " . $txnid;. When I raw-run this Query in phpMyAdmin, it works as expected. I just want to know, after I run the Query in PHP, how to fetch that row's data which came in as response from the Query using MySQLi?

This is the code which I am using to run the Query :

$servername = "localhost";
$username = "XXXXXXXXXXXXXX";
$password = "XXXXXXXXXXXXXX";
$dbname = "XXXXXXXXXXXXXXXX";

$conn = new mysqli($servername, $username, $password, $dbname);

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

$txnid = $_GET['id'];

$sql = "SELECT * FROM `testtable1` WHERE `txnid` = " . $txnid;

if ($conn->query($sql) === TRUE) {
    echo ""; //what should I do here, if I want to echo the 'date' param of the fetched row?
} else {
    echo "Error: " . $sql . "<br>" . $conn->error . "<br>";
}

Sorry @Ashok I forgot to name the param there - please consider there's a param name inside the braces. Thanks! — kartik, Sep 24 '20 at 04:19
Just as a warning: that code is widely open for SQL injection. Widely as in: you cannot make it less secure than that..... Please have a look at prepared statements! — Nico Haase, Sep 24 '20 at 13:29

score 6 · Accepted Answer · edited Sep 24 '20 at 08:42

Add LIMIT 1 to the end of your query to produce a single row of data.

Your method is vulnerable to SQL injection. Use prepared statements to avoid this. Here are some links you can review:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8mb4");

$txnid= $_GET['name_of_txnid_input_field'];

// prepare and bind
$stmt = $conn->prepare("SELECT * FROM `testtable1` WHERE `txnid` = ? LIMIT 1");
$stmt->bind_param("i", $txnid);

// set parameters and execute
$stmt->execute();

$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo $row['date_field_you_want_to_display'];

the txnid field looks unique. why adding LIMIT? – Your Common Sense Sep 24 '20 at 08:33 — Your Common Sense, Sep 24 '20 at 08:33

score -7 · Answer 2 · edited Sep 24 '20 at 06:02

-7

$txnid = $_POST['txnid'];   
$sql = "SELECT * FROM tableName WHERE txnid = $txnid";
$result = $conn->query($sql);

edited Sep 24 '20 at 06:02

Cray

2,347
7
17
27

answered Sep 24 '20 at 04:23

Manoj Kumar

11
2

Thanks, is $result the response row? – kartik Sep 24 '20 at 04:24
6

Be forewarned this answer would likely open a critical SQL injection vulnerability; a secure answer would leverage parameterized queries and prepared statements. – esqew Sep 24 '20 at 04:27

How to fetch a single row from a MySQL DB using MySQLi with PHP?

2 Answers2