4
\$\begingroup\$

Building a 'card' database: I'm simply learning to take input data and store to database. Incorporating JSON, PDO, SQL, and enforcing my general coding skills through PHP, hopefully.

$query = $dbh->prepare("SELECT * FROM " . $table);
$query->execute();

$result = $query->fetchAll(PDO::FETCH_CLASS);

$temp = array();
$data = array();

foreach($result as $key => $val){
    foreach($val as $key2 => $val2){
        $temp[$key2] = $val2;
    }
    array_push($data, $temp);       
}

echo json_encode($data);

Is there a better way to do what I am attempting?

\$\endgroup\$
11
\$\begingroup\$

Yes, there sure is. Effectively, what you seem to be doing can be reduced to this:

$stmt = $dbh->query("SELECT * FROM " . $table);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data);

I'll explain how I got to those three lines above, simplifying your code step by step.
The inner loop just does not make sense:

foreach($val as $key2 => $val2){
    $temp[$key2] = $val2;
}
array_push($data, $temp);  

What this does, is iterate over $val, which is either an iterable object, or an array. When doing so, you're creating a new array $temp, which is, essentially an array-version of $val. Of course, $temp is never "cleared", so the second time the inner loop is performed, $temp will be assigned new keys and values.
In short, suppose $temp looks like this, after the first iteration:

array('foo' => 123);

Then $data will look like this:

array(array('foo' => 123));

The second time, $temp might look like this:

array('bar' => 1234, 'foo' => 123);

Or, if $val had a key/property called foo, then $temp['foo'] will simply be reassigned to the new value, and $temp will look like

array('foo' => 'new value');

Either way, $data will look like:

array(array('foo' => 123), array('foo' => 'new value'));

But why the loop? Why don't you simply cast the $val to an array, and push it into the $data array?

foreach($result as $val)
{//you're not using the $key anywhere, so you can omit it
    array_push($data, (array) $val);
}

That's a hell of a lot shorter, isn't it? But you can simplify this even more. You're fetching the result as objects, only to convert it to an array. Why don't you just fetch the results as an array from the off? Simply change:

$result = $query->fetchAll(PDO::FETCH_CLASS);

To this:

$data = $query->fetchAll(PDO::FETCH_ASSOC);

You won't need the loops at all after this.
Just as an asside, fetching the results of a query is often done using a while loop. I'll save you the inner-workings-details, but last time I checked, a while loop was actually marginally more performant than a fetchAll call... The only way to be sure is to benchmark both versions, but I tend to prefer the following:

$data = array();
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
    $data[] = $row;
}

Ah well, the result is the same, anyway.

Other thoughts:
There are a couple of things you might want to consider, too:

$query = $dbh->prepare("SELECT * FROM " . $table);

Is not the safest way to go about your business: What is the value of $table? If it's a value that comes from the client-side, who's to say it doesn't contain an invalid table name? something like another_db.secret_table would make for the following query:

SELECT * FROM another_db.secret_table

Fetching data that possibly wasn't intended for the client to see. That's a security issue. And a big one at that.
Next, the variable $query is not the best name. PDO::prepare returns an instance of PDOStatement, which is a prepared statement, which isn't a query anymore. That's why you often see the return value of PDO::prepare being assigned to a variable called $stmt.

Either way, with a query without a WHERE clause, prepared statements only cause more overhead. You're better of executing it from the off. Prepared statements are there when you want to insert variables into a query:

$stmt = $pdo->prepare('SELECT * FROM tbl WHERE field = :fieldVal');
$result = $stmt->execute(array(':fieldVal' => $_POST['fieldVal']));

As an added bonus, you can re-use the same prepared statement over and over, to perform the same query a couple of times:

$stmt = $pdo->prepare('SELECT * FROM tbl WHERE field = :fieldVal');
$vals = array('foo', 'bar');
foreach($vals as $val)
{
    echo 'Executing query with value: ', $val, PHP_EOL;
    $result = $stmt->execute(array(':fieldVal' => $val));
    //process $result...
}

The code above will perform the same query twice, once for the value "foo" and once with the value "bar".

\$\endgroup\$
  • \$\begingroup\$ Thanks for all of the detail and explaination. I've learned a lot! \$\endgroup\$ – Qwiso Dec 13 '13 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.