user1957878 user1957878 - 3 months ago 9
MySQL Question

why i can't display my records in php mysql?

I have records in my database but I can't display them. Can someone check my codes, please. I'm just an amateur web developer. Thanks for any help.

<?php
$groups=mysql_query("SELECT * FROM groups ORDER BY id ASC");
$g_res=mysql_affected_rows();
if($g_res>0)
{
while($row=mysql_fetch_array($groups))
{
$g_id=$row['id'];
$g_name=$row['g_name'];

$members=mysql_query("SELECT * FROM members WHERE group='$g_id'");
$m_res=mysql_affected_rows();
if($m_res>0)
{
while($row2=mysql_fetch_array($members))
{
$m_id=$row2['id'];
$m_name=$row2['m_name'];
$m_email=$row2['m_email'];
echo "<tr><td>$m_name<br/>($g_name)</td><td>$m_email</td></tr>";
}
}
else
{
echo "<tr><td colspan=2>Nothing to display</td></tr>";
}
}
}
else
{
echo "<tr><td colspan=2>Error</td></tr>";
}
?>


With this code I get the
else
result which is
Error
. If I remove
WHERE group='$g_id'
from the query, all of my records are displayed randomly, but I'd like to show my records (members) by group.

Answer

You need to escape reserved words in MySQL like group with backticks

SELECT * FROM members WHERE `group` = '$g_id'
                            ^-----^-------------here

You can also spare the inner loop when you join your data like this

select g.id as gid, g.g_name, m.id as mid, m.m_name, m.m_email
from groups g
inner join members m on g.id = m.group
order by g.id asc

This is easier and will increase performance since you don't need to execute a lot of queries but just one.

Also please don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about Prepared Statements instead, and use PDO or MySQLi. See this article for a quick overview how to do it and why it is so important.