Put a PHP variable in a SQL Query

Question

I have the following code:

try
{
    $sql = 'SELECT id, type, date, amount, description, category 
FROM `transactions`
    WHERE type = "income"
    AND month(date) = '$monthselect'
    ORDER BY `transactions`.`id` DESC
    LIMIT 0,50';
    $result2 = $pdo->query($sql);
}

Now, I want to give this month(Date) a variable which month I want to select. if I put 1, it will give me January. So i thought, if I define a variable with 1, I can use it to select a month, right?

$monthselect = 1;

It doesnt work. What am I doing wrong?

You're using single quotes both in your query, and as string delimiters. That's just going to get confusing. Since you're using PDO, why not look at using a prepared statement, and a bound parameter? — andrewsi
– andrewsi, Commented Jun 19, 2013 at 20:04

nice ass · Accepted Answer · 2013-06-19 20:04:50Z

3

Use prepared statements:

$stm = $pdo->prepare('SELECT id, type, date, amount, description, category 
FROM `transactions`
    WHERE type = "income"
    AND month(date) = ?
    ORDER BY `transactions`.`id` DESC
    LIMIT 0,50');

$stm->execute(compact('monthselect'));
$result2 = $stm->fetchAll();

Since you're not adding "1" directly in your query, I'm assuming here that the variable comes from user input.

answered Jun 19, 2013 at 20:04

nice ass

16.8k8 gold badges54 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user925885 · Accepted Answer · 2013-06-19 20:04:05Z

1

To concatenate strings in PHP you need to use the . operator.

$sql = 'SELECT id, type, date, amount, description, category 
    FROM `transactions`
    WHERE type = "income"
    AND month(date) = ' . $monthselect . '
    ORDER BY `transactions`.`id` DESC
    LIMIT 0,50';

answered Jun 19, 2013 at 20:04

user925885

Comments

Thomas Kelley · Accepted Answer · 2013-06-19 20:06:31Z

1

I'll frequently use double quotes to substitute variables in PHP:

$sql = "SELECT id, type, date, amount, description, category 
    FROM `transactions`
    WHERE type = 'income'
    AND month(date) = $monthselect
    ORDER BY `transactions`.`id` DESC
    LIMIT 0,50";

Note that you need to swap the existing double quotes (inside the string) to single quotes. You can escape them too, but I find this way makes it much more readable.

answered Jun 19, 2013 at 20:06

Thomas Kelley

10.3k1 gold badge38 silver badges43 bronze badges

Comments

Traveling_Monk · Accepted Answer · 2013-06-19 20:18:29Z

1

Your issue is that you are trying to use a variable inside single quotes, inside which php is not translated

I find by using double quote marks around my queries it allows me to not only use variables in them but to also be able to use single quote mark around the values passed to the db

$sql = "SELECT id, type, date, amount, description, category 
FROM `transactions`
WHERE type = 'income'
AND month(date) = $monthselect
ORDER BY `transactions`.`id` DESC
LIMIT 0,50";

edited Jun 19, 2013 at 20:18

answered Jun 19, 2013 at 20:08

Traveling_Monk

7882 gold badges11 silver badges29 bronze badges

3 Comments

Thomas Kelley Over a year ago

Best to remove the single quotes around $monthselect though, since it's an integer, and should be compared to MONTH() as such.

Thomas Kelley Over a year ago

...and you need to escape the double quotes around income, or replace them with single quotes. As it is, this will cause a Parse error. eval.in/34178

Traveling_Monk Over a year ago

Thanks @ThomasKelley i have made those amendments to my answer

Collectives™ on Stack Overflow

Put a PHP variable in a SQL Query

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related