[RESOLVED] What's wrong with this syntax?

fritobag

I'm getting the following error message:

Using query: select * from Provider where Provider.ProviderId in (select ProviderId from Junction where Junction.CategoryId = 24);. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ProviderId from Junction where Junction.CategoryId = 24)

The message refers to:
<?php
$db = mysql_connect("erased", "erased", "erased");
mysql_select_db("fritoba_forms",$db);
$titlequery = "select CategoryName from fritoba_forms.Category where CategoryId = XXXX;";
$id = $_REQUEST['cat_id'] ;
$titlequery = str_replace("XXXX", $id, $titlequery);
$query = "select * from fritoba_forms.Provider where fritoba_forms.Provider.ProviderId in (select ProviderId from fritoba_forms.Junction where fritoba_forms.Junction.CategoryId = XXXX);" ;
$query = str_replace("XXXX", $id, $query);
$result = mysql_query($query,$db);
$titleresult = mysql_query($titlequery,$db);
echo "<table border=0>\n";
while ($mytitlerow = mysql_fetch_row($titleresult)) {
printf("<tr><td><font class=\"arthead\">%s</font></td></tr>", $mytitlerow[0]);
echo "<hr width=\"100%\">";
}
while ($myrow = mysql_fetch_row($result)) {
echo "<tr><td><table border=0>";
printf("<tr><td align=\"right\" class=\"fieldlabel\">Company Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[1]);
printf("<tr><td align=\"right\" class=\"fieldlabel\">Contact Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[2]);
if($myrow[3]) {
printf("<tr><td align=\"right\" class=\"fieldlabel\">Address:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[3]);
} ...........

The function I'm hoping the code to perform draws from three tables, one being "Junction," which has three columns, JunctionID, ProviderId, and CategoryID.

In our mssql database, the code works, successfully posting all the providers of the selected category. (You can see it at: http://209.35.178.249/home/director...at.php?cat_id=3 )

But the mysql convertion at http://www.birf.info/home/directory/resource_dir.php/
and
http://www.birf.info/home/directory/resource_cat.php/ does not work. We're using mysql 4.0.26.

Can anyone figure out what's wrong with this line of code?

I'd really appreciate the help. Our website provides necessary information to persons affected by brain injury.

bogu

Mysql 4.0.26 dont support subselect, that was implemented only from mysql 4.1.7, I think, but u should go to http://dev.mysql.com/ to read more about it ...

fritobag

I went to http://dev.mysql.com/. Unfortunately I'm a newbie and I couldn't understand what I was looking at. I'm not even sure I'm I'm trying to do a "join," and if so, I couldn't figure out how to write the code that utilizes three tables, put onlt posts from one.

Do you have any idea how to rewrite this line of code in a fashion supported by 4.0.26?

bogu

Try this:

SELECT Provider.* FROM Provider 
LEFT JOIN Junction USING(ProviderId)
WHERE Junction.CategoryId = XXXX

fritobag

Again, please excuse my ignorance. Am I inserting the suggested code correctly?

<?php
$db = mysql_connect("erased", "erased", "erased");
mysql_select_db("fritoba_forms",$db);
$titlequery = "select CategoryName from fritoba_forms.Category where CategoryId = XXXX;";
$id = $_REQUEST['cat_id'] ;
$titlequery = str_replace("XXXX", $id, $titlequery);
$query = "SELECT Provider.* FROM Provider
LEFT JOIN Junction USING(ProviderId)
WHERE Junction.CategoryId = XXXX;
$query = str_replace("XXXX", $id, $query);
$result = mysql_query($query,$db);
$titleresult = mysql_query($titlequery,$db);
echo "<table border=0>\n";
while ($mytitlerow = mysql_fetch_row($titleresult)) {
printf("<tr><td><font class=\"arthead\">%s</font></td></tr>", $mytitlerow[0]);
echo "<hr width=\"100%\">";
}
while ($myrow = mysql_fetch_row($result)) {
echo "<tr><td><table border=0>";
printf("<tr><td align=\"right\" class=\"fieldlabel\">Company Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[1]);
printf("<tr><td align=\"right\" class=\"fieldlabel\">Contact Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[2]);
if($myrow[3]) {
printf("<tr><td align=\"right\" class=\"fieldlabel\">Address:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[3]);
} ...........

If so, I'm getting the following error message:

Parse error: parse error, unexpected T_STRING in /hsphere/local/home/fritobag/birf.info/home/directory/resource_cat.php on line 481

Line 481 refers to the line following the one inserted. I tried removing it, but came up with the same error message.

bogu

NOOOOOO

Look at your code:

$query = "SELECT Provider.* FROM Provider
LEFT JOIN Junction USING(ProviderId)
WHERE Junction.CategoryId = XXXX;

Where did u close your string?

U forgot to close your double quote ...

fritobag

You are the man!!! (or woman...I don't know) Excellent! I've been trying to solve this for almost a week. I learned a few things in the process. Thank you, thank you for your help. I'm very grateful.

bogu

I'm glad that I could help.

Have fun coding. 😃