Главная » Языки программирования

Php read from mysql

На чтение 9 мин
Содержание
  1. Getting Data From MySQL Database
  2. Example
  3. Example
  4. Example
  5. Releasing Memory
  6. Example
  7. PHP MySQL Select Data
  8. Select Data With MySQLi
  9. Example (MySQLi Object-oriented)
  10. Example (MySQLi Procedural)
  11. Example (MySQLi Object-oriented)
  12. Select Data With PDO (+ Prepared Statements)
  13. Example (PDO)
  14. PHP MySQL- Retrieving Data From Table
  15. Before We Start
  16. Retrieve Data from MySQL Table Using PHP
  17. Fetch Data Using MySQLi (Procedural)
  18. Fetch Data Using MySQLi (Object-Oriented)
  19. Fetch Data Using PDO
  20. Conclusion

Getting Data From MySQL Database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Below is a simple example to fetch records from employee table.

Example

Try out following example to display all the records from employee table.

 $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < echo "EMP ID : 
". "EMP NAME :  
". "EMP SALARY :  
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?>

The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.

In above example the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

Example

Try out following example to display all the records from employee table using mysql_fetch_assoc() function.

 $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_assoc($retval)) < echo "EMP ID : 
". "EMP NAME :  
". "EMP SALARY :  
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?>

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example

Try out following example to display all the records from employee table using MYSQL_NUM argument.

 $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_NUM)) < echo "EMP ID : 
". "EMP NAME :  
". "EMP SALARY :  
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?>

All the above three examples will produce same result.

Releasing Memory

Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Читайте также:  Css width table cellspacing

Example

Try out following example

 $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_NUM)) < echo "EMP ID : 
". "EMP NAME :  
". "EMP SALARY :  
". "--------------------------------
"; > mysql_free_result($retval); echo "Fetched data successfully\n"; mysql_close($conn); ?>

While fetching data you can write as complex SQL as you like. Procedure will remain same as mentioned above.

Источник

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

Читайте также:  Html тілінде сілтемелер 10 сынып мж

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

Источник

PHP MySQL- Retrieving Data From Table

Php Mysql Fetch Data From Table

In this tutorial, we will see how can we retrieve the data from the MySQL table and display it on the webpage using PHP. Retrieving data from the MySQL table is a simple process. However, fetching all the data from the table and displaying it on the webpage in the proper manner requires you to write additional lines of code.

In this tutorial, we will go through each and every step that you will need to fetch and display the data using PHP. Moreover, we will see how to retrieve the data from the MySQL table by using MySQLi as well as PDO. So, let’s get started!

Before We Start

As you know, we use the SELECT statement to retrieve the data from one or more tables. You can use joins to fetch data from multiple tables. However, in this tutorial, we will demonstrate to retrieve the data from a single table.

Note that, to retrieve only particular column values from the table, we use the following statement.

SELECT column_names FROM table;Code language: SQL (Structured Query Language) (sql)

Retrieve Data from MySQL Table Using PHP

In this tutorial, we will use an already created table with the data inserted into it as shown below-

Emp Table Data

As you can see, we have three attributes in the table- id, name and city. So, we will display all the table data in the below examples.

Fetch Data Using MySQLi (Procedural)

 $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) < die("Connection failed:" . mysqli_connect_error()); > $query = "SELECT * FROM emp"; $result = mysqli_query($conn, $query); if (mysqli_num_rows($result) > 0) < while ($row = mysqli_fetch_assoc($result)) < echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - City: " . $row["city"] . "
"; > > else < echo "No result found"; > mysqli_close($conn); ?>Code language: PHP (php)

Let’s see how the above program works.

First, we write the SELECT query and assign it to the variable.

Second, we execute the query using the mysqli_query() function. After the query is executed, the result gets stored into the variable $result.

Third, we check if the result that we are getting consists of greater than zero rows using the mysqli_num_rows() function. If there are more than zero rows, the if block executes.

Forth, the mysqli_fetch_assoc() function will create an array of records by using the result that we fetched earlier in the $result variable.

Fifth, we use the WHILE loop to iterate over the array of results. To print id, name and city, we use the column names as the index of arrays.

When we execute the PHP program in the browser, we get the following result.

Fetch Data Using Mysqli Procedural

We have received the data as it is in the table. However, it is not looking good.

To arrange it in the proper format, we can use the HTML table. Check the PHP program below to understand how we can use the HTML table to arrange the data.

 $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) < die("Connection failed:" . mysqli_connect_error()); > $query = "SELECT * FROM emp"; $result = mysqli_query($conn, $query); if (mysqli_num_rows($result) > 0) < echo "
Fetch Data Using MySQLi (Object-Oriented)
 $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > $sql = "SELECT * FROM emp"; $result = $conn->query($sql); echo "Fetch result using MySQLi Object-Oriented Method
"; if ($result->num_rows > 0) < while ($row = $result->fetch_assoc()) < echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - City " . $row["city"] . "
"; > > else < echo "No result found"; > $conn->close(); ?>Code language: PHP (php)
Fetch Data Using Mysqli Object Oriented
Fetch Data Using PDO
  $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = new mysqli($servername, $username, $password, $dbname); try < $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT * FROM emp"); $stmt->execute(); $i = 0; $result = $stmt->fetchAll(PDO::FETCH_ASSOC); echo "Data fetched using PDO
"; foreach ($result as $row) < echo "id hljs-string">'id'] . ", Name hljs-string">'name'] . ", City hljs-string">'city'] . "
"; $i++; > > catch (PDOException $e) < echo "Error: " . $e->getMessage(); > $conn = null; ?>Code language: PHP (php)
Fetch Data Using Mysqli PDO
Conclusion
In this tutorial, we learned how to retrieve data from a MySQL table and display it on a webpage using PHP. We have seen the data retrieving procedure by all methods- MySQLi procedural, MySQLi object-oriented and PDO. We also understood how to arrange the data in the proper format on the webpage using an HTML table. I hope you understood the topic very well. Stay tuned to mysqlcode.com for more interesting tutorials!
Источник

        
Оцените статью

		
    

		
        		            


    

    

        

            
        

    



    

        


            
            


    

        © 2023 Программирование