php ~ select from database help

[jennatar]

Hiya!

 

So, I'm beating my head against the wall on this simple little thing. I don't understand why it won't work, so I'd appreciate it if someone could toss me a bone. I'm "self-taught", meaning it's probably something really obvious.

 

I'm trying to query a second table "while" an initial query is in progress.

 

I've echo'ed at various points to see what's going wrong; $gid returns a value, so the first query is working fine. I then use a second query to search another table based on the value of $gid. This query turns up a null result (zero echos), even though the table has a row that matches $gid.

 

Code:

$result = mysql_query("SELECT * FROM groupuser WHERE user='$username'");

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

$gid = $row['gid'];

echo "GIDs is " . $gid . ".<br />";

$gresult = mysql_query("SELECT * FROM group where gid='$gid'");

while ($grow = mysql_fetch_array($gresult)) {

echo $grow['name'];

}

}

 

The second query is set up exactly like the first one, so I don't understand why it won't work. In regards to the table 'group', it does contain both columns for 'gid' and 'name'. None of the fields are empty. 'gid' is the primary key for the table, set on auto_increment. Both tables are within the same database.

 

So... can you not use a query within a query? If that's the case, how would you go about doing a query based upon a return from an earlier query?

 

Or... is it that I can't query the auto_increment...? (I doubt it's this, but... I have no idea.)

 

Help, anyone? Thanks!

 

 

[Mchl]

1. You could probably do it in one query instead of two.

 

2. Put some debugging code into your script, that will display mysql error messages. These messages will help in finding errors.

 

Example:

 

$result = mysql_query("SELECT * FROM groupuser WHERE user='$username'") or die(mysql_error());

 

(do the same for the second query)

 

 

[NoorAdiga]

Hey ...

 

 

u r making two nested fetch ... I think this is the problem ...

I didn't use mysql b4 , I use Oracle database but there I can't make nested fetch ...

fetch after another

 

Solution1:

$result = mysql_query("SELECT * FROM groupuser WHERE user='$username'");

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

        $gid = $row['gid'];

        echo "GIDs is " . $gid . ".<br />";

}

        $gresult = mysql_query("SELECT * FROM group where gid='$gid'");

        while ($grow = mysql_fetch_array($gresult)) {

        echo $grow['name'];       

        }

}

 

close the first fetch before starting the second

 

Solution2:

if u need the data from the two tables at the same time .... u can make a join relation between 'em if there is any common things and execute a single query with single fetch

 

Solution3:

if u r going to use the first data in order to get the second data ... fetch all the first data and store it inside an array then take 'em from that array .... the make the second fetch

 

I hope this will work, plz try any solution and tell me back about the result

 

Greetings,

Noor

 

 

[rhodesa]

GROUP is a reserved word in mysql. you should change the name of your table if you can. otherwise, put backticks around it:

$gresult = mysql_query("SELECT * FROM `group` where gid='$gid'");

[jennatar]

Thank you, everyone!

 

rhodesa, that was it! I thought it was probably something like that, obvious that I just never learned. Reserved words... aha... I think I'll go look those up O.o

 

Thanks again!

[Psycho]

As NoorAdiga stated, having looping queries such as that is terribly inefficient. Learn how to do JOINS and you will unlock the real power of a relational database. Also, theres no need to select '*' when you only need a particular field.

 

This single query would get you the same result

 

$query = "SELECT `group.name`
          FROM `group`
          JOIN `groupuser`
            ON `groupuser.gid` = `group.gid`
          WHERE `groupuser.user` = '$username'";

$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{
    echo "Name{$record['name']}<br />";
}

[Mchl]

Hey ...

 

u r making two nested fetch ... I think this is the problem ...

I didn't use mysql b4 , I use Oracle database but there I can't make nested fetch ...

fetch after another

 

MySQL has no problems with that. (And I doubt Oracle actually has, although I never used it)

 

jennatar: You should still think about making it a single query. It will be faster.

[rhodesa]

As NoorAdiga stated, having looping queries such as that is terribly inefficient. Learn how to do JOINS and you will unlock the real power of a relational database. Also, theres no need to select '*' when you only need a particular field.

 

This single query would get you the same result

 

$query = "SELECT `group.name`
          FROM `group`
          JOIN `groupuser`
            ON `groupuser.gid` = `group.gid`
          WHERE `groupuser.user` = '$username'";

$result = mysql_query($query) or die (mysql_error());

while ($record = mysql_fetch_assoc($result))
{
    echo "Name{$record['name']}<br />";
}

 

jennatar...definitely look into JOINs (just like noted above). Also, here is the list of reserved words:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[NoorAdiga]

well ... Oracle has this problem ... I spent a lot of time till I figure out why my code didn't work and after removing the nested fetch it worked well 

Mysql is more friendly ... maybe I will convert to it in the next project

 

Regards,

Noor

[Mchl]

well ... Oracle has this problem ... I spent a lot of time till I figure out why my code didn't work and after removing the nested fetch it worked well 

Mysql is more friendly ... maybe I will convert to it in the next project

 

Regards,

Noor

 

It is not really MySQL vs Oracle we're talking here, but how their relative PHP extensions work.