Help > SQL Query Brings up Blank Page

[damanic]

Hi there,

Im trying to write an SQL query that takes info from two tables and matches them by ID.

However when I use the code it takes out my whole page - nothing shows - not even the html on the page.

My query 
[PHP//connect to server with username and password
$connection = mysql_connect ("localhost","blah", "blah") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("china",$connection) or die ("Cannot connect to database");

//SQL query

$tealink = mysql_query(
"SELECT description, taste, uses, benefits, name, image_url, product_url
FROM clproducts, teainfo WHERE offer_id=teainfo.offer_id_info");

while ($tea = mysql_fetch_array($tealink)) {
$description = $tea["description"];
$taste = $tea["taste"];
$uses = $tea["uses"];
$benefits = $tea["benefits"];
$name = $tea["name"];
$image_url = $tea["image_url"];
$product_url = $tea["product_url"];

// Display the info
echo( "<p>$description $taste $uses $benefits $image_url<br></br>" .
"(by <a href='$product_url'>$name</a>)</p>" );
}

Can anyone tell me what I have missed here?

M.

[Roger_Ramjet]

Try learning how to write a query with a JOIN correctly, and pay especial attention to the type of join you need and how to specify the join condition . Also bear in mind that when dealing with multiple tables, sql needs to know which table a column is in.

In other word RTFM

[damanic]

I am trying to learn to do it correctly! And that code I put up there was taken from a tutorial manual! So yes I am trying to read the manual but not all of it makes sence!

I have looked at the page you've given me and come up with this select query.

Any better?

$tealink = mysql_query(
"SELECT t2.description, t2.taste, t2.uses, t2.benefits, t1.name, t1.image_url, t1.product_url
FROM clproducts AS t1, teainfo AS t2 WHERE t1.offer_id=t2.offer_id_info");

Still blank 🙁

[Roger_Ramjet]

Well, it's a start.

Most of the books and tutorials out there are crap. PHP and MySQL are developing so fast that code gets out-of-date very quickly. At least that is the charitable explanation for the examples from them that we see here, code that just can not work on current versions, and maybe never could on any version.

Your query is better, and would be perfect if you got the join condition right. That's the ON or USING bit, not the where clause, and in this case would be

$sql = "SELECT t2.description, t2.taste, t2.uses, t2.benefits, t1.name, t1.image_url, t1.product_url
FROM clproducts AS t1, teainfo AS t2 ON t1.offer_id=t2.offer_id_info";
$tealink = mysql_query($sql) or die($sql . '<br />' . mysql_error());  

// will print out the query and any error on failure
$count = $mysql_num_rows($tealink);
echo $count . ' rows found';
// this will show how many records were found.

Now, if you are not getting any results then it will be because there are no rows where the ids match.

[damanic]

Thanks!

It still doesnt work but at least something shows up on the page 🙂

I get the following message

SELECT t2.description, t2.taste, t2.uses, t2.benefits, t1.name, t1.image_url, t1.product_url FROM clproducts AS t1, teainfo AS t2 ON t1.offer_id=t2.offer_id_info

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 'ON t1.offer_id=t2.offer_id_info' at line 2

Looks like it doesnt like the ON command! :o

[Roger_Ramjet]

No, it does not like the ON clause with the implicit join (just listing tables with a comma). My fault, should have checked what I was copy/pasting.

Just copy/paste this exactly

 $sql = "SELECT t2.description, t2.taste, t2.uses, t2.benefits, t1.name, t1.image_url, t1.product_url 
FROM clproducts AS t1 INNER JOIN teainfo AS t2 ON t1.offer_id=t2.offer_id_info"; 

Now, sql is one language that will murder you if you get into bad habits: and the short-cuts that mysql accepts are some of the worst habits you can ever develop. Oh fine! You can save a few key strokes here and there, then you try and work with another db and see what happens.

So, over the years I have developed certain practices, and forget that not everyone else uses them. I ALWAYS specify join types explicitly, always specify join conditions with ON or USING, never a WHERE clause. Most times it makes no difference, but when it does it really matters.

PS you got something on the page because of the 'or die($sql . '<br />' . mysql_error())' bit. Basic construction to use when developing your scripts so you can see what is going wrong. You should always use it when developing a page or site, and remove it from the live version. Either output something for the user, or add @ to suppress any error messages.

[damanic]

Damn it I feel like I've sucked you into the hell of this query!

Pasted as you put it - Code looks like this;

//our SQL query

$sql = "SELECT t2.description, t2.taste, t2.uses, t2.benefits, t1.name, t1.image_url, t1.product_url 
FROM clproducts AS t1 INNER JOIN teainfo AS t2 ON t1.offer_id=t2.offer_id_info"; 
$tealink = mysql_query($sql) or die($sql . '<br />' . mysql_error());   

// will print out the query and any error on failure 
$count = $mysql_num_rows($tealink); 
echo $count . 'rows found'; 
// this will show how many records were found.

?>

No error this time - blank page!