PHP MySQL query within query

[Saldash]

Hey folks,

I have an interesting conundrum, I'm in the midst of creating an additional page
to my phpBB site, that lists all my installed badges (info stored in a DB Table).

The first part of the query works fine, it queries the table for all of it's information
and displays it exactly as I intended it to do so.

Each badge record has a field called "system_name", and the value stored here corresponds
to a field name in my users table. Each user has a 1 or 0 in these fields to indicate they have
that badge.

What I'm trying to do with the second part of my query is to get the names of each user for each badge
using the following query;


$sql = 'SELECT * FROM badges WHERE 1';
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
$field_name = $row['system_name'];
$list = mysql_query("SELECT * FROM users WHERE '$field_name' = 1;") or die(mysql_error());
$name_list = mysql_fetch_array( $list );

$template->assign_block_vars('ranks', array(
'BADGE_NAME' => $row['badge_title'],
'BADGE' => $row['system_name'],
'BADGE_DESC' => $row['badge_desc'],
'NAME_LIST' => $name_list,
));
}
$db->sql_freeresult($result);


I'm not exactly sure how to get the names out of the query from this point on,
and phpBB.com do not provide any sort of general php programming help at all.

Would be great if someone here could help me with this :)

Cheers

[Peter]

You can't do what you're thinking of doing in this way (if I follow you).

mysql_fetch_array() will only return one array of results for one row, you need to loop with it in order to get all of the results. Also you should try and use the phpBB libraries for querying wherever possible.

Although it's extremely difficult for me to get the exact scenario you're trying to reproduce, try something like this:

$sql = 'SELECT * FROM badges WHERE 1';
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
$field_name = $row['system_name'];
$list = "SELECT * FROM users WHERE '".$db->sql_escape($field_name)."' = 1";
$name_list = $db->sql_query($list);
$name_list_array = array();
while ($name_row = $db->sql_fetchrow($name_list)) {
$name_list_array[] = $name_row['name'];
}

$template->assign_block_vars('ranks', array(
'BADGE_NAME' => $row['badge_title'],
'BADGE' => $row['system_name'],
'BADGE_DESC' => $row['badge_desc'],
'NAME_LIST' => $name_list_array,
));
$db->sql_freeresult($name_list);
}
$db->sql_freeresult($result);

You need to replace the "name" part of $name_row['name'] if your column is named differently.

[VJ]

i'm not entirely sure what you want your end result to be.. and what fields your tables have..
From my understanding...
- there's 2 tables one called 'badges' the other called 'users'.
The badge table:
- has a field 'system_name' which matches something in the users table identically.
The users table:
- i'm assuming has usernames in it.

In my small experience of mysql, I only started recently; but i'm making slow progress on it etc. :)

I have tables 'users' and 'questions' (and others but they're not relevant for this example).

The 'users' table has fields:
- username
- userid (which is incremental (adds 1 to every new record))

The 'questions' table has fields:
- question
- userid (to store the userid of the user)

What i wanted to do in the php part of the webpage was display a question (from the 'questions' table) and the user who asked it (from the 'users' table) next to it.

with this kind've setup, i can fetch things from other tables as long as there is a field that's like directly related? (if that's the word, you get what i mean right? :D )

to do this in php...
lets create an array for all the fields in a record of the 'questions' table


$question = mysql_query("SELECT * FROM questions") or die(mysql_error());
$arrquestion = mysql_fetch_array( $question );

i did this properly like with more fields so this makes more sense, so right now this would have errors as theres probably going to be more records etc. But i'm doing this from memory/my understanding atm

next we can select that 'userid' field of that question record we just pulled... lets call that 'current_user'


$current_user = $arrquestion['userid'];


then we can use this variable to execute another mysql query to pull the username from the 'users' table:


$current_username = mysql_query("SELECT username FROM users WHERE userid=$current_user") or die(mysql_error());


and hey presto, your username is in the current_username variable :D


I'm pretty sure there's going to be some syntax errors in my code blocks here, though i'm just trying to show something ;)

I hope this was helpful :)

[Peter]

then we can use this variable to execute another mysql query to pull the username from the 'users' table:


$current_username = mysql_query("SELECT username FROM users WHERE userid=$current_user") or die(mysql_error());
and hey presto, your username is in the current_username variable

Be careful with this, the username is not in the $current_username variable, the MySQL resource containing your results (including the username is). To get the actual username into the $current_username variable, you'd need to do something like this:

$current_username_resource = mysql_query("SELECT username FROM users WHERE userid=$current_user") or die(mysql_error());
$current_username_array = mysql_fetch_assoc($current_username_resource);
$current_username = $current_username_array['username'];

Really VJ, in your scenario, you should have used a join as you didn't need to do a comparison. You should have used something like this:

$question_info = mysql_query("SELECT * FROM questions, users WHERE questions.userid = users.userid");
while ($question_data = mysql_fetch_assoc($question_info)) {
// Handle your question data here, you have access to everything from both the questions and users table...
}

[Saldash]

Thanks Peter,
I don't get any errors with your code, but the only output is the word "Array" for each record.

These are the tables (partial screenshots) that hold the data I want to access
http://www.world-recoded.com/download/table_data.png

The top table holds all the information about each badge, and each badge has a "badge_name"
that corresponds to a field name on the users table below.

For example;
The script generates block data for each badge record, in the first record, the value of $row['badge_name'] = "medal_staff".
There is a field in the users table called medal_staff.

While still in that loop, i want to query the users table and get a list of all the usernames where a "1" exists in the medal_staff field.
The next loop the value of $row['badge_name'] = "medal_tester", effectively select * where $row['badge_name'] = "1" during the loop for that badge.

The page I'm working on is here (http://www.world-recoded.com/badges.php).
It might be a little more clear.

[VJ]

Thanks Peter. As i said before i am completely new to MySQL and php for that matter :D

[Peter]

The reason it says "Array" on the output is because that's exactly what it is. If you want it to do something else, you'll need to manipulate that array first. Try playing around with something like this:

$sql = 'SELECT * FROM badges WHERE 1';
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
$field_name = $row['system_name'];
$list = "SELECT * FROM users WHERE '".$db->sql_escape($field_name)."' = 1";
$name_list = $db->sql_query($list);
$name_list_array = array();
while ($name_row = $db->sql_fetchrow($name_list)) {
$name_list_array[] = $name_row['name'];
}

$template->assign_block_vars('ranks', array(
'BADGE_NAME' => $row['badge_title'],
'BADGE' => $row['system_name'],
'BADGE_DESC' => $row['badge_desc'],
'NAME_LIST' => implode("<br />", $name_list_array),
));
$db->sql_freeresult($name_list);
}
$db->sql_freeresult($result);Or:

