...

View Full Version : Insert and retrieve long data from database using php



pelehelp
03-22-2009, 01:04 PM
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);
?>

alilg
03-22-2009, 01:07 PM
you can use of "TEXT" instead of "VARCHAR" which have not limitation!
go to your phpmyadmin and change "VARCHAR" to "TEXT" for description.

steelaz
03-22-2009, 01:09 PM
VARCHAR type can only be 255 long. For long texts use TEXT data type.

pelehelp
03-22-2009, 02:05 PM
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?

abduraooft
03-22-2009, 02:27 PM
Choose a suitable one from http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html according to your storage requirement.

steelaz
03-22-2009, 02:29 PM
When you enter long text, do you get any error messages? Can you verify that it gets inserted to the database?

abduraooft
03-22-2009, 02:32 PM
Can you verify that it gets inserted to the database?
That's a good point. Change your
mysql_query($insert); to

mysql_query($insert) or die(mysql_error());

oesxyl
03-22-2009, 09:11 PM
you have a lot of "'" in text you want to insert:

'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/function.mysql-real-escape-string.php

best regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum