Only one array in json object

Question

I have 5 tables for different files that user can upload. I would like to get all the files with corresponding data in one array so 5 files data in one array.

All the tables have same structure:

user_id, file_type, file_size, file_name, file_new_name, file_path, date_created

In PHP I am using this query select * from degree_files, profile_pictures, cpr_files, backgroundcheck_files, video_files where degree_files.user_id = :user_id which gives me all 5 file data if I run it in workbench, but json object is only one array.

How can I show all the data in one array or what would be the right solution, because right now I am querying each table separately?

I tried:

$backgroundCheck = $user_home->runQuery("select * from degree_files, profile_pictures, cpr_files, backgroundcheck_files, video_files where degree_files.user_id = :user_id");
$backgroundCheck->execute(array(":user_id"=>$user_id));
$nanny_backgroundCheck_file = $backgroundCheck->fetchAll(PDO::FETCH_ASSOC);


$file_name = array();
foreach ($nanny_backgroundCheck_file as $c){
    $file_name[] = $c['file_name'];
}

$arr[] = array('file_name'=>$file_name);
echo json_encode($arr, JSON_UNESCAPED_UNICODE);

And AJAX call:

$.getJSON("PHP/nannyInfo.php", function(data) {
        //SELECT2 DATA
        $.each(data.nanny_backgroundcheck_file, function(index, data) {
        console.log(data);

        });

});

What I see in console.

So when you console.log(data) - what do you see?

u_mulder
– u_mulder

2016-07-19 06:30:56 +00:00
Commented Jul 19, 2016 at 6:30 — u_mulder
– u_mulder, Commented Jul 19, 2016 at 6:30
@u_mulder check the edit

z0mbieKale
– z0mbieKale

2016-07-19 06:32:33 +00:00
Commented Jul 19, 2016 at 6:32 — z0mbieKale
– z0mbieKale, Commented Jul 19, 2016 at 6:32

Jiri · Accepted Answer · 2016-07-20 05:05:54Z

2

The query first. You're trying to get a single column from multiple tables, right? If so, I'd suggest this syntax:

$sql = "
    select file_name from degree_files where user_id = :user_id
    UNION select file_name from profile_pictures where user_id = :user_id
    UNION select file_name from cpr_files where user_id = :user_id
    UNION select file_name from backgroundcheck_files where user_id = :user_id
    UNION select file_name from video_files where user_id = :user_id";

Then, the actual SQL execution is mostly OK but I'd simplify it to:

$backgroundCheck = $db->prepare($sql);
$backgroundCheck->execute(array(":user_id"=>1)); // 
$fileNames = $backgroundCheck->fetchAll(PDO::FETCH_COLUMN, 'file_name');

echo json_encode($fileNames, JSON_UNESCAPED_UNICODE);

edited Jul 20, 2016 at 5:05

answered Jul 19, 2016 at 7:54

Jiri

4352 silver badges8 bronze badges

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

3 Comments

Jakumi Over a year ago

I like the prepare, also protects agains sql injections.

z0mbieKale Over a year ago

@jirka Thank you man! but what if I want to get all the data from these 5 tables?

z0mbieKale Over a year ago

@jirka cant thank you enough. you are the legend. Couldnt think of the UNION.

Collectives™ on Stack Overflow

Only one array in json object

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related