CODEWITHSUNDEEP
phpmysqlarrays
Krunk
Krunk

Reputation: 53

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

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.

Upvotes: 5

Views: 514

Answers (2)

David Barker
David Barker

Reputation: 14620

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:

  1. Get as much data in one query as possible.
  2. Loop through once and sort data into one or multiple arrays eliminating duplications due to joins.

Upvotes: 3

hskrijelj
hskrijelj

Reputation: 433

Your data is, as I see, applied to the $result array - both the tickets-data and the tickets_winner-data. What is your question then? Maybe I have totally misunderstood it.

Upvotes: 0

Related Questions