View Full Version : MySQL Table Properties/Attributes

i am the nut
08-26-2006, 11:52 PM
Well, first of all... This is my first thread because yesterday I just so happened to leave my "PHP and MySQL for Dummies" book at school. It's a great book, really.

Anyways, I can't get any further of course without having my tables in my database and this is what I am trying to do:

Create a tutorial submission program in PHP and MySQL (of course).

This is what I can't manage to do:
Figure out how to create a query that allows as many characters or as much text as needed (unlimited) so that the tutorial can be as long as possible.

I don't know how. I've tried Google and the MySQL manual but I'm not getting anywhere.


Oh yes, also, this is what I currently have:

CREATE TABLE `tutorials` (

08-26-2006, 11:55 PM
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

should be long enough

i am the nut
08-27-2006, 12:37 AM
So you always have a limit no matter what? Doesn't the query have anything at the end I've missed? Because from what I've seen they have something after the ")" or end of the query.

Would the code be like this?:

CREATE TABLE `tutorials` (
`tutorial` LONGTEXT,
`title` LONGTEXT,
`author` LONGTEXT,
`date` LONGTEXT,

Also, what does int(11) mean? Doesn't it mean it can be a series of digits from a negative amount to a positive? Or am I wrong.

I often see it in MySQL queries in the ID column.

08-27-2006, 01:20 AM
you would see INT(11) when you use a program like phpmyadmin which unnecessarily assigns those values to INT, BIGINT, SMALLINT etc.

look iin the mysql manual and you will see the specific values that can be held in any of those sizes. The 11 is only relevant when you use zerofill. so if you wanted zeros in front of your numbers and you assigned:

create table mynumbers(myid INT(4) zerofill)
and then did an insert

insert into mynumbers values (1000),(456789),(7),(23)
and then withdrew them they would be displayed as


because you are telling the zerofill that you want the values of myid padded out with zeros to a minimum of four digits.

INT(11) would do the same thing but pad them out to 11 digits. You will not above we are not limited to a four digit number, but by the value that INT can hold which is
-2147483648 to 2147483647 for signed and 0 to 4294967295 for unsigned.

You also wouldn't use LONGTEXT for a date field you would use DATE, DATETIME, or TIME for such a field.

and using LONGTEXT for all of your columns just shows bad database design, it means you haven't thought out the specific sizes of the data you would need in each case.

surely for an author you'd need nothing longer than varchar(60) or so.

08-27-2006, 01:37 AM
surely for an author you'd need nothing longer than varchar(60) or so.
I dunno, some people have REALLY long names ;) But being serious, I would store authors in a separate table and just store their id with the article, assuming authors can post multiple articles and they have to be logged in or otherwise verified to post (else anyone could post under any name, including that of another author)

i am the nut
08-27-2006, 02:42 AM
I'm not exactly sure what you are talking about here as I am just learning how to do this...

I don't know what DATETIME does or anything so I don't know it's value or whatever.

What I want is for the ID to be all digits that consist of only 10 characters and for the author etc to be as long as possible.

Speak "noob" to me babe. :P

CREATE TABLE `tutorials` (
`title` VARCHAR(50),
`author` VARCHAR(50),


08-27-2006, 03:49 AM
In your date field use the type of DATE and insert your dates as yyyymmdd and then use DATE_FORMAT to display the dates the way you want.

as for the tutorial part to be as long as possible why? is it a document you want to create? do you need to search the document or just display it? if just to display the document then store the documents in a folder on your computer and then store a path to the folder and document in your database.

i am the nut
08-27-2006, 04:37 AM
Alright, I'm a noob at MySQL so I don't know what you're talking about when you say "the date format" or anything else okay? Just tell me what I need to put there.

Also, you do not require a reason for what I need. All you need to know is what I need and I'm asking for it.

Think of what I'm doing as a news system. It posts tutorials/news into the databse and I retrieve it on another page. Catch my drift here?

Please, this really shouldn't take all night.

08-27-2006, 03:44 PM
I just told you what to put there. I can't help it if you won't read the manual.
the reason I'm telling you about the other is because if you think you need to store a document that large but are only retrieving it and not displaying it then you are going to unnecessarily slow down your database retrievals.

you're the one telling us you know nothing about mysql. we're explaining things to you so you don't fall into the same mistakes countless other new users fall into.

so far all you are doing is deciding what you are going to do and aren't willing to take the time to learn anything. you might want to rethink that approach.

i am the nut
08-27-2006, 04:19 PM
Actually, for your information, I have an obsessive compulsive disorder which creates me to have everything perfection. And in my mind, I cannot use other people's scripts. I have to use my own.

I am willing to learn. I need to know what to learn before I start learning, correct? I want to learn what is only required for this project rather than learning everything.

"as for the tutorial part to be as long as possible why? is it a document you want to create? do you need to search the document or just display it? if just to display the document then store the documents in a folder on your computer and then store a path to the folder and document in your database."

Why did you need to know that my tutorial needed to be as long as possible? I've explained to you the script would be similar to a news script and would.

Let me explain, again. I am trying to create a basic tutorial submission script. All it will do is have a "add_tutorial.php" page, and a "display_tutorials.php" page. The "add_tutorial.php" page will obviously include the form you use to add the tutorial. The "display_tutorials.php" page will obviously display the information that was sent into the database from the "add_tutorial.php" page. It's simple. I could write this script in less than 3 minutes without a single error.

All I needed was some information on what was required as attributes in the table columns and why they were needed. Nothing more.

But now as I have retrieved help from another person on a messenger service I no longer require your assistance. Thanks anyway.

08-27-2006, 07:52 PM
If they didn't tell you to use DATE or DATETIME field for your date type data they would be wrong. ask them why they would suggest varchar/char type. You would lose all use of DATE and TIME functions for that column. that is why I told you it was important to use it. You would then use DATE_FORMAT to display them in the manner you would like.

since you know very little about mysql let me elaborate on my other point about the column type.

If you are not going to search the document then don't put the document in your database. only store the path to the file in the database. it makes your database significantly faster for searching. if you use LONGBLOB and don't need it then you are not using the correct type. If you do have to use LONGBLOB or LONGTEXT and do fill it then you will not be able to index that column efficiently. your searches would then be SIGNIFICANTLY slower than they would be with that column not used and you merely point to the document.

If you do need to search the document then you can either set up a full text index to search that column. in order to do that you would need to use myisam type for your table rather than innodb.

if you know you don't have to search the entire document for every possible word but would use some combinations of important words, then you would set up the documents in their own table identified by a document id. from there you would also create an additional table to store various rows with the specific document id and then also a keyword. you would store as many rows for each document id as you have key words you want to search on.

these two methods will give you significantly faster search times, again another reason I was asking exactly what you wanted to do.

it is great that you want to learn the bare minimum to do what you want and it is great that you don't have to come back here because you got your answers elsewhere.

that doesn't mean the answers you got were the correct ones nor does it mean that because that person gave you the answer you were only willing to hear that that person has more knowledge than either Nancy or I. It doesn't mean they have less. It only means that you came here with a preconceived answer in mind and left because you didn't get it.

you will take much longer to learn as a result, and I'll wager that your application will run much slower down the road because you haven't taken the time to take someone's thought out advice over your own answer.

08-27-2006, 10:27 PM
OCD is no excuse for rudeness. You asked a question and were answered.
You were also offered advice on the impracticalities of your proposed system. An incorrect database schema will give you problems along the road. You should concern yourself only with the data you want to store - not how it will look afterwards.
The correct structure, as guelphdad has said, depends on what you intend to do with your application. But if you dont want our advice then the answer to your question was in the first post.

i am the nut
08-30-2006, 04:36 AM
My intension was not to be rude but it was a statement of frustration rather than a shot at your knowledge towards this matter. When you actually can't tell the tone or expression of a person by reading unless stated.

Ex) "Please, this really shouldn't take all night" said Evan frustratedly.

My OCD was not a statement or reason for me being unintensionally rude, it was a statement of my perfectionist way and how everything has to be understandable in my view.

Ex) If I cannot understand a script I'm creating, it's not perfect in my view.

