Php getting data from a database

quinnd6 · 20-02-2015 7:29pm #1

I was just wondering what is php's pdo equivalent of java's resultset.getString?
I'd like to be able to seperate column values and give them a different css styling and am finding it tricky.
Here's an example of what I mean that I'd like to be able to do in php but it's in java

ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String name = rs.getString("name");
            String description = rs.getString("description");
            double price = rs.getDouble("price");
           
        }

FSL · 20-02-2015 9:37pm

Here is an equivalent using mysql and php

<?php
$query = "Query string";
$result=mysql_query($query) or die(mysql_error("Query Failed!"));
$num=mysql_numrows($result);
$i=0;
While ($i < $num) {
$name=mysql_result($result,$i,"name");
$description=mysql_result($result,$i,"description");
$price=mysql_result($result,$i,"price");
$i++;
}
?>

bpmurray · 20-02-2015 9:57pm

Something like:

<?php
try {
   $stmt = $db->prepare("SELECT * FROM foo");
	$stmt->execute();
} catch ( PDOException $ex ) {
	echo "An Error occured when retrieving commenta: " . $ex->getMessage ();
}

if ($stmt->rowCount() < 1) {
   echo 'Nothing returned';
} else {
   while ($row = $stmt->fetch()) {
      echo '<div class="cls1">First col '
           .htmlspecialchars($row['foo'], ENT_HTML5, 'UTF-8', false)
           .'</div>';
      echo '<div class="cls2">'
           .htmlspecialchars($row['bar'], ENT_HTML5, 'UTF-8', false)
           .'</div>';
   }
}
%>

Talisman · 22-02-2015 8:00am

By default the PDO fetch statement returns the data in two arrays. One is an associative array with the field names as keys, the other as a numerical array. You can get an improvement in the performance by specifying the style you want to use as a parameter in the fetch statement.

Modification to bpmurray's code above:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

You can read about the other fetch style options in the PHP.net documentation: PDOStatement::fetch.

quinnd6 · 24-02-2015 10:05pm

Brilliant thanks very much lads.

bpmurray · 25-02-2015 12:34am

Actually, I forgot to mention - you need to create your db connection first. This is very similar to the JDBC mechanism:

// Link to the database server
$db = new PDO ( 'mysql:host=localhost;dbname=mydatabase;charset=UTF-8', 'root', 'passw0rd' );

// Attributes, e.g. if an error occurs, we want to catch it as an "exception"
$db->setAttribute ( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Php getting data from a database

Comments