PDO - What if table doesn't exist?

[Korferer]

Here is my code so far (I say it's PDO but it pretty much isn't. What is the object here? The database connection? LOL!);
 

$sql = 
	"
	SELECT 
	SUM(IF(`sent` != 'N' , 1 , 0 )) as 'Emails Sent',	
	SUM(IF(`completed` NOT IN('N','paper') , 1 , 0 )) as 'Completed Electronically', 	
	SUM(IF(`completed` = 'paper' , 1 , 0 )) as 'Completed Manually', 
	SUM(IF(`completed` != 'N' , 1 , 0 )) as 'Total Number Completed',
	SUM(IF(`remindercount` = '1' , 1 , 0 )) as 'Reminder Sent Once',
	SUM(IF(`remindercount` = '2' , 1 , 0 )) as 'Reminder Sent Twice',
	SUM(IF(`remindercount` = '3' , 1 , 0 )) as 'Reminder Sent Thrice'
	FROM `tokens_$survey_id`
	";
				
$statement = $dbh->prepare($sql);
$statement->execute();

$result = $statement->fetch(PDO::FETCH_OBJ);
				
foreach($result as $key => $value) {
	 echo "<tr>
		<td>$key</td>
		<td>$value</td>
	       </tr>";
}

This is all well and good if the tokens_$survey_id table is actually there. Sometimes, for a genuine reason, there won't be a tokens table for that particular survey. How do I account for this? At the moment I get an error..
 

Warning: Invalid argument supplied for foreach() in /var/www/html/index.php on line 149 

I tried this but I am not satisfied this is correct;
 

if(!$result) {
die();
}

I don't want the code to die! If I take out the die() statement then this if is ignored for some reason I don't understand.

[fastsol]

You would need to run a query beforehand to gather the available table names and check if the tokens_$survey_id is in the array of names returned, then only run the next query if it does exist.

[Ch0cu3r]

If the query failed you could use PDO ErroCode to retrieve the error code, if it matches 42S02 (MySQLs error code for table does not exist) then you can output the appropriate error message to the user.

if(!$result)
{
   if($dbh->errorCode == '42S02')
   { 
      // handle table does not exist error
      echo 'Sorry invalid survey id: ' . $survey_id;
   }
   else
   {
      // some other error, handle this differently
   }
}
else
{
    // output results
}

You should also be very careful when using variables in queries as part of column/table names, as this can lead to SQL Injection, regardless of fact you are using prepared statements.  I agree with fastcol you should first get an array of acceptable table names. Then compare $survey_id to see if its acceptable to use.

[kicken]

What you should probably do is re-design your system so that you don't have dynamic table names to start with. Instead $survey_id would be a column in the table and your select would look something like:

$sql = 
	"
	SELECT 
	SUM(IF(`sent` != 'N' , 1 , 0 )) as 'Emails Sent',	
	SUM(IF(`completed` NOT IN('N','paper') , 1 , 0 )) as 'Completed Electronically', 	
	SUM(IF(`completed` = 'paper' , 1 , 0 )) as 'Completed Manually', 
	SUM(IF(`completed` != 'N' , 1 , 0 )) as 'Total Number Completed',
	SUM(IF(`remindercount` = '1' , 1 , 0 )) as 'Reminder Sent Once',
	SUM(IF(`remindercount` = '2' , 1 , 0 )) as 'Reminder Sent Twice',
	SUM(IF(`remindercount` = '3' , 1 , 0 )) as 'Reminder Sent Thrice'
	FROM `tokens`
        WHERE survey_id = ?
	";
				
$statement = $dbh->prepare($sql);
$statement->execute(array($survey_id));

$result = $statement->fetch(PDO::FETCH_OBJ);

[Psycho]

What you should probably do is re-design your system so that you don't have dynamic table names to start with. Instead $survey_id would be a column in the table . . . 

+1

 

What he said. If you are creating dynamic tables, chances are extremely high that you are simply doing it wrong.

[Korferer]

kicken, on 07 Jul 2014 - 10:59 PM, said:

 

What you should probably do is re-design your system so that you don't have dynamic table names to start with. Instead $survey_id would be a column in the table . . .

 

Ha. I would attempt that but it's an open source application - LimeSurvey. And I just have to work with it.

 

I have gone for the Ch0cu3r method except for $dbh->errorCode == '42S02' just doesn't work. Even if I try to print that it just prints nothing.

 

I am also running $survey_id through a function to check if the table exists or not first.

 

function tableExists($pdo, $table) {

    // Try a select statement against the table
    // Run it in try/catch in case PDO is in ERRMODE_EXCEPTION.
    try {	
        $result = $pdo->query("SELECT 1 FROM $table LIMIT 1");
		
    } catch (Exception $e) {
        
	// We got an exception == table not found
        return FALSE;
		
    }
    // Result is either boolean FALSE (no table found) or PDOStatement Object (table found)
    return $result !== FALSE;
}

 Not very glamorous but it'll do.

 

 

[ginerjm]

ErrorCode is a function hence:  

 

 
if($dbh->errorCode() == '42S02')