PHP Converting a PDO Object into array and echo foreach

Question

I'm using the Medoo database Framework. The following query returns a PDO object.

$datas=$database->query('
    SELECT lethal_servers.LETHAL_ServerName AS "lethal_servers LETHAL_ServerName", 
           lethal_servers.LETHAL_ServerID AS "lethal_servers LETHAL_ServerID", 
           lethal_servers.LETHAL_ServerPort AS "lethal_servers LETHAL_ServerPort", 
           lethal_user.LETHAL_UserName AS "lethal_user LETHAL_UserName" 
      FROM (
               (           lethal_servers AS lethal_servers
                INNER JOIN lethal_server_user AS lethal_server_user 
                        ON (lethal_server_user.LETHAL_ServerID  = lethal_servers.LETHAL_ServerID )
               )
                INNER JOIN lethal_user AS lethal_user 
                        ON (lethal_server_user.LETHAL_UserID  = lethal_user.LETHAL_UserID )
           )
     WHERE (lethal_user.LETHAL_UserName = $lethal_UserName)
';

Using the following doesn't work since it takes $datas as array.

foreach($datas as $data)
{
    echo "servername:" . $data["LETHAL_ServerName"] . " port:" .     $data["lethal_servers.LETHAL_ServerPort"] . "<br/>";
}

I'm struggling to find an answer anywhere on the web. Either it uses deprecated mysql_* functions or does not suit my needs.

With the code above it will return:

Undefined variable: datas
Invalid argument supplied for foreach()

Could someone take a minute and explain to me how I'd go about this? Thanks.

what do you mean doesn't work, you haven't check for any errors yet — Kevin, Feb 15 '16 at 22:50
Please provide more information which driver or framework you are using. Also take a look at the example on the [mysqli execute()](http://php.net/manual/en/mysqli-stmt.execute.php) function. You can fetch every row as an array. — Mischa, Feb 15 '16 at 22:52
Shouldn't `$lethal_UserName` be in quotes because it is a SQL string? You should use prepared statements... or rather this whole thing is in single quotes so that isn't a variable, it is literal. — chris85, Feb 15 '16 at 22:53
@StanleyMachnitzki, start with combining the above parts of code, then tell the result — RomanPerekhrest, Feb 15 '16 at 22:59
the result is: Undefined variable: datas Invalid argument supplied for foreach() — Stanley Machnitzki, Feb 15 '16 at 23:00
can you show that part of code that is placed between the query and foreach loop? — RomanPerekhrest, Feb 15 '16 at 23:04
@RomanPerekhrest There's no part between these. I select and try to return the result — Stanley Machnitzki, Feb 15 '16 at 23:06
I don't know, I don't think this is the actual code, or it is being executed in some other way. The `Invalid argument supplied for foreach()` makes sense because the `$datas` is false, not an array. — chris85, Feb 15 '16 at 23:13

score 0 · Accepted Answer · answered Feb 16 '16 at 00:02

My bet is that the query is failing, and PDO is returning FALSE.

If we don't want to check for PDO errors, we can have PDO throw an exception for us when an error occurs, by setting an attribute:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Otherwise, you need to test $datas. If it's FALSE, then some kine of error has occurred. If your code was working, it would be vulnerable to SQL Injection. I don't know how you can stand working with SQL like that, with unnecessary parens and humongous table aliases. I prefer to use backticks to escape identifiers. (I believe double quotes will work to escape identifiers if sql_mode includes ANSI_QUOTES.)

# enable PDO to throw an exception when an error occurs
$database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

# use backticks rather than double quotes to escape identifiers
# use short table aliases to make the SQL easier to decipher
# eliminate unnecessary parens
# properly escape potentially unsafe values in SQL text
$datas=$database->query('
  SELECT s.LETHAL_ServerName    AS `lethal_servers LETHAL_ServerName`
       , s.LETHAL_ServerID      AS `lethal_servers LETHAL_ServerID`
       , s.LETHAL_ServerPort    AS `lethal_servers LETHAL_ServerPort`
       , u.LETHAL_UserName      AS `lethal_user LETHAL_UserName`
    FROM lethal_servers      s
    JOIN lethal_server_user  r ON r.LETHAL_ServerID  = s.LETHAL_ServerID 
    JOIN lethal_user         u ON u.LETHAL_UserID    = r.LETHAL_UserID
   WHERE u.LETHAL_UserName  = ' . $database->quote( $lethal_UserName ) 
);

# if we haven't enabled PDO exceptions, then we need to check for errors
if(!$datas) {
  # this isn't the best way to handle an error, but at least we handle it
  echo "PDO query returned FALSE, so we know an error has occurred";
  print_r($database->errorInfo());
  exit(1);
}

When you loop through the resultset, the column names are going to be what was specified as a column alias.

For example, this is wrong:

$data[LETHAL_ServerName]

Because there is no column named LETHAL_ServerName returned by the query.

The query assigned the column name as "lethal_servers LETHAL_ServerName". (It's not clear why you need to have the column named with that long name with a space in it; I'm going to assume that it has something to do with the Medoo framework, and leave the column name as is. We'd need to reference the contents of the returned column with a string that matches the assigned column name, e.g.

$data['lethal_servers LETHAL_ServerName']

NOTE: I don't have any experience with the Medoo framework. So I'm just guessing that $database is a reference to the PDO connection object.

This seems to be the answer. Atleast for me. After dumping the `$data` it returned a `boolean`. Thanks again @spencer7593 — Stanley Machnitzki, Feb 16 '16 at 00:07

score -1 · Answer 2 · answered Feb 15 '16 at 23:08

-1

Your code is missing a fetch all statement, so query returns null / undefined.

use

$data = $database->query("SELECT email FROM account")->fetchAll();

and read the docs

answered Feb 15 '16 at 23:08

Mischa

693
8
15

1

PDO query with `while` works fine even without `->fetch`. Try it yourself. – fusion3k Feb 15 '16 at 23:09

PHP Converting a PDO Object into array and echo foreach

2 Answers2