943,844 Members | Top Members by Rank

Web Development > PHP > How to properly use tables in db?
Ad:
  • PHP Discussion Thread
  • Marked Solved
  • Views: 2339
  • PHP RSS
You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page
Permalink
Dec 5th, 2007
0

Re: How to properly use tables in db?

For clients I recommend to start with id, fname (first name), lname, username, email

For journal, id, id_client, title, body, date

You could use text type for any field except id (I suggest int, 11 chars length for this)
Reputation Points: 52
Solved Threads: 23
Posting Whiz in Training
martin5211 is offline Offline
271 posts
since Aug 2007
Permalink
Dec 5th, 2007
0

Re: How to properly use tables in db?

Thanks. I have found this code below for creating a table. I edited it to incorporate what was mentioned here previously. At the end, it has PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id), what exactly is this for? There was an short explanation, but it didn't make any sense to me. Also, there is no space after the ',' in the code. Is that correct? I think I would have a habit to put in a space if I wasn't just copying and pasting. Would that cause a problem?
$query="CREATE TABLE clients (id int(11) NOT NULL auto_increment,fname varchar(15) NOT NULL,lname varchar(15) NOT NULL,username varchar(20),email varchar(20),PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
Reputation Points: 10
Solved Threads: 0
Light Poster
jay64 is offline Offline
40 posts
since Nov 2007
Permalink
Dec 5th, 2007
0

Re: How to properly use tables in db?

UNIQUE and KEY id_2 isn't necessary, let alone primary key (id), indexes are meant to use a basic field for lookups, you could use more fields like fname and lname to perform special full-text lookups using MATCH().

And yes, you could avoid spaces or make carriage returns between commas to make the code more readable.
Reputation Points: 52
Solved Threads: 23
Posting Whiz in Training
martin5211 is offline Offline
271 posts
since Aug 2007
Permalink
Dec 6th, 2007
0

Re: How to properly use tables in db?

Would this be the correct way to make the second table?

$query="CREATE TABLE food_journal (id int(11) NOT NULL auto_increment, id_client(11) NOT NULL, timestamp varchar(8) NOT NULL,water varchar(8) NOT NULL, breakfast varchar(8) NOT NULL, breakfast_calories int(8) NOT NULL, breakfast_time varchar(8) NOT NULL, snack_1 varchar(8) NOT NULL, snack_1_calories varchar(8) NOT NULL, snack_1_time varchar(8) NOT NULL, lunch varchar(8) NOT NULL, lunch_calories varchar(8) NOT NULL, lunch _time varchar(8) NOT NULL, snack_2 varchar(8) NOT NULL, snack_2_calories varchar(8) NOT NULL, snack_2_time varchar(8) NOT NULL, dinner varchar(8) NOT NULL, dinner_calories varchar(8) NOT NULL, dinner_time varchar(8) NOT NULL, snack_3 varchar(8) NOT NULL, snack_3_calories varchar(8) NOT NULL, snack_3_time varchar(8) NOT NULL";
mysql_query($query);
Reputation Points: 10
Solved Threads: 0
Light Poster
jay64 is offline Offline
40 posts
since Nov 2007
Permalink
Dec 6th, 2007
0

Re: How to properly use tables in db?

There is some little mistakes, like missing type on id_client field, a UNIX timestamp normally takes ten positions and lunch_time have one space inside (table names can't have any space). I recommend make the first field (id) as primary key usually.
The resultant SQL would be:

PHP Syntax (Toggle Plain Text)
  1. CREATE TABLE food_journal(
  2. id INT( 11 ) NOT NULL AUTO_INCREMENT ,
  3. id_client INT( 11 ) NOT NULL ,
  4. TIMESTAMP INT( 11 ) NOT NULL ,
  5. water VARCHAR( 8 ) NOT NULL ,
  6. breakfast VARCHAR( 8 ) NOT NULL ,
  7. breakfast_calories INT( 8 ) NOT NULL ,
  8. breakfast_time VARCHAR( 8 ) NOT NULL ,
  9. snack_1 VARCHAR( 8 ) NOT NULL ,
  10. snack_1_calories VARCHAR( 8 ) NOT NULL ,
  11. snack_1_time VARCHAR( 8 ) NOT NULL ,
  12. lunch VARCHAR( 8 ) NOT NULL ,
  13. lunch_calories VARCHAR( 8 ) NOT NULL ,
  14. lunch_time VARCHAR( 8 ) NOT NULL ,
  15. snack_2 VARCHAR( 8 ) NOT NULL ,
  16. snack_2_calories VARCHAR( 8 ) NOT NULL ,
  17. snack_2_time VARCHAR( 8 ) NOT NULL ,
  18. dinner VARCHAR( 8 ) NOT NULL ,
  19. dinner_calories VARCHAR( 8 ) NOT NULL ,
  20. dinner_time VARCHAR( 8 ) NOT NULL ,
  21. snack_3 VARCHAR( 8 ) NOT NULL ,
  22. snack_3_calories VARCHAR( 8 ) NOT NULL ,
  23. snack_3_time VARCHAR( 8 ) NOT NULL ,
  24. PRIMARY KEY ( id )
  25. )
Reputation Points: 52
Solved Threads: 23
Posting Whiz in Training
martin5211 is offline Offline
271 posts
since Aug 2007
Permalink
Dec 6th, 2007
0

Re: How to properly use tables in db?

Click to Expand / Collapse  Quote originally posted by martin5211 ...
I recommend make the first field (id) as primary key usually.
I thought you stated that the primary key (id) wasn't necessary??

Click to Expand / Collapse  Quote originally posted by martin5211 ...
UNIQUE and KEY id_2 isn't necessary, let alone primary key (id), indexes are meant to use a basic field for lookups, you could use more fields like fname and lname to perform special full-text lookups using MATCH().
Reputation Points: 10
Solved Threads: 0
Light Poster
jay64 is offline Offline
40 posts
since Nov 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: js problem
Next Thread in PHP Forum Timeline: Sending a form via sendmail





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC