Query keeps failing...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Query keeps failing...

Post 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);

?>
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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 ;)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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....
Last edited by infolock on Wed Mar 16, 2005 3:52 am, edited 1 time in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

exxxcellent smithers :twisted:
Post Reply