Adding multiple entries to a table

Question

I am trying to register customers for a continuing education web site I am creating and need to add multiple entries to the phpMyAdmin table "users" for registration purposes. I am trying to add multiple entries, 25 total.

As you will see, I have tried the mysqli_multi_query() function to add them all but I cannot create a new record of those entries.

It shows that I am connected to the database and I have checked all values in the code with those in the table and they are ordered. So my questions are:

Is there a limit of entries per table?
Is it best to add few entries at a time than a sign-in page with multiple lines?
Am I trying to do too much in one file and need to split my job?

Error I am getting:

You are connected to the database. Error: INSERT INTO users (myName, home1, home2) VALUES (?, ?, ?);INSERT INTO users (city, ste, zip) VALUES (?, ?, ?);INSERT INTO users (email, certification, experience) VALUES (?, ?, ?);INSERT INTO users (employer, marketing, gender) VALUES (?, ?, ?);INSERT INTO users (dob, recert, full_name) VALUES (?, ?, ?);INSERT INTO users (phone, bHome1, bHome2) VALUES (?, ?, ?);INSERT INTO users (bCity, bState, bZip) VALUES (?, ?, ?);INSERT INTO users (payment, cardNum, expDate) VALUES (?, ?, ?);INSERT INTO users (pwd) VALUES (?);
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 '?, ?, ?);INSERT INTO users (city, ste, zip) VALUES (?, ?, ?);INSERT INTO users (' at line 1

The code so far validates all entries, checks if there are blank entries, and uses the function test-input. Any help is appreciated, including sources from where to learn PHP that worked better for your education. Thanks in advance and thank you for listening.

" . mysqli_error($con);
    }


    $hashPwd = password_hash($pwd, PASSWORD_DEFAULT);

    $stmt->bind_param("sssssssssssssssssssssssss", $myName, $home1, $home2, $city, $ste, $zip, 
        $email, $certification, $experience, $employer, $marketing, $gender, $dob, $recert, 
        $full_name, $phone, $bHome1, $bHome2, $bCity, $bState, $bZip, $payment, $cardNum,
        $expDate, $hashPwd); 
      
    mysqli_close($con);
}

Rager · Accepted Answer

You need to prepare your sql, bind the params and then execute. Forget the mysqli functions.

 $sql = "INSERT INTO TABLE users (myName, home1, home2, city, ste, zip,   email, employer, marketing, gender, certification, experience, dob, recert, full_name, phone, bHome1, bHome2, bCity, bState, bZip, payment, cardNum, expDate, pwd) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

 $stmt = $con->prepare($sql);
    
 $hashPwd = password_hash($pwd, PASSWORD_DEFAULT);
    
 $stmt->bind_param("sssssssssssssssssssssssss", $myName, $home1, $home2, $city, $ste, $zip, 
            $email, $certification, $experience, $employer, $marketing, $gender, $dob, $recert, 
            $full_name, $phone, $bHome1, $bHome2, $bCity, $bState, $bZip, $payment, $cardNum,
            $expDate, $hashPwd); 

 $stmt->execute();
      
 mysqli_close($con);

You're getting that error because mysql doesn't know what ? is. You are litterally try to execute INSERT INTO users (city, ste, zip) VALUES (?, ?, ?); which is not valid sql. The variables have to be converted first.

Also, this might be a little advanced for you but you can definitely refactor a lot of redundant code out of this... Just practice and you'll get it!

Here's a rough in of what I'm talking about

if ($_SERVER["REQUEST_METHOD"] != "POST") { 
    //Better to exit on smaller if then wrap everything in if statement.
    die();
}

$list = [
    'myName' => [ 'type' => 's', 'value' => '', 'err' => 'Name required.'],
    'home1' => [ 'type' => 's', 'value' => '', 'err' => 'Address required.'],
    'home2' => [ 'type' => 's', 'value' => '', 'err' => '']
    // Complete all your entries
];

$hasErr = false;
foreach($list as $key => &$item){
    if (empty($_POST[$key])) {
        $item['value'] = $item['err'];
    } else {
        $hasErr = true;
        $item['value'] = test_input($_POST[$key]);
        switch($key){
            case'myName':
                if (!preg_match("/^[a-zA-Z-' -.]*$/", $item['value'])) {
                    $item['value'] = "Only letters and white space allowed";
                }
                break;
            // Add more casses for more special proccessing.
        }
    }
}
unset($item); //Always unset pointers after loop.

if(!$hasErr){
    $sql = "INSERT INTO users(";
    $sqlCols = [];
    $sqlVals = [];

    foreach($list as $key => $item){
        $sqlCols[] = $key;
        $sqlVals[] = "?";
    }
    $sql .= implode(",", $sqlCols) . ") values ( " . implode(",", $sqlVals ). " )";
    $stmt->prepare($sql);
    foreach($list as $key => $item){
        // Actually not sure this is possible, worth a shot though.
        $stmt->pind_param($item['type'], $item['value']);
    }
    $stm->execute();
} else{
    //Handle error
}

Adding multiple entries to a table

Answers (2)

Related Questions