I am trying to use mySQLi for the first time. I have done it in the case of loop. Loop results are showing but I am stuck when I try to show a single record. Here is loop code that is working.
<?php
// Connect To DB
$hostname="localhost";
$database="mydbname";
$username="root";
$password="";
$conn = mysqli_connect($hostname, $username, $password, $database);
?>
<?php
$query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid";
$result = mysqli_query($conn, $query);
$num_results = mysqli_num_rows($result);
?>
<?php
/*Loop through each row and display records */
for($i=0; $i<$num_results; $i++) {
$row = mysqli_fetch_assoc($result);
?>
Name: <?php print $row['ssfullname']; ?>
<br />
Email: <?php print $row['ssemail']; ?>
<br /><br />
<?php
// end loop
}
?>
How do I show a single record, any record, name, or email, from the first row or whatever, just a single record, how would I do that? In a single record case, consider all the above loop part removed and let's show any single record without a loop.
When just a single result is needed, then no loop should be used. Just fetch the row right away.
In case you need to fetch the entire row into associative array:
$row = $result->fetch_assoc();
in case you need just a single value
$row = $result->fetch_row();
$value = $row[0] ?? false;
The last example will return the first column from the first returned row, orfalse
if no row was returned. It can be also shortened to a single line,
$value = $result->fetch_row()[0] ?? false;
Below are complete examples for different use cases
When variables are to be used in the query, then a prepared statement must be used. For example, given we have a variable$id
:
$query = "SELECT ssfullname, ssemail FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
// in case you need just a single value
$query = "SELECT count(*) FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$value = $result->fetch_row()[0] ?? false;
The detailed explanation of the above process can be found in my article. As to why you must follow it is explained in this famous question
In your case, where no variables to be used in the query, you can use thequery()
method:
$query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid";
$result = $conn->query($query);
// in case you need an array
$row = $result->fetch_assoc();
// OR in case you need just a single value
$value = $result->fetch_row()[0] ?? false;
By the way, although using raw API while learning is okay, consider using some database abstraction library or at least a helper function in the future:
// using a helper function
$sql = "SELECT email FROM users WHERE id=?";
$value = prepared_select($conn, $sql, [$id])->fetch_row[0] ?? false;
// using a database helper class
$email = $db->getCol("SELECT email FROM users WHERE id=?", [$id]);
As you can see, although a helper function can reduce the amount of code, a class' method could encapsulate all the repetitive code inside, making you to write only meaningful parts - the query, the input parameters and the desired result format (in the form of the method's name).
Usemysqli_fetch_row()
. Try this,
$query = "SELECT ssfullname, ssemail FROM userss WHERE user_id = ".$user_id;
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($result);
$ssfullname = $row['ssfullname'];
$ssemail = $row['ssemail'];
If you assume just one result you could do this as in Edwin suggested by using specific users id.
$someUserId = 'abc123';
$stmt = $mysqli->prepare("SELECT ssfullname, ssemail FROM userss WHERE user_id = ?");
$stmt->bind_param('s', $someUserId);
$stmt->execute();
$stmt->bind_result($ssfullname, $ssemail);
$stmt->store_result();
$stmt->fetch();
ChromePhp::log($ssfullname, $ssemail); //log result in chrome if ChromePhp is used.
OR as "Your Common Sense" which selects just one user.
$stmt = $mysqli->prepare("SELECT ssfullname, ssemail FROM userss ORDER BY ssid LIMIT 1");
$stmt->execute();
$stmt->bind_result($ssfullname, $ssemail);
$stmt->store_result();
$stmt->fetch();
Nothing really different from the above except for PHP v.5
Instead of using $row = $query->fetch();
try to use:
$result = $query->get_result();
$row=$result->fetch_assoc();
every time you can check if you get data from mysql tables by displaying them using:
var_export( $row['put your column name here'] );
The first answer will do the job but in this case you don't need to loop the result.
$row = $result->fetch_row();
Just echo the column
echo $row['column'];
This should do the trick
mysqli_result::fetch_column()
is availableYou can usemysqli_result::fetch_column()
to fetch a single scalar value from the result set.
The new method accepts 0-based position of the column you want to read. The default value is 0.
$query = "SELECT ssfullname, ssemail FROM userss WHERE ud=?";
$stmt = $conn->prepare($query);
$stmt->execute([$id])
$result = $stmt->get_result();
$ssemail = $result->fetch_column(1);
Beware that this method will move the internal pointer to the next row, just like the otherfetch_*
methods do. When it reaches the end, it will returnfalse
. Therefore, in the above example, you would be better off usingfetch_assoc()
to fetch the entire row into an array.
It can also be used withmysqli::query()
for SQL statements without parameters.
$query = "SELECT count(*) FROM userss";
$count = $conn->query($query)->fetch_column();
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.