469,304 Members | 2,018 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

PHP PDO MYSQL problem

65
Hi All,

On posting a GET to this script, I take those values, along with the image, and upload as mysql blob.

On adding an additional variable to the url i get an error message. I can echo out the get variables to that page so i know they have been captured

Your help would be appreciated

The URL is: http://........./index.php?id=1&p=1

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

and the code is

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. /*** check if a file was submitted ***/
  4. if(!isset($_FILES['userfile']))
  5.     {
  6.     echo '<p>Please select a file</p>';
  7.     }
  8. else
  9.     {
  10.     try    {
  11.         upload();
  12.         /*** give praise and thanks to the php gods ***/
  13.         echo '<p>Thank you for submitting</p>';
  14.         }
  15.     catch(Exception $e)
  16.         {
  17.         echo '<h4>'.$e->getMessage().'</h4>';
  18.         }
  19.     }
  20. ?>
  21. <?php
  22.  
  23. /**
  24.  *
  25.  * the upload function
  26.  * 
  27.  * @access public
  28.  *
  29.  * @return void
  30.  *
  31.  */
  32. function upload(){
  33. /*** check if a file was uploaded ***/
  34. if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
  35.     {
  36.     /***  get the image info. ***/
  37.  
  38.     $size = getimagesize($_FILES['userfile']['tmp_name']);
  39.     /*** assign our variables ***/
  40.     echo $user = $_REQUEST['id'];
  41.     echo $owner = $_REQUEST['p'];
  42.     echo $type = $size['mime'];
  43.     $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
  44.     $size = $size[3];
  45.     $name = $_FILES['userfile']['name'];
  46.     $maxsize = 99999999;
  47.  
  48.  
  49.     /***  check the file is less than the maximum file size ***/
  50.     if($_FILES['userfile']['size'] < $maxsize )
  51.         {
  52.         /*** connect to db ***/
  53.         $dbh = new PDO("mysql:host=localhost;dbname=db", 'un', 'pw');
  54.  
  55.                 /*** set the error mode ***/
  56.                 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  57.  
  58.             /*** our sql query ***/
  59.         $stmt = $dbh->prepare("INSERT INTO testblob (user_id, item_id, image_type ,image, image_size, image_name) VALUES (?, ?, ? ,?, ?, ?)");
  60.  
  61.         /*** bind the params ***/
  62.         $stmt->bindParam(1, $user);
  63.         $stmt->bindParam(2, $owner);
  64.         $stmt->bindParam(3, $type);
  65.         $stmt->bindParam(4, $imgfp, PDO::PARAM_LOB);
  66.         $stmt->bindParam(5, $size);
  67.         $stmt->bindParam(6, $name);
  68.  
  69.         /*** execute the query ***/
  70.         $stmt->execute();
  71.         }
  72.     else
  73.         {
  74.         /*** throw an exception is image is not of type ***/
  75.         throw new Exception("File Size Error");
  76.         }
  77.     }
  78. else
  79.     {
  80.     // if the file is not less than the maximum allowed, print an error
  81.     throw new Exception("Unsupported Image Format!");
  82.     }
  83. }
  84. ?> 
  85. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  86.   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  87.  
  88.   <html>
  89.   <head><title>File Upload To Database</title></head>
  90.   <body>
  91.   <h2>Please Choose a File and click Submit</h2>
  92.   <form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>" method="post">
  93.   <input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
  94.   <div><input name="userfile" type="file" /></div>
  95.   <div><input type="submit" value="Submit" /></div>
  96.   </form>
  97.  
  98. </body></html>
  99.  
Feb 15 '10 #1

✓ answered by Dormilich

This is the paradox i dont understand.

On page load, i echo out the two values, 1 from session and 1 from get. The values exist.

On form submit, the content of P nolonger shows. and it all happens on 1 page.
there is nothing paradox in there. the URI you send the form values to simply does not contain the GET parameters ($_SERVER['PHP_SELF'] does only contain the file name) youd either use $_SERVER['REQUEST_URI'] or append the GET using $_SERVER['QUERY_STRING'].

14 4385
Dormilich
8,651 Expert Mod 8TB
what data type is user_id in your DB?
Feb 16 '10 #2
whitep8
65
The data type is an INT, the same as owner.

The wierd thing is if it totally remove the user_id it all works fine
Feb 16 '10 #3
Dormilich
8,651 Expert Mod 8TB
how about passing user_id with PDO::PARAM_INT ?

PS. if the statement is executed only once, $stmt->bindValue() should suffice.
Feb 16 '10 #4
whitep8
65
I have amended my code to echo out the variables at each stage. The issue is that the P variable is assigned by GET when the form is displayed, but on clicking submit, its value is lost.

"Inside" and "outside" echo 1, but after submit the following is shown

top u1
top p
inside u1
inside p
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'item_id' cannot be null

outside u1
outside p

