Hello all, I hope that some can help or point me in the right direction. Yes, I'm fairly new to PHP & MySQL stuff, so please bear with me I'm going to make this as detailed as possible). I want to set up a Quote Of The Day database on my page, I'm pretty sure I know how to how to pull the data out of the database and format it how I want (if not i'm sure i'll ask later on), but I'm working on the "Administrator's" part of the site, so that myself and one other person once authenticated (which I also have down pat) can insert new quotes. Its only one quote per day unlike some other sites that do random quotes.
Anyway, the current format that I have is not very efficient, so I devised a MySQL Table as show below:
--- begin SQL ---
CREATE TABLE qotd (
qid int(10) DEFAULT '0' NOT NULL auto_increment,
date date DEFAULT '0000-00-00' NOT NULL,
quote mediumtext DEFAULT '' NOT NULL,
whosaidit text DEFAULT '' NULL,
PRIMARY KEY (qid)
);
--- end SQL ---
I'm not quite sure that this is efficient either. Ideally, I'd like to be able to have the whole table searchable my date, month, year, day of week, author, quote, etc. My current setup has each of those variables separate (however not KEYed, or INDEXed). So one of my questions is, is that SQL listed above efficient enough, or is there a better way of doing it, I don't even know how to INDEX the 'quote' or 'whosaidit' parts, but I'm still reading along on some books, so I might be able to pick that up.
The next part is the administrators PHP frontend. Which is just find the way it's setup (as crappy as that already is). I've been trying to figure out a way to get work with the date() function, and inserting new/old data into this table. The way it's currently set up is through forms, I have <select> options that gives you a pull down list for Sunday-Saturday, January - December, 01-31, and 2000-2011. Each of these has it's own row in the current database. As you can see in the SQL code above, I only have one 'date' row. Is it possible to keep those items separate like they are, and once "Submit" is hit, combine that information together to be inserted into the 'date' row? I realize I may need a 'dayofweek' row for the Sunday thru Saturday stuff. But is that even possible to do? Or if I change over to this new way, would I have to manually enter the date each time in a text-based form (ex: Date: 2000-12-12)?
If I confused you in anyway and you want to try and help, please respond or send me an email, I'll show you the source files, or whatever you need (minus the passwords 😉
Thank you for reading.
./brm