$sql = 'SELECT * FROM badges WHERE 1';
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
$field_name = $row['system_name'];
$list = "SELECT * FROM users WHERE '".$db->sql_escape($field_name)."' = 1";
$name_list = $db->sql_query($list);
$name_list_array = array();
while ($name_row = $db->sql_fetchrow($name_list)) {
$name_list_array[] = $name_row['name'];
}

$template->assign_block_vars('ranks', array(
'BADGE_NAME' => $row['badge_title'],
'BADGE' => $row['system_name'],
'BADGE_DESC' => $row['badge_desc'],
'NAME_LIST' => implode(",", $name_list_array),
));
$db->sql_freeresult($name_list);
}
$db->sql_freeresult($result);


Thanks Peter. As i said before i am completely new to MySQL and php for that matter :D

No problem, if you need help in the future just ask. Usually when you want data from multiple tables, there's a way using a join which will cut it to just 1 query. There are some exceptions, like what we're discussing now though. :p

[Saldash]

Cheers Peter, I'll start giving that a go now.
I'm ok with some bits of PHP, but with things like this, I dunno where I'd be without ya :thumb:

[DoubleTop]

for this type of thing, I tend to use place the sql result into an array to loop, rather than sql inside sql that will be quite an intensive load on mysql.

So dump the content into an array from the first call that gives you all your types and info, then loop make the sql call for getting the actual users with that assignment.

I've never checked if it's faster that way, but it's a damn site easier for another coder to come along and understand what is happening :)

DT.

[Peter]

It's six and two threes as far as MySQL is concerned (with the example above at least). As I'm sure you know, usually the fewer queries the better, but with the example above, the results are available from the MySQL resource anyway, whether you pull them out at the start into an array or loop through them pulling from the resource doesn't really make much difference because the query has already been executed by the MySQL server. I guess if we're getting really nitpicky, the option of pulling it out into an array first uses ever so slightly more memory than not.

My suggestion would be to pull it out into an array if you want PHP's sorting or array_map() options or something.

[DoubleTop]

very true Peter, a lot of it can be just personal ways of coding these types and things and there are always more hammers available than nuts ;)

there's a JOIN in there to do that query in a single go, that if I weren't so tired I'd sit and work out. Needed football though, released a lot of stress on the pitch tonight :lol:

DT.

[Saldash]

I've tweaked the script ever so slightly to give me clickable links and user colour formatting,
phpbb has a function for this but i can't find it and my head is aching xD

My final script now looks like this;

$sql = 'SELECT * FROM phpbb_badges WHERE 1';
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
$field_name = $row['field_name'];
$list = "SELECT * FROM phpbb_users WHERE $db->sql_escape($field_name) = 1";
$name_list = $db->sql_query($list);
$name_list_array = array();
while ($name_row = $db->sql_fetchrow($name_list)) {
$name_list_array[] = '<a href="memberlist.php?mode=viewprofile&u=' .$name_row['user_id']. '"><font color="' .$name_row['user_colour']. '">' .$name_row['username']. '</font></a>';
}

$template->assign_block_vars('ranks', array(
'BADGE_NAME' => $row['badge_title'],
'BADGE_TYPE' => $row['badge_type'],
'BADGE' => $row['file_name'],
'BADGE_DESC' => $row['badge_desc'],
'NAME_LIST' => implode(", ", $name_list_array),
));
$db->sql_freeresult($name_list);
}
$db->sql_freeresult($result);

The really long line could probably be neatened up a little, but for now it works wonderfully.
I changed a couple of the field names to make more sense for what I wanted doing, and
added a new field to define the type of badge being listed.

The link to the page in one of my previous posts still works, and your welcome to have a look
and see what it is that I have done with the script.
Thank you peter, once again you saved the day!

[Saldash]

Scratch that, I managed to figure out a way :)