Insert statement error

lukeurtnowski · July 6, 2014, 10:59pm

I have a table set up like


[FONT=arial]CREATE TABLE Properties([/FONT]
[FONT=arial]id TINYINT NOT NULL AUTO_INCREMENT,[/FONT]
[FONT=arial]PRIMARY KEY(id),[/FONT]
[FONT=arial]title VARCHAR(50),[/FONT]
[FONT=arial]building TINYINT,[/FONT]
[FONT=arial]unit sMALLINT,[/FONT]
[FONT=arial]squarefeet VARCHAR(50),[/FONT]
[FONT=arial]baths TINYINT,[/FONT]
[FONT=arial]rooms TINYINT,[/FONT]
[FONT=arial]saleprice DOUBLE,[/FONT]
[FONT=arial]rentprice DOUBLE,[/FONT]
[FONT=arial]type TINYINT,[/FONT]
[FONT=arial]description TEXT,[/FONT]
[FONT=arial]image1 VARCHAR(150),[/FONT]
[FONT=arial]image2 VARCHAR(150),[/FONT]
[FONT=arial]image3 VARCHAR(150),[/FONT]
[FONT=arial]image4 VARCHAR(150),[/FONT]
[FONT=arial]image5 VARCHAR(150),[/FONT]
[FONT=arial]image6 VARCHAR(150),[/FONT]
[FONT=arial]image7 VARCHAR(150),[/FONT]
[FONT=arial]image8 VARCHAR(150),[/FONT]
[FONT=arial]created DATE,[/FONT]
[FONT=arial]display CHAR(1)[/FONT]
[FONT=arial]);[/FONT]

But when I try to insert a record, I get an error…I also output all the _POST variables for good measure (screenshot)

did I set something up wrong?

r937 · July 7, 2014, 12:57am

try your INSERT statement directly in mysql (i.e. not via php) using hardcoded values

that should determine whether you have a database problem or a php problem

lukeurtnowski · July 7, 2014, 5:45pm

I’m trying to make my form as secure as possible

So I submitted it using only the required info and get

Here’s my php code when I try to echo the query

<?php
var_dump($_POST);


$title = $_POST['title'];
$bldg = $_POST['bldg'];
$unit = $_POST['unit'];
$area = $_POST['area'];
$baths = $_POST['baths'];
$rooms = $_POST['rooms'];
$sale = $_POST['sale'];
$rent = $_POST['rent'];
$type = $_POST['type'];
$desc = $_POST['desc'];
$area = $_POST['area'];
$image1 = $_POST['image1'];
$image2 = $_POST['image2'];
$image3 = $_POST['image3'];
$image4 = $_POST['image4'];
$image5 = $_POST['image5'];
$image6 = $_POST['image6'];
$image7 = $_POST['image7'];
$image8 = $_POST['image8'];
$display = $_POST['display'];
$date_created = $_POST['date_created'];


$username = "root";
$password = "";


