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

rubhadubhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kingofninesCommented:
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
0
rubhadubhAuthor Commented:
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?
0
kingofninesCommented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

rubhadubhAuthor Commented:
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
0
kingofninesCommented:
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
0
rubhadubhAuthor Commented:
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.
0
kingofninesCommented:
Your process for displaying the data will need to change then. I know you where hoping for a simple fix, but there isn't one in this case. The data is unrelated so it cant be queried together. UNION requires that the fields from each query match.

I will say one final time, the only way I see to do this is with an array stored in a session. you CAN identify a member or a place with my suggestion by using a multi-dimensional array...

$data['Place'][] = $record_from_query1['place'];
$data['Member'][] = $record_from_query2['menber'];


Alan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rubhadubhAuthor Commented:
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?
0
kingofninesCommented:
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
0
rubhadubhAuthor Commented:
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?
0
kingofninesCommented:
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
0
rubhadubhAuthor Commented:
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...
0
kingofninesCommented:
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'];

0
rubhadubhAuthor Commented:
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!
0
rubhadubhAuthor Commented:
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?
0
rubhadubhAuthor Commented:
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?
0
rubhadubhAuthor Commented:
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...
0
rubhadubhAuthor Commented:
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?
0
rubhadubhAuthor Commented:
aha! array_slice()

$aSlice = array_slice($_SESSION['searchresult'], $offset, $limit);
foreach($aSlice as $item => $v){
0
rubhadubhAuthor Commented:
Thanks for your help. I worked out the detail, but you got me on the right track
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.