PHP/MySQL Problem

dagrog007 · 02-07-2008 12:31pm #1

Hey, I am kind of new to PHP/MySQL, so I am hoping someone will be able to help me out. I have the code below to upload a photo to a database and then retrieve it and display it to the screen. I am using a Digi Web server to host the site and for database storage.

However, my problem is that the picture wont display after being uploaded to the database. But, when I use a different server (UCD Server), I can get the same code to work, displaying the picture.

Can anyone sugest a reason that the Digi web server would be different and if so a fix?

Cheers

Dave

File test_imagedb_view.php

<html>
<head>
<style type="text/css">
//this does the roll-over stuff
#cssexample a {
position:relative;
}
#cssexample a img{
display:block;
position:absolute;
top:-999px;
left:0;
}
#cssexample a:hover img {
top:-50px;
left:0;
}
</style>

</head>
<body>
<form method="post" enctype="multipart/form-data">
<table width="350" border="0" cellpadding="1" cellspacing="1" class="box">
<tr>
<td width="246">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
<input name="userfile" type="file" id="userfile">
</td>
<td width="80"><input name="upload" type="submit" class="box" id="upload" value=" Upload "></td>
</tr>
</table>
</form>
<form method="post">
<input name="id" type="text" id="id">
<input name="delete" type="submit" id="add" value="delete imgid">
</form>

<?
$dbuser="xxxx";
$dbpass="xxxx";
$dbname = "xxxx";
$dbserver = "xxxx";

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");
//Retrieve the images - uses the other php file test_imagedb_create to do so
$sql = "SELECT imgid,imgtype FROM tblimage ORDER BY imgid";
$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!");
echo "<table border=1>\n";
echo "<tr><th>imgid</th><th>imgtype</th><th>imgdata</th></tr>\n";
while ($rs=mysql_fetch_array($result)) {
echo "<tr><td>".$rs[0]."</td>";
echo "<td>".$rs[1]."</td>";
echo "<td><p id=cssexample><a>View full size<img height=400 width=400 src=\"test_imagedb_create.php?imgid=".$rs[0]."\"></a><br><img height=80 width=80 src=\"test_imagedb_create.php?imgid=".$rs[0]."\"></p></td></tr>\n";
};
echo "</table>\n";
mysql_close($dbconn);

?>

</body>
</html>

File test_imagedb_create.php

<?
$dbuser="xxxx";
$dbpass="xxxx";
$dbname = "xxxx";
$dbserver = "xxxx";

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");
$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET["imgid"];
$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!");
$contenttype = @mysql_result($result,0,"imgtype");
$image = @mysql_result($result,0,"imgdata");
header("Content-type: $contenttype");
echo $image;
mysql_close($dbconn);
?>

Database code

CREATE TABLE `tblimage` (
`imgid` int(3) unsigned NOT NULL auto_increment,
`imgtype` varchar(16) NOT NULL default '',
`imgdata` mediumblob,
PRIMARY KEY (`imgid`)
) TYPE=MyISAM;

GoneShootin · 02-07-2008 12:45pm

Does your <form> tag need to have an ACTION paremeter in there? Also a NAME is often good convention.

Can you confirm that the image is actually in the DB when its uploaded?

Adam · 02-07-2008 12:55pm

I think where you're using $rs[0] and $rs[1] there's no guarantee that mysql will pull out the database fields in the order you expect them to, so you should use $rs and $rs specifically.

Beyond that, as GoneShootin said, can you confirm the image is uploaded sucessfully? Where is the script that actually uploads the image?

dagrog007 · 02-07-2008 12:59pm

I have tried putting ACTION in and it makes no difference. I am not sure what you mean by "NAME is often good convention". And I checked the database and the size of the file I uploaded matches the size in the database.

Cheers

GoneShootin · 02-07-2008 1:14pm

When I do forms I tend to write them such as

<form name="someFormName" method="post" action="file.php">

A good idea for debugging is to echo the $_POST array after the form has been submitted

print_r($_POST);

