asked on

PHP / MySQL pagination from unrelated query problem

I have a complex db query returning search results which I was paginating using LIMIT $offset, $limit - that is working fine.

The requirement now though is to add results, not search filtered, from a totally unrelated table, tack them on the end of the main search results but still paginate, and I can't work out how to do it.

I can't join the queries (or can I?), and I can't push all the results into an array because the data is also manipulated row by row in the output loop. How can I combine the results so that just one LIMIT statement will control the pagination, or can I store the resultset so it isn't being re-queried each time a new page is chosen?

It's not practical to post live code, so here's a demo of the function:

function buildSearchResults($form){
  $limit = (isset($form['limit'])) ? $form['limit']: 10;
  $page = empty($form['page']) ? 1 : $form['page'];
  $offset = $page * $limit - ($limit);
 
  $sql1 = "long complex query LIMIT $offset, $limit";
  $sql2 = "simpler query from unrelated table though shares some field names LIMIT $offset, $limit";
 
  $num_rows = add the mysql_num_rows from both queries
  $num_pages = ($limit > 0) ? $num_rows / $limit : 1;
 
  // create the pagination control
 
  // output the data - currently done in a while loop
  while($row1 = mysql_fetch_array($result1)){
 
  }
  while($row2 = mysql_fetch_array($result2)){
 
  }
}

Open in new window

kingofnines

Use sessions and store the results of both queries in an array. Then apply the pagination to the array instead of the query results.

Alan

rubhadubh

ASKER

I tried that, but there's processing within the first while loop that I couldn't get to work sifting through array elements.

Even if I could ignore the LIMIT statements, return all the results for both queries and use a FOR loop instead, starting the loop at $offset each time the function fires?

But I still need to combine the results somehow - is an array the only to do so, do you think?

kingofnines

It is the only way I would do it. Otherwise you have to process one result set and then process the other. You would have to create variable(s) to keep track of record counts so when you switch from one result to the other you know how many records are already displayed. That would be pretty messy in my opinion. An array in a session would be so much easier.

Alan

rubhadubh

ASKER

Ok, maybe I should persevere with the array option. I was hoping someone knew a way to combine 2 totally unrelated queries that I didn't know of!

I'll try it and come back to you - thanks

kingofnines

Databases are "relational". When you say "unrelated" that makes it sound to me that they cant be combined. Are there any like fields in the queries that we could link on?

Alan

rubhadubh

ASKER

One query is members and the other is places, and there's no direct relation between them. Each query is made up of joins between multiple tables, especially the members data.

The list should display members, followed by places, followed by padding rows to the balance of $limit.

The padding bit isn't a problem, but if I did manage to join the queries somehow (UNION ALL?) I would run into problems working out whether I'm processing a member or a place as they display differently in the display table. The same problem will occur with the array solution.

For example, the display code tests the value of, say, $row['fkMemberLevelID'], but will choke when the results switch from member data to place data.

ASKER CERTIFIED SOLUTION

kingofnines

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rubhadubh

ASKER

You may well be right! So I would use something like (simplified):

