PHP PDO MYSQL problem

Question

whiteyoh 0 Posting Pro in Training

14 Years Ago

Hi all,

When running the following i get this error:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'user_id' cannot be null

and heres the code:

function upload(){
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
    {
    /***  get the image info. ***/
	
    $size = getimagesize($_FILES['userfile']['tmp_name']);
    /*** assign our variables ***/
	echo $user = $_REQUEST['id'];
	echo $owner = $_REQUEST['p'];
    $type = $size['mime'];
    $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
    $size = $size[3];
    echo $name = $_FILES['userfile']['name'];
    echo $maxsize = 99999999;


    /***  check the file is less than the maximum file size ***/
    if($_FILES['userfile']['size'] < $maxsize )
        {
        /*** connect to db ***/
        $dbh = new PDO("mysql:host=localhost;dbname=db", 'un', 'pw');

                /*** set the error mode ***/
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            /*** our sql query ***/
        $stmt = $dbh->prepare("INSERT INTO testblob (user_id, item_id, image_type ,image, image_size, image_name) VALUES (?, ?, ? ,?, ?, ?)");

        /*** bind the params ***/
        $stmt->bindParam(1, $user);
		$stmt->bindParam(2, $owner);
		$stmt->bindParam(3, $type);
        $stmt->bindParam(4, $imgfp, PDO::PARAM_LOB);
        $stmt->bindParam(5, $size);
        $stmt->bindParam(6, $name);

        /*** execute the query ***/
        $stmt->execute();
        }
    else
        {
        /*** throw an exception is image is not of type ***/
        throw new Exception("File Size Error");
        }
    }
else
    {
    // if the file is not less than the maximum allowed, print an error
    throw new Exception("Unsupported Image Format!");
    }
}

mysql php

2 Contributors
23 Replies
597 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by whiteyoh

MrYrm 0 Light Poster

14 Years Ago

its kinda obvious since the error tells you whats wrong: Column 'user_id' cannot be null.
does the 9th line give any output?

MrYrm 0 Light Poster

14 Years Ago

Then you should check what the return value is of the bind method because as the error says the user_id is empty.

MrYrm 0 Light Poster

14 Years Ago

the problem is in your html:

<form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>" method="post">

submitting the form will post to the url index.php and NOT index.php?p=1 so p is not available as a GET parameter after you submit the form.

Edited 14 Years Ago by MrYrm because: n/a

MrYrm 0 Light Poster

14 Years Ago

Your welcome,

Just a little tip to make life a little "easier", your using all different kind off ways to store stuff you need on the next page why not just pick one and use that?
Its not wrong to use $_REQUEST $_GET $_SESSION while your making a post but in a simple case like this its probably not necessary (depending on the rest of your application).

I have a little question, are you trying to save the hole image in the database?

Edited 14 Years Ago by MrYrm because: n/a

MrYrm 0 Light Poster

14 Years Ago

I think you should take a good look at the following line and find out what it does:

$imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');

As for your question if there is no form, meaning no user input why would you wanna post it to the next page?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

whiteyoh 0 Posting Pro in Training · Answer 1 · 2010-02-16T05:51:54+00:00

Hi,

It certainly does. The value comes from a GET which is present in the URL so its not that obvious as to why its not working. You can also see from the code that the GET is converted to a variable which is binded in the stmt.

whiteyoh 0 Posting Pro in Training · Answer 2 · 2010-02-16T15:48:38+00:00

I understand that. Please can you help me to do so?

I have a get parameter that echo's.
i have added the datatype into the pindparam

whiteyoh 0 Posting Pro in Training · Answer 3 · 2010-02-16T16:47:27+00:00

I have made some code adjustments with echos and I can see that its not actually "GETTING" the passed variable.

here is my new code. Any assistance with changes would be greatly appreciated.

<?php
session_start();
echo "top u";
echo $_SESSION['user_id'];
echo "top p";
echo $_GET['p'];
/*** check if a file was submitted ***/
if(!isset($_FILES['userfile']))
    {
    echo '<p>Please select a file</p>';
    }
else
    {
    try    {
        upload();
        /*** give praise and thanks to the php gods ***/
        echo '<p>Thank you for submitting</p>';
        }
    catch(Exception $e)
        {
        echo '<h4>'.$e->getMessage().'</h4>';
        }
    }
?>
<?php
echo "outside u";
echo $_SESSION['user_id'];
echo "outside p";

echo $user_id = $_GET['p'];
;
/**
 *
 * the upload function
 * 
 * @access public
 *
 * @return void
 *
 */