This will give you all data submitted via the form.

Another thing you could do is echo out a MySQL SELECT statement and let us see the data coming back in your SQL query.

dagrog007 · 02-07-2008 1:19pm

Mirror - I change $rs[0] and $rs[1] to $rs and $rs as you suggested but with no luck. The code for uploading the picture is

//Uploading code
if(isset($_POST) && $_FILES > 0)
{
//Gets the information of the photo
$fileName = $_FILES;
$tmpName = $_FILES;
$fileSize = $_FILES;
$fileType = $_FILES;
//Reading in the file
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

Cheers for the help guys - but how come the same code works on a different server (site and database are on a UCD server and pictures get displayed. Also I thought the problem might be in the test_imagedb_create.php file on this line:
$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET["imgid"];
and specifically with the .$_GET["imgid"] but I could be totally wrong.

Cheers

dagrog007 · 02-07-2008 1:45pm

I entered the code <?php print_r($_POST); ?> just after I closed the first </form> and I got:
Array()
printed on the screen. When I click the upload button (with out choosing a picture to upload), I get:
Array([MAX_FILE_SIZE]=>2000000[upload]=>Upload)
printed on the screen. If I choose a file first and then upload all I get is the:
Array()
printed on the screen.

When you say echo out MySQL SELECT do you mean
$sql = "SELECT imgid,imgtype FROM tblimage ORDER BY imgid";
line of code? I entered the code:
echo "$sql";
and SELECT imgid,imgtype FROM tblimage ORDER BY imgid was printed on the screen.

daymobrew · 02-07-2008 1:53pm

Mirror wrote: »

I think where you're using $rs[0] and $rs[1] there's no guarantee that mysql will pull out the database fields in the order you expect them to, so you should use $rs and $rs specifically.

I disagree. dagrog007's SELECT calls specify the order of the fields so they are returned in that order. If * was used then the order is not guaranteed.

<form name="someFormName" method="post" action="file.php">

I think that the default action is to call the current file/script. "name" is not required but is useful for scripting or use with CSS.
[PHP]//Uploading code
if(isset($_POST) && $_FILES > 0)
{
//Gets the information of the photo
$fileName = $_FILES;
$tmpName = $_FILES;
$fileSize = $_FILES;
$fileType = $_FILES;
//Reading in the file
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp); [/PHP] Where is $content being written to the database?

BTW, I used [PHP] tags to get the coloured code.

Adam · 02-07-2008 2:34pm

daymobrew wrote: »

I disagree. dagrog007's SELECT calls specify the order of the fields so they are returned in that order. If * was used then the order is not guaranteed.

Sorry, you're right there! However I would still recommend naming the fields for clarification, especially for later works where you may be dealing with a lot more fields.

daymobrew wrote: »
<form name="someFormName" method="post" action="file.php">
I think that the default action is to call the current file/script. "name" is not required but is useful for scripting or use with CSS.
[php]//Uploading code
if(isset($_POST) && $_FILES > 0)
{
//Gets the information of the photo
$fileName = $_FILES;
$tmpName = $_FILES;
$fileSize = $_FILES;
$fileType = $_FILES;
//Reading in the file
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp); [/php] Where is $content being written to the database?

BTW, I used [php] tags to get the coloured code.

+1

OP, the code here does not insert the image in to the database, it merely processes the image in preparation for such action.

daymobrew · 02-07-2008 2:39pm

Mirror wrote: »

Sorry, you're right there! However I would still recommend naming the fields for clarification, especially for later works where you may be dealing with a lot more fields.

Agreed.

Mirror wrote: »

OP, the code here does not insert the image in to the database, it merely processes the image in preparation for such action.

The "works in UCD" is baffling me. It must be something simple with setup - or the "works in UCD" is not quite true.

dagrog007 · 02-07-2008 3:03pm

Sorry, here is the code that uploads to database
$query = "INSERT INTO tblimage VALUES (NULL,'$intLatitude<br/>$intLongitude', '$content')";
mysql_query($query) or die('Error, query failed');

The $intLatitude<br/>$intLongitude' is other things that will go in to the database, but it doesnt affect what I am doing now.

With regard to the other server - I got a friend to upload the code to a server he has access to in UCD and the pictures (which is what I am having problems with) get displayed. I just dont understand how this can be.

Cheers

Adam · 02-07-2008 3:22pm

dagrog007 wrote: »

Sorry, here is the code that uploads to database
$query = "INSERT INTO tblimage VALUES (NULL,'$intLatitude<br/>$intLongitude', '$content')";
mysql_query($query) or die('Error, query failed');

The $intLatitude<br/>$intLongitude' is other things that will go in to the database, but it doesnt affect what I am doing now.

With regard to the other server - I got a friend to upload the code to a server he has access to in UCD and the pictures (which is what I am having problems with) get displayed. I just dont understand how this can be.

Cheers

Well let's put it this way. If I'm not mistaken, the code simply can't work, on account of the fact that you're using [php]header("Content-type: $contenttype");[/php] because when the code get's to that part it's just going to tell you you can't modify the headers because you've already output data before calling the script that retrieves the images.

Your friend is lying to you! :pac:

ejvilla · 02-07-2008 3:52pm

[php]$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET["imgid"];[/php]

Been a while since I've looked at php code but the use of the quotation marks looks erroneous to me..

Perhaps this is contributing to your problem.

Try:
[php]
$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET;
[/php]

ejvilla · 02-07-2008 4:04pm

What is displayed when the script is run?

I'm assuming you get a new page with a table - are the image IDs displayed?

When you look at the page source after the script has run and your table has been displayed, what does the image tag look like (paste it here).

I have the feeling the medium blob you populated is dumping a representation of the image into the src attribute..

I'm pretty sure you're going to have to convert that into a temporary image on the server and link to it.

I wrote an app a few years back that put word docs into blobs, when I wanted to retrieve them a link was provided which, when clicked, created the file on the fly and presented it as a download. I can look at the code later (at work now) and see if there's anything useful if you'd like..

daymobrew · 02-07-2008 4:09pm

Mirror wrote: »

Well let's put it this way. If I'm not mistaken, the code simply can't work, on account of the fact that you're using [php]header("Content-type: $contenttype");[/php] because when the code get's to that part it's just going to tell you you can't modify the headers because you've already output data before calling the script that retrieves the images.

I disagree again. That line is from the test_imagedb_create.php script and the line is its first output.
[PHP]// File test_imagedb_create.php
<?
$dbuser="xxxx";
$dbpass="xxxx";
$dbname = "xxxx";
$dbserver = "xxxx";

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");
$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET["imgid"];
$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!");
$contenttype = @mysql_result($result,0,"imgtype");
$image = @mysql_result($result,0,"imgdata");
header("Content-type: $contenttype");
echo $image;
mysql_close($dbconn);
?>[/PHP] I suggest calling this script on its own e.g. http://localhost/test_imagedb_create.php?imgid=12345
Even better - comment out the $image lines and display the $contenttype value - to ensure that the mysql_query returned what you expected.

Also, can you try changing all your short tags ("<?") to full php tags ("<?php"). Maybe UCD servers allow short tags and your local setup doesn't.

ejvilla wrote:

Been a while since I've looked at php code but the use of the quotation marks looks erroneous to me..

It's not an issue. You can use either single or double quotes (though I prefer single quotes for strings that will not have variables in them).

Zombrex · 02-07-2008 4:23pm

Firstly

Use mysql_real_escape_string instead of addslashes to escape your raw image data before you place it in the database. This is a more robust method.

use -

[php]
$content = fread($fp, filesize($tmpName));
$content = mysql_real_escape_string($content);
[/php]

instead of -

[php]
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
[/php]

Secondly

Check that on your server the PHP configuration variable "magic_quotes_gpc" is turned off. What this does if turned on is automatically escape any input from the users. It is normally turned off as the PHP developers are trying to get people to stop relying on it as it doesn't work very well. But some set ups may still have it turned on.

With it on your data is already escaped before you get it and you are escaping it twice with the addslashes function, but only automatically unescaping it one time when it is pulled back from the database. This means your image data string is still escaped one time, and will appear corrupt if you output it as an image file as it still has a set of escape characters embedded throughout it.

This would explain why your code fails and your friends works, it is down to how PHP is configured on each server.

dagrog007 · 02-07-2008 4:32pm

hi ejvilla, i have attached a screen shot of the output

Adam · 02-07-2008 4:34pm

Ok OP, I don't know what changes if any are actually on this script, but this works on my dev environment, test it out:

Upload Page
[php]
<?

$dbuser="root";
$dbpass="";
$dbname = "test";
$dbserver = "localhost";

//Uploading code
if(isset($_POST) && $_FILES > 0)
{
//Gets the information of the photo
$fileName = $_FILES;
$tmpName = $_FILES;
$fileSize = $_FILES;
$fileType = $_FILES;
//Reading in the file
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");

$query = "INSERT INTO tblimage VALUES (NULL,'$fileType', '$content')";
mysql_query($query) or die('Error, query failed');

}

?>
<html>
<head>
<style type="text/css">
//this does the roll-over stuff
#cssexample a {
position:relative;
}
#cssexample a img{
display:block;
position:absolute;
top:-999px;
left:0;
}
#cssexample a:hover img {
top:-50px;
left:0;
}
</style>

