Page 2 - Conceptual Design for PHP/MySql?

Hi

So, something along the following lines is what you would recommended?

-------

ARTICLES(articleID, relAuthorID, relTopicID, articleTitle, articleDate articleContent)

AUTHORS(authorID, authorFname, authorSname,
authorEmail, authorDate)

TOPICS(topicID, topicTitle)

(NOTICE: no repeated reltopicID in the AUTHORS table)

-------

Could you possibly give me some examples of SQL queries that would, for example, do the following:

1. I can click on an articleTitle to give me the articleTitle and authorName, articleTopic and articleContent (OF COURSE!!)

2. I can click on an authorName to give me the author details and ALL articles by THIS author

3. I can click on an articleTopic to give me ALL articles in THIS topic

-------

Thanks for your help.. much appreciated.

Cheers.

Mak.

1. select articleTitle, authorFname || authorSname as 'authorName', articleTopic, articleContent from articles a, authors b where a.relauthorid = b.authorid and articleTitle = '[whatever]'

* some DB systems require that the 'join parameter' is in the returned values. eg authorId in the select part.

2. More complicated and I'm not sure exactly if it would work but, possibly:

select articleTitle from articles a, authors b where a.relauthorid = b.authorid and b.authorFname || b.authorSname = '[whatever]'

* I would probably separate the names into first and last to be on the safe side.

3. select articleTitle, topicTitle from articles a, topics t where a.relTopicId = t.topicId and topicTitle = '[whatever]'

Not totally sure about them, especially the || for joining parameters. Best go look at:

http://www8.silversand.net/techdoc/teachsql/index.htm

It's a whole book online!

Hi,

Thanks for that... been very helpful.

One thing, however, that I am still curious about is the actual *type* of the data. I am especially curious for the articleContent. In MySql, for example, what type is sufficiently large to hold an article?

Any suggestions on the other types?

Thanks a lot.

Mak.

Use the text or blob data types for storing the article contents. There are tiny, medium and long text/blob to choose from. Check this link for more info: http://www.mysql.com/doc/C/o/Column_types.html

//NoXcuz

Oh, I forgot this... -> http://www.mysql.com/doc/B/L/BLOB.html

//NoXcuz

Hi

Thanks for that.

-------
ARTICLES(articleID, relAuthorID, relTopicID, articleTitle, articleDate articleContent)

AUTHORS(authorID, authorFname, authorSname,
authorEmail, authorDate)

TOPICS(topicID, topicTitle)
-------

For the table design above, the following is what I have come up with as 'create table' commands for MySQL.

Could you possibly help me out with the places with '???'.. ( I am unsure of what type the the relations should be) and other fields that would be better as different types?

Thanks

Mak,

-------


create table Articles (
articleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
relAuthorID ???,
relTopicID ???,
articleTitle TEXT,
articleDate DATE NOT NULL,
articleContent LONGTEXT or LONGBLOB ???
);

create table Authors (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
authorFname VARCHAR(50),
authorSname VARCHAR(50),
authorEmail VARCHAR(50),
authorDate DATE NOT NULL
);

create table Topics (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
topicTitle TEXT
);

I would use something like this:

create table Articles (
articleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
relAuthorID INT NOT NULL,
relTopicID INT NOT NULL,
articleTitle VARCHAR(200) NOT NULL,
articleDate DATE NOT NULL,
articleContent LONGTEXT
);

create table Authors (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
authorFname VARCHAR(50),
authorSname VARCHAR(50),
authorEmail VARCHAR(50),
authorDate DATE NOT NULL
);

create table Topics (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
topicTitle VARCHAR(200)
);

I don't think you need to have articletitle and topictitle as TEXT types, a varchar field with size 200-255 should do, or?

//NoXcuz

Hi

Thank you for that.. it cleared up a lot of things for me!

What I was also wondering is that if, for example, I want to enforce that the Author must enter an authorFname and authorSname (as an example).. do I just insert a "NOT NULL" after the field and type declaration; such as the following (where I have put * next to fields.. basically ALL that I want data entered into) - what do you think?

-------

create table Articles (
articleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
relAuthorID INT NOT NULL,
relTopicID INT NOT NULL,
articleTitle VARCHAR(200) NOT NULL,
articleDate DATE NOT NULL,
articleContent LONGTEXT NOT NULL ***
);

create table Authors (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
authorFname VARCHAR(50) NOT NULL, ***
authorSname VARCHAR(50) NOT NULL, ***
authorEmail VARCHAR(50) NOT NULL, ***
authorDate DATE NOT NULL
);

create table Topics (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
topicTitle VARCHAR(200) NOT NULL ***
);

-------

Another thing I was wondering is why use VARCHAR(200) as opposed to CHAR(200)? What advantages does VARCHAR offer?

Thank you, once again.

Mak.

The problem is that unless you're only using MySQL (or any other db for that matter) to insert data, your scripting language (PHP, PERL, ASP etc...) will mess things up for you if you don't watch out.

Let's take your topics table as an example...
create table Topics (
authorID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
topicTitle VARCHAR(200) NOT NULL ***
);

If you try to insert this directly from MySQL, you *should* get an error:
mysql>INSERT INTO Topics (authorID) VALUES ('');

You've declared topicTitle as a NOT NULL field, and you haven't provided a DEFAULT for the field, which means that the query would fail coz topicTitle requires a value, and you didn't give it any...

