Select data from two tables with a $variable

[LordBucketHead]

Hi, newbie, so new I hope I can ask the question intelligently enough. I also hope I’m in the right forum [sql or php)
MySQL database has two tables;
tblnames > [clmid, clmnames]
tblpol > [clmDate, clmCategory, clmnames, clmComment]

My goal is: The user selects a category from a dropdown list, clicks a button & returns all rows of [tblpol.clmDate, tblnames.clmnames, tblpol.clmComment] data that match the user selection value $Category.

tblpol.clmnames contains only a names id so I don’t want to display that, I want to display the full text name which is stored in tblnames.clmnames I think I need a JOIN to do this. Here is my attempt which doesn’t work of course & I get the error “Unknown column ‘XYZ’ in ‘where clause’”

[php]$clmCategory = $_GET[‘clmCategory’];
$clmCategory = mysql_real_escape_string($clmCategory);
$query = “SELECT tblpol.clmDate, tblnames.clmnames, tblpol.clmComment
FROM tblpol
INNER JOIN tblnames ON (tblpol.clmnames = tblnames.clmnames)
WHERE clmCategory = $clmCategory”;
$qry_result = mysql_query($query) or die(mysql_error());
$display_string = “

”;
while($row = mysql_fetch_array($qry_result)){
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
}
$display_string .= “

$row[clmDate]

$row[clmnames]

$row[clmComment]

”;[/php]

[Strider64]

You really should be using mysqli or PDO instead of mysql which is obsolete.

[LordBucketHead]

Hi Strider64,

thanks for your advice. I’ll look into that.
stackoverflow solved my problem within 1 minute of asking and here’s the solution;

WHERE clmCategory = $clmCategory";
should be
WHERE clmCategory = ‘$clmCategory’";

missing quotes.

Thanks for your help.

[astonecipher]

It’s not the quotes specifically, it’s the lack of prepared statements. Glad it’s fixed though.