InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

Menu
  • Home
  • Sitemap

Python Programming Language Best Tutorials and Code Examples

Learn Python Right Now!
Home
PHP
Querying an SQL Database in PHP
PHP

Querying an SQL Database in PHP

InfinityCoder December 1, 2016

You want to retrieve some data from your database.

Use PDO::query() to send the SQL query to the database, and then a foreach loop to retrieve each row of the result, as shown in Example 10-9.
Example 10-9. Sending a query to the database

1
2
3
4
$st = $db->query('SELECT symbol,planet FROM zodiac');
foreach ($st->fetchAll() as $row) {
   print "{$row['symbol']} goes with {$row['planet']} <br/>\n";
}

The query() method returns a PDOStatement object. Its fetchAll() method provides a concise way to operate on each row returned from a query.
The fetch() method returns a row at a time, as shown in Example 10-10.
Example 10-10. Fetching individual rows

1
2
3
4
$rows = $db->query('SELECT symbol,planet FROM zodiac ORDER BY planet');
$firstRow = $rows->fetch();
print "The first results are that {$firstRow['symbol']} goes with ↵
{$firstRow['planet']}";

Each call to fetch() returns the next row in the result set. When there are no more rows available, fetch() returns false.
By default, fetch() returns an array containing each column in the result set row twice —once with an index corresponding to the column name and once with a numerical index.

That means that the $firstRow variable in Example 10-10 has four elements: $firstRow[0] is Archer, $firstRow[1] is Jupiter, $firstRow[‘symbol’] is Archer, and $firstRow[‘planet’] is Jupiter.
To have fetch() return rows in a different format, pass a PDO::FETCH_* constant to query() as a second argument.

You can also pass one of the constants as the first argument to fetch(). The allowable constants and what they make fetch() return are listed in Table 10-1.

Table 10-1. PDO::FETCH_* constants

Constant Row format
PDO::FETCH_BOTH

PDO::FETCH_NUM

PDO::FETCH_ASSOC

PDO::FETCH_OBJ

PDO::FETCH_LAZY

 

 

Array with both numeric and string (column names) keys. The default format.

Array with numeric keys.

Array with string (column names) keys.

Object of class stdClass with column names as property names.

Object of class PDORow with column names as property names. The properties aren’t populated until accessed, so this is a good choice if your result row has a lot of columns. Note that if you store the returned object and fetch another row, the stored object is updated with values from the new row.

In addition to the choices in Table 10-1, there are other ways a row can be structured.
These other ways require more than just passing a constant to query() or fetch(), however.
In combination with bindColumn(), the PDO::FETCH_BOUND fetch mode lets you set up variables whose values get refreshed each time fetch() is called. Example 10-11 shows how this works.
Example 10-11. Binding result columns

1
2
3
4
5
6
7
8
$row = $db->query('SELECT symbol,planet FROM zodiac',PDO::FETCH_BOUND);
// Put the value of the 'symbol' column in $symbol
$row->bindColumn('symbol', $symbol);
// Put the value of the second column ('planet') in $planet
$row->bindColumn(2, $planet);
while ($row->fetch()) {
   print "$symbol goes with $planet. <br/>\n";
}

In Example 10-11, each time fetch() is called, $symbol and $planet are assigned new values. Note that you can use either a column name or number with bindColumn(). Column numbers start at 1.
When used with query(), the PDO::FETCH_INTO and PDO::FETCH_CLASS constants put result rows into specialized objects of particular classes.

To use these modes, first create a class that extends the built-in PDOStatement class. Example 10-12 extends PDOState ment with a method that reports the average length of all the column values and then sets up a query to use it.

Example 10-12. Extending PDOStatement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
class AvgStatement extends PDOStatement {
    public function avg() {
        $sum = 0;
        $vars = get_object_vars($this);
        // Remove PDOStatement's built-in 'queryString' variable
        unset($vars['queryString']);
        foreach ($vars as $var => $value) {
            $sum += strlen($value);
        }
        return $sum / count($vars);
    }
}
$row = new AvgStatement;
$results = $db->query('SELECT symbol,planet FROM zodiac',PDO::FETCH_INTO, $row);
// Each time fetch() is called, $row is repopulated
while ($results->fetch()) {
   print "$row->symbol belongs to $row->planet (Average: {$row->avg()}) <br/>
   \n";
}

In Example 10-12, the second and third arguments to query() tell PDO “each time you fetch a new row, stuff the values into properties of the $row variable.”

Then, inside the while() loop, the properties of $row are available, as well as the newly defined avg()
method.
PDO::FETCH_INTO is useful when you want to keep data around in the same object, such as whether you’re displaying an odd- or even-numbered row, throughout all the calls to fetch().

But when you want a new object for each row, use PDO::FETCH_CLASS. Pass it to query() like PDO::FETCH_INTO, but make the third argument to query() a class name, not an object instance.

The class name you provide with PDO::FETCH_CLASS must extend PDOStatement.

Share
Tweet
Email
Prev Article
Next Article

Related Articles

Reading an HTTP Header in PHP
You want to read an HTTP request header. For a …

Reading an HTTP Header in PHP

Persisting a Local Variable’s Value Across Function Invocations in PHP
You want a local variable to retain its value between …

Persisting a Local Variable’s Value Across Function Invocations in PHP

About The Author

InfinityCoder
InfinityCoder

Leave a Reply

Cancel reply

Recent Tutorials InfinityQuest

  • Adding New Features to bash Using Loadable Built-ins in bash
    Adding New Features to bash Using Loadable …
    June 27, 2017 0
  • Getting to the Bottom of Things in bash
    Getting to the Bottom of Things in …
    June 27, 2017 0

Recent Comments

  • fer on Turning a Dictionary into XML in Python
  • mahesh on Turning a Dictionary into XML in Python

Categories

  • Bash
  • PHP
  • Python
  • Uncategorized

InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

About Us

Start learning your desired programming language with InfinityQuest.com.

On our website you can access any tutorial that you want with video and code examples.

We are very happy and honored that InfinityQuest.com has been listed as a recommended learning website for students.

Popular Tags

binary data python CIDR convert string into datetime python create xml from dict python dictionary into xml python how to create xml with dict in Python how to write binary data in Python IP Address read binary data python tutorial string as date object python string to datetime python

Archives

  • June 2017
  • April 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
Copyright © 2021 InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more
Programming Tutorials | Sitemap