webdev.gk webdev.gk - 3 months ago 23
MySQL Question

Convert to PDO type mysql_fetch_array

I would like to ask for assistance on how to convert the code below to PDO type especially the mysql_fetch_array. I can make it work properly using this code but I want to convert to PDO.

<?php

$query = "SELECT * from name";
$result = mysql_query($query);

$cols = 6;
echo "<table>";
do {
echo "<tr>";
for ($i = 1; $i <= $cols; $i++) {

$row = mysql_fetch_array($result);
if ($row) {
$name = $row['fname'];
?>
<td>
<table>
<tr valign="top">
<td>
<?php echo '<input type="checkbox" name="name[]" id="name[]" value="' . $name . '"/>' . $name . "\n"; ?>
</td>
<td width="30">&nbsp;</td>
</tr>
</table>
</td>
<?php
} else {
echo "<td>&nbsp;</td>";
}
}
} while ($row);
echo "</table>";
?>

Answer

First off, you can read more at http://php.net/pdo.

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = "SELECT * from name";

$stmt = $dbh->prepare($query);

$stmt->execute();

// Start your html col/row setup

// Loop through your cols

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

For your example it's not necessary to 'prepare' however it is a good practice when you start passing values to your query.