</head>
<body>
<form method="post" enctype="multipart/form-data">
<table width="350" border="0" cellpadding="1" cellspacing="1" class="box">
<tr>
<td width="246">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
<input name="userfile" type="file" id="userfile">
</td>
<td width="80"><input name="upload" type="submit" class="box" id="upload" value=" Upload "></td>
</tr>
</table>
</form>
<form method="post">
<input name="id" type="text" id="id">
<input name="delete" type="submit" id="add" value="delete imgid">
</form>

<?

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");
//Retrieve the images - uses the other php file test_imagedb_create to do so
$sql = "SELECT imgid,imgtype FROM tblimage ORDER BY imgid";
$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!");
echo "<table border=1>\n";
echo "<tr><th>imgid</th><th>imgtype</th><th>imgdata</th></tr>\n";
while ($rs=mysql_fetch_array($result)) {
echo "<tr><td>".$rs[0]."</td>";
echo "<td>".$rs[1]."</td>";
echo "<td><p id=cssexample><a>View full size<img height=400 width=400 src=\"test_imagedb_create.php?imgid=".$rs[0]."\"></a><br><img height=80 width=80 src=\"test_imagedb_create.php?imgid=".$rs[0]."\"></p></td></tr>\n";
};
echo "</table>\n";
mysql_close($dbconn);