"Understand, Nancy?" said Connor/Nut in an polite and not intensionally rude to your assumptions way.

08-30-2006, 02:56 PM
Sorry, no, go back and re-read your posts. You were rude.

08-30-2006, 09:29 PM
Also, you do not require a reason for what I need. All you need to know is what I need and I'm asking for it.

Anyone, specifically a self proclaimed "mysql noob", should give as much information as possible to help us help you.

Learning and knowing the different mysql data types is a basic prerequisite to creating a table. You're trying to get from point A to point C without knowing a darn thing about point B. If you think you're saving time now, you're going to pay for it in the future.

i am the nut
08-30-2006, 11:49 PM
Psh, I may seem rude but I was just frustrated. I do get carried away often but clearly that's not a so called "excuse" (says Nancy).

Ja, Mr.arnyinc you aren't exactly in this so why don't you mind your own buisness. Yes, I meant that rudely.

And as I said I was not being intensionally rude, but I never said I wasn't in your opinion rude. My opinion on the other hand, well, it's on the other hand.

Okay? So, it's over. I've been helped, and clearly not much by you.

Yes, I meant that quite "rudely". Have a nice day.

08-31-2006, 10:05 PM
I don't know what you mean by saying that I'm not "in this":
You posted a message asking for help.
I read the message and the replies.
I gave you constructive input.
You launched a personal attack.

If you'll read my message again, you'll note that I didn't accuse you of being rude and I didn't insult you. I gave you some basic pointers on how to get help on this forum and how to write a successful project.