MySQL INSERT error via PHP

[nickfernando]

Hi All,

 

I haven't posted on a forum before, I've checked the rules and hope I've managed to adhere as best I can. If not please excuse any misdemeanors or lack of appropriate etiquette.

 

I've been modifying PDO based scripts i found on phpro.org to work in a simple php 4 friendly way. The trouble is, I'm rubbish at PHP!  :'( While I've not had too much trouble editing the ones which pull the images back out of the database, the one for putting stuff in is driving me bananas ??? I keep getting the following error:- Error adding submitted Slide: 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 2 -: which is a result of failing to successfully execute the INSERT into the table tbl_sld.

 

The table structure is:

 

-- Table structure for table `tbl_sld`
--

CREATE TABLE `tbl_sld` (
  `slideID` int(6) NOT NULL auto_increment,
  `slideNo` smallint(2) default NULL,
  `slideType` varchar(25) NOT NULL,
  `slideData` longblob,
  `slideHeight` int(5) NOT NULL,
  `slideWidth` int(5) NOT NULL,
  `slideThumb` blob NOT NULL,
  `thumbHeight` int(5) NOT NULL,
  `thumbWidth` int(5) NOT NULL,
  `slideName` varchar(50) NOT NULL,
  `slideInfo` varchar(255) default NULL,
  `rspText` varchar(255) default NULL,
  `presNo` smallint(6) default NULL,
  `qsNo` smallint(6) default NULL,
  PRIMARY KEY  (`slideID`),
  KEY `tbl_prestbl_sld` (`presNo`),
  KEY `tbl_qstbl_sld` (`qsNo`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=47 ;

--

 

The code that does the uploading bit is as below:

 

 $slideID      = '';
    $slideNo	  = $x; 
    $image_type   = $size['mime'];
    $imgfp        = fopen($_FILES['uploadFile' . $x]['tmp_name'], 'rb');
    $image_width  = $size[0];
    $image_height = $size[1];
    $image_size   = $size[3];
    $image_name   = $_FILES['uploadFile' . $x]['name'];
    $slideInfo    = $file_desc;
    $rspText      = '';
    $presNo      = $_SESSION['pres_no'];
    $qsNo         = rand(1,6);
    $maxsize      = 1000000;

    /***  check the file is less than the maximum file size ***/
    if($_FILES['uploadFile' . $x]['size'] < $maxsize )
        {
        /*** create a second variable for the thumbnail ***/
        $thumb_data = $_FILES['uploadFile' . $x]['tmp_name'];

        /*** get the aspect ratio (height / width) ***/
        $aspectRatio=(float)($size[0] / $size[1]);

        /*** the height of the thumbnail ***/
        $thumb_height = 100;

        /*** the thumb width is the thumb height/aspectratio ***/
        $thumb_width = $thumb_height * $aspectRatio;

        /***  get the image source ***/
        $src = imagecreatefromjpeg($thumb_data);

        /*** create the destination image ***/
        $destImage = ImageCreateTrueColor($thumb_width, $thumb_height);

        /*** copy and resize the src image to the dest image ***/
        ImageCopyResampled($destImage, $src, 0,0,0,0, $thumb_width, $thumb_height, $size[0], $size[1]);

        /*** start output buffering ***/
        ob_start();

        /***  export the image ***/
        imageJPEG($destImage);

        /*** stick the image content in a variable ***/
        $image_thumb = ob_get_contents();

        /*** clean up a little ***/
        ob_end_clean();

        /*** connect to db ***/
        include("dbconn.php");
     
        /*** prepare the sql***/ 
        $sql = "INSERT INTO tbl_sld (slideID, slideNo, slideType ,slideData, slideHeight, slideWidth, slideThumb, thumbHeight, thumbWidth, slideName, slideInfo, rspText, presNo, qsNo)
        VALUES ('{$slideID}','{$slideNo}' , '{$image_type}', '{$imgfp}', '{$image_height}', '{$image_width}', '{$image_thumb}', '{$thumb_height}', '{$thumb_width}', '{$image_name}', '{$slideInfo}', '{$rspText}', '{$presNo}', '{$qsNo}') ";
        
       
        /*** prepare the sql ***/
        // $sql = "INSERT INTO tbl_sld SET slideNo ='$slideNo', slideType = '$image_type', slideData = '$imgfp', slideHeight = $image_height, slideWidth = $image_width, slideThumb = '$image_thumb', thumbHeight = $thumb_height, thumbWidth = $thumb_width, slideName = $image_name, slideInfo = $slideInfo, rspText = '$rspText', presNo = $presNo, qsNo = $qsNo";
        /*** execute the query ***/
        if (@mysql_query($sql)) {
      echo '<p>Your Slide has been added.</p>';
    } else {
      echo '<p>Error adding submitted Slide: ' .
          mysql_error() . '</p>';
    }

 

If anybody can tell me what is going wrong, it would be greatly appreciated. I've searched through similar posts and made suggested changes, but while these solved the problem for the original posters, mine still persists 

[Barand]

Just before you execute the query,

 

echo $sql;

 

Usually it's easy to spot the error when you see what is actually being executed.

[nickfernando]

Hi Barand,

 

Thanks that was extremely helpful. Referring back to my original post, the echo of $sql shows:

 

 slideNo ='0', slideType = 'image/jpeg', slideData =[bINARY GOOP],
slideHeight = '600', slideWidth = '800', slideThumb =[bINARY GOOP] 
//none of the remaining fields in the INSERT statement are displayed at all

 

and then the original error: 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

 

That would narrow it down to thumbHeight, thumbWidth, slideName, slideInfo, rspText, presNo, qsNo. I thought it might be the fact that thumbWidth is a float and the data type for it in the DB is int, so I used round() but no joy.

 

You wouldn't be able to give me any tips as to what it could possibly be could you?

 

Many Thanks

[nickfernando]

I tested an INSERT with the following dummy data via phpMyAdmin:

 

INSERT INTO tbl_sld (slideID, slideNo, slideType ,slideData, slideHeight, slideWidth, slideThumb, thumbHeight, thumbWidth, slideName, slideInfo, rspText, presNo, qsNo) VALUES ('','32','image/jpeg','11111','800','600','2222','160','120','slide01.jpg','hi mum','','11','2')

 

And that worked. So I'm still at a loss as to why mine is not...please help...getting severe depression...

[Barand]

..., slideInfo = $slideInfo, ...

 

should be quoted

 

..., slideInfo = '$slideInfo', ...

 

In fact, if some of the other unquoted (numeric) values are blank you get an error as the query reads

 

SET x =, y=, .... etc

[nickfernando]

Many thanks. I think I've isolated the problem as now everything inserts....

 

        $sql = "INSERT INTO tbl_sld (
        slideID, slideNo, 
        slideType ,slideData, 
        slideHeight, slideWidth,
        thumbHeight, thumbWidth,
        slideName, slideInfo, 
        rspText, presNo, 
        qsNo)
        VALUES (
        '{$slideID}', '{$slideNo}', 
        '{$image_type}', '{$content}', 
        '{$image_height}', '{$image_width}',
        '{$thumb_height}','{$thumb_width}',
        '{$image_name}', '{$slideInfo}',
        '{$rspText}', '{$presNo}',
        '{$qsNo}'
        ) ";

 

 

 

....apart from $image_thumb which when I attempt to insert it into the table throws the error: 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 ''

 

Would you be kind enough to have a look at the original post and give me a pointer. The only thing I've done is strip out the PDO code and swap it with my own brand of flakiness  If I subbed the PDO bits back the whole thing works without a hitch. This is really baffling!!  ???

 

Any suggestions you can provide would be gratefully received.

[nickfernando]

I've tried to insert the binary data for the thumbnail separately:

 

      $sql = "INSERT INTO tbl_sld (
        slideID, slideNo, 
        slideType ,slideData, 
        slideHeight, slideWidth,
        thumbHeight, thumbWidth,
        slideName, slideInfo, 
        rspText, presNo, 
        qsNo)
        VALUES (
        '{$slideID}', '{$slideNo}', 
        '{$image_type}', '{$content}', 
        '{$image_height}', '{$image_width}',
        '{$thumb_height}','{$thumb_width}',
        '{$image_name}', '{$slideInfo}',
        '{$rspText}', '{$presNo}',
        '{$qsNo}'
        ) ";
        $sql_2 = "INSERT INTO tbl_sld SET thumbData = '$image_thumb'";

     ob_start();
        $thumb = mysql_query($sql_2);
        $slide = mysql_query($sql);
    	ob_end_clean();    
        if ($slide) {
      echo '<p>Your Slide has been added.</p>';
    } else {
      echo '<p>Error adding submitted Slide: ' .
          mysql_error() . '</p>';
    }
	if ($thumb) {
      echo '<p>A thumbnail has been created for this slide.</p>';
    } else {
      echo '<p>Error creating thumbnail: ' .
          mysql_error() . '</p>';
    }

 

I get the Error creating thumbnail, but not the mysql_error. Does anybody have any ideas?

[nickfernando]

I spotted that I set the column as thumbData in the second insert statement and corrected to slideThumb but still no joy in getting an error to show.

[nickfernando]

I'm appealing to anybody out there who may be able to help...i'm getting really frustrated and can't figure this out.