[PHP-DB] Best table structure
Please can someone let me know their opinion on the following:
I have written some code for submitting a top 20 music chart online.
I use the following to insert into mysql:
INSERT INTO chart (name, chartpos, artist, title, label) VALUES
('$name', '1', '$artist', '$title', '$label'),
('$name', '2', '$artist2', '$title2', '$label2'),
('$name', '3', '$artist3', '$title3', '$label3'),
-- repeat til -
('$name', '20', '$artist20', '$title20', '$label20'),
Another page queries the table and sorts by name (multiple people submit
charts) and latest date.
My question is this:
Would I be better keeping this format and inserting multiple rows on each
submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x
Artists, 20 x Titles, 20 x Labels.)
I know that the latter will be easier to query.
Also, without maintenance, the size of the table for the current method will
get extremely large. Will that affect server performance?
I am still a newbie, so plain explanations would be most appreciated.
Thanks very much in advance.
Tom
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Date Select
How can I query a MySQL table to get the latest results from a date field? Basically, I am inserting several records at a time at the end of each week. I want to have a page that displays the results for the last week only. The date format in the field is -MM-DD
RE: [PHP-DB] Date Select
Jeffrey, Thanks for this, I've just realised that it's always the same no of inserts, so perhaps I should just order by date then my previous sort field and then limit the result to 20? (It's all 20 results that I need displayed) Thanks, Tom -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 25 June 2004 12:15 To: Tom Chubb Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Date Select How can I query a MySQL table to get the latest results from a date field? Basically, I am inserting several records at a time at the end of each week. I want to have a page that displays the results for the last week only. The date format in the field is -MM-DD if you want the latest row - select * from table order by max(date_column) limit 1; if you want rows in a range of dates there are lots o ways, and instead of showing one or two, I'd suggest taking a look at the manual on dates http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html, and seeing what fits your needs best. Seems like you may want to look at BETWEEN And if you don't want to use those, you are always free to use select * from table where date_column '-mm-dd' and date_column '-mm-dd' order by date_col desc HTH Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Posting Data to MySQL
I am trying to design a form that posts the data to a DB, but being new to PHP/MySQL, it takes me ages to hand write the code and I'm sure there must be an easier way to do it? My form has 100 fields using 20 rows and 5 columns and it's taking me ages to write the code for it. Can anyone advise how they code large forms like that and if you know of any software to make it easier? I've searched Google for hours and checked loads of Database scripts on sites like Hotscripts, but can't find anything. Thanks, Tom
