Inserting a text file to MySQL
Brian Tafoya
plug-discuss@lists.plug.phoenix.az.us
Mon, 10 Feb 2003 16:35:32 -0700
Search Google for "insert image into mysql php"
I found it yesterday, and have it working from an upload. :)
Here is a sample...
<?
/*
* Update 2001-09-07
* Fixed to work with php4 in safe_mode.
* tested with php-4.0.6, apache-1.3.20, and mysql-3.23.41
*/
/*
* Update 2000-07-05
* It was reported today that you get an error about
* an invalid mysql link id on the last mysql_close
* statement.
*
* Look for MICHALE_VINCE_FIX to see the change.
*
* Thanks to Michael Vince.
*/
/*
* Update 2000-02-08
* The problem of the retrieved file always being named
* fileman.php3 seems to have been fixed.
*
* Look for SANDER_BOSMAN_FIX to see the 2 additions.
*
* Thanks to Sander Bosman (gedrain@home.nl)
*/
/*
* This script is for testing of sending and receiving files
* from a mysql database using php3.
*
* This script was typed in by Mike Leidy (mike@nerdfest.org) and was
* inspired by one written by Florian Dittmer (dittmer@gmx.net).
*
* Requred Table Schema:
*
* CREATE TABLE binary_data (
* id int(6) DEFAULT '0' NOT NULL auto_increment,
* description varchar(50) DEFAULT '' NOT NULL,
* bin_data longblob NOT NULL,
* filename varchar(50) DEFAULT '' NOT NULL,
* filesize varchar(50) DEFAULT '' NOT NULL,
* filetype varchar(50) DEFAULT '' NOT NULL,
* hits varchar(50) DEFAULT '' NOT NULL,
* PRIMARY KEY (id),
* KEY description (description),
* KEY hits (hits)
* );
*
* Other Options:
* -- added -O max_allowed_packet=24M to the startup command
* -- changed in php3.ini upload_max_filesize = 10485760
*/
if ($id) {
$conn_id = mysql_connect ('localhost', 'root', '');
mysql_select_db ('db_myfile');
$query = "select * from tbl_binary_data where id = $id";
$res_id = mysql_query ($query, $conn_id);
if (! $res_id) {
printf ("MySQL Error: (%d) %s<br><br>%s\n",
mysql_errno (),
mysql_error (),
$query);
mysql_close ($conn_id);
exit ();
}
$row = mysql_fetch_array ($res_id);
if ($row) {
$data = $row["bin_data"];
$type = $row["filetype"];
/*
* SANDER_BOSMAN_FIX
*/
$filename = $row["filename"];
$query = "update tbl_binary_data set hits = hits + 1
where id = $id";
$res_id = mysql_query ($query, $conn_id);
if (! $res_id) {
printf ("MySQL Error: (%d) %s<br><br>%s\n",
mysql_errno (),
mysql_error (),
$query);
mysql_close ($conn_id);
exit ();
}
mysql_close ($conn_id);
/*
* SANDER_BOSMAN_FIX
*/
Header ("Content-disposition: filename=".$filename);
Header ("Content-type: $type");
echo $data;
exit ();
}
else {
printf ("There were no rows found with id = %d\n", $id);
mysql_close ($conn_id);
}
}
printf ("<html>\n");
printf ("<head>\n");
printf ("<title>File Upload</title>\n");
printf ("</head>\n");
printf ("<body bgcolor=\"#FFFFFF\">\n");
printf ("<p>\n");
printf ("This is my test page for sending files to and getting files
from a mysql database with php4.\n");
printf ("</p>\n");
printf ("<p>\n");
printf ("<a href=\"fileman.phps\">Click Here</a> to view the
source.\n");
printf ("</p>\n");
if ($submit == 'Upload File') {
printf ("<p>\n");
printf ("Your file %s was successfully uploaded.<br>\n",
$input_file_name);
$file_size = filesize ($input_file);
$fp = fopen ($input_file, 'r');
$data = addslashes (fread ($fp, $file_size));
fclose ($fp);
unlink ($input_file);
mysql_connect ('localhost', 'username', 'password');
mysql_select_db ('database');
$res_id = mysql_query ("insert into tbl_binary_data values (
0,
'$description',
'$data',
'$input_file_name',
'$input_file_size',
'$input_file_type',
'0'
)");
$new_id = mysql_insert_id ();
/*
* MICHAEL_VINCE_FIX
* the following line was mysql_close ($conn_id);
*/
mysql_close ();
if ($new_id > 0) {
printf ("Database id for your file: %s<br>\n", $new_id);
}
else {
printf ("Your file was not successfully inserted!\n");
}
printf ("</p>\n");
}
printf ("<p>\n");
printf ("*** Send File ***\n");
printf ("</p>\n");
printf ("<p>\n");
printf ("<form action=\"fileman.php\" method=\"POST\"
enctype=\"multipart/form-data\">\n");
printf ("<input type=\"hidden\" name=\"MAX_FILE_SIZE\"
value=\"10485760\">\n");
printf ("File description: \n");
printf ("<input type=\"text\" name=\"description\"><br>\n");
printf ("File name: \n");
printf ("<input type=\"file\" name=\"input_file\"><br>\n");
printf ("<input type=\"submit\" name=\"submit\" value=\"Upload
File\">\n");
printf ("</form>\n");
printf ("<p>\n");
printf ("<p>\n");
printf ("*** Retrieve File ***\n");
printf ("</p>\n");
printf ("<p>\n");
printf ("<form action=\"fileman.php\" method=\"POST\">\n");
printf ("Database Id: \n");
printf ("<input type=\"text\" name=\"id\" value=\"$new_id\"><br>\n");
printf ("<input type=\"submit\" value=\"Retrieve File\">\n");
printf ("</form>\n");
printf ("<p>\n");
printf ("</body>\n");
printf ("</html>\n");
?>
----- Original Message -----
From: "Sundar" <linux@esaravana.com>
To: <plug-discuss@lists.plug.phoenix.az.us>
Sent: Monday, February 10, 2003 4:16 PM
Subject: Re: Inserting a text file to MySQL
> Thanks David,
>
> When you say
>
> insert into foo ( [contents of file]);
>
> Does it mean
>
> insert into foo ( filename );
>
> I am just trying to achieve it with a SQL query.
>
> - Sundar
>
>
> David A. Sinck wrote:
>
> >\_ SMTP quoth Sundar on 2/10/2003 10:26 as having spake thusly:
> >\_
> >\_ Hi,
> >\_
> >\_ I am retrieving some text files from our Oracles server and been asked
> >\_ to populate some feasible local server. So, obviously I selected
MySQL,
> >\_ though I haven't personally used it much. Now, I have a problem. How
do
> >\_ I store a text file in MySQL?
> >
> >create table foo (
> > thefile text
> >);
> >
> >insert into foo ( [contents of file]);
> >
> >YMMV. There's also 'longtext' and 'verylongtext' IIRC.
> >
> >David
> >---------------------------------------------------
> >PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> >To subscribe, unsubscribe, or to change you mail settings:
> >http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> >
> >
>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>