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.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Insert and retrieve long data from database using php

    Hi,
    I am a beginner of both MySQL and PHP, and I have a question about a project I am working on. I have to create a cinema database to store movies information and hope can retrieve all the data from the database and display it in HTML table. But I have encountered a problem that the data I store is too long, I not very sure that is there a limitation in the databse.
    Basically I noticed that i face the problem when the I insert too long data into the database, for eg. the 'description' of the movie. But I have set the description length as VARCHAR(500), finally it still cant display it out in the table. What was my error then and how to fix it?Below shows my code:

    <?php
    //Connect to the MySQL server
    $db_host = "localhost";
    $db_username = "root";
    $db_password = "fsktm";
    $server = mysql_connect($db_host,$db_username,$db_password)or die("Could not connect!/n");

    //Create a database called "cinema"
    $db_name = "cinema";
    mysql_query("CREATE DATABASE $db_name",$server);

    //Select a database that has created
    mysql_select_db($db_name)or die("Could not select the database $db_name!\n");

    //Create multiple tables
    $table = "CREATE TABLE movie(
    movie_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    movie_name VARCHAR(50) NOT NULL,
    duration VARCHAR(20),
    description VARCHAR(1000),
    category_id VARCHAR(3) NOT NULL
    )";

    mysql_query($table);

    //Insert multiples data into multiples tables
    $insert = "INSERT INTO movie(movie_name, duration, description, category_id)
    VALUES('Bride Wars','1 hr. 30 min.','Liv and Emma are best friends who since childhood have planned every detail of their respective weddings. At the top of their bridal must have list: a ceremony at New York's ultimate bridal destination, the Plaza Hotel. Now, at age 26, they're both about to get married; they're about to realize their dreams; and they're about to live happily ever after. Or maybe not. When a clerical error causes a clash in wedding dates--they're now to be married on the same date!--Liv, Emma and their lifelong friendship are put to the ultimate test. Liv, a successful lawyer who is used to getting what she wants, including the perfect job and the perfect man, won't settle for anything less than the perfect wedding she has dreamed of for years. Emma, a schoolteacher who has always been good at taking care of others, but not so much in looking after herself, discovers her inner Bridezilla and comes out swinging when her own dream wedding is imperiled. Now, the two best friends who'd do anything for each other find themselves in a no-holds-barred, take-no-prisoners struggle that threatens to erupt into all-out war.','CM1'),
    ('Yes Man','1 hr. 44 min.','AlliensA man signs up for a self-help program based on one simple principle: say yes to everything... and anything. At first, unleashing the power of yes transforms his life in amazing and unexpected ways, but he soon discovers that opening up his life to endless possibilities can have its drawbacks.','CM1'),
    ('Sex Drive','1 hr. 49 min.','Eighteen year old Ian finally gets the opportunity to lose his virginity when a woman he meets on-line offers to have sex with him if he drives to Knoxville to meet her. Accompanied by friends Lance and Felicia, whom Ian has a thing for, but she in turn has a thing for Lance, take off on a road trip in Ian's brother, Rex's beloved 1969 GTO -- without permission of course.','CM1'),";

    mysql_query($insert);

    mysql_close($server);
    ?>


    And below shows how i retrieve and display the movies information in a HTML table form:

    <?php
    //Connect to the MySQL server
    $db_host = "localhost";
    $db_username = "root";
    $db_password = "fsktm";
    $server = mysql_connect($db_host,$db_username,$db_password)or die("Could not connect!/n");

    //Select a database that has created
    $db_name = "cinema";
    mysql_select_db($db_name)or die("Could not select the database $db_name!\n");

    //Retrieve or select the data
    $retrieve = mysql_query("SELECT * FROM movie");

    echo"<table border = 1 width = 500% style = 'background-color:#F0F8FF;'>
    <tr>
    <th>Movie_ID</th>
    <th>Movie_Name</th>
    <th>Duration</th>
    <th>Description</th>
    <th>Category_ID</th>
    </tr>";

    //Display the data from the database in a table using aray
    while($row = mysql_fetch_array($retrieve))
    {
    echo "<tr>";
    echo"</td><td>";
    echo $row['movie_id'];
    echo "</td><td>";
    echo $row['movie_name'];
    echo "</td><td>";
    echo $row['duration'];
    echo "</td><td>";
    echo $row['description'];
    echo "</td><td>";
    echo $row['category_id'];
    echo "</tr>";
    }

    echo "</table>";

    mysql_close($server);
    ?>

  • #2
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you can use of "TEXT" instead of "VARCHAR" which have not limitation!
    go to your phpmyadmin and change "VARCHAR" to "TEXT" for description.

  • #3
    New Coder
    Join Date
    Mar 2009
    Location
    Chicago, IL
    Posts
    69
    Thanks
    0
    Thanked 15 Times in 15 Posts
    VARCHAR type can only be 255 long. For long texts use TEXT data type.

  • #4
    New Coder
    Join Date
    Mar 2009
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks to u guys....
    But my problem still occur, the long data still cant be stored into the database although I have tried to use TEXT as the data type.Can u guys kindly help me again?any suggestion?

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,849
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Choose a suitable one from http://dev.mysql.com/doc/refman/5.0/...uirements.html according to your storage requirement.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    New Coder
    Join Date
    Mar 2009
    Location
    Chicago, IL
    Posts
    69
    Thanks
    0
    Thanked 15 Times in 15 Posts
    When you enter long text, do you get any error messages? Can you verify that it gets inserted to the database?

  • #7
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,849
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by steelaz View Post
    Can you verify that it gets inserted to the database?
    That's a good point. Change your
    Code:
     mysql_query($insert);
    to
    PHP Code:
     mysql_query($insert) or die(mysql_error()); 
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    you have a lot of "'" in text you want to insert:
    Code:
    'Liv and Emma are best friends who since childhood have planned every detail of their
    respective weddings. At the top of their bridal must have list: a ceremony at New York's
    ultimate bridal destination, the Plaza Hotel. Now, at age 26, they're both about to get
    married; they're about to realize their dreams; and they're about to live happily ever after. Or maybe not. When a clerical error causes a clash in wedding dates--they're now to be married on the same date!--Liv, Emma and their lifelong friendship are put to the ultimate test. Liv, a successful lawyer who is used to getting what she wants, including the perfect job and the perfect man, won't settle for anything less than the perfect wedding she has dreamed of for years. Emma, a schoolteacher who has always been good at taking care of others, but not so much in looking after herself, discovers her inner Bridezilla and comes out swinging when her own dream wedding is imperiled. Now, the two best friends who'd do anything for each other find themselves in a no-holds-barred, take-no-prisoners struggle that threatens to erupt into all-out war.'
    use mysql_real_escape_string:

    http://www.php.net/manual/en/functio...ape-string.php

    best regards


  •  

    Posting Permissions

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