- 5 years ago
- Zaid Bin Khalid
- 5,808 Views
-
4
The SQL SELECT query is utilized to bring information from the MySQL database. So, you can run this query in the MySQL command line and in any other language like PHP.
Language structure.
SELECT field1, field2,...fieldN TABLE1, TABLE2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
Here is a nonexclusive SQL linguistic structure of SELECT direction to get information from the MySQL table.
- You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of the SELECT command.
- You can specify stars (*) in place of fields. In this case, SELECT will return all the fields of the selected table.
- You can fetch data from the database table with the help of SELECT Query.
- You can specify any condition using the WHERE clause. And it is written just after the TABLE name in the SQL statement.
- You can specify an offset using OFFSET from where SELECT will start returning records. By default, offset is zero.
- You can limit the number of returns using the LIMIT attribute.
A command-line example is given below.
This will use SQL SELECT command to fetch data from MySQL table YOURTABLENAME.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use YOURDBNAME;
Database changed
mysql> SELECT * from YOURTABLENAME;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
+----+------+
3 rows in set (0.00 sec)
mysql>
Getting Data Using PHP Script.
You can utilize the same SQL SELECT direction into PHP work mysqli_query(). So, this capacity is utilized to execute SQL query later another PHP work mysqli_fetch_assoc() can be utilized to bring all the choose information. This capacity returns push as a cooperative exhibit, a numeric cluster, or both. This capacity returns FALSE if there are no more columns.
The following is a straightforward guide to get records from the Database Table.
#Selecting Table in MySQLi Server
<?php
//Start of code of connecting with DB
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'YOUR-DB-NAME';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
//End of code of connecting with DB
//Getting all records
$sql = 'SELECT field1, field2 FROM YOURTABLE WHERE 1';
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)){
echo "field1: " . $row["field1"]. "<br>";
echo "field2: " . $row["field2"]. "<br>";
}
}else{
echo "No Record(s) Found!";
}
mysqli_close($conn);
?>
The substance of the lines is allocated to the variable $row and the qualities in the push are then printed.
Remember: The SQL statement only executes in PHP when you made a connection with DB. There are many ways to connect with DB in PHP.
SELECT query with prepared statements.
You can also write SELECT Query with the help of the prepared statement in PHP the code is given below. In bind_param() first parameter types there are few types also listed below.
<?php
// SQL with parameters
$sql = "SELECT * FROM users WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$user = $result->fetch_assoc(); // fetch data
?>
In the above example, I do not use a while statement you can also use while loop to learn how loops work in PHP click here. The below code will help you to get all the data from the select statement.
while ($row = $stmt->fetch_assoc()) {
echo $row['name'];
}
- 5 years ago
- Zaid Bin Khalid
- 5,808 Views
-
4
