PHP PDO MYSQL problem

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • whitep8
    New Member
    • Oct 2009
    • 65
    #1

    PHP PDO MYSQL problem

    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

    Code:
    <?php

/*** 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

/**
 *
 * the upload function
 * 
 * @access public
 *
 * @return void
 *
 */
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'];
    echo $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);
		$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!");
    }
}
?> 
<!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>
    Tags: action
    • Dormilich
      Recognized Expert Expert
      • Aug 2008
      • 8694
      #2
      what data type is user_id in your DB?

        Comment

        • whitep8
          New Member
          • Oct 2009
          • 65
          #3
          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

            Comment

            • Dormilich
              Recognized Expert Expert
              • Aug 2008
              • 8694
              #4
              how about passing user_id with PDO::PARAM_INT ?

              PS. if the statement is executed only once, $stmt->bindValue() should suffice.

                Comment

                • whitep8
                  New Member
                  • Oct 2009
                  • 65
                  #5
                  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

                  Code:
                  <?php
session_start();
echo "top u";
echo $_SESSION['user_id'];
echo "<br>";
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 "<br>";
echo "outside u";
echo $_SESSION['user_id'];
echo "<br>";
echo "outside p";

echo $user_id = $_GET['p'];
;
/**
 *
 * the upload function
 * 
 * @access public
 *
 * @return void
 *
 */
function upload(){
	echo "<br>";
	echo "inside u";
echo $_SESSION['user_id'];
echo "<br>";
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>

                    Comment

                    • Dormilich
                      Recognized Expert Expert
                      • Aug 2008
                      • 8694
                      #6
                      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.

                        Comment

                        • whitep8
                          New Member
                          • Oct 2009
                          • 65
                          #7
                          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

                            Comment

                            • Dormilich
                              Recognized Expert Expert
                              • Aug 2008
                              • 8694
                              #8
                              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) you’d either use $_SERVER['REQUEST_URI'] or append the GET using $_SERVER['QUERY_STRING'].

                                Comment

                                • whitep8
                                  New Member
                                  • Oct 2009
                                  • 65
                                  #9
                                  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

                                    Comment

                                    • Dormilich
                                      Recognized Expert Expert
                                      • Aug 2008
                                      • 8694
                                      #10
                                      sorry if I’m sounding rude, but did you understand, what I tried to tell you the last 2 posts?

                                        Comment

                                        • whitep8
                                          New Member
                                          • Oct 2009
                                          • 65
                                          #11
                                          sorry i didnt refresh, just seen them now.

                                            Comment

                                            • whitep8
                                              New Member
                                              • Oct 2009
                                              • 65
                                              #12
                                              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

                                                Comment

                                                • Dormilich
                                                  Recognized Expert Expert
                                                  • Aug 2008
                                                  • 8694
                                                  #13
                                                  post #8.

                                                    Comment

                                                    • whitep8
                                                      New Member
                                                      • Oct 2009
                                                      • 65
                                                      #14
                                                      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

                                                        Comment

                                                        • Dormilich
                                                          Recognized Expert Expert
                                                          • Aug 2008
                                                          • 8694
                                                          #15
                                                          Code:
                                                          <form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['REQUEST_URI']);?>" method="post">
                                                          didn’t work?

                                                            Comment

                                                            Previous template Next
                                                            Working...