A couple of PHP questions - limiting results, huge 'INSERT'

[caedas]

Heya everyone! Love the site!

 

I am writing what will be my first real PHP application (I have done PHP 'reverse engineering' and a few small programs) and I am running into a few issues.

 

First, in my program, users will input several fields of data which will, in turn, be displayed. However, over time, these entries will become numerous.

 

First Question: How can I make it so that results are displayed on more than one page?

 

- For example...when you do a search for like..."PHP" on google and you are returned with several thousand results, and (depending on the option you set) google displays multiple clickable page numbers...or a forum that has thousands of posts (::wink wink:: ).

 

Secondly, as I said, the user will be entering several fields of data. The data will be need to get information from a few different tables on the database.

 

I'll go into detail here:

 

A user enters the following (most is obtained from a drop down menu) - a category, a name, several (~5 but it's not constant) items and who is to get those items...just to name a few (there are 8 total entries). The category is displayed from a category table and is stored as that categories 'cat_id' (correspondingly) in the 'entry' table, the name is displayed from another table and stores as an id (like the category), etc. In total, I think there are 6 values that are done this way.

 

Now, my final question, what is the most efficient way to do this? The way I had was using a massive query using about 6 LEFT JOINs...but I got to thinking, I have to get the values from other tables to display them, so there has to be a better way.

 

ANY and ALL help is GREATLY appreciated!

 

 

[phpknight]

1--Use a pagination class.  Your query on each page will just have LIMIT in it.  Pretty simple actually.

 

2--I'm not sure I understand your issue, but I would just break up this up into separate queries or funtions.  It could be that your database is not designed right unless it is super complex because I never use queries like that.

[btherl]

6 left joins is not so bad as long as the left side of each join has a small number of results, AND the right side of the join uses an index.  Say you have 3 results on the left, and a matching index on the right.  Then that is only 3 index scans into the right-hand table to execute the join.

 

If those conditions aren't met though, then you may be in for a long query

 

If you're finding that performance is not acceptable, then there are many generic approaches, including APC (alternative php cache) and memcached (memory cache daemon), as well as other forms of caching.

 

Then there is specific optimization, which will depend on your queries and data.

 

I tend to use caching when results get into the thousands, as it takes too long to generate them for each request.  Multiple levels of caching can be very effective when you find the right level at which to cache.  Such as caching a topic's data, caching an entire forum's data, caching a post's data.  Each cache is a different level, and some will be more effective than others.

[caedas]

Thanks for the help! I know about using LIMIT functions in MySQL...but would I just use like...a foreach loop depending on a variable (set by the user) on how many results to display? Or...since the app is in it's primary stages, should I just set it to a unchangeable default for now?

 

Also, where can I find more information on using pagination?

[btherl]

If you set the limit clause based on a variable set by the user, then that's enough.  The foreach can display all results returned from mysql.

 

And yes, I would make it an unchangeable default first.  Doing things 1 at a time makes life easier.  I wouldn't hardcode it everywhere though, I would set it as a variable at the start and use that variable throughout the code.

 

Pagination is always messy .. the approach I've used is to break it down into seperate tasks

 

1. Generate "First" link

2. Generate "Prev" link

3. Generate links before current page

4. Generate links after current page

5. Generate "Next" link

6. Generate "Last" link

 

All of those tasks are simple in themselves.  And if you do them all, then you have all the links you need for full featured pagination.  I would also include a "jump to page" feature.