PHP Variables inside SQL

Codes here !

Moderators: macek, egami, gesf

Post Reply
seandisanti
php-forum Fan User
php-forum Fan User
Posts: 973
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Post by seandisanti » Tue Nov 13, 2012 1:23 pm

Sure thing. normally your query would look like this if you were putting it directly into mysql or a sql file
SELECT
stringfield,
numericfield
FROM
aTable
WHERE
stringfield = 'value'
AND numericfield > 0
now i'm sure you see that the string literal assigned to the string field is enclosed in single quotes, and there are none for numericfield. Dates or date times can be a little tricky depending on the myriad of different ways that those values can be stored in the database. Sometimes they need quotes, other times they don't. Rather than going through all of the different date/time formats and how to represent them, it's easiest to just do a test case after selecting a valid value from exiting data, and making sure that you format dates accordingly.

Ok so the variables if you were passing variables into the query above would replace the values that the fields are being compared against. since the values themselves are basically being echo'd directly into place, a string value would have no single quotes, and the mysql interpreter would think that you're referencing a nonexistent field instead of a literal string.

seandisanti
php-forum Fan User
php-forum Fan User
Posts: 973
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Post by seandisanti » Wed Nov 14, 2012 8:47 am

The double quotes aren't required, single quotes let sql know it's a string literal. the only reason to have the double quotes is to wrap the entire query string; like:

Code: Select all

<?php
$string = "blah";
$number = 4;

$sql = "SELECT field1, field2, field3 FROM table1 WHERE field4='" . $string . "' and field5=" . $number;
echo $sql;
would output:

SELECT field1, field2, field3 FROM table1 WHERE field4='blah' and field5=4

notice how there's no extra double quote after the variable holding the number, and the one before it is only ending the literal string portion of the query being concatenated before the number?

seandisanti
php-forum Fan User
php-forum Fan User
Posts: 973
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Post by seandisanti » Thu Nov 15, 2012 8:50 am

No problem, man, glad to help

Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 1 guest