backend database layout

[Testin da Cable]

ewo guys and dolls now that Ive gotten some extra time inasmuch as I'm on holiday I was thinking of /finally/ getting back to work on the total re-write of my site, front- and backend.

just a quick question re layout of tables and column etc, as I have never actually done something like this. I have a general (vague) idea of how to set the database up, a la a table that I auth against and a table that holds just about everything else.

While smacking a basic news+archive+posting system together I've run into some snags dreaming up querys to pull information I want out of the table. SQL really isn't a strong suit for meh heh, and due to those snags I've come to the conclusion that I've set up the content table incorrectly. I was wondering if there are any examples to be had from the people around here?

 

[Panda On Smack]

y0 TDC

Can you give me an example of what you have and what you are trying to retrieve?

Do you have to query 2 tables to get something etc?

 

[Testin da Cable]

well atm I have everything lumped into one table, but that seems to have made things tough. I have a table called "users" that contains a list of usernames and encrypted passwords, and a table called "news" that contains something a little like so:

+----+----------------+------+----------------+
| id | data           | user | date           |
+----+----------------+------+----------------+
|  1 | news goes here | tdc  | 20030513215846 |
|  2 | more news here | tdc  | 20030519194616 |
+----+----------------+------+----------------+

what I want is to be able to split entries into months / years in an "archive" table, and I want to have a column that I can flag with a marker that I can grab to always have the latest (or current or whatever) news displayed on the news page. something like so:

SELECT data FROM news WHERE current=1;

then if there's a new post I could UPDATE news.current to NULL or whatever and set it to 1 for the new post.

does any of that make sense? I'm kinda coasting along now heh.

 

[nylex23]

My table structure for 'news' table:

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     |      | PRI | NULL    | auto_increment |
| Author   | varchar(20) | YES  |     | NULL    |                |
| Title    | varchar(64) | YES  |     | NULL    |                |
| PostDate | datetime    | YES  |     | NULL    |                |
| Post     | blob        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

So:

+----+--------+------------+---------------------+-----------------------------------------+
| ID | Author | Title      | PostDate            | Post                                    |
+----+--------+------------+---------------------+-----------------------------------------+
|  1 | Nylex  | First post | 2003-04-24 11:52:30 | News script set up on my LAMP server :) |
+----+--------+------------+---------------------+-----------------------------------------+

Also have tables set up for comments and users (don't have an archive system though, but I don't imagine it would be too difficult to do).

 

[Panda On Smack]

No that all makes sense

You could query the database for the newest record via the date

SELECT TOP 1 *
FROM table
ORDER BY Date DESC

should get you the newest News item

Save spliting the databse you could again just use the date to get items between a certain period

SELECT *
FROM table
WHERE (Date >= '05/01/2003') AND (Date <= '06/01/2003')
ORDER BY Date DESC

The date field may need to be changed due to the Date Format of your SQL server. The above example is getting all records between the 5th and 6th months, not days.

 

[Panda On Smack]

I'm not Mr SQL though so there maybe someone who has a better way

 

[Testin da Cable]

cool beans, thanks for the help I think I can work with that I'm teaching myself SQL and PHP as I go along though, so there will be a myriad more questions before I'm done I guess.

I never was the 'content' person you see, just the serversetterupper. I've discovered that it's fun to play with things inside servers too, and it's not turning out to be extraordinarily hard so that seems to be a plus.

I'll report back with some results when I get it done*. don't touch that dial!



* after dishes, dinner, sports and sexor. and sleep. and cleaning the house. and sexor. um. damn :/