Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

PHP Variables inside SQL

Codes here !

Moderators: macek, egami, gesf

PHP Variables inside SQL

Postby mikeh0001 » Mon Nov 12, 2012 4:04 pm

I was wondering if someone can explain the syntax for when a php variable is used inside an SQL statement (as seen below). It appears that when the variable refers to a string, the format is:

<column_name> = ' ".$variableName." ' But I've also seen: <column_name> = ' {$variableName} '

And when the php variable refers to a number, the format seems to be:

<column_name> = " .$variableName. "

(Note: I have used extra space around quotes only for readability)

Can someone explain the above syntax?

Thanks, Mike
mikeh0001
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Sep 11, 2012 5:26 pm

Re: PHP Variables inside SQL

Postby 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: 773
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Postby mikeh0001 » Tue Nov 13, 2012 5:35 pm

I'm sorry, but I'm afraid I still don't get it. I'm not sure why the double quotes are concatenated onto the php variable. Why not just put the variable inside the single quotes like: stringField = '$stringVariable1' ?


Thanks for your help, Mike
mikeh0001
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Sep 11, 2012 5:26 pm

Re: PHP Variables inside SQL

Postby 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: 773
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Postby mikeh0001 » Wed Nov 14, 2012 5:38 pm

Yes, I'm finally getting it. I just wasn't looking at the overall expression. I was thinking that the double quotes were for the php variable for some unknown reason, which, as you pointed out, is not the case.

Thanks very much for your assistance. I appreciate the help.

Mike
mikeh0001
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Sep 11, 2012 5:26 pm

Re: PHP Variables inside SQL

Postby seandisanti » Thu Nov 15, 2012 8:50 am

No problem, man, glad to help
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 773
Joined: Mon Oct 01, 2012 12:32 pm

Re: PHP Variables inside SQL

Postby lanshan75 » Sat Dec 08, 2012 10:19 pm

Guys,do you love cats, i am a girl who love cats very much,i just want to find some new friends to share the happiness of keeping cats.
__________________________________
share love,share happy,share useful information about Buy Diablo 3 Gold and RS Gold
lanshan75
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sat Dec 08, 2012 10:14 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron