PHP MySQL Select Data
The SELECT statement is used to select data from one or more tables:
or we can use the * character to select ALL columns from a table:
To learn more about SQL, please visit our SQL tutorial.
Select Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:
Example (MySQLi Object-oriented)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) // output data of each row
while($row = $result->fetch_assoc()) echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>
$conn->close();
?>
Code lines to explain from the example above:
First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.
Then, the function num_rows() checks if there are more than zero rows returned.
If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.
The following example shows the same as the example above, in the MySQLi procedural way:
Example (MySQLi Procedural)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) // output data of each row
while($row = mysqli_fetch_assoc($result)) echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>
You can also put the result in an HTML table:
Example (MySQLi Object-oriented)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) echo «
| ID | Name |
|---|---|
| «.$row[«id»].» | «.$row[«firstname»].» «.$row[«lastname»].» |
«;
> else echo «0 results»;
>
$conn->close();
?>
Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.
It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:
Example (PDO)
class TableRows extends RecursiveIteratorIterator <
function __construct($it) <
parent::__construct($it, self::LEAVES_ONLY);
>
function current() return «
«;
>
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;
try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests»);
$stmt->execute();
Return One Row from MySQL
Amateur question, but something I’ve been wondering about. What is the PHP for selecting one row from a MySQL query? AKA you’re picking something by unique ID (you know there’s only one row that matches your request) and want to get only that value. Do you still have to use a while loop and mysql_fetch_row?
$query = "SELECT name,age FROM people WHERE uid = '2'"; $result = mysql_query($query); // what php is used to return the name and age? preferably without a loop? As stated in the PHP manual for the mysql_query() function: Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
7 Answers 7
Add limit 0,1 and for PHP 7 use mysqli
$query = "SELECT name,age FROM people WHERE uid = '2' LIMIT 0,1"; $result = mysqli_query($query); $res = mysqli_fetch_assoc($result); echo $res["age"]; Editor’s Note: I approved the edit to change mysql to mysqli because the mysql function has been deprecated from PHP7, the current lowest supported version. For time travelers who still use PHP5, the mysql is the correct function.
If uid is your primary key, or it has a unique constraint, you can simply call mysql_fetch_row($res) once.
If you want to verify that you actually got a result, you can check if mysql_num_rows($res) == 1 .
There is not any loop required, just call the function to extract one row from a Query Resultset:
$query = "SELECT name,age FROM people WHERE uid = '2'"; $result = mysql_query($query); $row = mysql_fetch_assoc( $result ); var_dump( $row ); I did not added any limit because using a Primary Key as filter(as it is in this specific case), using the Limit is just redundant.
Add LIMIT in your query. For instance,
SELECT name,age FROM people WHERE uid = '2' LIMIT 1 fetch_object => Returns the current row of a result set as an object
$sql = "SELECT * from contacts where = $conn->query($sql); $allData =$result -> fetch_object(); var_dump($allData); fetch_assoc => Fetch a result row as an associative array
$sql = "SELECT * from contacts where = $conn->query($sql); $allData =$result -> fetch_assoc(); var_dump($allData); You can use fetch_assoc or fetch_object according to your need.
mysql_fetch_assoc this extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead use mysqli_fetch_assoc — this will fetch a result row as an associative array.
Example 1: Object oriented style
$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 1"; $result = $mysqli->query($query); $row = $result->fetch_assoc(); echo $row['Name']; Example 2: Procedural style
$link = mysqli_connect("localhost", "my_user", "my_password", "dbname"); /* check connection */ if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 1"; if ($result = mysqli_query($link, $query)) < /* fetch associative array */ $row = mysqli_fetch_assoc($result); echo $row['Name']; >How to Select First Row Only in PHP MySQL?
This is a short guide on how to select first row only in php mysql. I would like to share with you select first row of database sql with code examples. it’s simple example of display only first row mysql using php. you will learn php and mysql select first row only.
To return only the first row that matches your SELECT query, you need to add the LIMIT clause to your SELECT statement.
$sql = CREATE TABLE students (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
subject varchar(20),
gender varchar(20)
);
Insert some records in the table using insert command :
$sql = INSERT INTO students (name, subject, gender) VALUES(‘Mark’, ‘Math’, ‘male’);
$sql = INSERT INTO students (name, subject, gender) VALUES(‘Sarah’, ‘English’, ‘female’);
$sql = INSERT INTO students (name, subject, gender) VALUES(‘Nathan’, ‘English’, ‘male’);
Display all records from the table using select statement :
+————+————-+————+
| Id | Name | subject | gender |
+————+————-+————+
| 1 | Mark | Math | male |
| 2 | Sarah | English | female |
| 3 | Nathan | English | male |
+————+————-+————+
To return only the first row, you need to execute the following SQL query:
SELECT * FROM students LIMIT 1;
+————+————-+————+
| Id | Name | subject | gender |
+————+————-+————+
| 1 | Mark | Math | male |
+————+————-+————+
✌️ Like this article? Follow me on Twitter and Facebook. You can also subscribe to RSS Feed.