here is my amended code

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. session_start();
  3. echo "top u";
  4. echo $_SESSION['user_id'];
  5. echo "<br>";
  6. echo "top p";
  7. echo $_GET['p'];
  8. /*** check if a file was submitted ***/
  9. if(!isset($_FILES['userfile']))
  10.     {
  11.     echo '<p>Please select a file</p>';
  12.     }
  13. else
  14.     {
  15.     try    {
  16.         upload();
  17.         /*** give praise and thanks to the php gods ***/
  18.         echo '<p>Thank you for submitting</p>';
  19.         }
  20.     catch(Exception $e)
  21.         {
  22.         echo '<h4>'.$e->getMessage().'</h4>';
  23.         }
  24.     }
  25. ?>
  26. <?php
  27. echo "<br>";
  28. echo "outside u";
  29. echo $_SESSION['user_id'];
  30. echo "<br>";
  31. echo "outside p";
  32.  
  33. echo $user_id = $_GET['p'];
  34. ;
  35. /**
  36.  *
  37.  * the upload function
  38.  * 
  39.  * @access public
  40.  *
  41.  * @return void
  42.  *
  43.  */
  44. function upload(){
  45.     echo "<br>";
  46.     echo "inside u";
  47. echo $_SESSION['user_id'];
  48. echo "<br>";
  49. echo "inside p";
  50. $owner_id =  $_GET['p'];
  51. /*** check if a file was uploaded ***/
  52. if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
  53.     {
  54.     /***  get the image info. ***/
  55.  
  56.     $size = getimagesize($_FILES['userfile']['tmp_name']);
  57.     /*** assign our variables ***/
  58.     $owner_id = $_REQUEST['id'];
  59.     $user_id = $_SESSION['user_id'];    
  60.     $type = $size['mime'];
  61.     $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
  62.     $size = $size[3];
  63.     $name = $_FILES['userfile']['name'];
  64.     $maxsize = 99999999;
  65.  
  66.  
  67.     /***  check the file is less than the maximum file size ***/
  68.     if($_FILES['userfile']['size'] < $maxsize )
  69.         {
  70.         /*** connect to db ***/
  71.         $dbh = new PDO("mysql:host=localhost;dbname=db", 'un', 'pw');
  72.  
  73.                 /*** set the error mode ***/
  74.                 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  75.  
  76.             /*** our sql query ***/
  77.         $stmt = $dbh->prepare("INSERT INTO testblob (user_id, item_id, image_type ,image, image_size, image_name) VALUES (?, ?, ? ,?, ?, ?)");
  78.  
  79.         /*** bind the params ***/
  80.         $stmt->bindParam(1, $user_id, PDO::PARAM_INT);
  81.         $stmt->bindParam(2, $owner_id, PDO::PARAM_INT);
  82.         $stmt->bindParam(3, $type);
  83.         $stmt->bindParam(4, $imgfp, PDO::PARAM_LOB);
  84.         $stmt->bindParam(5, $size);
  85.         $stmt->bindParam(6, $name);
  86.  
  87.         /*** execute the query ***/
  88.         $stmt->execute();
  89.         }
  90.     else
  91.         {
  92.         /*** throw an exception is image is not of type ***/
  93.         throw new Exception("File Size Error");
  94.         }
  95.     }
  96. else
  97.     {
  98.     // if the file is not less than the maximum allowed, print an error
  99.     throw new Exception("Unsupported Image Format!");
  100.     }
  101. }
  102. ?> 
  103. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  104.   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  105.  
  106.   <html>
  107.   <head><title>File Upload To Database</title></head>
  108.   <body>
  109.   <h2>Please Choose a File and click Submit</h2>
  110.   <form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>" method="post">
  111.   <input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
  112.   <div><input name="userfile" type="file" /></div>
  113.   <div><input type="submit" value="Submit" /></div>
  114.   </form>
  115.  
  116. </body></html>
  117.  
Feb 16 '10 #5
Dormilich
8,651 Expert Mod 8TB
I have amended my code to echo out the variables at each stage. The issue is that the P variable is assigned by GET when the form is displayed, but on clicking submit, its value is lost.
if these values don’t show up in the form’s action attribute, they are never there from the beginning.
Feb 16 '10 #6
whitep8
65
This is the paradox i dont understand.

On page load, i echo out the two values, 1 from session and 1 from get. The values exist.

On form submit, the content of P nolonger shows. and it all happens on 1 page.

I have 3 echo's. top, outside and inside. The outside and inside refer to the upload(). When isset occurs its losing that value.

The screen output before selecting the file is:

top u1
top p1

Please select a file

outside u1
outside p1
Feb 16 '10 #7
Dormilich
8,651 Expert Mod 8TB
This is the paradox i dont understand.

On page load, i echo out the two values, 1 from session and 1 from get. The values exist.

On form submit, the content of P nolonger shows. and it all happens on 1 page.
there is nothing paradox in there. the URI you send the form values to simply does not contain the GET parameters ($_SERVER['PHP_SELF'] does only contain the file name) youd either use $_SERVER['REQUEST_URI'] or append the GET using $_SERVER['QUERY_STRING'].
Feb 16 '10 #8
whitep8
65
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
Feb 16 '10 #9
Dormilich
8,651 Expert Mod 8TB
sorry if I’m sounding rude, but did you understand, what I tried to tell you the last 2 posts?
Feb 16 '10 #10
whitep8
65
sorry i didnt refresh, just seen them now.
Feb 16 '10 #11
whitep8
65
Ok, so the session variable is fine as its global.

Please could you help me with the syntax and how i could amend it?

I appreciate your time
Feb 16 '10 #12
Dormilich
8,651 Expert Mod 8TB
post #8.
Feb 16 '10 #13
whitep8
65
Hi,

I have finally managed to get it working!! Took a while and a lot of scrap paper.

I was calling the url information in the wrong place. I moved it down to the form and then added two hidden elements that posted the variable to SELF then carried on as normal.

Thank you very much for your patience and help with this
Feb 16 '10 #14
Dormilich
8,651 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. <form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['REQUEST_URI']);?>" method="post">
didn’t work?
Feb 16 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Lenz Grimmer | last post: by
3 posts views Thread by Kirk Soodhalter | last post: by
10 posts views Thread by Caffeneide | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.