try {
  $pdo = new PDO('mysql:host=localhost;dbname=shoresrentals', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('INSERT INTO Properties (title,building,unit,squarefeet,baths,rooms,saleprice,rentprice,type,description,image1,image2,image3,image4,image5,image6,image7,image8,created,display) VALUES title,:bldg,:unit,:area,:baths,:rooms,:sale,:rent,:type,:desc,:image1,:image2,:image3,:image4,:image5,:image6,:image7,:image8,,:date_created,:display)');
  
  $stmt->execute(array(
    ':title' => $title,
    ':bldg' => $baths,
    ':unit' => $title,
    ':area' => $area,
    ':baths' => $baths,
    ':rooms' => $rooms,
    ':sale' => $sale,
    ':rent' => $rent,
    ':type' => $type,
    ':desc' => $desc,
    ':image1' => $image1,
    ':image2' => $image2,
    ':image3' => $image3,
    ':image4' => $image4,
    ':image5' => $image5,
    ':image6' => $image6,
    ':image7' => $image7,
    ':image8' => $image8,
    ':display' => $display,
    ':date_created' => $date_created
  ));
echo $stmt;  
 
  # Affected Rows?
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

I looked at the structure of the database with phpmyadmin

I’m not sure where the error is, doesn’t the table allow me to insert nothing into a field?

r937 · July 7, 2014, 6:36pm

unless you want to pursue this as a php problem right away (in which case we should move the thread to the php forum), all i can do is repeat my earlier request that you run the query without all the php variables, with hardcoded values, directly in mysql, i.e. not from php

that should determine whether you have a database problem or a php problem

SpacePhoenix · July 7, 2014, 7:25pm

With regard to the database structure, can you be 100% sure that there will only be exactly 8 photos for a property? If there’s less then it’ll be covered but what if there is more then 8 photos for a given property?

r937 · July 7, 2014, 7:42pm

nice try, spacephoenix

luke, your error message is pointing to your date value

so there could be a missing value in front of that, which would be two consecutive commas, hence the syntax error

so, yeah… php problem

SpacePhoenix · July 8, 2014, 6:22am

@lukeurtnowski; try laying out your query using “leading commas”, an example is shown below, it’s the query in post number 3 of this thread, it helps to spot things like stray commas


<?php
$sql = "
INSERT INTO
    Properties
    (
          title
        , building
        , unit
        , squarefeet
        , baths
        , rooms
        , saleprice
        , rentprice
        , type
        , description
        , image1
        , image2
        , image3
        , image4
        , image5
        , image6
        , image7
        , image8
        , created
        , display
    )
    VALUES 
        (
              title
            , :bldg
            , :unit
            , :area
            , :baths
            , :rooms
            , :sale
            , :rent
            , :type
            , :desc
            , :image1
            , :image2
            , :image3
            , :image4
            , :image5
            , :image6
            , :image7
            , :image8
            ,
            , :date_created
            , :display
        )
";
$stmt = $pdo->prepare($sql);

lukeurtnowski · July 8, 2014, 5:33pm

ok, will do…thx

lukeurtnowski · July 8, 2014, 5:41pm

I naver knew you could break the query down like that

r937 · July 8, 2014, 6:18pm

formatting your sql is de rigueur, but the leading comma convention will save you many tears of woe

oh, and jenkins – this is not the first time you’ve had the error with commas

lukeurtnowski · July 8, 2014, 6:47pm

ok, changed the PHP code to locate the extra ,
But now I get a critical error when I try to submit the form,

How do I echo out the query (using the PDO format) to see what the problem is?

<?php
var_dump($_POST);


$title = $_POST['title'];
$bldg = $_POST['bldg'];
$unit = $_POST['unit'];
$area = $_POST['area'];
$baths = $_POST['baths'];
$rooms = $_POST['rooms'];
$sale = $_POST['sale'];
$rent = $_POST['rent'];
$type = $_POST['type'];
$desc = $_POST['desc'];
$area = $_POST['area'];
$image1 = $_POST['image1'];
$image2 = $_POST['image2'];
$image3 = $_POST['image3'];
$image4 = $_POST['image4'];
$image5 = $_POST['image5'];
$image6 = $_POST['image6'];
$image7 = $_POST['image7'];
$image8 = $_POST['image8'];
$display = $_POST['display'];
$date_created = $_POST['date_created'];


$username = "root";
$password = "";


try {
  $pdo = new PDO('mysql:host=localhost;dbname=shoresrentals', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('INSERT INTO Properties 
  (title,
  building,
  unit,
  squarefeet,
  baths,
  rooms,
  saleprice,
  rentprice,
  type,
  description,
  image1,
  image2,
  image3,
  image4,
  image5,
  image6,
  image7,
  image8,
  created,
  display) 
  VALUES 
  (:title,
  :bldg,
  :unit,
  :area,
  :baths,
  :rooms,
  :sale,
  :rent,
  :type,
  :desc,
  :image1,
  :image2,
  :image3,
  :image4,
  :image5,
  :image6,
  :image7,
  :image8,
  :date_created,
  :display)');
  
  $stmt->execute(array(
    ':title' => $title,
    ':bldg' => $baths,
    ':unit' => $title,
    ':area' => $area,
    ':baths' => $baths,
    ':rooms' => $rooms,
    ':sale' => $sale,
    ':rent' => $rent,
    ':type' => $type,
    ':desc' => $desc,
    ':image1' => $image1,
    ':image2' => $image2,
    ':image3' => $image3,
    ':image4' => $image4,
    ':image5' => $image5,
    ':image6' => $image6,
    ':image7' => $image7,
    ':image8' => $image8,
    ':display' => $display,
    ':date_created' => $date_created
  ));
echo $stmt;  
 
  # Affected Rows?
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

SpacePhoenix · July 8, 2014, 7:07pm

That’s probably the

echo $stmt;

causing that. Swap that for:

$stmt->debugDumpParamas();

lukeurtnowski · July 8, 2014, 9:31pm

I made the swap, but oh noooes

SpacePhoenix · July 9, 2014, 5:58am

Can you please post the code for line 109?

Drummin · July 10, 2014, 12:45am

Forgive me for budding in but this doesn’t look right to me.

id TINYINT NOT NULL AUTO_INCREMENT,

Shouldn’t the primary auto increment field be INT?

r937 · July 10, 2014, 1:02am

not if the table is never going to have more than 127 rows

Drummin · July 10, 2014, 1:40am

I tend to build my sql as a string within full quotes like SpacePhoenix did in his post above then place it with the prepare statement. It might be due to the fact that your building sql within single quotes.

lukeurtnowski · July 10, 2014, 3:16am

oh it works best when I break everything down like spacephoenix suggested,

Thanks all for your help!

Drummin · July 10, 2014, 3:27am

Glad you got it to work. Your code would probably work if it was in full quotes and no obvious errors like extra comma.

Topic		Replies	Views
Insert statement Databases	5	625	October 8, 2014
Help me solve this? PHP	16	1253	April 10, 2017
Why can't insert data into the database? PHP	21	108568	June 25, 2010
INSERT statement Databases	8	1349	August 13, 2021
Need help with mysqli insert PHP	9	1380	June 16, 2023

Insert statement error

Related topics