sorting out data
Using PHP and Mysql, say I do a search query and I get 100 results, how can I sort them into groups of say 20 but only display 80 per page or rather per viewed page (second lot would be on same page but without the first lot) and beable to produce next and previous to sort thru the stuff. - am starting to confuse myself here
html table
----------------------
first 20 | second 20 |
----------------------
/table
html table
----------------------
third 20 | fourth 20 |
----------------------
/table
then have option to view last 20 (or more) on same page but where the first 20 is (these are images from a database). I need two tables as I'll be outputting at least 200 results (links) per tables and becomes real slow loading
So I'd need a next previous from the results but would also need next/previous from the results, I tried using an array but it goes to the next id number instead of the next array id number
id's from the results could be: 1,3,47,102,202,234,1002,1234,2141 ...
I should of downloaded an image gallery script but oh no, stubun ole me whats to reinvent the wheel This is the last part, everything else is done
Mark Hensler posted this at 17:52 — 9th January 2003.
He has: 4,048 posts
Joined: Aug 2000
It depends on how you want your tables to be structured and the data to be sorted...
Because you want the groups of 20 to be in columns, I've sorted (or printed) the results down then over.
(code not tested)
<?php
output:
+--------------------------------------+--------------------------------------+
| +----------------------------------+ | +----------------------------------+ |
| | 1 | | | 6 | |
| +----------------------------------+ | +----------------------------------+ |
| | 2 | | | 7 | |
| +----------------------------------+ | +----------------------------------+ |
| | 3 | | | 8 | |
| +----------------------------------+ | +----------------------------------+ |
| | 4 | | | 9 | |
| +----------------------------------+ | +----------------------------------+ |
| | 5 | | | 10 | |
| +----------------------------------+ | +----------------------------------+ |
+--------------------------------------+--------------------------------------+
// get $result here
// right now, we haven't printed anything
$j=0;
// loop while there are more results, and we have printed less than 80 results
//
// second table is optional, and is only printed if there are more than
// 40 results
while ((mysql_num_rows($result) > $j) && ($j < 80)) {
echo \"<table >\n\";
echo \" <tr>\n\";
echo \" <td>\n\";
// left colum
echo \"\n\";
echo \"<table >\n\";
// we require 20 rows, fill with results while we have them
for ($i=1; $i<=20; $i++) {
echo \" <tr>\n\";
if ($tmp = @mysql_fetch_array($result)) {
echo \" <td>$tmp[field1]</td>\n\";
}
else {
echo \" <td> </td>\n\";
}
echo \" </tr>\n\";
}
echo \"</table >\n\";
echo \"\n\";
echo \" </td>\n\";
echo \" <td>\n\";
// right colum
echo \"\n\";
echo \"<table >\n\";
// we require 20 rows, fill with results while we have them
for ($i=21; $i<=40; $i++) {
echo \" <tr>\n\";
if ($tmp = @mysql_fetch_array($result)) {
echo \" <td>$tmp[field1]</td>\n\";
}
else {
echo \" <td> </td>\n\";
}
echo \" </tr>\n\";
}
echo \"</table >\n\";
echo \"\n\";
echo \" </td>\n\";
echo \" </tr>\n\";
echo \"</table>\n\";
echo \"\n\";
echo \"<br>\n\";
echo \"\n\";
// we've now printed 40 rows
$j+=$i;
}
?>
The next one is more tricky. We could use a series of mysql_data_seek() calls. But I think using an array is more efficient, so I'll use that.
<?php
output:
+--------------------------------------+--------------------------------------+
| 1 | 6 |
+--------------------------------------+--------------------------------------+
| 2 | 7 |
+--------------------------------------+--------------------------------------+
| 3 | 8 |
+--------------------------------------+--------------------------------------+
| 4 | 9 |
+--------------------------------------+--------------------------------------+
| 5 | 10 |
+--------------------------------------+--------------------------------------+
// get $result here
// right now, we haven't printed anything
$j=0;
// loop while there are more results, and we have printed less than 80 results
//
// second table is optional, and is only printed if there are more than
// 40 results
while ((mysql_num_rows($result) > $j) && ($j < 80)) {
// we require 40 rows, fill with results while we have them
$cache = array(); // clear our cache
for ($i=1; $i<=40; $i++) {
// ternary operator
$cache[] = ($tmp = @mysql_fetch_array($result)) ? $tmp : NULL;
}
echo \"<table >\n\";
// left colum
// we require 20 rows, fill with results while we have them
for ($i=1; $i<=20; $i++) {
echo \" <tr>\n\";
if ($cache[$i] == NULL) {
echo \" <td> </td>\n\";
}
else {
echo \" <td>\".$cache[$i][field1].\"</td>\n\";
}
if ($cache[{$i+20}] == NULL) {
echo \" <td> </td>\n\";
}
else {
echo \" <td>\".$cache[{$i+20}][field1].\"</td>\n\";
}
echo \" </tr>\n\";
}
echo \"</table>\n\";
echo \"\n\";
echo \"<br>\n\";
echo \"\n\";
// we've now printed 40 rows
$j+=$i;
}
?>
ternary opperator
Mark Hensler
If there is no answer on Google, then there is no question.
Busy posted this at 21:21 — 9th January 2003.
He has: 6,151 posts
Joined: May 2001
Thanks I'll try the above and let you know what happens but might end up doing it at least 10 times as the results will vary between under 1000 to over 2500 results with only 200 being displayed each time.
Busy posted this at 23:48 — 9th January 2003.
He has: 6,151 posts
Joined: May 2001
Got "page contains no data" after adding the above code.
What I have works for two sections but I can't get it to carry on for more than one page, here is what I had:
<?php
$data = mysql_query($result);
echo mysql_error();
$fi = 0;
$columns = 2;
$num_rows = mysql_num_rows($data);
//echo $num_rows;
if ($num_rows <= 10){ $ded = $num_rows; }
else { $ded = 10; }
echo \"\n<table border=\\"1\\" width=\\"100%\\">\";
for($i = 0; $i < $ded; $i++) {
$row = mysql_fetch_array($data);
if($i % $columns == 0) {
echo \"<tr>\n\";
} $fi++;
$auth = $row[\"desc\"];
$num = $row[\"id\"];
echo \"<td><a href=\\"view.php?id=\" . $num . \"\\">\"
. $auth . \"</a><br></td>\n\";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows)
{
echo \"</tr>\"; }
}
echo \"</table>\n\";
//this is set to display all of whats left for the moment
if ($num_rows >= \"10\"){ $ded = ($num_rows - 10); }
else { $ded = ($num_rows - 10); }
echo \"\n<table border=\\"1\\" width=\\"100%\\">\";
$i = 0;
for($i = 0; $i < $ded; $i++) {
$row = mysql_fetch_array($data);
if($i % $columns == 0) {
echo \"<tr>\n\";
} $fi++;
$auth = $row[\"desc\"];
$num = $row[\"id\"];
echo \"<td><a href=\\"view.php?id=\" . $num . \"\\">\"
. $auth . \"</a><br></td>\n\";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows)
{
echo \"</tr>\"; }
}
echo \"</table>\n\";
}else{ just list all of them }
?>
Mark Hensler posted this at 01:21 — 10th January 2003.
He has: 4,048 posts
Joined: Aug 2000
Instead of a large switch, use a formula...
<?php
$perpage = 10;
if (!isset($page)) {
$page = 0;
}
$ded = $page * $perpage;
?>
Busy posted this at 09:59 — 18th January 2003.
He has: 6,151 posts
Joined: May 2001
OK me again
Still trying to get this thing to work (been adding and subtracting stuff) got it so it kind of works but it needs two queries to the database to work, here is what I'm trying to do in english
if ($pg==2){
get search results from database starting at result #100 to #200
display results using a while or for statement
add previous button (pg1)
if total overall results is more than 200 add link to pg3
}
elseif ($pg==3){
get search results from database starting at result #200 to #300
display results using a while or for statement
add previous button (pg2)
if total overall results is more than 300 add link to pg4
}
....
else {
display all
}
the above works except I can't get total number of results and the limited results to display.
The pg* only has 100 results with max of 5 pages where as the pg1 or default is 200 results per page and 12 pages
Is there a way to combine the two queries (find total results amount but only display limited results without selecting everything from the databse and using PHP to sort and display them.
Also is there an easier way to do the above (english code), I didnt post the actual code as it's way to big to post here
Mark Hensler posted this at 21:07 — 19th January 2003.
He has: 4,048 posts
Joined: Aug 2000
If you're on a mySQL version 4 server, you could use SQL_CALC_FOUND_ROWS (look for FOUND_ROWS() near the bottom)
If your not on a mySQL4 server, you'll probably want to do something like this:
$query = "SELECT count(id) FROM table_name WHERE exact_same_where_clause";
$result = mysql_query($query);
list($total_records) = mysql_fetch_row($result);
if (isset($pg)) {
$start = (($pg-1) * 100);
get search results from database ( LIMIT $start,100 )
display results using a while or for statement
if ($pg > 1) add 'previous' link to ($pg - 1)
if ($total_results > ($start+100)) add 'next' link to ($pg + 1)
}
else {
display all
}
Mark Hensler
If there is no answer on Google, then there is no question.
Busy posted this at 09:18 — 21st January 2003.
He has: 6,151 posts
Joined: May 2001
Has to be the second option.
you have:
list($total_records) = mysql_fetch_row($result);
and
if ($total_results > ($start+100)) add 'next' link to ($pg + 1)
Is the $total_records and $total_results meant to be the same thing?
Also
"get search results from database ( LIMIT $start,100 )"
is from what I had above which would be a query - $query = "SELECT * FROM ...
Is this what you meant?
Mark Hensler posted this at 17:18 — 21st January 2003.
He has: 4,048 posts
Joined: Aug 2000
Yes, and yes. With the "LIMIT $start,100 " actually being part of the SQL for your query. The rest of that line was just not even pseudo-code.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.