Help needed with INNER JOIN

damanic

I have the following mysql query that I have changed around numerous times trying to get it to work. What am I doing wrong here?

<?php 

$query = "SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id, teainfo.img, teainfo.teatype FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')";

$result = mysql_query($query);

if (!$result) {
   $message  = 'Invalid query: ' . mysql_error() . "\n";
   $message .= 'Whole query: ' . $query;
   die($message);
}


while ($row = mysql_fetch_assoc($result)) {
$name = $row["clproducts.name"];
$price = $row["clproducts.price"];
$image_url = $row["clproducts.image_url"];
$offer_id = $row["clproducts.offer_id"];
$product_url = $row["clproducts.product_url"];
?>

Sxooter

Ummm. What error are you getting, if any?

Does that query produce any output if you run it from a command line utility?

My guess is that you have no rows in the two tables that match up, but without some more data from you, I'm kinda batting at shadows here.

damanic

Hi Sxooter,

Yes it works as a mysql query but not when I put it in php code. I thought perhaps it needed to be put differently in php.

No Errors... blank page.

Sxooter

Is there more code here that you've not posted, I assume? I.e. the rest of the while loop with print statements and all that?

What do you get for an output of mysql_num_rows etc... ?

P.s. when you say it's the same query, are you printing this one out and cutting and pasting it into the mysql monitor?

krotkruton

You also might want to try adding in some echos to see exactly where the error stops your code. In my limited experience, blank pages usually mean that there is something wrong with the query statement, i.e. you tried to SELECT something that doesn't exist because you mistyped it. One way to see where your code is crashing (when you aren't getting any errors) is to scatter some echos:

<?php 
echo "Before the query<br>";
$query = "SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id, teainfo.img, teainfo.teatype FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')"; 

$result = mysql_query($query); 
echo "after the query<br>";

if (!$result) { 
   $message  = 'Invalid query: ' . mysql_error() . "\n"; 
   $message .= 'Whole query: ' . $query; 
   die($message); 
   echo "At end of the inside of the if<br>";
} 

echo "Outside the if<br>";

