MYSQL query editor, whats with the single quotes? 1

[jpo245]

Hi, I'm having problems with single quotes that are output by my graphical query editor.

This code WORKS:
$query= "SELECT
userlevelid,
userlevelname
FROM
userlevel";
$result=mysql_query($query,$connect) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
$userlevelid = $row['userlevelid'];
$userlevelname= $row['userlevelname'];

$userlevel_details .= <<<EOD
<tr>
<td><center>$userlevelid</center></td>
<td><center>$userlevelname</center><td>
</tr>
EOD;
}

However, my graphical query editor spits out SQL in the following form:

SELECT
`user`.`UserID`,
`user`.`Name`,
`userlevel`.`UserLevelName`
FROM
`user`,
`userlevel`
WHERE
(`user`.`UserLevelID` = `userlevel`.`UserLevelID`)";

And this code DOESNT WORK:

$query= "SELECT
`user`.`UserID`,
`user`.`Name`,
`userlevel`.`UserLevelName`
FROM
`user`,
`userlevel`
WHERE
(`user`.`UserLevelID` = `userlevel`.`UserLevelID`)";


$result=mysql_query($query,$connect) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
$userid = $row['user.UserID'];
$name = $row['user.Name'];
$userlevelname = $row[userlevel.UserLevelName];



$user_details .= <<<EOD
<tr>
<td><center>$userid</center></td>
<td><center>$name</center></td>
<td><center>$userlevelname </center></td>

</tr>
EOD;
}


I've tried all sorts of ways to get the quotes to work out:
$userid = $row['user.UserID'];
$userid = $row[user.UserID];
$userid = $row['`user`.`UserID`'];

I can't seem to get it to work!
Apart from manually removing all the single quotes from the select query, is there any other way i can solve this?

 

[TonyGroves]

The "backticks" (grave accent characters) are only needed to distinguish a field/table name from an SQL reserved word, so they can normally be left out.

Also, in Perl (this is Perl isn't it?), any string enclosed in backticks will be interpreted and executed as a system command, unless the backticks are within a string enclosed by single-quotes.

Therefore, two solutions two your problem are:
(1) Remove the backticks from your SQL (assuming you have avoided using reserved words as field/table names);
(2) Enclose your SQL in single-quotes rather than double-quotes (but of course, this will also prevent the interpolation of expressions within the string).

 

[TonyGroves]

On closer inspection, no it's not Perl! Must be PHP.

However, my first solution should still apply, though I don't know about the second.