multidimensional array skips all values except last one using php and mysql

Question

I have two tables: one called scripts and hold script_name and the called users and holds user_id, im trying to create multidimensional array that will assign each of the script names to every existing user , for example: if i have two users [user_id:1, user_id:2] and two scripts [script_id:5,script_id:6] the array will look something like :

[
 (user_id:1,script_id:5),
 (user_id:1,script_id:6),
 (user_id:2,script_id:5),
 (user_id:2,script_id:6)
]

the problem is that the array im creating holds only the last user (user 2) and in it all scripts as i want:

[
 (user_id:2,script_id:5),
 (user_id:2,script_id:6)
]

this is my code:

//Create an array
$json_response = array();

//get all users
$usersResult = mysql_query("select user_id from users");

    while ($row = mysql_fetch_array($usersResult, MYSQL_ASSOC)) {
        $row_array['user_id'] = $row['user_id'];
    }

//get all script name's and cron format's
$scriptsResult = mysql_query("select script_name from scripts");

    while ($row = mysql_fetch_array($scriptsResult, MYSQL_ASSOC)) {
        $row_array['script_name'] = $row['script_name'];
        array_push($json_response,$row_array);
    }
echo json_encode($json_response);

Every while iteration overwrites $row_array[KEY] value in usersResult while loop. What did you expect? — u_mulder
– u_mulder, Commented Feb 8, 2015 at 11:10
i tried to add [index++] before ['user_id'] but it didnt solve my problem... — Al.s
– Al.s, Commented Feb 8, 2015 at 11:13

ReallyMadeMeThink · Accepted Answer · 2015-02-08 11:31:54Z

1

If you're just joining the two tables you don't need two queries, just do:

$json_response = array();

$results= mysql_query("select user_id,script_name from users,scripts");
while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
   array_push($json_response,$row);
}

answered Feb 8, 2015 at 11:31

ReallyMadeMeThink

1,0818 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Al.s Over a year ago

thanks for such a clean solution, if i will try to get another value from scripts (cron_format), dows it work the same with just adding it to the query?

ReallyMadeMeThink Over a year ago

Yes, you're just performing an outer join on the two tables, you can include as many columns in the results as you want.

Collectives™ on Stack Overflow

multidimensional array skips all values except last one using php and mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related