?>

</body>
</html>
[/php]

test_imagedb_create.php
[php]
<?

$dbuser="root";
$dbpass="";
$dbname = "test";
$dbserver = "localhost";

$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable");
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable");
$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=".$_GET["imgid"];
$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!");
$contenttype = @mysql_result($result,0,"imgtype");
$image = @mysql_result($result,0,"imgdata");
header("Content-type: $contenttype");
echo $image;
mysql_close($dbconn);

?>
[/php]

Adam · 02-07-2008 4:38pm

Wicknight wrote: »

Firstly

Use mysql_real_escape_string instead of addslashes to escape your raw image data before you place it in the database. This is a more robust method.

use -

[php]
$content = fread($fp, filesize($tmpName));
$content = mysql_real_escape_string($content);
[/php]

instead of -

[php]
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
[/php]

Secondly

Check that on your server the PHP configuration variable "magic_quotes_gpc" is turned off. What this does is automatically escape any input from the users. It is normally turned off as the PHP developers are trying to get people to stop relying on it as it doesn't work very well. But some set ups may still have it turned on.

With it on your data is already escaped before you get it and you are escaping it twice with the addslashes function, but only automatically unescaping it one time when it is pulled back from the database. This means your image data string is still escaped one time, and will appear corrupt if you output it as an image file as it still has a set of escape characters embedded throughout it.

