PHP PDO column name thru switch function structure

Question

I am trying to use a switch function for security to get a column name I will use in the mysql query. I am not that familiar with php enough to fix. Currently the testing echo returns, but not the query results. I was hoping to keep the same structure to keep it clean if possible. Thanks for any help.

I got the switch code from another post: Can PHP PDO Statements accept the table or column name as parameter?

Here is my attempt trying to integrate it:

<?php
//1. Create a database connection
require_once('configfile.php');
    $mysql_host = DB_HOST;
    $mysql_database = DB_NAME;
    $mysql_username = DB_USER;
    $mysql_password = DB_PASS;

try {
    $db = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
    // set the PDO error mode to exception
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8    

} catch (Exception $e) {
    die("Unable to connect: " . $e->getMessage());
}    

$building = $_POST["building"];
$shift = $_POST["shift"];    

function buildQuery( $shift ) 
{
    switch($shift)
    {
        case 'first':
            $column = 'columnA';
            break;    

        case 'second':
            $column = 'columnB';
            break;
    }    

    echo "This is the column : $column"; //testing
}    


try {
    // Return errors
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Begin transaction
    $db->beginTransaction();
// Query 1    

    buildQuery($shift);
    $stm= $db->prepare("SELECT definition FROM schedules
        WHERE building = :building
        AND $column = '1'");             

    $stm->bindParam(':building', $building, PDO::PARAM_STR);     
    $stm->execute();
    foreach( $stm as $row )
    {
        echo $row['definition'] . "|";
    };    

    $db->commit();
    $db = null;        // Disconnect
} catch (Exception $e) {
    // If transaction fail, use checkpoint and rollback
    $db->rollBack();
    echo "ColumnSwitch Failed: " . $e->getMessage().'<br />';
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}    

?>

Just out of curiosity, what should happen if `$shift` isn't "first" or "second"? — Chris Forrence, Nov 29 '18 at 22:29
@ Chris If not 1st or 2nd, like 3rd it will return empty results. I look for empty result when I get the data back and act on it there — newpie, Nov 29 '18 at 22:31
Right now, your query would look something like "SELECT definition FROM schedules WHERE building :building AND = '1'"; I'd suggest including a default column or potentially adding a bail-out if the shift isn't what you're expecting — Chris Forrence, Nov 29 '18 at 22:34

score 2 · Accepted Answer · answered Nov 29 '18 at 22:28

2

You should change buildQuery to return the value you want.

function buildQuery( $shift ) 
{
    switch($shift)
    {
        case 'first':
            $column = 'columnA';
            break;    

        case 'second':
            $column = 'columnB';
            break;
    }    

    return $column;
}

And then call it like this:

$column = buildQuery($shift);

The other option would be to make $column a global variable but that is not really good practice so I don't recommend it.

answered Nov 29 '18 at 22:28

Cfreak

19,191
6
49
60

2

To add to _why_ it isn't currently working, the magic words are "[variable scope](http://php.net/manual/en/language.variables.scope.php)"; basically, the global value of `$column` isn't normally accessible inside of a function, and the function has its own `$column` variable. – Chris Forrence Nov 29 '18 at 22:31
@Cfreak, yes that was it, I did try that return $ column piece prior but didn't realize this piece $column = buildQuery($shift); so it did not work for me. I will mark as answer after the timer is up. Thank you for quick reply – newpie Nov 29 '18 at 22:32

PHP PDO column name thru switch function structure

1 Answers1