Forum Moderators: coopster
Is it possible, or do I need to create a new page?
Another words, If $book_array =5, then start a new page
Here is the code as exists:
function display_books($book_array)
{
//display all books in the array passed in
if (!is_array($book_array))
{
echo '<br />No items currently available in this category<br />';
}
else
{
//create table
echo '<table width = \"100%\" border = 0>';
//create a table row for each book
foreach ($book_array as $row)
{
$url = 'show_book.php?item_number='.($row['item_number']);
echo '<tr><td>';
if (@file_exists('images/'.$row['item_number'].'.jpg'))
{
$team = '<img src=\'images/'.($row['item_number']).'.jpg\' border=0>';
do_html_url($url, $team);
}
else
{
echo ' ';
}
echo '</td><td>';
$team = $row['player'].' of the '.$row['team'];
do_html_url($url, $team);
echo '</td></tr>';
}
echo '</table>';
}
echo '<hr/>';
}
As always, thanks for your help.
Thanks
For dynamic, I'd use the limit syntax on the sql query to break the records up into different pages. That way all the data ain't stored in memory at once.
For example,
$num_per_page=20;
$start_record=(($page_number * $num_per_page) - $num_per_page);
$sql="select field1,field2 from table limit $start_record,$num_per_page";
Then increment $page_number each time the visitor hits "Next Page".
To make it prettier by knowing total number of pages at the start and letting someone jump into the middle, you need to do a seperate db hit at start (e.g. select count(some_field) where records=what_I_want).
For dynamic, I'd use the limit syntax on the sql query
Hmm. I agree and that's what I do, but I also thought that's what the evolt article suggested and what I meant to indirectly suggest. I'm pretty sure I've seen an article that shows how to use
LIMIT offset, numrecs
In any case it's not that hard. The key thing to remember is that you want the offset to be
(page_number -1) * results_per_page
so that for page 1, the offset is 0 (for example).
Is there not a relativley simple way of staying,
if book array = 10 start a new page?
umm, it must if you're using a db...
Here is the code as exists:
function display_books($book_array)
{
//display all books in the array passed in
if (!is_array($book_array))
{
echo '<br />No items currently available in this category<br />';
}
else
{
//create table
echo '<table width = \"100%\" border = 0>';
//create a table row for each book
foreach ($book_array as $row)
{
$url = 'show_book.php?item_number='.($row['item_number']);
echo '<tr><td>';
if (@file_exists('images/'.$row['item_number'].'.jpg'))
{
$team = '<img src=\'images/'.($row['item_number']).'.jpg\' border=0>';
do_html_url($url, $team);
}
else
{
echo ' ';
}
echo '</td><td>';
$team = $row['player'].' of the '.$row['team'];
do_html_url($url, $team);
echo '</td></tr>';
}
echo '</table>';
}
echo '<hr/>';
}
It is more of a show me than a ask me type, if that makes sense.
If you need help with setting the query up to do this and how to control it from page to page - that is a seperate issue but we can help you with that.
Alternately, if you really want to control the results after the query we can switch gears. ;)
To answer your question, yes I would need assistance in setting up they queries.
Class time! ;)
Goal:
Build an application that queries a db, posts the first 10 results in a template page and builds a navigation system.
Step 1:
The query is the control mechanism for the results. On this premise we simply need to limit the number of results we get with the query. But we also need to know how many results there are altogether.
I'm a proponent of using the count() function so I'd approach it this way.
$num_query = "SELECT count(item_number)
FROM book_table";
counts the total number of rows
if(!isset($offset))
$offset = 0;
$page_query = "SELECT item_number, book_title, book_author, ISBN, num_pages, book_publisher, book_copyright (I'm guessing at field names)
FROM book_table
LIMIT $offset,10";
The first part learns the total number of records we need to deal with so we can build the correct number of links (based on 10 results/page) and populate the link URL with the appropriate arguments (variables) to get the correct result set.
The second part looks to see if an offset has been established (necessary for the pages that include the results after the first 10). With the offset in place, we begin the first query.
So far so good? I'm headed home in a few minutes and will pick up again later. Others may see where I'm going with this and can fill in. Also, if anyone has a better way of doing this, let's post it and discuss it. Hopefully we won't confuse Acternaweb with too many options.
Also, do I need to have every field name present or just those I want displayed?
Thanks again
Caveat: I've told Acternaweb he needs to build this. We'll help him work his way through it.
The files:
show_cat.php: this file is the template file. It simply calls another file (book_sc_fns.php) into itself and runs some functions to display an output. It's job is to show all of the books within a given category.
output_fns.php: a collection of functions for outputting the HTML.
book_fns.php: is one of the files that the above file calls. It's a collection of functions for storing and retrieving book data. (It holds the queries we want to modify).
In order to get some sort of navigation in place we'll need to edit some of the existing functions.
I'm going to talk through this without actually giving you the code. See if this works - if not then I'll give you more. Here we go.
The way the code is written it queries for all of the available books for a given category and displays them all at once. We want to control the number of books displayed and we want to create a navigation so the viewer can get to the next/previous selections. I will go on with the assumption that we will set the number of books that will be displayed on the output pages.
The concept I'm after is to limit the query rather than try to control the output of the array that is created by the code.
SO, the query is actually located in the file called book_fns.php. The function which gets the books to be displayed based on category is called get_books().
Locate this and tell me what the query should look like if we want to limit the output from all books to only the first 5. Don't worry about the rest of the books just yet. Hint you'll need to get familiar with the options you can use with SELECT [mysql.com].
if (!$catid ¦¦ $catid=='')
return false;
$conn = db_connect();
$query = "select * from books where catid='$catid'";
$result = @mysql_query($query);
if (!$result)
return false;
$num_books = @mysql_num_rows($result);
if ($num_books ==0)
return false;
$result = db_result_to_array($result);
return $result;
}
if($result > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($result - 10) .
"\">Previous</a><BR>\n";
if($num_books> ($result + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($result + 10) .
"\">Next</a><BR>\n";
Don't worry - we're going to go slow.
Re: your post. You're in the right neighborhood but skip all the rest of what you posted and focus on just the query. I'm not sure where found the function db_result_to_array() but I suspect it was created in the example. Let's ignore this for now and stick to the basics. You posted:
$query = "select * from books where catid='$catid'";
The query above will give ALL records that match the criteria of catid = '$catid'. We want to limit this. What do we need to change this query to in order to limit the result set to no more than 5?
$query = "select * from books where catid='$catid'" limit 5;
or
$limit=5
$query = "select * from books where catid='$catid'" $limit;
As always, thanks
Now, take a close look at the 'limit' option. What other arguments can we pass to it?
$query = "select * from books where catid='$catid'" limit 5;
we change the limit to
limit $offset,5
Where $offset is a variable that we'll control from the results page. Make sense?
I was thinking about that, but I must not understand it. I thought it would just look at those rows for specific data. FOr example, search the first 5 records for name=joe smith.
So to bring it to my level the limit and offset breaks the results into seperate "pages?"
How do you know many records there will be. I read where you can code it till the end,but how many increments of 5 do I need if I don't know how many records I will have?
I was thinking about that, but I must not understand it. I thought it would just look at those rows for specific data. FOr example, search the first 5 records for name=joe smith.
Nope. It basically says search for ALL records where the condition is true (name='joe smith') but only return 5 starting at record number X (the offset).
How do you know many records there will be. I read where you can code it till the end,but how many increments of 5 do I need if I don't know how many records I will have?
Don't worry about this just yet.
Try this. Create a stand alone page that has the same query and write the code that will return the results to the page. Then play with the LIMIT option so you can see what it does. Don't worry about passing variables to the query just yet - hard-code them in (instead of 'limit $offset,5' use 'limit 0,5')
I tried 'limit 3,2' and got the 3rd and 4th record.
What if I didn't know how many records there were?
There are a couple ways that you could find out how many records there are. Both involve reissuing the query, but without the limit. To go back a few posts:
$query = "select * from books where catid='$catid'";The query above will give ALL records that match the criteria of catid = '$catid'
So that's what you need - a query that gives you a count of ALL the records. The easy way is:
$query = "select count(*) from books where catid='$catid'";
This query will not return all records where catid=1, but it will return a *count* of all records meeting that criterion. From there it is simplicity itself to find out how many pages there are.
$page_count = $record_count/$offset;
But, you ask, what if there are 13 records and the offset is 5? Won't that give me 2.6 pages? Yes, so you need to round up.
$page_count = ceil($record_count/$offset);
There's another way I use for the case where a multi-page result set will be rare... but I'm afraid lorax will make me go sit in the corner if I talk about that without raising my hand ;-)
Tom
I'm not sure I dare to butt in on lorax's intricately designed and ultimately quite entertaining Socratic tutorial..
;) I don't mind at all.
Next piece. So now you've edited the query and are feeling pretty good. So let's put the edits back in context of the function. The function this query is a part of is 'get_books($catid)'
get_books is the function and $catid is an argument passed to the function for it to use. So if we're going to control the offset for the query then we need to add another variable/argument to the function. So we'll change it to:
get_books($cat_id, $offset)
and change the 'LIMIT' option to be 'LIMIT $offset,5. We're going to leave the number of results hard-coded for now. Once you see how easy this is you'll be able to make that selectable too.
Now, this function is called by show_cat.php. In show_cat.php you'll see a line of code that reads:
$book_array = get_books($catid);
I think you know what we need to do here. We need to pass the offset so that line should read:
$book_array = get_books($catid,$offset);
Now here's the tough part. How do we set the offset? Somewhere beneath the code that calls this function is the code that outputs the results. Locate the line that reads:
display_books($book_array);
Now locate that function - it's in the file called output_fns.php. Look over the code for that function carefully and then look over the HTML output (I assume you still have a working copy of the store to play with). I think you'll see how it works.
Put some HTML comments within the function's code and see where they show up in the source. See if you can get something to echo on screen above the <hr> that seperates the last book entry from the buttons at the bottom of the page.
SFSG?
Locate the line that reads:display_books($book_array);
I found the code, but what has to ben done?
(I assume you still have a working copy of the store to play with). - Nope there are no html pages, the html is in the output_fns.php page.
Put some HTML comments within the function's code and see where they show up in the source... OK I will try it.
Thanks again
