Problem with insert using PDO

PHP
1

Hi,
I use PDO for the first time.I succeeded to make a connection and to read a table.
But when I try to insert a record it fails. I looked at many tutorials, and thought I found the right, but it goes through the script without errors, doing nothing. Here is the code I used. The variables I set before, they have values, the connection is made and tested.

    $stmt = $pdo->prepare("INSERT INTO messageboard (parent, uname, mdat)
    VALUES (:parent, :uname, :mdat)");
    $stmt->bindParam(':parent', $parent);
    $stmt->bindParam(':uname', $uname);
    $stmt->bindParam(':mdat', $now);
    $stmt->execute();

It inserts nothing.
Glad for any help.

2

Try omitting the colons from your bindParam statements.

3

There’s nothing wrong on the face of it, presuming that you have those valid column names in your table, and that the data that you are trying to insert is valid. The colons are optional (which I didn’t realise until recently) but I have them in my code and they don’t stop anything working.

If you have a read through that PDO site (https://phpdelusions.net/pdo#reporting_errors) there’s quite a section on how to deal with PDO errors that might prove helpful.

Also note that if $now is just the current date/time, you can save a bit of time by setting the default value for that column to be current_timestamp (or something like that, phpmyadmin will show you) and just not mention it in your insert query. It will then default to current date/time.

1 Like
4

I set timestamp for mdate, that is ok in phpmyadmin.
I also made parent default 0, so the only variable reamaining is uname, which is “Dag”, also shows me with

echo $uname;

Still it does not make any entry. In front of this routine I have a test to read the table, so that I know the connection is established, The routine now look like this:

    try{
    $stmt = $pdo->prepare("INSERT INTO messageboard (uname)
    VALUES (:uname");
    $stmt->bindParam(':uname', $uname);
    $stmt->execute();

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

I see no error and get no error, Could an output (echo) in front of the insert routine have any influence?

5

Any error before could make the script stop working. But there’s a syntax error in your new code because of a missing bracket in the end. Are you sure you implemented all the necessary error reporting settings? You should start here

and also should implement the ini_set stuff.

6

I will try to make the connection exactly as in the tutorial.
But I counted the brackets, seems right to me, if I add one more the script is false.
EDIT:

Syntax error or access violation: 1064 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

Where does this come from? Seems like opening connection. I used “localhost” as host, also tried “127.0.0.1” as in the tutorial.

7

Notice where the highlight color changes

("INSERT INTO messageboard (uname) VALUES (:uname");
8

Somehing wrong there?
I had a linebreak in mine, but I changed that. Didn’t change the result.
The problem right now seems to be: PDO has a problem with MySQL, or the version.
But first after I activated the error trap. Before I did that, I could use the connection to read, at least. You see me confused,

9

You’ve probably been looking at it too long to be able to see it. Maybe indenting will help you see it

( 
  "INSERT INTO messageboard ( 
     uname 
  ) VALUES ( 
    :uname" 
  );
10

This is yours:

( 
  "INSERT INTO messageboard ( 
     uname 
  ) VALUES ( 
    :uname" 
  );

This is mine:

(
"INSERT INTO messageboard (
uname
) VALUES (
:uname"
);

I see no difference.

11

Count the number of left brackets and the number of right brackets…

12

That’s right. But if I add a right bracket the script fails. Means the outputs I have as control disappear and an entry it still doesn’t make. How many traps more…
Perhaps I should make a break. I feel more than silly right now

13

Where are you adding it?

14

At the end. Am I completly blind?

("INSERT INTO messageboard (uname) VALUES (:uname"));
15

I guess they could be called “enclosures”. They come in pairs. eg.

single quotes ' something '
double quotes " something "
angles < something >
square brackets [ something ]
curly braces { something }
parentheses ( something )

They kind of signify “what is between these are a thing” (OK, I realize that doesn’t say it very well. Anyway … )

Code can get messy when there are a lot of them especially when they are nested. I’ll try indenting again. After a “start” I’ll indent the next line until there is a “close” which I’ll put at the same indentation as the previous “start”

( 
  " 
      INSERT INTO messageboard 
    ( 
      uname 
    ) 
      VALUES 
    ( 
      :uname 
    " 
  ) 
);
16

I tried this, also copied what you wrote inserted that in the script. and the script fails. I I have no idea why, because I see you are right, and it is not logical at all. I will,as I said make a break, breatht through and write it all new.

17

That’s because the closing bracket is in the wrong place.

18

Can you write it in one line?

19

Look at the code.
Look at the order of brackets and quote marks.
The indented version may be clearer for you.

20

Once again. I used that in the script and the script fails. I will, as I said, do it all over again, but not right now,