Boggled... MYSQL + JOIN + PAGINATION

Question

I've spent far too long trying to come up with a good solution to this problem on my own. Haven't found any good answers to help, though I've tried meshing solutions from different answers without luck.

Goal: Present a paginated Projects page containing data from multiple tables.

I'm using several joins in a single MYSQL query, because I wanted to avoid running queries in a PHP loop. That alone has introduced a fetch_assoc problem, where I had to build my own results array from the while(fetch_assoc) loop because of the repetitive rows resulting from the query. However, the next problem is that the query runs through a pagination class which I use to generate pagination links and get query data like num_rows and such.

So right now, I have a page showing the data I want, but the pagination class is showing me the wrong amount of rows and pagination links. Not sure what the best way is around this problem. Hoping to get some ideas from one of you brilliant experts!

Code is basically:

$sql = "...."

$projects = new pager($sql)

echo $projects->num_rows

while($projects->fetch_assoc){
build new array from data
$project_array[][][]
}

foreach($project_array){
display data from all tables...
}

$projects->showPageLinks

This SQLFiddle provides an example of the data I get:

Notice that there are 7 result rows, but for only 4 projects. So the pagination class shows 7 num_rows (rightfully) but there's only 4 projects.

Any ideas welcome!

I though of maybe concatenating sub query results from other tables into string value in own columns and parsing that with php explode() and such, which would then produce single project rows with all the data, but I haven't been able to accomplish that yet.

Thank you in advance for your help!

Answer 1

The only solution I was able to come up with was to concatenate results from sub queries / joins into single columns in the primary resultset. That allows me to get the results I need without having to modify the pagination class.

See example of concatenated results in this SQLFiddle

Example Query:

SELECT p.id pid, p.name pname, c.name cname,
GROUP_CONCAT(DISTINCT CONCAT_WS('|',w.hours,w.notes,t.name) SEPARATOR ';') as works

FROM projects p
LEFT JOIN (SELECT * FROM clients) AS c ON c.id=p.client_id
LEFT JOIN (SELECT * FROM work) AS w ON w.project_id=p.id
LEFT JOIN (SELECT * FROM tools) AS t ON t.id=w.tool_id
GROUP BY p.id

GROUP_CONCAT(DISTINCT ... is what concatenates the individual rows from the joined results, and the CONCAT_WS('',col,col,..) concatenates the individual result columns. Both allow to you to use separators you define, just like php implode() would. Ultimately, you end up with a formatted string of results in a single column/row on which you can use php to explode() rows and then their columns.

Now I get the right result count, and simply explode the rows and fields by the defined separators.

Example Code:

$wkeys = array('hours','notes','toolname'); // to use as array key (column name)

while($r=$projects->fetch_assoc()){
    if(strpos($r['works'],';')!==false){
        $wrows = explode(";",$r['works']); // individual rows

        foreach($wrows as $k=>$v) { 
            $wv = explode("|",$v); // individual col=val pairs
            $works[] = array_combine($wkeys,$wv); 
        }
    }
}

Hope this helps someone else who may be facing the same situation.

EDIT: While this solution does work quite well for me, I did end up running into a problem with the default length limit for GROUP_CONCAT which is 1024. Any value that was over 1024 was cut down to 1024. See this answer to resolve that. I'd advise you be proactive and change it now, unless you're absolutely sure your values will never be longer than 1024.