Single result from database using mysqli
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.
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.
Actually i want to remove the loop part and and without limit i want to get one record with query to show single record.
6 Answers 6
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_row(); $value = $row[0] ?? false; The last example will return the first column from the first returned row, or false 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
Variables to be used in the query
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 = $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 = $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
No variables in the query
In your case, where no variables to be used in the query, you can use the query() 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 = prepared_select($conn, $sql, [$id])->fetch_row[0] ?? false; // using a database helper class $email = $db->getCol("SELECT email FROM users WHERE [$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).
How to get single value from MySQL query in PHP
I know this post is similar to many other Stack Overflow questions. However, they quite didn’t help to sort out this issue. I want the query to return single value and store it in variable. The SQL query is working fine in the database but not with PHP. Where have I gone wrong here?
$datelink = $_GET['bdate']; $nid = $mysqli->query("select `newsletterId` from `newsletter` where ndate='$datelink'")->fetch_object()->name; @Mario, I did this but didn’t work.. $nid = $mysqli->query(«select newsletterId from newsletter where ndate=’30-01-2015′»)->fetch_object()->newsletterId;
@Nephil: if ndate is a DATE column (and it should be, since you’re using it to store dates) you need to use the yyyy-mm-dd date format, like 2015-01-30 . You won’t get results when you use other formats.
3 Answers 3
You should initiate your connection before quering the database.
$mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) < echo "Failed to connect to MySQL: " . $mysqli->connect_error; > It’s also dangerous to just append your string from $_GET. Your site can be attacked with SQL injection. You definitely should use PDO with prepared statements. Or some another library like that, I like dibi for example.
Update: You are accessing column name that doesn’t exist. It should throw a notice ( PHP Notice: Undefined property: stdClass::$name ). If you don’t see notices and you are developing locally (and you definitely should), you can enable it in your php.ini file with error_reporting = E_ALL .
You probably want column newsletterId . So altogether:
$mysqli = new mysqli('example.com', 'user', 'password', 'database'); if ($mysqli->connect_errno) < echo 'Failed to connect to MySQL: ' . $mysqli->connect_error; > $dateRaw = $_GET['bdate']; // for example '22.12.2012', if this value is already in format YYYY-MM-DD, you can step next line and use it $date = date('Y-m-d', strtotime($dateRaw)); $statement = $mysqli->prepare("SELECT `newsletterId` FROM `newsletter` WHERE `ndate`=? LIMIT 1"); $statement->bind_param('s', $date); // replace the question mark with properly escaped date $statement->execute(); $result = $statement->get_result(); if ($result->num_rows) < $newsletterObject = $result->fetch_object(); $newsletterId = $newsletterObject->newsletterId; // you had $newsletterObject->name here > else < echo "Newsletter with the date doesn't exist"; >$statement->close(); Single Value Mysqli [duplicate]
I am trying to write a function that will check for a single value in the db using mysqli without having to place it in an array. What else can I do besides what I am already doing here?
function getval($query)< $mysqli = new mysqli(); $mysqli->connect(HOST, USER, PASS, DB); $result = $mysqli->query($query); $value = $mysqli->fetch_array; $mysqli->close(); return $value; > 8 Answers 8
$name = $mysqli->query("SELECT name FROM contacts WHERE This assumes that the scalar being returned has a known field name. This is not consistent with OP’s code, which passes in ANY query (that is expected to return a single value; a «scalar» query). It might not even be a query of a record field — it could be returning a count, or even a system variable.
In case the query fails, this will cause a Fatal error (Call to a member function fetch_object() on boolean).
The mysql extension could do this using mysql_result, but mysqli has no equivalent function as of today, afaik. It always returns an array.
If I didn’t just create the record, I do it this way:
$getID = mysqli_fetch_assoc(mysqli_query($link, "SELECT userID FROM users WHERE something = 'unique'")); $userID = $getID['userID']; Or if I did just create the record and the userID column is AI, I do:
$userID = mysqli_insert_id($link); And nobody, nobody, nobody cares that the only meaningful part of this long-sought «one-liner» is gone far beyond visible screen.
downvoted solely because fetch_object() existed many years before this answer, which returns a simple object, which means that at the time you wrote this mysqli did have a better option. Also your answer is written in procedural commands while OP’s question was written via object-based mysqli.
This assumes that the scalar being returned has a known field name. This is not consistent with OP’s code, which passes in ANY query (that is expected to return a single value; a «scalar» query). It might not even be a query of a record field — it could be returning a count, or even a system variable.
Always best to create the connection once at the beginning and close at the end. Here’s how I would implement your function.
$mysqli = new mysqli(); $mysqli->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE); $value_1 = get_value($mysqli,"SELECT ID FROM Table1 LIMIT 1"); $value_2 = get_value($mysqli,"SELECT ID FROM Table2 LIMIT 1"); $mysqli->close(); function get_value($mysqli, $sql) < $result = $mysqli->query($sql); $value = $result->fetch_array(MYSQLI_NUM); return is_array($value) ? $value[0] : ""; > Yes, it is. stackoverflow.com/questions/1684993/… From php.net, «This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.»
this answer, and the more recent answers by John and rybo111, are the only ones that directly address OP’s code. OP’s question shows a method that accepts ANY scalar-returning query — does not require knowing name of a field to be returned.
Here’s what I ended up with:
function get_col($sql) < global $db; if(strpos(strtoupper($sql), 'LIMIT') === false) < $sql .= " LIMIT 1"; >$query = mysqli_query($db, $sql); $row = mysqli_fetch_array($query); return $row[0]; > This way, if you forget to include LIMIT 1 in your query (we’ve all done it), the function will append it.
$first_name = get_col("SELECT `first_name` FROM `people` WHERE `id`='123'"); Minor issue: The query could contain limit (lowercase) instead of LIMIT . So if you use the part of the answer involving LIMIT , might want to make the test if (strpos($sql, ‘LIMIT’) === false && strpos($sql, ‘limit’) === false)
Let’s just hope that there is no string literal in the SQL that happens to contain the string «limit».
@trincot True, it was an afterthought — the if statement could look for LIMIT N (where N is 0-9) using regexp instead.
Even this is an old topic, I don’t see here pretty simple way I used to use for such assignment:
list($value) = $mysqli->fetch_array; you can assign directly more variables, not just one and so you can avoid using arrays completely. See the php function list() for details.
This doesn’t completely avoid the array but dispenses with it in one line.
function getval($query) < $mysqli = new mysqli(); $mysqli->connect(HOST, USER, PASS, DB); return $mysqli->query($query)->fetch_row()[0]; > Such a function should support prepared statements
Also, such a function should never connect on its own, but accept an existing connection variable as a parameter.
Given all the above, only acceptable way to call such a function would be be like
$name = getVal($mysqli, $query, [$param1, $param2]); allowing $query to contain only placeholders, while the actual data has to be added separately. Any other variant, including all other answers posted here, should never be used.
function getVal($mysqli, $sql, $values = array()) < $stm = $mysqli->prepare($sql); if ($values) < $types = str_repeat("s", count($values)); $stm->bind_param($types, . $values); > $stm->execute(); $stm->bind_result($ret); $stm->fetch(); return $ret; > $name = getVal("SELECT name FROM users WHERE [$id]); and it’s the only proper and safe way to call such a function, while all other variants lack security and, often, readability.