How to correctly sort database results

Question

I have been asked to post a new question about how to correctly sort my data. Here is what I am trying to do. I want to make a single trip to my database for all of the data that I will need for this page. Once I have the data returned from the database, I need to sort it out and use it.

For brevity, I am only posting a single row of data. There are, in actuality, 8 rows being returned. Here is the structure of the data that is being returned:

col1    col2    col3    col4    col5    col6    col7    col8    col9

Now, here is the problem I am having. Columns 1 through 7 hold a 1:1 dataset. Columns 8 and 9 however, hold numerous rows, hence the 8 total rows. Because I have columns 1 through 7 mixed in with the 1:M rows, I am echoing rows 1-7 7 times more than I need to.

How can I resolve this?

---

Let me just add that there are 4 inner joins being used in this query. One of the tables, holds page specific data that I only need to access once. The other joins are what are creating the other rows. Let me just say tat the output of this resultset is absolutely correct. I just need to figure out how to sort it.

---

OK, this should make sense....

dealerId  empId  lotId  rackId  porterId  vehicle  vin
1         1      1      1       1         Geo      12JI997676JH
1         1      1      1       1         Ford     87HJ879854HS
1         1      1      1       1         BMW      567876HCS456
1         1      1      1       1         Mercedez 1JI8787GS687

In this set, notice that I have 4 rows of repeating data. What I need is this:

dealerId  empId  lotId  rackId  porterId
1         1      1      1       1

AND then for the multiple rows, I need this:

vehicle  vin
Geo      12JI997676JH
Ford     87HJ879854HS
BMW      567876HCS456
Mercedez 1JI8787GS687

So, this setup now gives me the single row of my dealer data and I now have a new array with the vehicle data. :)

Answer 1

Two options that I know of (and use, depending on how large I expect the datasets to be).

Either:
Get all rows, like you do now, and sort it out with php, with something like:

$result = array();
foreach( $rowset as $row )
{
    // if the parent row is not present in the result array (assuming col1 is PK)
    if( !isset( $result[ $row[ 'col1' ] ) )
    {
        // create it, and make arrays of col8 and col9
        $result[ $row[ 'col1' ] ] = array(
            'col1' => $row[ 'col1' ],
            'col2' => $row[ 'col2' ],
            'col3' => $row[ 'col3' ],
            'col4' => $row[ 'col4' ],
            'col5' => $row[ 'col5' ],
            'col6' => $row[ 'col6' ],
            'col7' => $row[ 'col7' ],
            'col8' => array( $row[ 'col8' ] ),
            'col9' => array( $row[ 'col9' ] )
        )
    }
    else
    {
        // the parent row is present, so push values in col8 and col9 arrays
        $result[ $row[ 'col1' ] ][ 'col8' ][] = $row[ 'col8' ];
        $result[ $row[ 'col1' ] ][ 'col9' ][] = $row[ 'col9' ];
    }
}

Or:
Make more trips to the database, by first selecting the unjoined rows, and loop trough these rows and select the child rows for them.

So, concluding: if you really only want one trip to the DB, the first option is the only available option (as far as I know).

PS:
Other options would probably be Stored Procedures or Views, but I don't have enough experience with them to give you good examples of them, especially Views (not even sure Views can be used for these purposes).

But the benefit of a Stored Procedure would be a single trip to the DB I guess.

Answer 2

OK, so the question is, how do you want to use this data? If you want to display it in this fashion, one row per vehicle, it makes sense to fetch the data JOINed and just go through it:

foreach ($row as $vehicle) {
    echo "$vehicle[vehicle] … $vehicle[dealerId]\n";
}

Each retrieved row would represent one set of related data.

If, OTOH, the JOINed vehicle data doesn't actually have to do much with the dealerIds etc, it makes a lot more sense to retrieve these two things in separate queries. It's probably faster too.

Retrieve (and store, to begin with) data in the simplest way that makes sense so you have to do as little post-massaging in your PHP code as possible. Don't set the goal to be "retrieve everything in a single query" just because. If you fear several trips to the database are too expensive, they're probably not. Your time is much more valuable than the split second it takes to make a second request.