This would explain why your code fails and your friends works, it is down to how PHP is configured on each server.

Good effort, but I just checked and with the code I posted above, and magic_quotes_gpc turned on, my code still works...

GoneShootin · 02-07-2008 4:52pm

Magic quotes shouldnt apply in this case as he isnt adding quotes themselves to the $content.

What I love about this kind of question is the way that the PHP headers pile on and try to out-do eachother

ejvilla · 02-07-2008 4:54pm

Thanks dagrog, looks like the file type isn't being returned either... this indicates that the file isn't being put into the database in the first place.

Is it possible that max_file_size is set too low? It's currently set to just under 2mb... which is surely enough but thought I'd ask just in case.

One way to confirm whether or not the information is being put in the database is to query it using the command line or something like sqldeveloper (not sure if this only looks at Oracle dbs though...) Just run a " SELECT * FROM tblimage " on the cmd line and make sure it's returning the data you expect to see..

When you view the source of the generated html page, does the image tag look like this: <img height=400 width=400 src=""> ?

dagrog007 wrote: »

hi ejvilla, i have attached a screen shot of the output

ejvilla · 02-07-2008 4:59pm

I'm a Java "header" thanks you very much! :P

I'd say it's more to do with developers knowing php can be a bisnatch to debug...

GoneShootin wrote: »

Magic quotes shouldnt apply in this case as he isnt adding quotes themselves to the $content.

What I love about this kind of question is the way that the PHP headers pile on and try to out-do eachother

daymobrew · 02-07-2008 5:01pm

GoneShootin wrote: »

What I love about this kind of question is the way that the PHP headers pile on and try to out-do eachother

I disagree :P

No one has mentioned that the returned image will be resized via the img width/height attributes - a horrible way to create thumbnails.

Adam · 02-07-2008 5:03pm

daymobrew wrote: »

I disagree :P

No one has mentioned that the returned image will be resized via the img width/height attributes - a horrible way to create thumbnails.

I saw it in the flesh(browser)...looks awful tbh! >_<

Dagrog, any joy with my code?

dagrog007 · 02-07-2008 6:02pm

Mirror, I had no joy with your code. Images are still not being displayed

Thanks for the help

Adam · 02-07-2008 6:07pm

dagrog007 wrote: »

Mirror, I had no joy with your code. Images are still not being displayed

Thanks for the help

Well what is the output now?

If you copied and pasted my code to the letter (excluding the database connection details obviously) then it should definitely work. So that means the code is fine, and the problem is with your php configuration.

dagrog007 · 02-07-2008 6:33pm

Hi Mirror - I have attached the output. The result is the same as before. If you say the problem is with PHP configuration, do you mean the hosting end (digiweb)? and if so, is my only option to get on to them and say what?

Thanks for the help

dagrog007 · 02-07-2008 6:41pm

Sorry Mirror - I have attached a new screen shot using your code. Image didnt display but file type did.

Adam · 02-07-2008 7:07pm

dagrog007 wrote: »

Sorry Mirror - I have attached a new screen shot using your code. Image didnt display but file type did.

Well it would seem so, but let's see if we can narrow down the problem further.

Did you try as suggested above and put http://www.yoursite.com/test_imagedb_create.php?imgid=5 for example and see if the image is displayed? Obviously ensure that there is an image with the id number you put in to the url.

dagrog007 · 02-07-2008 7:37pm

Mirror - I did what you said, and I have attached the output.

Adam · 02-07-2008 8:22pm

dagrog007 wrote: »

Mirror - I did what you said, and I have attached the output.

Ah, that means the content type isn't being set properly. Basically the image is being pulled from the database sucessfully, but it's in binary format, and the content type header tells the browser to convert the data back to an image. So the header(Content-type: $content) code is failing it would seem...

PHP/MySQL Problem

Comments