将两个mysqli_fetch_array放入一个数组中

Question

I'm having problem assigning data from second select statment to already existing array: first select works fine and main array gets the data as it should:

$scriptsSolo_SQL = "select * from funkeyword.scripts where pause = '0' AND solo= '1' ";

$result = mysqli_query($conn,$scriptsSolo_SQL);

while ($row = mysqli_fetch_assoc($result)) {
      $mainArray[] = array("script_id" => "$row[id]" , "cron_format" => "$row[cron_format]");
}

but the problem its when im trying to add to $mainArray data from second table :

$usersSolo_SQL = "select * from funkeyword.users where pause = '0' AND solo= '1' ";
$result = mysqli_query($conn,$usersSolo_SQL);
//Get Data from users table
while ($row = mysqli_fetch_assoc($result)) {
    $mainArray[] = array( "user_id" => "$row[id]");
}

how can i add the data from users to same array created from scripts data ??

Answer 1

a) at the very least add some basic error handling

$result = mysqli_query($conn, $usersSolo_SQL);
if ( !$result ) {
    trigger_error('query failed', E_USER_ERROR);
}

b) (trust me for now, I'll explain later): Don't use SELECT * but SELECT only,the,fields,you,process

$scriptsSolo_SQL = "select id,cron_format from funkeyword.scripts where pause = '0' AND solo= '1' ";
$result = mysqli_query($conn,$scriptsSolo_SQL);
if ( !$result ) {
    trigger_error('query failed', E_USER_ERROR);
}
[...]
$usersSolo_SQL = "select id from funkeyword.users where pause = '0' AND solo= '1' ";
$result = mysqli_query($conn, $usersSolo_SQL);
[...]

c) install a debugger, see e.g. https://netbeans.org/kb/docs/php/debugging.html
Or (if you must) add debug out to check the state(s) of the script (some var_export()s or var_dump()s sometime work miracles. But you have to remove tem afterwards; and it may affect the execution and/or output significantly. printf-debuggers: we all use them; still they are ...suboptimal)

d) use a UNION statement to fetch both result sets as one. And that's the reason to ask you not to use * in this particular case. You should query UNION result sets only with exactly the same number of fields per record. Hard to tell from *. But with SELECT id,cron_format and SELECT id it's easy to tell "oh, I must come up with another field in the second result set"

<?php
$scriptsSolo_SQL = "
    (
        SELECT
            id,cron_format
        FROM
            funkeyword.scripts
        WHERE
            pause = '0' AND solo= '1'
    )
    UNION
    (
        SELECT
            id,null as cronformat
        FROM
            funkeyword.users
        WHERE
            pause = '0' AND solo= '1'
    )
";

$result = mysqli_query($conn,$scriptsSolo_SQL);
if ( !$result ) {
    trigger_error('query failed', E_USER_ERROR);
}
while ( $row=mysqli_fetch_assoc($result) ) {
    $mainArray[] = $row;
}

[ e) does this table structure really, really make sense? It looks a bit strange.... ]