function upload(){
	echo "inside u";
echo $_SESSION['user_id'];
echo "inside p";
$owner_id =  $_GET['p'];
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
    {
    /***  get the image info. ***/
	
    $size = getimagesize($_FILES['userfile']['tmp_name']);
    /*** assign our variables ***/
	$owner_id = $_REQUEST['id'];
    $user_id = $_SESSION['user_id'];	
    $type = $size['mime'];
    $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
    $size = $size[3];
    $name = $_FILES['userfile']['name'];
    $maxsize = 99999999;


    /***  check the file is less than the maximum file size ***/
    if($_FILES['userfile']['size'] < $maxsize )
        {
        /*** connect to db ***/
        $dbh = new PDO("mysql:host=localhost;dbname=db", 'un', 'pw');

                /*** set the error mode ***/
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            /*** our sql query ***/
        $stmt = $dbh->prepare("INSERT INTO testblob (user_id, item_id, image_type ,image, image_size, image_name) VALUES (?, ?, ? ,?, ?, ?)");

        /*** bind the params ***/
        $stmt->bindParam(1, $user_id, PDO::PARAM_INT);
		$stmt->bindParam(2, $owner_id, PDO::PARAM_INT);
		$stmt->bindParam(3, $type);
        $stmt->bindParam(4, $imgfp, PDO::PARAM_LOB);
        $stmt->bindParam(5, $size);
        $stmt->bindParam(6, $name);

        /*** execute the query ***/
        $stmt->execute();
        }
    else
        {
        /*** throw an exception is image is not of type ***/
        throw new Exception("File Size Error");
        }
    }
else
    {
    // if the file is not less than the maximum allowed, print an error
    throw new Exception("Unsupported Image Format!");
    }
}
?> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

  <html>
  <head><title>File Upload To Database</title></head>
  <body>
  <h2>Please Choose a File and click Submit</h2>
  <form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>" method="post">
  <input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
  <div><input name="userfile" type="file" /></div>
  <div><input type="submit" value="Submit" /></div>
  </form>

</body></html>

whiteyoh 0 Posting Pro in Training · Answer 4 · 2010-02-16T16:49:37+00:00

the test give the following output after submitting
top u1
top p
inside u1
inside p
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'item_id' cannot be null

outside u1
outside p

so P is not being passed

whiteyoh 0 Posting Pro in Training · Answer 5 · 2010-02-16T18:28:52+00:00

I have been into the database and set both values to allow NULL.

So now my problem lies with the P variable not passing into the function.

the url on the upload page looks like this....

http://..................../index.php?p=1

whiteyoh 0 Posting Pro in Training · Answer 6 · 2010-02-16T20:14:55+00:00

Ah ok i understand! That gives me an idea....

Thank you for your time on this.

whiteyoh 0 Posting Pro in Training · Answer 7 · 2010-02-16T20:44:42+00:00

MrYrm, thanks you so much for your help i fixed it!!!!!

I called the gets above the form, then posted to SELF as hidden form lines, and then carried on from there.

Thank you so much!

whiteyoh 0 Posting Pro in Training · Answer 8 · 2010-02-16T21:07:07+00:00

Thank you for your advice it is much appreciated.

Am at a fair level in php, however my mind still doesnt fully believe it which is why i use get and post. post is what i trust and know works, where get is what i need to physically see work. I will eventually use post all of the time.

With regards to images, yes i am storing them as a BLOB file.

1 question i have thought of, how can i post data to the next file without a form? or do i just make every button a single entity form?

whiteyoh 0 Posting Pro in Training · Answer 9 · 2010-02-16T21:35:20+00:00

i was thinking of security really. I have written a small include file that checks to see if the GET variables actually register against the logged in user. I just thought that posts were maybe not more secure, but you cant actually see them when posting

whiteyoh 0 Posting Pro in Training · Answer 10 · 2010-02-16T21:38:11+00:00

with regards to fopen. it just makes the file a read only binary stream?

Or have i missed something?

MrYrm 0 Light Poster · Answer 11 · 2010-02-16T21:40:43+00:00

I'm sorry somehow (probably because English is not my native language) i just cant understand what your trying to do..

whiteyoh 0 Posting Pro in Training · Answer 12 · 2010-02-16T21:48:53+00:00

Ah ok I see, your english is fine.

I am making a classified advert site. When an item is listed the user can upload a photo. The table it is stored in has userid and itemid as foreign keys and the image is stored as binary, I use the fopen to convert it, then other cells to store data such as file size and file type

MrYrm 0 Light Poster · Answer 13 · 2010-02-16T22:17:50+00:00

Ok but the return value of fopen() is a "file pointer resource" to get the real image you have to read the file contents if im not mistaken.

whiteyoh 0 Posting Pro in Training · Answer 14 · 2010-02-16T22:30:26+00:00

Yes thats correct. When i need the image i have showfile.php?image_id= which is part of every listing.

Thats why i needed this part to work as i want to change the unique identifier from the image primary key, to the item foreign key instead

MrYrm 0 Light Poster · Answer 15 · 2010-02-16T22:45:26+00:00

Still i don't see how it's going to work since your only saving a file handle to a temporary file.

whiteyoh 0 Posting Pro in Training · Answer 16 · 2010-02-16T22:53:27+00:00

its actually storing the file as binary, then showfile.php builds it back to an image, but actually displays it in browser as showphp.php and not the actual image URL.. That part works absolutly fine

MrYrm 0 Light Poster · Answer 17 · 2010-02-17T00:00:19+00:00

i must be missing something then.

$imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');

That line makes $imgfp a file handle but does not contain the image data.

so you are using that file handle in your showfile.php ?

whiteyoh 0 Posting Pro in Training · Answer 18 · 2010-02-17T03:58:48+00:00

whiteyoh 0 Posting Pro in Training

14 Years Ago

i can send you the code if you wish?