CODEWITHSUNDEEP
phparraysarray-push
NewUser
NewUser

Reputation: 13323

php push the results to an array by getting from the table

I have tables like this

listing_id 
1
4
345
654

listing documents

listing_id      folder                  filename
000345        full_menu_file            testfile098
000345        header_menu_file          testfile067
000004        full_menu_file            testfile
000001        menu_file                 testfile567
000004        footer_menu_file          testfile76
000004        test_menu_file            testfile65
000345        footer_menu_file          testfile764 
000654        footer_menu_file          testfile098
000654        footer_menu_file          testfile078

Now I want to make an array and put those related data in that array. So the final output will be like this

array(
    [1] => Array
            (
                [listing_id] => 1
                [full_menu_file] => 
                [header_menu_file] => 
                [menu_file] => 
                [footer_menu_file] => 
                [test_menu_file] => 

            )
    [1] => Array
            (
                [listing_id] => 345
                [full_menu_file] => testfile067
                [header_menu_file] => testfile067
                [menu_file] => 
                [footer_menu_file] => testfile764   
                [test_menu_file] =>

            )

    [2] => Array
            (
                [listing_id] => 4
                [full_menu_file] => testfile
                [header_menu_file] => 
                [menu_file] => 
                [footer_menu_file] => testfile76
                [test_menu_file] => testfile65

            )                           
)

So I made my php code like this

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "listings";
$mysqli = new mysqli($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$listing_query = "SELECT * FROM `listings` ORDER BY `listing_id` ASC ";
$cat_array = array();
if( $result = $mysqli->query($listing_query) ) {
    while( $obj = $result->fetch_object() ) {
        $listing_id = $obj->listing_id;

        //Get all the file names
        $get_images_name = "SELECT * FROM `listing_documents` WHERE `listing_id` = ".str_pad($listing_id, 6, '0', STR_PAD_LEFT)." ";
        if( $img_query = $mysqli->query($get_images_name) ) {
            while( $object = $img_query->fetch_object() ) {
                if( $object->folder == 'full_menu_file' ) {
                    $full_menu_file_name = $object->filename;
                }
                if( $object->folder == 'header_menu_file' ) {
                    $header_menu_file_name = $object->filename;
                }
                if( $object->folder == 'menu_file' ) {
                    $menu_file_name = $object->filename;
                }
                if( $object->folder == 'footer_menu_file' ) {
                    $footer_menu_file_name = $object->filename;
                }
            }

            $listing_array['listing_id'] = $listing_id;
            $listing_array['full_menu_file_name'] = $object->full_menu_file_name;
            $listing_array['header_menu_file'] = $object->header_menu_file;
            $listing_array['menu_file'] = $object->menu_file;
            $listing_array['footer_menu_file'] = $object->footer_menu_file;

            array_push($cat_array, $listing_array); 

    }
}
print_r($cat_array);

But it is not showing the result as I need. So can someone tell me how to do this? Any suggestions will be really appreciable. Thanks

Upvotes: 0

Views: 124

Answers (3)

Reed
Reed

Reputation: 14984

Your schema doesn't make sense to me. I don't know why you would use the padded string instead of an integer in your listing_documents table. But, using your current schema you could do:

$result = $mysqli->query(
<<<'QS'
SELECT listings.listing_id, listing_documents.folder, listing_documents.filename
    FROM listings
    JOIN listing_documents
        ON listing_documents.listing_id LIKE LPAD(CONVERT(listings.listing_id,char),6,'0')
    ORDER BY listings.listing_id ASC
QS;

I'm not actually sure if you need to convert the listing_id for lpad. And It probably is more performant to convert listing_documents.listing_id to an INT like ON CONVERT(listing_documents.listing_id,UNSIGNED INTEGER) = listings.listing_id
Then to add your results to an array: 

$outputData = array();  
while ($object=$result->fetch_object()){
    if (!isset($outputData[$object->listing_id]))
        $outputData[$object->listing_id] = array();
    $outputData[$object->listing_id][$object->folder] = $object->filename;
}

With this approach, if a particular listing_id in listing_documents does not have all the different folders, then the key would be skipped.

Now, $outputData will have the data with array keys being the listing IDs.

To reset the array keys, you could do:

$outputData = array_values($outputData);

Upvotes: 0

Barmar
Barmar

Reputation: 782107

You need to put quotes around the result of str_pad so it will be treated as a string, not a number with leading zeroes.

$get_images_name = "SELECT * FROM `listing_documents` WHERE `listing_id` = '".str_pad($listing_id, 6, '0', STR_PAD_LEFT)."' ";

You could also combine both queries into a single JOIN:

SELECT l.listing_id as l_listing_id, ld.*
FROM listings as l
JOIN listing_documents AS ld ON ld.listing_id = LPAD(l.listing_id, 6, '0')
ORDER BY l_listing_id

The equivalent query without a join is:

SELECT *
FROM listing_documents
WHERE listing_id IN (SELECT LPAD(listing_id, 6, 0) FROM listings)
ORDER BY listing_id

Upvotes: 1

Clyff
Clyff

Reputation: 4076

I think your problem is the str_pad. You could use CONVERT or CAST instead:

SELECT * FROM `listing_documents` WHERE CAST(listing_id AS INT) = $listing_id

SELECT * FROM `listing_documents` WHERE CONVERT(INT, listing_id) = $listing_id

Upvotes: 0

Related Questions