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

Question

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.

**warning** your code maybe vulnerable to sql injection attacks. — Daniel A. White, Jun 24 '13 at 11:55
Use `mysql_error()` to get the error from a SQL query; add this into your else statement somehow so you can see the error — Alex Coleman, Jun 24 '13 at 11:56
Don't forget to fix: `echo "".$m_name."
(".$g_name.")".$m_email."";` — Sergio, Jun 24 '13 at 19:08

score 3 · Accepted Answer · edited May 23 '17 at 11:44

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.

i didn't know that 'group' is a reserved words in MySQL. thank u man for pointing that out. this solved my problem. — user1957878, Jun 24 '13 at 12:04

GautamD31 · Answer 2 · 2013-06-24T12:00:59.340

0

Try like

$members=mysql_query("SELECT * FROM members WHERE `group` = '".$g_id."');

or simply

$members=mysql_query("SELECT * FROM members WHERE `group` = '$g_id'");

edited Jun 24 '13 at 12:00

answered Jun 24 '13 at 11:55

GautamD31

28,552
10
64
85

Don't forget the apostraphes in your second example there around $g_id – Alex Coleman Jun 24 '13 at 11:56
Since it is an integer value we dont need to put apostaphes for that I thnk – GautamD31 Jun 24 '13 at 11:57
Can't hurt though in case it isn't integer, id generally is integer but there doesn't appear to be anything in his code that forces it to be – Alex Coleman Jun 24 '13 at 12:00
1

Ok @AlexColeman I have added that also...thanku for the valuable suggestion – GautamD31 Jun 24 '13 at 12:01

Sergio · Answer 3 · 2013-06-24T19:08:51.447

0

You have to concatenate your variables. Try this:

$members=mysql_query("SELECT * FROM members WHERE `group`='".$g_id."'");

And

echo "<tr><td>".$m_name."<br/>(".$g_name.")</td><td>".$m_email."</td></tr>";

edited Jun 24 '13 at 19:08

answered Jun 24 '13 at 11:56

Sergio

28,539
11
85
132

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

3 Answers3