Newbie looking for strange sorting help

djiles

I am new to php and mysql and I have hit a wall. I am writting a php that adds numbers to a database and then displays those numbers sorted on the same page. This in its most simple form works great. but what I want to do now is have it split the database results in to multiple table. Example

The table will hold eight entries. If you add nine entries it will then split the results into two tables with five in the first table and four in the second yet still sorting them from lowest to highest. New entries then go to the proper table keeping a balance until they fill up at which time it splits again into three tables with six, six, and five entries.

Basically I just need a way to count the returned results having the first half or third display in a table and then the second in the second table etc.

I hope I have explained myself well enough and i have not added any code because I have no clue were to even start with this process.

Thanks

amchargue

Why do you want to do that? What ever the reason is, I'm guessing there's a better way.

UNION query might work, which wasn't implemented in mysql until fairly recently (4.1 or something like that)

Piranha

Like amchargue I can't understand why you want to do that. The only reason to split in different tables is if you have lots and lots of rows (millions at least), and even then it is best done to split by date, then you don't have to work through all the data to split it.

Can you please tell why you want it split this way, I'm curious and think there is a better solution for it.

djiles

Well I guess I made it seem more complex then it really is. I am writing a script that receives qualifying times for a race and then sorts it from fastest to slowest. This part is done and works fine. The slots in this race are limited to 8 drivers. So when you have more than 8 you have to split the drivers into 2 groups. So then again if you have over 16 it goes to 3 groups. I should never need more than 4 tables of 8 entries but once I get the first split worked out I should be able to build the rest. I hope this makes more sense.

Piranha

But you don't need new tables for that. You can use a table like this:

CREATE TABLE race(
    driverid int
    qualifyingtime // some datatype
    raceno int
)

Then you can use a PHP script to sort what race what driver will be in, and set the raceno from that. When retreiving who should race in a specific race you only add a WHERE-string like this: "WHERE raceno = $raceno";

Creating new tables all the time is never good, it will result in a database that is somewhere between hard and impossible to handle. As an example phpbb forums have 29 tables instead of one table per thread. Learn to use databases this way and you will spare yourself lots and lots of trouble in the future.

I hope that you understand what I'm trying to say. If not, don't be afraid to ask since this is basic and you need to understand it to do good database designs.

djiles

I think I miss stated what I wanted to do. I want the results from a datadbase query to divide its self over multiple HTML tables. I should have said this at first but being new I didn't think about multiple meanings for tables in php.

Thanks for everyones help so far.

Piranha

Then I understand it much better. You can count the number of rows with COUNT in SQL:

SELECT COUNT(id) AS c FROM...

Or you can retrieve all the rows and then use the PHP command mysql_num_rows($result). I think that the easiest way is to do the math here and then get the correct rows. If you do it this way you should use the COUNT function in SQL.

When choosing what to display you can use the LIMIT function in SQL:

SELECT id FROM table LIMIT 0, 6 will return the first 6 rows
SELECT id FROM table LIMIT 6, 6 will return 6 rows starting from the seventh row.

And you should use ORDER BY to get the result in the correct order. You can order either ASCending or DESCending.

SELECT id FROM table ORDER BY date ASC LIMIT 0, 6

djiles

Thanks that really points me in the right direction. Like I said I am new to this so it will probablly still take a while to get it right but I truly had no idea how to start this.

spprime

why not have a counter on the loop when outputing the data, that way you can have all the data in one query and just spit out the table based on the count. You can also do a count on the array to find the total as well. Just my coupe of cents...

Piranha

spprime wrote:
why not have a counter on the loop when outputing the data, that way you can have all the data in one query and just spit out the table based on the count. You can also do a count on the array to find the total as well. Just my coupe of cents...

It is possible. But it is normally a bad idea because when you program you never know how much the query will return. It could be that a query will return 100.000 rows in a year, but while testing it only returns a few. It will mean that it take lots of time and memory to retrieve all the rows, but with first a count and then pagination (or, to put it in another way, using LIMIT) you won't have that problem.