1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Not sure if php loop or mysql join call to build an array

Discussion in 'PHP' started by whofarted, Oct 4, 2020.

  1. #1
    Hi all I'm trying to build an array I've got it as I wanted until I decided to add ability to ad images/files.
    I'm trying to build on an existing array for multiple files & i'm having a brain fart. please help. :confused:

    here's how i'm building the array now:
    // Pull all the post to display
    $sql = "SELECT *
            FROM complaint
            ORDER BY c_time DESC
            LIMIT 20";
    
    
    $result = $db->sql_query($sql);
    
    // build the array for display
    while($thisdata = $result->fetch_assoc())
    {
        $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                    'ID'                => $thisdata['id'],
                                    'TIME'                => date("g:i a", $thisdata['c_time']),
                                    'COMPLAINT'         => nl2br($thisdata['complaint']),
                                    'COMPLAINT_EDIT'     => $thisdata['complaint']);
    }
    PHP:
    I'm trying to figure out how to make it show more than one file per complaint. currently it (the array) looks like something like this:
    Array
    (
        [0] => Array
            (
                [id] => 245
                [c_time] => 1602451200
                [complaint] => rest 2
            )
    
        [1] => Array
            (
                [id] => 244
                [c_time] => 1602450600
                [complaint] => new test
            )
    }
    Code (markup):
    I want it to have something like this:
    Array
    (
        [0] => Array
            (
                [id] => 245
                [c_time] => 1602451200
                [complaint] => rest 2
                [files] => (AN ARRAY OF FILES HERE IF THERE ARE ANY)
            )
    
        [1] => Array
            (
                [id] => 244
                [c_time] => 1602450600
                [complaint] => new test
            )
    }
    Code (markup):
    I'm not sure how to do this. Would this be a call to the db with a JOIN or two different sql calls and then loop with php & somehow add them to the array that way?
    FYI: my tables are setup like this:
    complaints : id - c_time - complaint
    and
    complaint_images : id - c_id - name
    SEMrush
    TIA for any help. :)
     
    whofarted, Oct 4, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    26,437
    Likes Received:
    3,956
    Best Answers:
    111
    Trophy Points:
    665
    #2
    I'd start with something like this

    
    //your query
    $sql = "SELECT complaint.id, complaint.complaint, complaint.c_time, complaint_images.name
    FROM complaint left join complaint_images on complaint.id = complaint_images.c_id
    ORDER BY c_time DESC
    LIMIT 20";
    
    //your array
    $output = [];
    
    $result = $db->sql_query($sql);
    // build the array for display
    while($thisdata = $result->fetch_assoc()){
    
       if (!array_key_exists($thisdata['c_time'], $output['DAYS']) || !array_key_exists($thisdata['id'], $output['DAYS'][$thisdata['c_time']){
    
          //don't try to make the key fancy
          $output['DAYS'][$thisdata['c_time']][$thisdata['id']] = [
             'ID' => $thisdata['id'],
            'TIME' => date("g:i a", $thisdata['c_time']),
            'DATE' => date("D M j, Y", $thisdata['c_time']),
            'COMPLAINT' => nl2br($thisdata['complaint']),
            'COMPLAINT_EDIT' => $thisdata['complaint'],
            'IMAGES' => []
          ];
       }
       $output['DAYS][$thisdata['c_time']][$thisdata['id']]['IMAGES'][] = $thisdata['name'];
    }
    
    
    Code (php):
     
    sarahk, Oct 4, 2020 IP
    JEET likes this.
  3. whofarted

    whofarted Peon

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    I was trying out yur code but I had to fix a few things a missing ] in the if line after $output['DAYS'][$thisdata['c_time']
    then, the misssing ' in $output['DAYS]
    After that I get an errorsaying it doesn't need the ; in line 24 "];" but if I remove it I get a new error, but it looks right so I'm not sure what to do with that.

    I got my code to work, but I know it's sloppy to have a DB call in a loop. But this is working for now.
    // Pull all the post to display
    $sql = "SELECT *
            FROM complaint
            ORDER BY c_time DESC
            LIMIT 20";
    
    
    $result = $db->sql_query($sql);
    $files = array();
    
    // build the array for display
    while($thisdata = $result->fetch_assoc())
    {
        $sql2 = "SELECT *
                FROM complaint_images
                WHERE c_id = '$thisdata[id]'";
        $result2 = $db->sql_query($sql2);
    
        while($thisdata2 = $result2->fetch_assoc())
        {
            $files[] = $thisdata2['name'];
        }
    
    
        $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                    'ID'                => $thisdata['id'],
                                    'TIME'                => date("g:i a", $thisdata['c_time']),
                                    'COMPLAINT'         => nl2br($thisdata['complaint']),
                                    'COMPLAINT_EDIT'     => $thisdata['complaint'],
                                    'FILES'                => $files);
        $files = array();
    }
    PHP:
    Don't laugh, I know it's sloppy. any advice on a better way?

    P.S. A partial example of the array i'm getting now:
    Array
    (
        [DAYS] => Array
            (
                [Sun Oct 11, 2020] => Array
                    (
                        [0] => Array
                            (
                                [ID] => 245
                                [TIME] => 4:20 pm
                                [COMPLAINT] => rest 2
                                [COMPLAINT_EDIT] => rest 2
                                [FILES] => Array
                                    (
                                        [0] => file1.png
                                        [1] => file2.png
                                        [2] => file3.png
                                    )
    
                            )
    
                        [1] => Array
                            (
                                [ID] => 244
                                [TIME] => 4:10 pm
                                [COMPLAINT] => new test
                                [COMPLAINT_EDIT] => new test
                                [FILES] => Array
                                    (
                                        [0] => test-1.png
                                        [1] => test-2.png
                                    )
    
                            )
    
                    )
    Code (markup):
     
    Last edited: Oct 5, 2020
    whofarted, Oct 5, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    26,437
    Likes Received:
    3,956
    Best Answers:
    111
    Trophy Points:
    665
    #4
    cut it down to a single database call.

    If you have 50 complaints returned by your first query that is another 50 database hits just to generate your array.
     
    sarahk, Oct 5, 2020 IP
    JEET likes this.
  5. JEET

    JEET Notable Member

    Messages:
    3,492
    Likes Received:
    423
    Best Answers:
    16
    Trophy Points:
    235
    #5
    You can try something like this:

    <?php
    $sql=" select c.id, c.complaint, c.c_time,
    group_concat( i.name ) as name
    from complaints as c
    left join complaint_images as i
    on c.id = i.c_id
    order by c.c_time desc limit 20 ";

    $result= $mysqli->query($sql);
    $s=array();
    while($row=$result->fetch_array(MYSQLI_ASSOC)){
    $s[]=$row;
    }
    ?>


    Your array will be like this:
    array(
    [0]=>Array(
    'id'=> 123,
    'complaint' => text of complaint,
    'c_time' => time of complaint
    'name' => file1.jpg, file2.jpg, file3.jpg
    ),
    [1]=>Array(
    'id'=> 456,
    'complaint' => text of complaint,
    'c_time' => time of complaint
    'name' =>
    )
    );


    You will have comma separated list of image names, instead of in an array.
    Do a "explode" on it to turn it into an array when processing.
     
    JEET, Oct 5, 2020 IP
  6. whofarted

    whofarted Peon

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    Thanks for all the help. I've got it down to 2 sql calls now. I'll "prolly" :) stick with this:
    // get all the file attachments into an array
    
    $file_array = array();
    $sql2 = "SELECT c_id, name
                FROM complaint_images";
    
    $result2 = $db->sql_query($sql2);
    while($thisdata2 = $result2->fetch_assoc())
    {
        $file_array['c_id'][$thisdata2['c_id']][] = $thisdata2['name'];
    }
    
    // Pull all the post to display
    $sql = "SELECT *
            FROM complaint
            ORDER BY c_time DESC
            LIMIT 20";
    
    
    $result = $db->sql_query($sql);
    
    // build the array for display
    while($thisdata = $result->fetch_assoc())
    {
    
        $files = @$file_array['c_id'][$thisdata['id']];
    
        $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                    'ID'                => $thisdata['id'],
                                    'TIME'                => date("g:i a", $thisdata['c_time']),
                                    'COMPLAINT'         => nl2br($thisdata['complaint']),
                                    'COMPLAINT_EDIT'     => $thisdata['complaint'],
                                    'FILES'                => $files);
    }
    PHP:
     
    whofarted, Oct 6, 2020 IP
  7. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,399
    Likes Received:
    1,875
    Best Answers:
    245
    Trophy Points:
    515
    #7
    Why build an array wasting memory on making copies of everything for no good reason, instead of just sending the data -- or the result handler -- to the flipping template? This looks like you're violating separation of concerns and making everything work ten times harder than need be.
     
    deathshadow, Oct 16, 2020 IP