Solved

PHP / MySQL pagination from unrelated query problem

Posted on 2009-06-30
20
687 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:rubhadubh
  • 13
  • 7
20 Comments
 
LVL 5

Expert Comment

by:kingofnines
ID: 24744737
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
 

Author Comment

by:rubhadubh
ID: 24744797
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
 
LVL 5

Expert Comment

by:kingofnines
ID: 24744843
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
 

Author Comment

by:rubhadubh
ID: 24744897
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
 
LVL 5

Expert Comment

by:kingofnines
ID: 24744943
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
 

Author Comment

by:rubhadubh
ID: 24745435
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
 
LVL 5

Accepted Solution

by:
kingofnines earned 500 total points
ID: 24745537
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
 

Author Comment

by:rubhadubh
ID: 24745726
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
 
LVL 5

Expert Comment

by:kingofnines
ID: 24745760
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
 

Author Comment

by:rubhadubh
ID: 24746550
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Expert Comment

by:kingofnines
ID: 24746582
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
 

Author Comment

by:rubhadubh
ID: 24802930
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
 
LVL 5

Expert Comment

by:kingofnines
ID: 24803009
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
 

Author Comment

by:rubhadubh
ID: 24803418
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
 

Author Comment

by:rubhadubh
ID: 24803692
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
 

Author Comment

by:rubhadubh
ID: 24803998
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
 

Author Comment

by:rubhadubh
ID: 24804877
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
 

Author Comment

by:rubhadubh
ID: 24805970
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
 

Author Comment

by:rubhadubh
ID: 24813602
aha! array_slice()

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

Author Closing Comment

by:rubhadubh
ID: 31598315
Thanks for your help. I worked out the detail, but you got me on the right track
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now