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);
}
3
Answers
Tangentially Perpendicular & Rager pointed me in the right direction in the sense that my entries were wrong. Using multi-query (mysqli_multi_query) is the wrong way to do what I needed to do and had nothing to do with adding multiple entries into a table. mysqli_multi_query executes one or multiple queries which are concatenated by a semicolon (https://www.php.net/manual/en/mysqli.multi-query.php).
Yes, you can add as many entries per record as you want (if you want to complicate your life) but simple is better. Finally, the reason I could not get data into the table (besides using multi-query and that my entries were wrong), is that my version of MAMP (in a mac) was running version 7.4 and not PHP 8.0 as in my computer. Once I checked marked version 8 on MAMPs I was able to get my query in the table without any other issues.
You need to prepare your sql, bind the params and then execute. Forget the mysqli functions.
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
Your multi-query is completely wrong. It will create nine new rows, each with a portion of the data for a user, instead of one. You only have one set of data, so you don’t need multi_query at all.
You need