Originally Posted by jay64

I don't really know how to explain this easily. I guess I will layout the project I am trying to do, and the problem I am having with it, and maybe someone could explain it better to me.
I am trying to build an online food journal for a fitness/nutrition company. I have a form page made out that will allow their clients to input their eating habits for the day.
http://befit4riding.com/food_journal.html
I was thinking that I would have a db for each client, and each day would be a new table. I need to be able to store each day of data and keep it accecible for a later time. I also need to email the data to the company each time new data is submitted. Turns out, the server I am working on only allows 1 db. So how do I organize the tables so that they are all under the same client, but I can have multiple clients? Is there some kind of 'sub-table' (db/table/sub-table)? If I am not explaining myself adequately, please ask some specifics and I will be happy to try again.

notes on the page. I didn't have it automatically input the date, because sometimes people don't update everyday, so they need to be able to come in and put in multiple days at once, but have the correct date for each one. Eventually I might put in a calendar function, but I would have to learn that one and trying to just get this going first.

Thanks.

Originally Posted by jay64

Ok, after doing some reading, I think I got an idea of how to structure the table, client_logs. I can have a row for the client, with a specific date, and then all the fields from the page for that date. Then when I want to pull up a specific day of log entry, I can query the db with

SELECT * FROM client_logs WHERE client='value', date='value'

That is what I got from my research on this, but it looks like digital-ether, has a slightly different method, which sounds like it might save time in the long run. Any suggestions/critiques/ways to do it better on this approach?

Originally Posted by digital-ether

Thats pretty much the same as what I mentioned.
Do you have a separate table for clients?

So you're query would be like:

SELECT * FROM client_logs WHERE client={clientid}, date='{timestamp}' LIMIT 1

where the {clientid} is the index of the client's row in the clients table and {timestamp} is any date/time format.

Note the LIMIT 1. It's good practice to let the DB know that you only want a single entry. The reason is that the DB stops iterating through its index as soon as it reaches the first valid row. Otherwise, it goes through all rows. If you have something like 100 000 rows, this makes a huge difference.