Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL Table Properties/Attributes

    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.

    Help!

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

    Code:
    CREATE TABLE `tutorials` (
    `id`,
    `tutorial`,
    `title`,
    `author`,
    `date`,
    PRIMARY KEY (`id`)
    )
    Last edited by i am the nut; 08-27-2006 at 01:38 AM.

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    Code:
    LONGTEXT	A string with a maximum length of 4294967295 characters.
    LONGBLOB	A string with a maximum length of 4294967295 characters.
    should be long enough

  • #3
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Edit:
    Would the code be like this?:

    Code:
    CREATE TABLE `tutorials` (
    `id`,
    `tutorial` LONGTEXT,
    `title` LONGTEXT,
    `author` LONGTEXT,
    `date` LONGTEXT,
    PRIMARY KEY (`id`)
    )
    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.
    Last edited by i am the nut; 08-27-2006 at 01:38 AM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:

    Code:
    create table mynumbers(myid INT(4) zerofill)
    and then did an insert
    Code:
    insert into mynumbers values (1000),(456789),(7),(23)
    and then withdrew them they would be displayed as

    1000
    456789
    0007
    0023

    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.
    Last edited by guelphdad; 08-27-2006 at 12:22 AM.

  • #5
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    Quote Originally Posted by guelphdad

    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)

  • #6
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    CREATE TABLE `tutorials` (
    `id` (I WANT THIS TO BE ALL DIGITS FOR AN ID WITH TEN DIGIT PLACES),
    `tutorial` (I DON'T KNOW WHAT TO PUT HERE I WANT IT AS LONG AS POSSIBLE),
    `title` VARCHAR(50),
    `author` VARCHAR(50),
    `date` (I DON'T KNOW WHAT TO PUT HERE... I WANT IT TO APPEAR LIKE "THURSDAY, AUGUST 5, 2006"),
    PRIMARY KEY (`id`)
    )
    Help!
    Last edited by i am the nut; 08-27-2006 at 02:08 AM.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #8
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by i am the nut; 08-27-2006 at 03:44 AM.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.
    Last edited by guelphdad; 08-27-2006 at 03:07 PM.

  • #10
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #12
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    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.

  • #13
    Registered User
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #14
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Sorry, no, go back and re-read your posts. You were rude.

  • #15
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by i am the nut
    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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •