[SOLVED] can't retrieve name field in left join statement

[sherric]

i'm trying to compile data from different tables for a report. this code:

 

$result4 = mysql_query("select reportequipment.id, equipment.name from reportequipment LEFT JOIN equipment

ON reportequipment.id = equipment.id where webid='$myid'")or die(mysql_error());

 

print "<div id='e42' style='position:absolute; left:0px; top:121px; height: 16px; vertical-align:top; width: 215px;'>

<table width=200 border=0>

<div class='title'>Selected Equipment</div>

\n<tr>";

$i = 0;

while ( $row = mysql_fetch_array($result4) ) {

$i++;

echo "<input type='hidden' name='hidDelete' value='$row[id]}' />";

echo "<td>{$row[id]} {$row[name]} </td><td>";

 

 

returns the row id from my reportequipment table, but will not return the row name from the equipment table. if i remove the where statement, it performs fine.  how do i configure this to retrieve all of the data that i need.  btw the where statement filters the first table, the reportequipment. i'm sure that has a bearing, but i dont know how to reflect that in my select stmt.

 

thanks for taking the time to assist.

[kickstart]

Hi

 

The SQL looks OK. Suggests to me the issue is that there is no matching row on the 2nd table for any rows with the specified webid.

 

All the best

 

Keith

[sherric]

hi keith thanks for hollering back at me. 

 

reportequipment is the equipment selected to go with the incident being reported. in this table, i've used the equipment id # to identify.  the equipment table is my list of all equipment, with the id # and the names.  webid is my session #, which is the number that i use to track all transactions for each table.

 

when i enter information into my forms, i can then look and see the info reflected into the correct db table.  my problem is that i'm not sure how to pull out the info for my report. 

 

$result4 = mysql_query("select reportequipment.id, equipment.name from reportequipment LEFT JOIN equipment

ON reportequipment.id = equipment.id where webid='$myid'")or die(mysql_error());

 

with this query, i can return everything that i need, except for the name of the equipment from my equipment list table.  reportequipment.id returns.  equipment.name does not. nor can i return any values for the second table in my query.

 

 

 

[kickstart]

Hi

 

With the LEFT JOIN you are using it will bring back records from reportequipment whether there is a matching row on equipment or not. The only way I can see the cause of the issue you have is if there isn't a matching row on equipment.

 

Which table is webid on? Have you got some sample data and the table declares for us to have a look at.

 

All the best

 

Keith

[sherric]

hi again keith,

 

rtfm!  and ended up with

 

SELECT reportequipment.*,equipment.*

  FROM reportequipment LEFT JOIN equipment ON reportequipment.equip = equipment.id

WHERE reportequipment.webid='$myid';

 

which gives me the results that i need.  thanks again.  have a good one!