sql statement is prepared but not executed (according to log)

Question

So, I'm using usercake (open source php mysql combination for user management). Anyways I've got it installed and working perfectly no problems. Since I'd like my registration to not include both a displayname and username I've removed the displayname throughout all code this includes mysqldb and any other place it is called or used in vanilla install. My old users's can still login and functional normally, however I have an error when registering new users That i can't wuite figure out (I'm fairly new to mysql/php so im hoping someone here can help me explain my log files as this is first time i've ever needed to refer to them) first here is the code to actually prepare data/insert into mysql (all escaping and cleaning left out)

  $stmt = $mysqli->prepare("INSERT INTO ".$db_table_prefix."users (
          user_name,
          password,
          email,
          activation_token,
          last_activation_request,
          lost_password_request, 
          active,
          title,
          sign_up_stamp,
          last_sign_in_stamp
          )
          VALUES (
          ?,
          ?,
          ?,
          ?,
          '".time()."',
          '0',
          ?,
          'New Member',
          '".time()."',
          '0'
          )");

 $stmt->bind_param("sssssi", $this->username, $secure_pass, $this->clean_email, $this->activation_token, $this->user_active);
         $stmt->execute();
         $inserted_id = $mysqli->insert_id;
         $stmt->close();

 //Insert default permission into matches table
 $stmt = $mysqli->prepare("INSERT INTO ".$db_table_prefix."user_permission_matches  (
        user_id,
        permission_id
        )
        VALUES (
        ?,
        '1'
        )");
 $stmt->bind_param("s", $inserted_id);
 $stmt->execute();
 $stmt->close();

So everything above I think (keyword) is all good now here the log mysql log file from when the above code is called

           65 Prepare   INSERT INTO uc_users (
                user_name,
                password,
                email,
                activation_token,
                last_activation_request,
                lost_password_request, 
                active,
                title,
                sign_up_stamp,
                last_sign_in_stamp
                )
                VALUES (
                ?,
                ?,
                ?,
                ?,
                '1365565745',
                '0',
                ?,
                'New Member',
                '1365565745',
                '0'
                )
       65 Close stmt    
       65 Prepare   INSERT INTO uc_user_permission_matches  (
                user_id,
                permission_id
                )
                VALUES (
                ?,
                '1'
                )
       65 Execute   INSERT INTO uc_user_permission_matches  (
                user_id,
                permission_id
                )
                VALUES (
                '0',
                '1'
                )

Now there are two things i do not understand with everything posted above the first and more relevant is why is Execute INSERT INTO uc_user (... never called? secondly why does the rest of the code execute (and somehow take the user_id inserted into uc_user) and properly pass that into uc_user_permision_matches ?

part2 to anyone more familiar with inner working of UserCake what is this value bound to sssssi as i can not find a reference to it elsewhere ?

Are the queries wrapped in a transaction? 'ssssiii' simply indicates the placeholders (by datatype) to be expected during the binding process (s = string, i = int). — Mike Purcell, Apr 11 '13 at 00:11
okay, no the queries are not wrapped in a transaction They are simply executed in order no db rollback is in place at this time (which explains `uc_user_permission_matches` update). However why does my the insert into uc_user not get executed? ...this is my main problem currently — brendosthoughts, Apr 11 '13 at 00:47

score 2 · Accepted Answer · answered Apr 11 '13 at 00:51

2

Looks like the issue is that MySQL is rejecting the insert query for INSERT INTO uc_user_permission_matches, because the user_id is 0, which leads me to believe the previous query didn't complete correctly.

It looks like in your first query you have 5 placeholders, but you are attempting to bind 6, try this:

// Remove one of the s placeholders 
$stmt->bind_param("ssssi", $this->username, $secure_pass, $this->clean_email, $this->activation_token, $this->user_active);

answered Apr 11 '13 at 00:51

Mike Purcell

19,847
10
52
89

okay, your definately right about that, now i need all those variable s binded so, ive added an extra placeholder instead is a `?` and table name should do it then? – brendosthoughts Apr 11 '13 at 01:12

sql statement is prepared but not executed (according to log)

1 Answers1