SQL Query from Assoc. Array

PHP
1

My goal is to get a list of all user information to display in a form/table for all members signed up for a group.

I have one table “group” which lists each user that is signed up for a group. (Users can be in multiple groups which results in an entry for each group for that member.) The fields I’m using for the purpose here are league_id and userName.

I have another table “user” which has all the user information stored. To make things simple here, the table has the fields userName, firstName and city.

I’m trying to pull a list of all the user userName from “groups” as an array, then take the results and pull the firstName and city from “user”.

I’m not having any luck and have been going around in circles. I’ve tried using a foreach loop for the second query but can’t quite seem to get that to work. Also tried an array_walk, which would get the results, but I couldn’t get it to display properly.

If anyone has any tips as to how to approach this, I’d be very grateful.

Thank you,
Craig

2

it would help if you could post a list of the table names and the relevent columns names below them.

then “in theory” all you need to do is

  1. build an sql query to extract the records you need from the various tables by joining them on their common columns.

  2. run the query using [fphp]mysql_query[/fphp]

  3. loop through the result set outputed from 2) using [fphp]mysql_fetch_assoc[/fphp] to display the data retrieved from your sql query.

if you post the table structures and code you have so far, we can try to help you get it working.

3

table - group
Fields - league_id, userName

table - user
Fields - userName, firstName, city

I’m just using these fields to make things easier, but will be pulling more once I get it worked out.

This is the code for the query I’ve been using to start with. From your reply, it sounds like I may be off base.


try 
{
$sql = 'SELECT userName from group WHERE league_id = :league_id ORDER BY userName ASC';
$stmt = $db->prepare($sql);
$stmt->bindParam(':league_id', $league_id);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$printname[] = $row;
} 
}

catch (PDOException $e)
{
echo $e->getMessage();
}


print_r($printname);

This prints out as follows:

Array ( [0] => Array ( [userName] => Sample1 ) [1] => Array ( [userName] => Sample2 ) )

4

ok, I don’t use prepared statements.

felgall always suggests using them so hopefully he or someone else who uses them can help you.

5

Instead of querying for all usernames first and than query for each of the individual users separately it’s more efficient to use a JOIN.

The query:


SELECT
   user.userName
 , user.firstName
 , user.city
FROM `group`
INNER
 JOIN user
   ON `group`.userName=user.userName
WHERE
   league_id = :league_id
ORDER BY
   userName ASC
6

Alright. That got me the following value for $printname:

Array ( [0] => Array ( [userName] => Sample1 [firstName] => Bob [city] => Boston ) [1] => Array ( [userName] => Sample2 [firstName] => Arnold [city] => Orlando ) )

I’ll try to figure out how to get this array to display how I need it. I’ll post back with my result or any further questions.

Many thanks.

  • Craig