Php get row from mysql

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

Источник

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.

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.

Источник

mysqli_fetch_row

Fetches one row of data from the result set and returns it as an enumerated array, where each column is stored in an array offset starting from 0 (zero). Each subsequent call to this function will return the next row within the result set, or null if there are no more rows.

Note: This function sets NULL fields to the PHP null value.

Parameters

Return Values

Returns an enumerated array representing the fetched row, null if there are no more rows in the result set, or false on failure.

Examples

Example #1 mysqli_result::fetch_row() example

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT Name, CountryCode FROM City ORDER BY ID DESC» ;

$result = $mysqli -> query ( $query );

/* fetch object array */
while ( $row = $result -> fetch_row ()) printf ( «%s (%s)\n» , $row [ 0 ], $row [ 1 ]);
>

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT Name, CountryCode FROM City ORDER BY ID DESC» ;

$result = mysqli_query ( $mysqli , $query );

/* fetch associative array */
while ( $row = mysqli_fetch_row ( $result )) printf ( «%s (%s)\n» , $row [ 0 ], $row [ 1 ]);
>

The above examples will output something similar to:

Pueblo (USA) Arvada (USA) Cape Coral (USA) Green Bay (USA) Santa Clara (USA)

See Also

• mysqli_fetch_array() — Fetch the next row of a result set as an associative, a numeric array, or both
• mysqli_fetch_assoc() — Fetch the next row of a result set as an associative array
• mysqli_fetch_column() — Fetch a single column from the next row of a result set
• mysqli_fetch_object() — Fetch the next row of a result set as an object
• mysqli_query() — Performs a query on the database
• mysqli_data_seek() — Adjusts the result pointer to an arbitrary row in the result

User Contributed Notes 4 notes

It’s worth noting that the MySQLi functions (and, I presume, the MySQL functions) fetch a string regardless of the MySQL data type. E.g. if you fetch a row with an integer column, the corresponding value for that column and row will still be stored as a string in the array returned by mysql_fetch_row.

Note that mysqli_fetch() is deprecated but still is in PHP function list. mysqli_fetch_row() is nowadays mysql procedural style used, but is not listed in PHP functions.

Remember that fetch() and fetch_row() are two different things, and differ in the way to use them.

— fetch() is used on a statement (like an executed prepared statement) and needs to be used in association with bind_result().

— fetch_row() is used on a result (like the result of query()).

As a consequence, if you want to use to use fetch_row() with an executed prepared statement, first you’ll have to get the result out of this statement with mysqli_store_result() or mysqli_use_result().

4. Be careful when porting from ext/mysql to ext/mysqli. The following
functions return NULL when no more data is available in the result set
(ext/mysql’s functions return FALSE).

— mysqli_fetch_row()
— mysqli_fetch_array()
— mysqli_fetch_assoc()

Источник