Display Related Data From Another Table

[nikster]

Hi Everyone,

 

I'm a noob to PHP although I have made great progress in the last few weeks. But now I'm stumped on what seems to be something simple.

 

This project is a web app where customers enter in their info to ask for a free estimate to get their car body fixed which generates a lead. Because it's a franchise, at the end of the month, the owner counts the estimate requests (the leads) and bills the franchisees for each lead. (I know.. nice work if you can get it. :-)

 

So I have a table called 'estimates' that stores all the lead info and the location ID of the franchise the lead is for (loc_id). In another table called 'locations' I have all the individual franchisee's info including their location ID (loc_id).

 

So to get a count of the leads for the month I have the following query;

 

// perform query

$result = mysql_query("

SELECT loc_id, COUNT(*)

FROM estimates

WHERE date BETWEEN '$reportdatefrom' AND '$reportdateto'

GROUP BY loc_id;")

or die ('Error: '.mysql_error());

 

// output results

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td align='center' width='200'>" . $row['loc_id'] . "</td>";

echo "<td align='center' width='200'>" . $row['COUNT(*)'] . "</td>";

echo "</tr>";

 

.. which gives me the result I want, but not formatted how I want. I'd like to be able to show (using the estimates.loc_id & locations.loc_id relationship) the Franchise's name instead of just their their ID (loc_id) which is stored in the 'locations' table.

 

I feel a bit stupid because it's like I was able to bake the cake, which is supposed to be the hard part, but now I can't seem to put on the icing, which is supposed to be the easy part. Any help would be greatly appreciated. 

 

[AyKay47]

look into using a JOIN here

[nikster]

Thanks for your quick reply AyKay47.

 

I did look at JOIN. Because there are different methods of JOIN (INNER, LEFT, RIGHT) and so many variables, I tried for hours and couldn't figure it out. I'm not sure which JOIN to use and where it goes in the query.

 

If it's not too much trouble, can you give me an example or point me in then right direction?

[AyKay47]

the link that I provided has numerous examples and documentation on the purpose of each type of JOIN..can't add much to it..

however you probably want something like this

 

SELECT loc_id FROM estimates LEFT JOIN locations ON estimates.loc_id = locations.loc_id

[nikster]

Thanks again AyKay47.

 

I tried so many permutations of JOIN today, I'm pretty sure I tried the one you gave me. But it didn't work and I tried other things. I got the same error I was just getting;

 

Error: Column 'loc_id' in field list is ambiguous

 

But now I see that this is what I'm looking for, so I read some more from the link you posted and tried a few different things and got past that error. Now, the column I'm trying to get the name of the franchise to come up in is just blank. The numbers column is correct. I'm not getting any errors, but I cannot get the company name, or any data from either table to come up there weather I use the "locations." prefix or not. The only way any data will come up in that cell is if I use  $row['loc_id'].

 

Here's what the code looks like now; (A screen grab is also attached of the output.)

 

// query db

$result = mysql_query("

SELECT loc_id,

COUNT(*)

FROM estimates

LEFT JOIN locations ON estimates.loc_id = locations.loc_id

WHERE date BETWEEN '$reportdatefrom' AND '$reportdateto'

GROUP BY loc_id;")

or die ('Error: '.mysql_error());

 

// output

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td align='center' width='200'>". $row['locations.loc_name']. "</td>";

echo "<td align='center' width='200'>". $row['COUNT(*)']. "</td>";

echo "</tr>";

 

[attachment deleted by admin]

[AyKay47]

you are telling the server to only grab loc_id from the locations table...specify what you want it to grab..

 

SELECT estimates.loc_id, locations.company_name, estimates.example
   COUNT(*)
   FROM estimates
   LEFT JOIN locations ON estimates.loc_id = locations.loc_id
   WHERE date BETWEEN '$reportdatefrom' AND '$reportdateto'
   GROUP BY loc_id;")

 

insert the correct field names in place of what I have added of course

[nikster]

That did it AyKay47! Thank you so much. I've learned more in the past few hours than I have in the past week.

 

I can't wait to get good enough to help other guys like me.

 

Thanks again. Here's the final working code in case this come up for anyone else and attached is a screen grab of the output.

 

// query db

$result = mysql_query("

SELECT estimates.loc_id, locations.loc_name,

COUNT(*)

FROM estimates

LEFT JOIN locations ON estimates.loc_id = locations.loc_id

WHERE estimates.date BETWEEN '$reportdatefrom' AND '$reportdateto'

GROUP BY estimates.loc_id;")

or die ('Error: '.mysql_error());

 

// output

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td align='center' width='200'>". $row['loc_name']. "</td>";

echo "<td align='center' width='200'>". $row['COUNT(*)']. "</td>";

echo "</tr>"; 

 

[attachment deleted by admin]

[AyKay47]

no problem, please mark this as solved, lower left of thread