PHP while and MySql question

[FnTm]

So ive got this query

$query2="SELECT * FROM incident_news_subscript LEFT JOIN incident_news ON incident_news.id=incident_news_subscript.relation WHERE username='$coment_auth_name'";
$result2= mysql_query($query2) or die('Error : ' . mysql_error()); 
while($row2 = mysql_fetch_array($result2)){

And this output file.

echo "<tr><td>".$row2['type']."</td><td>".$row2['services']."</td><td>".$row2['Title']."</td><td>".$row2['time']."</td><td><span class=\"form\"><a href=\"".ADMIN."SQL_edit_subscript.php?del=".$row2['incident_news_subscript.id']."\" title=\"Dzēst šo pierakstu\">Dzēst</a></span>".$edit."</td></tr>";
unset($edit);

What i would like to know, is how to use this $row2['incident_news_subscript.id'] part correctly, so that i get a value here, because now, I am not getting any.

Any help?

Thanks!

[penders]

Is 'id' a field in your 'incident_news_subscript' table?

Then may be this:

$row2['incident_news_subscript.id']

Should be:

$row2['id']

?

Or are you refering to the 'relation' field? (Which seems to correspond to the 'id' field in your 'incident_news' table?)

[FnTm]

The idea is, that neither $row2['incident_news_subscript.id'] nor $row2['id'] works. They both return empty strings. And what i would like to know, is how to work around this.

[coopster]

It is probably not an empty string but a NULL value. You are using a LEFT JOIN which will take all rows from the left hand table and join them to the corresponding row in the right hand table. If there is no "connection", meaning that for any given id that exists in the left hand table that has no corresponding id in the right hand table, NULL values are returned for the columns named in the SELECT statement, which in your case is ALL columns because you are using *.

I recommend stating the column names required from each table and using an INNER JOIN if you want only those rows where rows exist in both tables for any given id. If not, you will have to check for NULL values in the right hand table columns to determine whether or not a corresponding row was located and joined. Also, you can eliminate any column ambiguity by preceding the column name with the table name:

$query2 =  
  "SELECT 
    incident_news_subscript.relation, 
    which-table-here.type, 
    which-table-here.services, 
    which-table-here.Title, 
    which-table-here.time 
  FROM incident_news_subscript 
  LEFT JOIN incident_news ON(incident_news_subscript.relation = incident_news.id) 
  WHERE 
    username = '$coment_auth_name'" 
;

Now you can use the column names during your fetch operation (note I used "relation" as opposed to the "id" you have been discussing).