If you add topicTitle VARCHAR(200) NOT NULL DEFAULT '', the above query would work, coz since you didn't provide any value, the default value would be inserted (an empty string).

So far, we've only been using the SQL part, but if we add your scripting language, things will be different...

Let's say your topicTitle has this field, topicTitle VARCHAR(200) NOT NULL, and your using a form to enter values into the table.
The form would contain one input field for topicTitle, and if you don't enter any value for this field, hit submit and insert this into the table, you would still be inserting something. The value of an empty string ('')...

It wouldn't matter if you declared this field as NOT NULL and with a DEFAULT 'whatever'. You would still be inserting the string '' into the table if you don't supply a value in the form. The only way to prevent this would be to add some value checking in your scripting language. If you use PHP, functions like empty(), isset() or some regular expression would help you out here.

So before you try to insert the values from the form into the table, check what's been entered by using say, empty(). And if the field is empty, instead of inserting it, abort the insert and show a message like 'You have to enter a topice title, stupid!'...

And there's a difference between a NULL value and an empty string (''), since an empty string still is a value...

To sum up the NOT NULL part: It's good practice to declare fields as NOT NULL, although it won't provide a bullet proof protection. You would have to check for things in your scripting language instead...


As for VARCHAR vs CHAR, the difference is that a VARCHAR(200) is a VARiable CHARacter field, with a maximum value containing 200 characters. However, if the string you're entering only contains 50 characters, the field in the table would take up 50 characters (plus some extra bytes). If you use a CHARacter field, even if entering only 50 characters, the field would still be using 200 characters of storing space in the table (plus some extra bytes).
So a VARCHAR should be used when you have variable lengths of the strings you're going to input, and CHARs should be used when you expect the string to have a somewhat similar length.

Hope I cleared some things up for you, and I think there's more to read in the MySQL manual if you need it...

//NoXcuz

hi

I am trying to insert an author into the authors table with the following command:

INSERT INTO authors SET
authorFname = Anjum,
authorSname = Naweed,
authorEmail = anaweed-yahoo.com,
authorDate = "2002-02-06"
;

However, it says:

ERROR 1054: Uknown column 'Anjum' in 'field list'

For some reason it is not see the authorID as AUTO_INCREMENT which is what it should be.

Any ideas?

Thanks.

Mak.

mysql> describe authors;
+-------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+------------+----------------+
| authorID | int(11) | | PRI | NULL | auto_increment |
| authorFname | varchar(50) | YES | | NULL | |
| authorSname | varchar(50) | YES | | NULL | |
| authorEmail | varchar(50) | YES | | NULL | |
| authorDate | date | | | 0000-00-00 | |
+-------------+-------------+------+-----+------------+----------------+
5 rows in set (0.00 sec)[SIZE=1]

Hmmm....
All strings need quotes around them in order for MySQL to be able to handle them. The same goes for dates/times, unless you skip the delimiters. So try this:
INSERT INTO authors SET
authorFname = 'Anjum',
authorSname = 'Naweed',
authorEmail = 'anaweed-yahoo.com',
authorDate = '2002-02-06';

BTW, I know that syntax works, but it feels kinda 'wrong' to me... I'm more used to INSERT INTO <table> (<field>, <field>, <field>) VALUES (<value>,<value>,<value>);. Just a matter of preferences, I guess...

//NoXcuz

Hi

Thanks for that.. it worked a treat.

It just occurred to me, however, that there are two ways I want to insert articles into the articles table:

create table Articles (
articleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
relAuthorID INT NOT NULL,
relTopicID INT NOT NULL,
articleTitle VARCHAR(200) NOT NULL,
articleDate DATE NOT NULL,
articleContent LONGTEXT
);

Firstly, just through MySQL and secondly from a web form into the database.

Say for example the article is in text format... how do I "put" it into the articleContent field?

Basically, what is the best way to have an article consisiting of text and/or pictures to be lifted from the database and to be generated to HTML.

Any ideas please?

Thanks.

Mak.

Getting very complicated now, well sort of anyway.

ARTICLE TEXT
-----------------

Simply do a usual insert to get it into the table. Your PHP (if you're using it) should simply take the form post of the article through:

$articleText = urldecode($HTTP_POST_VARS["articleText"]);

and doing the insert.



IMAGES
----------
What I would do in this case (or actually do do) is have a unique ID for each image stored in the database. The images are all stored in a single folder on the server each named with their unique ID. If there's only one image per article then it's a simple case of adding an ARTICLE_IMG_ID field and populating it.

To serve it you simply have to do a select query and get the unique id then do <img src="./images/<?php echo $uniqueID; ?>.gif"> or something very similar.

Hi

It just occurred to me.. regarding my tables (detailed previously) - do I need to (somehow in MySQL) indicate that there is a relationship between the tables. I mean, normally in Access or SQL Server you can drag and drop from table diagrams and thus indicate a relation. Do I need to specify that in my MySQL create table syntax that I wrote earlier?

Also, regarding the type for the articleContent and what you mentioned regarding images. I take it that I cannot basically have everything (article text and images) stored in one field articleContent and then just churn it out into a template html site? Reason I ask is that I have heard somewhere that I can do this using the BLOB/LongBlob type as opposed to Text/LongText.

What do you think?

Thanks.

Mak.