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.
<?php // Defined variables for validation $myNameErr = $home1Err = $home2Err =$cityErr = $steErr = $zipErr = $emailErr = ""; $certificationErr = $experienceErr = $employerErr = $marketingErr = ""; $genderErr = $dobErr = $recertErr = $full_nameErr = $phoneErr = $bHome1Err = ""; $bHome2Err = $bCityErr = $bStateErr = $bZipErr = $paymentErr = $cardNumErr = ""; $expDateErr = $pwdErr = $pwd2Err = ""; $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 = $pwd = $pwd2 = ""; // Validating fields by checking if fields are empty if ($_SERVER["REQUEST_METHOD"] == "POST") { // Checks full name if (empty($_POST['myName'])) { $myNameErr = "Name required."; } else { $myName = test_input($_POST['myName']); // check if name only contains letters and whitespace if (!preg_match("/^[a-zA-Z-' -.]*$/", $myName)) { $myNameErr = "Only letters and white space allowed"; } } // Checks address if (empty($_POST['home1'])) { $home1Err = "Address required."; } else { $home1 = test_input($_POST['home1']); } // Checks additional address input if (empty($_POST['home2'])) { $home2 = test_input($_POST['home2']); } // Checks for city if (empty($_POST['city'])) { $cityErr = "City is required."; } else { $city = test_input($_POST['city']); } // Checks for state if (empty($_POST['ste'])) { $steErr = "State is required."; } else { $ste = test_input($_POST['ste']); } // Checks for zipcode if (empty($_POST['zip'])) { $zipErr = "Zip code is required."; } else { $zip = test_input($_POST['zip']); } // Checks for email and if format is correct if (empty($_POST['email'])) { $emailErr = "Email is required."; } else { $email = test_input($_POST['email']); // check if e-mail address is well-formed if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { $emailErr = "Invalid email format"; } } // Confirms the current email if (empty($_POST['email2'])) { $email2Err = "Confirm your email."; } else { $email2 = test_input($_POST['email2']); // check if e-mail address is well-formed if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { $email2Err = "Invalid email format"; } // Check if emails match if ($email != $email2) { $email2Err = "Emails don't match!"; } } // Checks for modality certification if (empty($_POST['certification'])) { $certificationErr = "Current certification is required."; } else { $certification = test_input($_POST['certification']); } // Checks for years of experience if (empty($_POST['experience'])) { $experienceErr = "Years of experience are required."; } else { $experience = test_input($_POST['experience']); } // Checks for the current employer if (empty($_POST['employer'])) { $employerErr = "Current employer required."; } else { $employer = test_input($_POST['employer']); } // Input about how they heard about us if (empty($_POST['marketing'])) { $marketing = ""; } else { $marketing = test_input($_POST['marketing']); } // Checks for gender if (empty($_POST['gender'])) { $genderErr = "Gender required."; } else { $gender = test_input($_POST['gender']); } // Check the date of birth if (empty($_POST['dob'])) { $dobErr = "Date of birth required."; } else { $dob = test_input($_POST['dob']); } // Checks their end of certification date if (empty($_POST['recert'])) { $recertErr = "Recertification date required."; } else { $recert = test_input($_POST['recert']); } // Checks name as in credit card if (empty($_POST['full_name'])) { $full_nameErr = "Name as written in credit card required."; } else { $full_name = test_input($_POST['full_name']); } // Checks for phone number if (empty($_POST['phone'])) { $phoneErr = "Phone number is required."; } else { $phone = test_input($_POST['phone']); } // Billing Information // Checks for billing address if (empty($_POST['bHome1'])) { $bHome1 = ""; } else { $bHome1 = test_input($_POST['bHome1']); } // Checks for billing address 2 if (empty($_POST['bHome2'])) { $bHome2 = ""; } else { $bHome2 = test_input($_POST['bHome2']); } // Checks for billing city if (empty($_POST['bCity'])) { $bCity = ""; } else { $bCity = test_input($_POST['bCity']); } // Checks for billing state if (empty($_POST['bState'])) { $bState = ""; } else { $bState = test_input($_POST['bState']); } // Checks for billing zip code if (empty($_POST['bZip'])) { $bZip = ""; } else { $bZip = test_input($_POST['bZip']); } // Checks for payment mode if (empty($_POST['payment'])) { $paymentErr = "Mode of payment is required."; } else { $payment = test_input($_POST['payment']); } // Checks for credit card number if (empty($_POST['cardNum'])) { $cardNumErr = "Credit card number required."; } else { $cardNum = test_input($_POST['cardNum']); } // Checks for expiration date if (empty($_POST['expDate'])) { $expDateErr = "Card's expiration date required."; } else { $expDate = test_input($_POST['expDate']); } // Checks for password if (empty($_POST['pwd'])) { $pwdErr = "Password required."; } else { $pwd = test_input($_POST['pwd']); } // Asks to confirm password and if both match if (empty($_POST['pwd2'])) { $pwd2Err = "Confirm your email."; } else { $pwd2 = test_input($_POST['pwd2']); // Check if passwords match if ($pwd != $pwd2) { $pwd2Err = "Passwords don't match!"; } } } function test_input($data) { $data = trim($data); $data = stripslashes($data); $data = htmlspecialchars($data); return $data; } if(isset($_POST['submit'])){ $myName = $_POST['myName']; $home1 = $_POST['home1']; $home2 = $_POST['home2']; $city = $_POST['city']; $ste = $_POST['ste']; $zip = $_POST['zip']; $email = $_POST['email']; $certification = $_POST['certification']; $experience = $_POST['experience']; $employer = $_POST['employer']; $marketing = $_POST['marketing']; $gender = $_POST['gender']; $dob = $_POST['dob']; $recert = $_POST['recert']; $full_name = $_POST['full_name']; $phone = $_POST['phone']; $bHome1 = $_POST['bHome1']; $bHome2 = $_POST['bHome2']; $bCity = $_POST['bCity']; $bState = $_POST['bState']; $bZip = $_POST['bZip']; $payment = $_POST['payment']; $cardNum = $_POST['cardNum']; $expDate = $_POST['expDate']; $pwd = $_POST['pwd']; // Adding multiple values to database table users $sql = "INSERT INTO TABLE users (myName, home1, home2) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (city, ste, zip) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (email, certification, experience) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (employer, marketing, gender) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (dob, recert, full_name) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (phone, bHome1, bHome2) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (bCity, bState, bZip) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (payment, cardNum, expDate) VALUES (?, ?, ?);"; $sql .= "INSERT INTO TABLE users (pwd) VALUES (?);"; // Trying to save to the database if (mysqli_multi_query($con, $sql)) { echo "New records created successfully"; } else { echo "Error: " . $sql . "<br>" . 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); }
Advertisement
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 }