Advice on coding PHP / MySql listing program

[dgp0961]

I have been a developer (C/C++) for many years but I am new to Web development. I am developing a web site that will provide business listings for different categories for the pet care industry.

I have created and populated a MySQL database that contains all of the businesses in my directory. I have also created a home page that will allow the user to specify their search criteria (type of service that are looking for, City & State or Zip). Once they specify their search criteria, I query the database and read all the rows and store them in an array in a PHP class object. That way, I am only accessing the database once.

I then display the first 10 rows on a search page (via PHP) and provide a link (Next) for the remaining rows (up to 10 at a time) if the results return more than 10 rows. This is working fine because the search page created the PHP class object and has access to the array.

Now comes my question, what is the best way to implement the Next page functionality. I was thinking when the user selects the Next hyperlink, that I would load another page (via PHP) that contained the next 10 rows from the seacrh string and so on.

Is this the best way to do this or is there a way that I can stay on the current search page and just redisplay the next 10 items. That way, I still have access to my PHP class object.

Or maybe there is a better way to architect the web site.

Thanks.

[etully]

Once the PHP page is done displaying the first HTML page, it stops running and the object is gone. You can use Ajax to prevent another page reload but you'll still need to start a new PHP page that performs the query again.

The query you described is usually lightweight enough that it can be executed a dozen times a second with no serious impact on server performance. If your query was taking more than 2 seconds to perform, that could be a serious problem if you had a hundred visitors in a 20 second window and you might want to consider caching the result of the query in a text file or something.

You should benchmark your web site to see what it can actually handle. You can run a program called "ab" (apache benchmark) that will hit your site as fast as it can and tell you how many times your web page can be viewed in a certain amount of time.

If it turns out that your site can handle 1000 times the traffic you expect to get, then sure, optimization is still important, but maybe it's not the biggest concern on your plate.

I suspect that in a C/C++ environment, you are used to scripts running for as long as the user keeps them open? In web development, 98% of my scripts are designed to run for 1/10th of a second and then disappear with no expectation that the object will persist.

Disclaimer: That's just been my experience. Programmers for Amazon, Ebay, Yahoo and Google probably have clever ways to optimize a site that I don't know about.

[dgp0961]

Thanks for your insight it is very helpful.

One other question related to the site. Some others have suggested storing the class object in a session using the start_session() function on the first search page and then creating a second search page (when the user hits Next) and retrieving the class object from the $_SESSION array. Does this make sense? I have not been able to get this to work.

Just curious if this is good web design or not and if this really works with PHP objects.

[NogDog]

I'm not sure how much time (if any) storing the object in the $_SESSION array would save, as session data gets saved to disk between scripts (not in RAM). Thus if your DB is on the same server and your table design and queries are relatively efficient, there may not be a big difference. But certainly it's worth comparing if you think the time will be important.

The thing to remember is that you must load the class definition prior to the session_start() on the succeeding pages that will retrieve that object, so the general flow would be something like:

first file:

<?php
require_once "MyClass.class.php";
session_start();
$example = new MyClass();
$_SESSION = $example;
?>

Second file:

<?php
require_once "MyClass.class.php";
session_start();
$example = $_SESSION['example'];
// you can now work with object $example again
?>

[bpat1434]

Another option is to not store it at all and just properly use the LIMIT clause of SQL.

Since you're using a database, you may as well use it to its full potential. You can paginate it by doing something like:

• 25 records per page
• Query = "SELECT * FROM table WHERE ( . . . ) LIMIT 0, 25";
• User selects next page
• LIMIT clause is updated to be:
  • new_start = (page-1)25: 1=0, 2=25, 3=50, 4=75
    "SELECT FROM table WHERE (...) LIMIT new_start, 25";

Now, you're running small queries on your DB, and there's no need to worry about an object, or array to be carried over on the user's side.

I just thought I'd throw that out there as another valid option to use.

[etully]

nogdog is right, writing to disk is slow and you might not gain anything - doing the select again might be just as fast.

bpat1434 is right, LIMIT is good - it will help.

But you need to remember that some queries are huge - they can take 30 seconds even - maybe just to get a single word or a measly 20 bytes of information. In that particular case, then writing to session is going to be a huge savings over performing a 30 second select again (not to mention the savings on CPU and RAM). So really, there are no generalizations you can make - you have to optimize for the particular situation - and when they seem close, you have to submit the script to heavy benchmarking.

[dgp0961]

I like this approach - let SQL do the work for you. In order to make this work I will need to pass some variables from one page to the next to help control where I am related to the search records (1 - 25, 25 - 50, and so on....)

My question is: I know I can pass the parameters via the hyperlink with the URL but would like to not expose these via the hyperlink (if possible). Is there a way in PHP to pass these parameters from page to another using the $_POST array without using a form? Also, I would like to stay way from using the session functions.

[bpat1434]

Well, you could set a cookie; but that's kind of overkill for this type of thing. The easiest thing to do would be to send it through the URL utilizing the $_GET array.

To get $_POST populated, you either have to send a form, or modify it in the PHP code itself.

Here's what I suggest: use page numbers.

You could do something like this:

<?php

$page = $_GET['p']; // www.domain.com/mypage.php?p=1
$perPage = 25; // 25 records / items per page

// Now, decrement the page by 1 and then multiply by your perpage number
/* page 1: 1-1 = 0 * 25 = 0
 * page 3: 2-1 = 1 * 25 = 25
 * page 3: 3-1 = 2 * 25 = 50
 * etc.
 */
$start = --$page*$perPage;

$sql = "
SELECT *
FROM `tablename`
WHERE ( . . . )
LIMIT $start, $perPage";

?>

So you see how you can have page numbers, and not row numbers. You're only sending one item through the URL (which isn't that bad).

The other way is to use sessions, or AJAX so that the user never leaves the page. AJAX is more in-depth than you'd need probably, and you said you don't want sessions.

[Roger_Ramjet]

If you search these forums for pagination you will find every possible approach discussed in exaustive detail.

Why don't you want to use sessions? They are the backbone of efficient php programming.

You can pass the query string, or any part of it in an html <input type="hidden" element.