while ($row = mysql_fetch_assoc($result)) { 
$name = $row["clproducts.name"]; 
$price = $row["clproducts.price"]; 
$image_url = $row["clproducts.image_url"]; 
$offer_id = $row["clproducts.offer_id"]; 
$product_url = $row["clproducts.product_url"]; 
?>

This way, if your previously blank page just displays "Before the query" then you will know that it is your query that isn't working, and you probably have some typo in it that is causing it to die.

I'm not completely sure, but if you have a typo in your query, your code might not get to the point where you check to see if $result exists, so that might be why you are just getting a blank page. Obviously the echo method isn't a good way to detect errors in the long term, but it works pretty well as a temporary solution to narrow down where your error exists.

krotkruton

I also just noticed that your while loop is never closed with a "}" although I have a feeling you just missed copying that into the post.

damanic

Sxooter: There is more to the code.. here is the code in full. ...

when you say it's the same query, are you printing this one out and cutting and pasting it into the mysql monitor?

I mean I use mysql manager to test the query and it returns results where with my php script It doesnt.

Thanks for all your input - I have managed to get the page to load up but it is not inserting the info. It processes the echo tags but the variables are not replaced with info for eg.

echo

("<br><img src=$image_url>");

will print

<br><img src=>

on the page

My code:

<?php 

$query = "SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')";
$result = mysql_query($query);

if (!$result) {
   $message  = 'Invalid query: ' . mysql_error() . "\n";
   $message .= 'Whole query: ' . $query;
   die($message);
}
while ($row = mysql_fetch_assoc($result)) {
$product_url = $row["clproducts.product_url"];
$name = $row["clproducts.name"];
$price = $row["clproducts.price"];
$image_url = $row["clproducts.image_url"];
$offer_id = $row["clproducts.offer_id"];


echo ("<br><img src=$image_url>"); 
echo ("<span class=\"actlarge\"><b>$name</b></span><br>");
echo ("<span class=\"actprodref\">REF:$offer_id</span><br>");
echo ("<span class=\"actprice\">Price &pound; $price</span><br>");
}
?>

Its really odd...

Oh and Krotkruton - thanks for the advice, it helped me get the page to display... the page wouldnt load because of a syntax error in the echo.

damanic

ne one see it? Perhaps im on my own with this one.. will keep tying! . 🙂

Roger_Ramjet

ECHO the query string BEFORE you run it ALWAYS when developing. Take the echo out when you know it works and have displayed some results.

$query = "SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')";
echo $query;
$result = mysql_query($query) or die(mysql_error());  // like wise display any errors if query fails

damanic

Thans roger - I tried that but it echod

SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')

on screen.

So I done:

<?php 
$query = "SELECT clproducts.product_url, clproducts.name, clproducts.price, clproducts.image_url, clproducts.offer_id FROM clproducts INNER JOIN teainfo ON (clproducts.offer_id=teainfo.offer_id) WHERE (teainfo.teatype='Green')"; 
$result = mysql_query($query) or die(mysql_error());  // like wise display any errors if query fails 
echo $result; 
?>

And my page displayed

Resource id #2

Dont know where it got that from!!

Baffled now.

Sxooter

What Roger means is that you echo the query, then you cut and paste it into the mysql monitor, so that you KNOW that what you are running there is the same as what you are running in your program... Try that.

damanic

I havnt got mysql monitor...

I know that the query

works.

I have used it in mysql manager and it retrieves 7 results with no null values.

So theres something wrong with my php script. Its gotta be something wrong with the syntax or the while statement?

Sxooter

Yeah, as long as your slinging the same query at the db somehow.

damanic

same query. I even installed mysql monitor to double check and it returned 7 rows using the same query as echoed by my php script.

Its doing my head in. If the query retreives results then why do my $variable = rows[row] display nothing when echod?

krotkruton

This might not be much help either, but have you tried echoing each variable by itself instead of contained in a more complicated echo? By that I mean try this out:

$i = 1;
while ($row = mysql_fetch_assoc($result)) { 
$product_url = $row["clproducts.product_url"]; 
$name = $row["clproducts.name"]; 
$price = $row["clproducts.price"]; 
$image_url = $row["clproducts.image_url"]; 
$offer_id = $row["clproducts.offer_id"]; 

echo $i;
$i = $i+1;
echo $product_url;
echo "--";
echo $name;
echo "--";
echo $price;
echo "--";
echo $image_url;
echo "--";
echo $offer_id;
echo "<br>";
}

This should output each variable along with a number at the start. If you just get 7 numbered lines with only dashes in them, then you'll know that it is at least returning 7 results but there is a problem somewhere else. If you don't get 7 lines, then I don't know what to tell ya.

The only other thing I can think of is that you might want to try using

$offer_id = $row['clproducts.offer_id'];

instead (notice the apostrophes instead of quotation marks surrounding the name). I don't think that should make a difference, but I'm also confused as to why your code won't work so I'm grasping at straws here.

Also, I don't think you need to surround the thing you are echoing with parentheses, but since you still get the <br><img source=> to output, I don't think that will make a difference either.

Just curious, but what version of php are you running? If you don't know, you can make a page with

<? echo phpinfo() ?>

in the body of the html that will give you the version along with a lot of other information. Again, I doubt your version has anything to do with your problem, but it seems like you're looking for any suggestions so I'm just throwing that out there.

damanic

Thanks Kruton

I used your code with both apostrophes and quotation marks.

The results were the same:

1--------
2--------
3--------
4--------
5--------
6--------
7--------

So seven results just not showing them!

Im using php v4.310

damanic

Aha .. I think I've made a small breakthrough.

I used a different WHILE loop:

while ($row = mysql_fetch_object($result)){ 
print "<br>".$row->product_url."<br>";
print "<br>".$row->name."<br>";
print "<br>".$row->price."<br>";
print "<br>".$row->image_url."<br>";
print "<br>".$row->offer_id."<br>";
print "\n"; 
}

and it returned the information this time!!

However I would much rather have variables assigned to each row and echo instead of print. Any ideas?

Something like (doesnt work but for example)

while ($row = mysql_fetch_object($result)){
$offer_id = .$row->product_url.
echo $offer_id
}

Sxooter

Try putting a line like:

print_r($row)

inside your while loop and see what you get.

tomhath

Try getting rid of the table name in the associative array:

 $offer_id = $row['offer_id'];

Get rid of the extra periods in your php:

// not this:
$offer_id = .$row->product_url.

//this:
$offer_id = $row->product_url;