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.

Answer 1

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.

Answer 2

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