How do I organize two mysql tables into one big multidimensional array?

arrays, mysql, php

Solution

Try a left join on the ticket_winners table, don't use SELECT *, it is bad practice to do so and when using joins you should append each column with the table name.

SELECT tw.id,tw.userid,t,userName,t.id AS tick_id, t.ticketId 
    FROM `ticket_winners` AS tw
LEFT JOIN `tickets` AS t
    ON tw.userid = t.userid 
ORDER BY tw.id DESC LIMIT 5

The left join preserves the table format of your ticket winners table and will append t.id (in the output as tick_id),ticketId and userName.

This will give you an array consisting of multiple users, sorting them in one while loop will not be difficult as you simply create a winners array with the key of the usersid, within that array append the winning ticket id.

$results = array();

while ($sql = mysql_fetch_array($query, MYSQL_ASSOC) {
    $results[$sql['userid']]['userName'] = $sql['user'];
    $results[$sql['userid']]['tickets'][] = $sql['tick_id'];
}

The resulting array:

[1] => array 
    (
    ['userName'] => 'Craig',
    ['tickets'] => array 
        (
        [0] => 'GH7JNm72hN',
        [1] => 'JudM3rT3is'
        )
    )
[2] => array 
    (
    ['userName'] => 'Adam',
    ['tickets'] => array 
        (
        [0] => 'GfdT24sDgC'
        )
    )

This eliminates the need to a) run two seperate queries, and b) avoid creating an uneccesary while loop within a while loop speeding this process up.

A good practice to follow when doing this is:

- Get as much data in one query as possible.

- Loop through once and sort data into one or multiple arrays eliminating duplications due to joins.

Problem

I'm having a hard time figuring this out... I have two tables... ticket_winners, and tickets in the ticket_winners table, usernames / profile information... the tickets table is all of the tickets these users have for one userID, there could be 10+ tickets for each user in this table. Question: How can I loop through the second iteration of data when the table tickets has more than 1 row for each user ``` function pullTickets() { $sql = $this->mysql->retrieve("SELECT * FROM ticket_winners ORDER BY id DESC LIMIT 5"); $sql2 = $this->mysql->retrieve("SELECT id, userId, ticketId FROM tickets ORDER BY id ASC LIMIT 5"); while($row = mysql_fetch_array($sql)) { $results[$row['id']]['user'] = $row['userId']; while($row2 = mysql_fetch_array($sql2)) { if($results[$row['id']]['user'] == $row2['userId']) { $results[$row['id']]['tickets'][$row2['id']] = $row2['ticketId']; } else { } } } return $results; } ``` PHP page example: works fine ``` $data = $obj->pullTickets(); foreach($data as $user) { echo $user['username']; foreach($data['ticket'] as $ticket) { echo $ticket['ticketId']; } } ``` What the array looks like now: ``` [1] => Array ( [batch] => 1 [userId] => 200 [userName] => Craig [tickets] => Array ( [1] => GH7JNm72hN [2] => JudM3rT3is [3] => KiLPoXCmDF ) ) [2] => Array ( [batch] => 1 [userId] => 100 [userName] => Hewbie needs to continue looping ) ``` the tickets table isn't being looped through each user like in the [1] array. It skips all the other users' tickets.

Original source