CODEWITHSUNDEEP
phpmysqlinsertinnodb
Vitor Durante
Vitor Durante

Reputation: 1063

Multiple Inserts not Working with INNODB

I have a script that inserts users in the database. It used to work with MYISAM, but since I changed to INNODB, it only inserts the 1st row, then it stops.

This is the script. It basicaly gets all facebook friends and checks if the users are in the database. If it ain't, it'll create a new "unregistered" user. Since I changed to INNODB, it only inserts the first user. I'll also post the table creation code. Any tips?

$data = explode(";", $_POST['data']);
for($i=0; $i<count($data); $i++){
    $user = explode("^", $data[$i]);
    $fname = $user[0];
    $lname = $user[1];
    $face_uid = $user[2];
    $birthday = $user[3];
    $gender = $user[4];

    $temp_birth = explode("/", $birthday);
        if(count($temp_birth)!=3){
            $birthday="0000-00-00";
        } else {
            $birthday = $temp_birth[2]."-".$temp_birth[0]."-".$temp_birth[1];
        }

        if(ctype_alnum(str_replace(" ", "", $fname)) && ctype_alnum(str_replace(" ", "", $lname))){
            $result = mysql_query("SELECT * FROM popguest.user WHERE face_uid='$face_uid'");
            $row=mysql_fetch_array($result);
            if(mysql_num_rows($result)==0){

                $chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";  
                $size = strlen( $chars );
                $verify=0;
                while($verify==0){
                    $username='';
                    for($j = 0; $j < 10; $j++) {
                        $username .= $chars[ rand( 0, $size - 1 ) ];
                    }
                    $result2 = mysql_query("SELECT * FROM popguest.user WHERE username='$username'");
                    if(mysql_num_rows($result2)!=0){
                        $verify=0;
                    }else{
                        $verify=1;
                    }
                }

                $result = mysql_query("INSERT INTO popguest.user (face_uid, username, fname, lname, birthday, gender, registered) VALUES ('$face_uid', '$username', '$fname', '$lname', '$birthday', '$gender', '0')");

                echo $fname." ".$lname.";".$face_uid.";".$birthday.";".$gender."\n";

            } else {

            }
        } else {

        }
}

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(10) COLLATE latin1_general_ci NOT NULL,
 `face_uid` varchar(15) COLLATE latin1_general_ci NOT NULL,
 `fname` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `lname` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `email` varchar(50) COLLATE latin1_general_ci NOT NULL,
 `gender` varchar(6) COLLATE latin1_general_ci NOT NULL,
 `birthday` date NOT NULL,
 `type` int(1) NOT NULL,
 `registered` int(1) NOT NULL,
 `phone` varchar(15) COLLATE latin1_general_ci NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`),
 UNIQUE KEY `face_uid` (`face_uid`),
 UNIQUE KEY `email` (`email`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Upvotes: 0

Views: 187

Answers (1)

Glenn Plas
Glenn Plas

Reputation: 1638

There are a couple of things I would look at:

  • You don't mention if you get an error, your code isn't made for trapping one either, so I would start with adding debug to the script and check for mysql errors, e.g $error=mysql_errno($link) , trigger_error($error, E_USER_WARNING) kind.
  • You seem to be using latin1 collation, I wonder what happens if you happen to import a user with a username not belonging to that char set. I would use utf8 all the way for this. If the second user has that sort of data in there, my first suggestion would tell you why.

Upvotes: 1

Related Questions