INSERT MySQL Query Syntax
If I have the following query that grabs all employee names from a DB and it generates 20 returns. I am using this query to populate a form. The form is to schedule the certain employees at/on specific dates.
The form has checkboxes beside each employees date. If the employees name is checked it will be entered into the DB with the selected date. I want to make a specific entry into the DB for each employee chosen. I’m not sure how to do multiple inserts at one time. So If I choose 20 employees and a date the script will make an entry for each individual employee. I will have 10 new entries. Any hints how to write the INSERT Query?
The form has checkboxes beside each employees date. If the employees name is checked it will be entered into the DB with the selected date. I want to make a specific entry into the DB for each employee chosen. I’m not sure how to do multiple inserts at one time. So If I choose 20 employees and a date the script will make an entry for each individual employee. I will have 10 new entries. Any hints how to write the INSERT Query?
// Check if the form has been submitted:
if (isset($_POST['submitted'])) {
require_once (MYSQL);
$trimmed = array_map('trim', $_POST);
$std = FALSE;
// Check for a Start Date:
if (preg_match ('/^[A-Z0-9 \'.-]{2,45}$/i', $trimmed['start_date'])) {
$std = mysqli_real_escape_string ($dbc, $trimmed['start_date']);
} else {
echo '<p class="error">Please enter a start date!</p>';
}
// Check for a End Date:
if (preg_match ('/^[A-Z0-9 \'.-]{2,45}$/i', $trimmed['end_date'])) {
$edt = mysqli_real_escape_string ($dbc, $trimmed['end_date']);
} else {
$edt = "";
}
// Check for a first name:
if (preg_match ('/^[A-Za-z \'.-]{2,20}$/i', $trimmed['first_name'])) {
$fn = mysqli_real_escape_string ($dbc, $trimmed['first_name']);
} else {
$fn = "";
}
// Check for a last name:
if (preg_match ('/^[A-Za-z \'.-]{2,20}$/i', $trimmed['last_name'])) {
$ln = mysqli_real_escape_string ($dbc, $trimmed['last_name']);
} else {
$ln = "";
}
if ($std) { // If everything's OK.
// Make the query:
$q = ("INSERT INTO schedule (schedule_id, employee_id, first_name, last_name, start_date, end_date)
VALUES ('', '$eid', '$fn', '$ln', '$std', '$edt')");
$r = @mysqli_query ($dbc, $q);
if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
// Print a message:
echo '<fieldset style="text-align:left;margin-top:30px;clear:both;"><br /><br /><p class="success">The Schdule for <strong>'.$fn.' '.$ln. '</strong> has been added.</p></fieldset>';
include ('includes/footer.html'); // Include the HTML footer.
exit(); // Stop the page.
} else { // If it did not run OK.
echo '<p class="error">This schedule could not be added due to a system error. We apologize for any inconvenience.</p>'; // Public message.
echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
}
} else { // Report the errors.
echo '<p class="error"><strong>Please try again.</strong></p>';
} // End of if (empty($errors)) IF.
}
?>
<div id="add-schedule">
<fieldset>
<form action="add_schedule.php" method="post">
<table class="add-employee">
<tr>
<td align="left">Name:</td>
</tr>
<?php
$q = ("SELECT * FROM employees");
$r = @mysqli_query ($dbc, $q);
echo '<h3>DRIVERS::</h3><br />';
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
if ($row['position'] == "driver") {
echo '<tr>
<td align="left">
'.$row['first_name'].' '.$row['last_name'].':: <input type="checkbox" name="'.$row['employee_id'].'" id="'.$row['employee_id'].'" size="45" maxlength="60"/><br />';
if ($row['driver_class_a'] == "yes") { echo $row['first_name'].' '.$row['last_name'].' is a Class A Driver<br /> ';}
if ($row['driver_class_b'] == "yes") { echo $row['first_name'].' '.$row['last_name'].' is a Class B Driver<br /> ';}
if ($row['driver_class_b'] == "yes") { echo $row['first_name'].' '.$row['last_name'].' is a Class B Driver<br /> ';}
echo '</td>
</tr>';
}
}
echo '<h3>HELPRES::</h3><br />';
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
if ($row['position'] == "helper") {
echo '<tr>
<td align="left">
'.$row['first_name'].' '.$row['last_name'].':: <input type="checkbox" name="'.$row['employee_id'].'" id="'.$row['employee_id'].'" size="45" maxlength="60"/>
</td>
</tr>';
}
}
?>
<tr>
<td align="left">Start Date:</td>
<td align="left">
<input type="text" name="start_date" size="45" maxlength="45" class="datepicker" value="<?php if(isset($_POST['start_date'])) echo $_POST['start_date']; ?>" />
</td>
</tr>
<tr>
<td align="left">End Date:</td>
<td align="left">
<input type="text" name="end_date" size="45" maxlength="" class="datepicker" value="<?php if(isset($_POST['end_date'])) echo $_POST['end_date']; ?>" />
</td>
</tr>
</table>
If you are sending 20 sets of data back to your script, then you need to loop through the POST data and insert a record on each pass. To this, you need to name your Checkboxes using an array:
checkbox name="tickbox[1]"
checkbox name="tickbox[2]"
The the $_POST['checkbox'] will come into your script as an array and you can loop through it.
You also need to be dropping the mysql API from PHP and instead switching to using mySQLi or PDO.
There a few other issues as well, so if you give me a few minutes, I'll refactor your code and let you work on that.
checkbox name="tickbox[1]"
checkbox name="tickbox[2]"
The the $_POST['checkbox'] will come into your script as an array and you can loop through it.
You also need to be dropping the mysql API from PHP and instead switching to using mySQLi or PDO.
There a few other issues as well, so if you give me a few minutes, I'll refactor your code and let you work on that.
Actually, looking closer at your code, there's a lot that needs sorting - pretty much all of it to be honest!
Couple of questions. Is this all of the page code? Your form will try and POST a series of checkboxes and a single StartDate and a single EndDate, yet in your script, you are expecting FirstName and LastName in the POST array. Your INSERT also expects an EmployerID ($eid) which doesn't exist anywhere.
You seem to cleaning FirstName and LastName from the POST array (even thought hey don't exist) but only doing this once. Does each record not have a FirstName and LastName (i.e. 20 records = 20 people)
When outputting your table code, you are trying to use mysqli_fetch in 2 separate while{} loops. This won't work - Once it's looped through the first time- it reaches the last record so the 2nd loop has nothing to work with.
Also how to intend to deal with the Successful Inserted message. Do you simply want to say "xx number of records updated" or do you want an individual success message for each person.
As much as getting the code right is important, getting the logic right first is imperative. Before even touching a computer, sit down with a pen and paper (retro, right!) and write down in detail what you want to achieve - get the business logic right first.
Couple of questions. Is this all of the page code? Your form will try and POST a series of checkboxes and a single StartDate and a single EndDate, yet in your script, you are expecting FirstName and LastName in the POST array. Your INSERT also expects an EmployerID ($eid) which doesn't exist anywhere.
You seem to cleaning FirstName and LastName from the POST array (even thought hey don't exist) but only doing this once. Does each record not have a FirstName and LastName (i.e. 20 records = 20 people)
When outputting your table code, you are trying to use mysqli_fetch in 2 separate while{} loops. This won't work - Once it's looped through the first time- it reaches the last record so the 2nd loop has nothing to work with.
Also how to intend to deal with the Successful Inserted message. Do you simply want to say "xx number of records updated" or do you want an individual success message for each person.
As much as getting the code right is important, getting the logic right first is imperative. Before even touching a computer, sit down with a pen and paper (retro, right!) and write down in detail what you want to achieve - get the business logic right first.
OK. I use PDO instead of mySQLi so this example is based on PDO. I'll post the database script here, and then the HTML part in the next comment. It's not copy/paste code so you need to read through it and understand what's going on. Start by just reading the comments, so you can get a handle on the logic of the code.
<?php
//Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=yourBD", "username", "password");
//prepare the INSERT query with named parameters
$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date) VALUES (:employeeid, :firstname, :lastname, :startdate, :enddate)");
//if something's been POSTed, lets deal with it
if (!empty($_POST)):
//Get the date info. If your database stores DATE then you need to make sure the format is correct.
$startdate = $_POST['startdate'];
$enddate = $_POST['enddate'];
//start a record counter so we can track how many records are updated
$records = 0;
//loop through each set of POSTed data
foreach ($_POST['driver'] as $eid => $info):
//was the tick box ticked
if (isset($info['ticked'])):
//get the info to insert into the database
$data = array(
'startdate' => $startdate,
'enddate' => $enddate,
'employeeid' => $eid,
'firstname' => $info['firstname'],
'lastname' => $info['lastname']
);
//run the query
$stmt->execute($data);
//and increase the record counter
$records++;
endif;
endforeach;
//give some feedback
if ($records) :
printf("<p>%d record(s) were updated</p>", $records);
else :
echo "<p>No records were updated";
endif;
endif;
?>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This really isn't a question with an answer -- it's more of a requirement for application development, and if the application has any economic value it might make sense to hire a professional application developer. If you try to do it yourself, you will have to first learn all of the things a professional application developer already knows, and that will take a long time!
I'll offer my two cents on one tiny part of this - filtering the client input for a date/time data element. Please consider the following code:
Knowing about these things does not come by accident. It's the product of deliberate study using structured learning resources. This article can help you find the good learning resources and more importantly avoid the bad learning resources that teach, inter alia, lame regular expression filters.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
If I were asked to point out the best starting point for your studies, it would be the latest edition of the Welling/Thompson book. Get the latest edition today, and when a new edition is published, get the new edition. It will give you a good knowledge foundation that will enable you to build PHP applications.
I'll offer my two cents on one tiny part of this - filtering the client input for a date/time data element. Please consider the following code:
// Check for a Start Date:
if (preg_match ('/^[A-Z0-9 \'.-]{2,45}$/i', $trimmed['start_date'])) {
$std = mysqli_real_escape_string ($dbc, $trimmed['start_date']);
} else {
echo '<p class="error">Please enter a start date!</p>';
}Knowing about these things does not come by accident. It's the product of deliberate study using structured learning resources. This article can help you find the good learning resources and more importantly avoid the bad learning resources that teach, inter alia, lame regular expression filters.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
If I were asked to point out the best starting point for your studies, it would be the latest edition of the Welling/Thompson book. Get the latest edition today, and when a new edition is published, get the new edition. It will give you a good knowledge foundation that will enable you to build PHP applications.
Open in new window
note, there is no trailing comma on line 11obviously you would need to get the right parameters into those rows.
not sure if this is the information you wanted.
also note, if `schedule_id` is an auto_increment field, you don't include it in your inserts.