if(!isset($_SESSION['result'])){
while($row = mysql_fetch_array($result1){
$arrayResult['member'][] = $row;
}
while($row = mysql_fetch_array($result2){
$arrayResult['place'][] = $row;
}
$_SESSION['result'] = $arrayResult;
}

or would you explicitly name the array elements and set the value from the row value?

kingofnines

That depends on your query and what you are displaying. I will assume your query is only returning data that is being displayed. If there are more than one field being returned, then yes, I would save them individually in the array. Otherwise you will have to parse $row later.

Alan

rubhadubh

ASKER

How would you handle the pagination on the array then? My guess is to continue storing $offset and $limit and do something like:

for($t = $offset; $t < ($offset + $limit); $t++)

Is that going to work? Is there a better way?

kingofnines

thats how I would do it. And dont forget to pass your pagination variable values foward in the session also... you will need them when you display the next page.

Alan

rubhadubh

ASKER

Say the result has 3 fields (actually has more), what's the best structure to set for the array? I'm not as familiar with arrays as I should be. We're talking about a multi-dimensional array, right?

So the first 'container' will be $arrayResult['member'] and would I then assign each row from the result to the next 'container' level, like so:

$arrayResult['member']['id'][] = $row['pkMemberID'];
$arrayResult['member']['firstname'][] = $row['firstname'];
$arrayResult['member'][lastname][] = $row['lastname'];

Or is there a better way to store the information about each member?

Sorry, I got distracted on other stuff there for a few days...

kingofnines

That is the way I would do it, however, your syntax needs one small change...

$arrayResult['member']['id'][] = $row['pkMemberID'];
$arrayResult['member']['firstname'][] = $row['firstname'];
$arrayResult['member'][lastname][] = $row['lastname'];

should be...

$arrayResult['member']['id'] = $row['pkMemberID'];
$arrayResult['member']['firstname'] = $row['firstname'];
$arrayResult['member'][lastname] = $row['lastname'];

rubhadubh

ASKER

Oh right, thanks - I thought I would need the brackets to push each item onto the array, no?

I'm now having trouble getting the data back out again! In a loop, how do I access the array elements to output, say, a table like:
<tr>
<td>id</td>
<td>firstname</td>
<td>lastname</td>
</tr>

I guess a foreach loop, but not sure whether I also need a nested loop? Many thanks for your help, I'm making you work for the points!

rubhadubh

ASKER

Ok, I think I have it...

foreach($arrayResult as $item => $value){
if($item == 'member'){
echo('<tr class="resultrow">');
echo('<td>' . $item . '</td>');
foreach($value as $type){
echo('<td>' . $type . '</td>');
}
echo('</tr>');
}
elseif($item == 'place'){
echo('<tr class="resultrow">');
echo('<td>' . $item . '</td>');
foreach($value as $type){
echo('<td>' . $type . '</td>');
}
echo('</tr>');
}
}

I have to split members and places to be able to display them differently - does that look about right?

rubhadubh

ASKER

no, that's not right...

foreach($arrayResult as $item => $value){
if($item == 'member'){
echo('<tr class="resultrow">');
echo('<td>' . $value['id'] . '</td>';
echo('<td>' . $value['firstname'] . '</td>';
echo('<td>' . $value['lastname'] . '</td>';
echo('</tr>');
}
elseif($item == 'place'){
echo('<tr class="resultrow">');
echo('<td>' . $value['id'] . '</td>';
echo('<td>' . $value['placename'] . '</td>';
echo('</tr>');
}
}

does that look better?

rubhadubh

ASKER

Ok, I got the display right, but adding the rows to the array wasn't working. As I suspected, the loop goes through the result updating the array with the values rather than adding each row, so at the end of the loop, the array contains 1 element, the data of the last row.

What I did was:

$arrayResult['member'][$row['pkMemberID']]['id'][] = $row['pkMemberID'];
$arrayResult['member'][$row['pkMemberID']]['firstname'][] = $row['firstname'];
$arrayResult['member'][$row['pkMemberID']][lastname][] = $row['lastname'];

So that the 2nd level array was keyed on the id, and then on the display I had to nest the loop:

foreach($arrayResult as $item => $value){
if($item == 'member'){
foreach($value as $v){
echo('<tr class="resultrow">');
echo('<td>' . $v['id'] . '</td>');
echo('<td>' . $v['firstname'] . '</td>');
echo('<td>' . $v['lastname'] . '</td>');
echo('</tr>');
}
}
}

Still got the pagination and session work to do...

rubhadubh

ASKER

Still not quite right, the resulting array structure would make it hard to paginate, so:

$aResult[$row['pkMemberID']]['type'] = 'member';
$aResult[$row['pkMemberID']]['pkMemberID'] = $row['pkMemberID'];
$aResult[$row['pkMemberID']]['firstname'] = $row['firstname'];
$aResult[$row['pkMemberID']]['lastname'] = $row['lastname'];

then:

foreach($arrayResult as $item => $v){
if($v['type'] == 'member'){
echo('<tr class="resultrow">');
echo('<td>' . $v['id'] . '</td>');
echo('<td>' . $v['firstname'] . '</td>');
echo('<td>' . $v['lastname'] . '</td>');
echo('</tr>');
}
}

How do I use a for loop but still assign the array values to $v?

rubhadubh

ASKER

aha! array_slice()

$aSlice = array_slice($_SESSION['searchresult'], $offset, $limit);
foreach($aSlice as $item => $v){

rubhadubh

ASKER

Thanks for your help. I worked out the detail, but you got me on the right track