Echo record from referenced table

[prosad]

hi;

i have two tables in MySQL with:
1. TABLE(stat) column(c_id, FOREIGN KEY) referencing

2. TABLE (complaint) column (id_complaint, PRIMARY KEY).

TABLE (complaint) also contain a second column (complaint_type ).

How can i get PHP to echo second column (complaint type) in TABLE (complaint) when it is referencing the PRIMARY KEY of this table instead(id_comp laint). as regards the below mysql query:

[PHP]$sum= ("SELECT c_id, SUM(number) FROM stat
WHERE '$_POST[m_id]' = m_id AND '$_POST[l_id]' = l_id AND '$_POST[y_id]' = y_id
GROUP BY c_id") or die (mysql_error()) ;
$sum_1 = mysql_query($su m) or die (mysql_error()) ;
while ($sum_2 = mysql_fetch_arr ay($sum_1)) {echo "<b>Total:</b>"; echo $sum_2 ['c_id']; echo "&nbsp"; echo $sum_2['SUM(number)']; echo "<br>";}
[/PHP]

the area of concern which echoes the PRIMARY KEY is:
[PHP]echo $sum_2['c_id'];[/PHP]

and if i use:
[PHP]echo $sum_2['complaint_type '];[/PHP]it states that column does not exist which is understandable becos am selecting stat table in my query not complaint.

expecting any response. thanks

[nathj]

hi;

i have two tables in MySQL with:
1. TABLE(stat) column(c_id, FOREIGN KEY) referencing

2. TABLE (complaint) column (id_complaint, PRIMARY KEY).

TABLE (complaint) also contain a second column (complaint_type ).

How can i get PHP to echo second column (complaint type) in TABLE (complaint) when it is referencing the PRIMARY KEY of this table instead(id_comp laint). as regards the below mysql query:

[PHP]$sum= ("SELECT c_id, SUM(number) FROM stat
WHERE '$_POST[m_id]' = m_id AND '$_POST[l_id]' = l_id AND '$_POST[y_id]' = y_id
GROUP BY c_id") or die (mysql_error()) ;
$sum_1 = mysql_query($su m) or die (mysql_error()) ;
while ($sum_2 = mysql_fetch_arr ay($sum_1)) {echo "<b>Total:</b>"; echo $sum_2 ['c_id']; echo "&nbsp"; echo $sum_2['SUM(number)']; echo "<br>";}
[/PHP]

the area of concern which echoes the PRIMARY KEY is:
[PHP]echo $sum_2['c_id'];[/PHP]

and if i use:
[PHP]echo $sum_2['complaint_type '];[/PHP]it states that column does not exist which is understandable becos am selecting stat table in my query not complaint.

expecting any response. thanks

Hi,

You can only echo columns that you have selected. So if you select the information in the SQL you will be able to display it on the screen. The area you need to work on is the SQL. Probably a join will do the trick for you.

select a.c_id, b.complaint_type from stat a left outer join complaint b on b.complaint_id = a.c_id where ...

This should do the trick for you, as in the result you will have complaint_type and will therefore be able to display in the browser.

Cheers
nathj

[prosad]

Hi,

You can only echo columns that you have selected. So if you select the information in the SQL you will be able to display it on the screen. The area you need to work on is the SQL. Probably a join will do the trick for you.

select a.c_id, b.complaint_type from stat a left outer join complaint b on b.complaint_id = a.c_id where ...

This should do the trick for you, as in the result you will have complaint_type and will therefore be able to display in the browser.

Cheers
nathj

thnks for the lead, below is what i developed:

[PHP]$sum= ("SELECT a.complaint, SUM(b.number) Number FROM complaint a, stat b
WHERE '$_POST[m_id]' = m_id AND '$_POST[l_id]' = l_id AND '$_POST[y_id]' = y_id AND
a.id_complaint = b.c_id
GROUP BY c_id") or die (mysql_error()) ;
$sum_1 = mysql_query($su m) or die (mysql_error()) ;
echo "<table border = '1'>"; echo "<th>Compla int</th><th>Number</th>";
while ($sum_2 = mysql_fetch_arr ay($sum_1))
{echo "<tr><td>"; echo "<b>"; echo $sum_2 ['complaint']; echo "</td>";
echo "<td>"; echo $sum_2['SUM(b.number)']; echo "</td></tr>"; echo "<br>";}
echo "</table>";[/PHP]
my problem is that if i try to echo $sum_2['SUM(b.number)'] it does not produce anything in browser. though the SQL query produces intended result at the backend with columns 'complaint' and 'SUM(b.number)' .

[nathj]

Hi,

The trouble here is again in the SQL.

You need to call the result of sum something in the SQL, so you would have :

sum(b.field) as field_total

Then you could reference the field in the normal way as it would be named in the normal way.

Does that make sense?

Cheers
nathj

[prosad]

Hi,

The trouble here is again in the SQL.

You need to call the result of sum something in the SQL, so you would have :

sum(b.field) as field_total

Then you could reference the field in the normal way as it would be named in the normal way.

Does that make sense?

Cheers
nathj

thanks alot,
was just about to post a reply that have gotten round this problem wen i noticed u had already replied. what i did in the end is this (more simple logic then the former):

[PHP]$sum= ("SELECT complaint, SUM(number) FROM complaint, stat
WHERE '$_POST[m_id]' = m_id AND '$_POST[l_id]' = l_id AND '$_POST[y_id]' = y_id AND
id_complaint = c_id
GROUP BY c_id") or die (mysql_error()) ;
$sum_1 = mysql_query($su m) or die (mysql_error()) ;
echo "<table border = '1'>"; echo "<th>Compla int</th><th>Number</th>";
while ($sum_2 = mysql_fetch_arr ay($sum_1))
{echo "<tr><td>"; echo "<b>"; echo $sum_2 ['complaint']; echo "</td>";
echo "<td>"; echo $sum_2['SUM(number)']; echo "</td></tr>"; echo "<br>";}
echo "</table>";
[/PHP]