PHP - MYSQL - config question?

Question

my site is linked to a mysql database. For simplification, my main objective is to track journal entries and what date they were recorded on.

Desired Output:
Display a calendar in a table that has corresponding a hrefs if that particular date has an entry. Left Arrow and Right arrow at the bottom so the user can scroll through the months.

Database Setup:
1st Field(Store Dates) - INT(field type) - ex. 060830201255 - yymmdd-hhmmss - set as primary key
----August 30, 2006 8:12:55 PM
2nd Field(Store Journal Entries) -Text(field type) - ex - This is my entry for today, hello world.

a) Do you suggest that i add a field (listID) INT(field type) and make it auto increment....... and make this the primary key instead?
b)For storing the dates, should i make it a string(as above) or just use php datestamp or timestamp.

All this boils down to SQL FETCH staements and whats easier/faster to pickup the date within a specific range.

Also, any ideas on the calendar design.

TY

Answer 1

I would suggest a table layout like this:
journal_id (INT) auto_increment
time (DATETIME)
entry (TEXT)

The names are just suggestions.

This will allow you to have multiple journal entries on a single day if you want.

The calendar would be setup so you could select a year and month. The DATETIME helps here because it is setup as YYYY-MM-DD HH:MM:SS, so you can use a query like this: "SELECT * FROM table WHERE time LIKE 'YYYY-MM%'", the % being a wildcard. That way you have less load on the database, and can then just strip the date out using substr (substr($date,8,2) to get the date).

Then with your links, have them created so the link uses the journal_id, this makes it easier to retrieve the information.

It may sound complex, but once you start working on it, it will probably become much easier.

Answer 2

yes you should make an id column autoincrement.
If you ever want to add another table and build a relationship to this one you will be thankful you did. Also the primary key should be an integer whenever possible.
There are several things wrong with your concept.
Please just follow common sense.
Set the datestamp to a date type within SQL.
The value you pass to the database from PHP will be a string.
no matter what - if you save it accurately in the database you will be able to do range searches, and calculations on the database side. The way your doing it now will demand you pull the data and parse it then run overly complicated calculations on it, splits - huge headache with absolutely no upside at all.
I really can't imagine what train of logic leads a person to save a date as a string, and consider making that the primary key.
It's loco.

Also, I'm almost sad telling people about this little jewel since it's so sweet. But here is the answer to your calendar problems:
http://developer.yahoo.com/yui/calendar/

Answer 3

A) Yes... depending on expected use its entirely possible that two entrys will be entered at the same second... and then any updates on that "item" will update both entrys or of course someone will get an error about a primary key voilation :), so use a completely seperate ID field as the primary key and use that for all updates

B) Index that field and leave as numbers i say, numbers are faster to search on and its nice to index any field you search on for some speed (just slower insert/update/delete's but they are generally less often performed overall)

Answer 4

The best table schema is

listID - int - primary key - auto increment
store_date datetime
Store_Journal_Entries text ...

when u design a table u must take one auto increment primary key .....it u gives you lots of advantages when u edit or delete data ...or establish a forign key to other table ... take store_date as date time because it format is nice and easy to change (YYYY-MM-DD HH:ii:ss )

for faster query exec use limit qry ....say select * from limit 0, 6 .... which will fetch first 6 records .. use MYSQL_ASSOC OR MYSQL_NUM when u fetch record ... it will give be faster execution.... u can use also mysql_unbuffered_query function for faster execution if table has huge data ...but it locks tables it is better if ur table has huge data (say 1000000 ) ... use indexing in table ...which will give faster information if u search any data frm table ...

this table schema is also good for date search....


for java script nice pop up calendar u can visit http://www.softcomplex.com/products/tigra_calendar/ web site ...and u can download and add to ur application ...

good luck.....

Answer 5

you've 2 mistakes. a million. previously mysql_query, you ought to attach with the db. something like: $link = mysql_connect("localhost", "username", "password") or die ("blunders: ".mysql_error()); mysql_select_db("dbasename") or die("might want to no longer choose database: ".mysql_error()); 2. $r is RESSOURCE given by your initial question: you won't be able to substitute it! (it really is a pointer to an array lower back by the question). => even as($row = mysql_fetch_array($r)) => do not substitute $r! ==> yet you do it contained in the loop: => $r++ => eliminate! No, no longer something truly works: Your question returns an blunders (a textual content), then you fetch and array (the blunders!)...