Page 1 of 1

Query keeps failing...

Posted: Wed Mar 16, 2005 2:31 am
by Chris Corbyn
Anybody know why this keeps failing. Don't ask why I need to do it but I need to upload files directly to MySQL. I'm using a MEDIUMBLOB column type to store the data. Data could be up to 10MB.

I'm only practising at the moment so this couldn't be any simpler.... the table consists of just two columns. FileID (SMALLINT(4) AUTO_INCREMENT) and the one for the data, fileData (MEDIUMBLOB).

It apperas that small files (i.e. 10KB upload fine, but anything over about 50 or 60KB fails). I can't give an Error Message because it doesn't return one :-( I just get my die() statement.

I'm not being daft and the file IS there... :?

I'm probably not doing it right... I've followed tutorials which use the same method (except they do the obviousy adding filesize, and filename etc to other columns..)

Code: Select all

<?php

$conn = mysql_connect('localhost', 'myuser', 'mypass') or die ('Connect failed');
mysql_select_db('chriscor_main') or die ('Select DB failed');

$filebin = file_get_contents('./Shifts.xls');
//I've tried this too
//$filebin = fread(fopen('./Shifts.xls', 'r'), filesize('./Shifts.xls'));

$query1 = "INSERT INTO files (fileData) VALUES ('$filebin')";

$result = mysql_query($query1) or die('Query failed');

mysql_close($conn);

?>

Posted: Wed Mar 16, 2005 2:36 am
by infolock
instead of your statement " ...die('Query Failed!')"

try
or die(MySQL_Error())

that should at least tell you what the mysql error is ;)

Posted: Wed Mar 16, 2005 2:41 am
by Chris Corbyn
MySQL wrote:You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
It must be the file contents doing it.

Can I use addslashes()? This may be binary data and I'm not sure what affect it will have :? I'll test it anyway... i shouldn't be asking without trying LOL :p

Posted: Wed Mar 16, 2005 2:46 am
by Chris Corbyn
OK that seems to work

Code: Select all

<?php

$conn = mysql_connect('localhost', 'chriscor_admin', 'coch1983') or die ('Wrong credentials');
mysql_select_db('chriscor_main') or die ('Wrong database');

$filebin = addslashes(file_get_contents('./Shifts.xls', 'r'));

$query1 = "INSERT INTO files (fileData) VALUES ('$filebin')";

$result = mysql_query($query1) or die(MySQL_Error());

mysql_close($conn);

?>
file_get_contents() is a binary safe function... I'm not sure if this will mess it all up. I'll have to stripslashes() when In retreive the data but I guess that should just return it to its original state :?

Thanks for your help!

Posted: Wed Mar 16, 2005 2:47 am
by infolock
this is a good site for you to figure out where you are going wrong.. is where i learned :
http://www.php-mysql-tutorial.com/php-mysql-upload.php

edit: it shouldn't exactly mess it up, but to be on the safe side i'd try a test file before i'd try anything sensative....

Posted: Wed Mar 16, 2005 3:43 am
by Chris Corbyn
Reet :-D The solution was to addslashes() when creating the query, but there's no need to stripslashes() when retreiving it. In fact, it's corrupted if you do.

Works fine :-D

Thanks very much.

Posted: Wed Mar 16, 2005 3:52 am
by infolock
exxxcellent smithers :twisted: