PHP MySQL connection example

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 «

«;
// output data of each row
while($row = $result->fetch_assoc()) echo «

«;
>
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 «

» . parent::current(). «

«;
>

$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();

Источник

Многоуровневый select из базы данных

Примеры построения многоуровневых выпадающих списков (select option) и базы данных с применением рекурсии PHP.

В скриптах используется MySQL-таблица `category` с полями `id` , `parent` , `name` , где поле `parent` содержит id родителя.

Оформление вложенности пробелами

1. В начале получаем все записи из БД в виде ассоциативного массива.
2. С помощью функции array_to_tree() преобразуем его в древовидный, к элементам массива добавляется элемент «children» в который перемещаются все дочерние элементы.
3. С помощью функции out_options() рекурсивно выводятся все элементы массива.
4. Во втором аргументе функции out_options() указывается id элемента, которому нужно установить selected .

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < $out .= '$out .= '>'; if ($level > 1) < $out .= str_repeat('#emsp;', $level - 1); >$out .= $row['name'] . ''; if (!empty($row['children'])) < $out .= out_options($row['children'], $selected_id, $level); >> return $out; > ?>

Результат:

Оформление символами псевдографики

Оформление ветвей дерева с помощью символов ├ и └:

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < $out .= '$out .= '>'; if ($level > 1) < if ($level >2) < $out .= str_repeat(' ', $level - 1); >$keys = array_keys($array); $last_keys = $keys[count($array) - 1]; if ($last_keys != $i) < $out .= '├'; >else < $out .= '└'; >> $out .= $row['name'] . ''; if (!empty($row['children'])) < $out .= out_options($row['children'], $selected_id, $level); >> return $out; > ?>

Результат:

Использование optgroup

Использование . оправдано если необходимо выбрать только крайнюю категорию в дереве, но optgroup не поддерживает вложенность и никакие пробельные символы в начале label=». » . Поэтому в примере используется — – широкое тире.

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_optgroup_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < if (empty($row['children'])) < $out .= '$out .= '>'; if ($level > 1) < $out .= str_repeat('#emsp;', $level - 1); >$out .= $row['name'] . ''; > else < $out .= ''; $out .= out_optgroup_options($row['children'], $selected_id, $level); > > return $out; > ?>

Источник

Learning The Concept Of Select Query In PHP

We learned how to link a PHP web application to a MySQL database server in a previous PHP post. With the help of a PHP form example, we will learn how to pick data from a mysql database in this PHP lesson.

Basics to Advanced — Learn It All!

We create a simple select query in PHP form with a textbox and a button control in this example. While clicking the select button, we select data from the database to display on the webpage. We enter an integer id value in a textbox, and the Id-by-Id record is displayed on the result screen.

Example

//connection to the mysql database,

$dbhandle = mysqli_connect($hostname, $username, $password,$database);

$result = mysqli_query($dbhandle, «SELECT ID, Name, City FROM StudentMst where );

$result = mysqli_query($dbhandle, «SELECT ID, Name, City FROM StudentMst» );

//fetch the data from the database

while ($row = mysqli_fetch_array($result))

Output

What Is a SELECT Query?

To get data from the MySQL database, use the select query in PHP command. This command can be used at the mysql> prompt as well as in any PHP script.

The following is a generic select query in PHP syntax for retrieving data from a MySQL table using the SELECT command.

In SQL, ‘select’ queries are used to retrieve one or more records from a table/database, and can also support various condition clauses depending on the needs of the user. The resulting data set is temporarily kept on an output table set, also known as a «result-set.» If no condition statements are included in the ‘select’ query, it will return the entire table. If there are filters or conditions included in the ‘select’ query, it will return only the data you want.

Basics to Advanced — Learn It All!

Syntax of SQL

One of the most basic commands in a relational database management system is select. The select query in PHP keyword can be used as a prefix to select records from a table. The select query returns a set of records from one or more tables specified.

• SELECT is a command that can be used to choose a record from a table.
• The columns of the table My table name are column A>, column B>, and so on.
• The name of a table is my table name>.

Example

Query: SELECT Student_ID, First_name, Last_name from Student;

Output

SELECT Query using Object-Oriented Method

SELECT Query Using PDO Method

There are numerous ways to use PDO to run a select query in PHP query, which differ primarily in terms of the presence of parameters, the type of parameters, and the return type.

We’ll look at examples for each circumstance so you may pick the one that best fits your needs. Simply ensure that you have a properly set PDO connection variable in order to perform SQL queries using PDO and receive error notifications.

However, most queries require the usage of one or more variables, in which case a prepared statement (also known as a parameterized query) should be used, which involves first creating a query with parameters (or placeholder marks) and then running it, sending variables separately.

Both positional and named placeholders are available in select query in PHP. Positional placeholders for simple queries are preferable because they are less verbose; however, opinions on the same may vary.

Selecting Multiple Rows

If rows are to be handled one by one, this procedure may be advised. For example, if a select query in PHP processing is the only action required, or if the data must be pre-processed in some way before usage.

However, invoking the wonderful helper method fetchAll is the most preferable technique to get several rows that will be shown on a web page (). It will save all of the rows returned by a query in a PHP array, which can then be used to print data using a template (which is considered much better than echoing the data right during the fetch process).

Basics to Advanced — Learn It All!

How to Implement a SELECT Query?

The relational database can be used to execute the select statement indicated above. To choose the records, we can utilize a variety of select statement queries. A result set is always returned by the choose statement. There may be zero (0), one, or several records in this result set. We can use a variety of other options in the choose statement to attain the desired outcome. The JOIN keyword can be used to select records from two or more tables. To get records from one or more tables, you can use two or more select statements in a variety of ways. Any table should have a primary key so that each record may be uniquely identified.

With the select query in PHP statement, we may use the optional clause listed below:

• WHERE: We’ve already seen examples of this.
• Before utilizing the aggregate function, you must first GROUP BY.
• HAVING: We can use this over the GROUP BY statement to perform an aggregate function.
• ORDER BY: This can be used in conjunction with SELECT to sort the order of the results.
• AS: We’ve seen this before while trying to find the total number of records in a table. This AS can be used to create an alias for the selected column or the entire table.

Conclusion

The SQL select statement can be used to choose the required columns or records based on the business requirements. The select query in PHP command is used to select a record from a table in almost every relational database. Select can be used in a variety of ways. We can choose records based on conditions. On one or more tables, the select operation can be used. With the SELECT statements, we may use a variety of different commands. Relational database management systems include MySQL and ORACLE.

If you want to learn more about select query in PHP and other related topics, enroll in the Full Stack Web Development course from Simplilearn and accelerate your career as a software developer and become a full-stack technologist.

Find our Post Graduate Program in Full Stack Web Development Online Bootcamp in top cities:

Name	Date	Place
Post Graduate Program in Full Stack Web Development	Cohort starts on 15th Aug 2023, Weekend batch	Your City	View Details
Post Graduate Program in Full Stack Web Development	Cohort starts on 12th Sep 2023, Weekend batch	Your City	View Details
Post Graduate Program in Full Stack Web Development	Cohort starts on 10th Oct 2023, Weekend batch	Your City	View Details

About the Author

Simplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

Recommended Programs

Post Graduate Program in Full Stack Web Development

*Lifetime access to high-quality, self-paced e-learning content.

Источник