Retrieving Results of a Query to MySQL

Retrieving Results of a Query to MySQL

$result = mysqli_query(query); mysql fetch object($result);

The return value of mysql_query() or mysqli_query() is a pointer to the actual resultset. It can be used to iterate through the complete list of entries returned by a SELECT statement. For this, these functions come in handy:

• mysql_fetch_assoc() and mysqli_fetch_assoc() return the current row in the resultset as an associative array (field names become keys) and move farther to the next row.

• mysql_fetch_object() and mysqli_fetch_object() return the current row in the resultset as an object (field names become properties) and move farther to the next row.

• mysql_fetch_row() and mysqli_fetch_row() return the current row in the resultset as a numeric array and move farther to the next row.

Retrieving Data from MySQL (mysqli_fetch.php; excerpt)

<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year<
   /th></tr>
<?php
  if ($db = @mysqli_connect('localhost', 'user',
    'password')) {
    mysqli_select_db($db, 'phrasebook');
    $result = mysqli_query($db, 'SELECT * FROM
       quotes');
    while ($row = mysqli_fetch_object($result)) {
      printf(
 '<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></
   tr>',
        htmlspecialchars($row->id),
        htmlspecialchars($row->quote),
        htmlspecialchars($row->author),
        htmlspecialchars($row->year)
      );
    }
    mysqli_close($db);
  } else {
    echo '<tr><td colspan="4">Connection failed.
      </td></tr>';
  }
?>
</table>

There are other functions, as well; however, these three are the ones that are used more often. The following code uses mysql_fetch_assoc(), whereas the preceding listing prints out the contents of the database table with mysqli_fetch_object(). The main idea is to use a while loopall mysql_fetch_*/mysqli_fetch_* functions return false when no data is left in the resultset.

Retrieving Data from MySQL (mysql_fetch.php; excerpt)

<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year<
   /th></tr>
<?php
  if ($db = @mysql_connect('localhost', 'user',
    'password')) {
    mysql_select_db('phrasebook', $db);
    $result = mysql_query('SELECT * FROM quotes',
      $db);
    while ($row = mysql_fetch_assoc($result)) {
      printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></
   tr>',
        htmlspecialchars($row['id']),
        htmlspecialchars($row['quote']),
        htmlspecialchars($row['author']),
        htmlspecialchars($row['year'])
      );
    }
    mysql_close($db);
  } else {
    echo '<tr><td colspan="4">Connection failed.
      </td></tr>';
  }
?>
</table>

Figure 7.3 shows the contents of the database after some (political) quotes have been filled in. Sorry, I am from abroadI took the first ones I found.