getting single cell from database using PDO

Question

I am trying to get a piece of data from my database but would like to only get one cell using the PDO statement if this is possible.

Below is a screenshot of the table

The table name is called heating

I am trying to get the data from column called 'garage' and row id = 3

I have tried many ways but keep failing. The following is what I have so far but only returns the column name garage for some reason.

I am using the following which gives me the name garage

$room = 'garage';

require_once "connect.php";

$sql = 'SELECT :name FROM heating WHERE id = 3';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':name', $room);
$stmt->execute();

$sw = $stmt->fetch();
echo $sw[0];

If I do the following I gives me the correct outcome but I would like to replace garage with a variable

$sql = 'SELECT garage FROM heating WHERE id = 3';
$stmt = $pdo->prepare($sql);
$stmt->execute();

$sw = $stmt->fetch();
echo $sw[0];

Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) in short you cant bind the column name — , Jun 09 '19 at 21:29
How is this the same, the link you supplied talks about table names being replaced. This is not what i have asked — , Jun 09 '19 at 21:31
binding it, your query ius actully run like this `$sql = 'SELECT "garage" FROM heating WHERE id = 3';` being quotes, you get the word "garage" — , Jun 09 '19 at 21:36
OK so there is no way to pull a column I will have to pull out more data and sieve through it — , Jun 09 '19 at 21:38
Why would you want to replace the column name with a variable, Steve? Maybe this would help you: https://phpdelusions.net/pdo#identifiers — Dharman, Jun 09 '19 at 21:39
You can pull out a single column, you just can't bind the column name `$sql = 'SELECT '.$name.' FROM heating WHERE id = 3';` should probably bind the id :-) — , Jun 09 '19 at 21:40
The reason why i am sending a JSON string to the PHP file with the column name that i would like to get the data from. I could use multiple if statement and select the column data for the room data requested but i thought it would be easier this was — , Jun 09 '19 at 21:42

Dharman · Accepted Answer · 2019-06-09T22:43:50.373

2

You can create a white list of your column names and use it to select the right column. You can check the column against a white list with the help of in_array. The third parameter is very important as it checks that string is a string. You can only then safely concatenate the SQL with your PHP variables using PHP concatenation operator. For the good measure, the column names should be enclosed in backticks `, in case any of your column names is a reserved word or contains special characters.

$whiteListOfHeating = [
    'keyName',
    'den',
    'WC1',
    'hallway',
    'garage'
];

$room = 'garage';
if (in_array($room, $whiteListOfHeating, true)) {
    $sql = 'SELECT `'.$room.'` FROM heating WHERE id = 3';
    $stmt = $pdo->prepare($sql);
    // ...
} else {
    echo 'Invalid column name specified!';
}

edited Jun 09 '19 at 22:43

answered Jun 09 '19 at 22:17

Dharman

30,962
25
85
135

Thank you this seems to work perfectly. Although I do not fully understand it. I have never used in_array and why the period both side the variable. Will have to learn this thanks – Jun 09 '19 at 22:25
@Steve8428 I have updated the answer with more explanation. – Dharman Jun 09 '19 at 22:31
Got it thank you, i can easily do it with out the white list and using the concatenation operator this i did not realize I needed to use like this. 'SELECT '.$room.' FROM heating WHERE id = 3'; I did read about the backticks but could not understand how to implement this so thanks again. I will mark this as the answer – Jun 09 '19 at 22:41
Do not trust the data without checking first. You really need to make sure that only allowed column names are used otherwise your code will be vulnerable to SQL injection! – Dharman Jun 09 '19 at 22:42
OK thanks, I'm not great with SQL so not sure how a SQL injection will occur when only my JQuery script will be calling this php page with the room required. I will take your advice and keep the white list in – Jun 09 '19 at 22:47
1

[SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) happens when you put PHP variables inside SQL string, unless explicitly validated by your code, as is the case with the white list. – Dharman Jun 09 '19 at 22:50

Your Common Sense · Answer 2 · 2019-06-10T08:38:50.427

Sometimes simplest solutions are best.

require_once "connect.php";

$room = 'garage';

$sql = 'SELECT * FROM heating WHERE id = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([3]);
$sw = $stmt->fetch();

echo $sw[$room];

Besides, every time you need such a functionality, in means that most likely your database structure is wrong. A room should be a row, not column

require_once "connect.php";

$room = 'garage';

$sql = 'SELECT value FROM heating_room WHERE heating_id=3 and room = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$room]);
$sw = $stmt->fetchColumn();

echo $sw;

will make it straight

getting single cell from database using PDO

2 Answers2