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?
// 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>
		

Open in new window

Its line 45 and 46 that I don't know how to write.
LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
the basic sql syntax difference is shown below:
/* a single record */                   
INSERT INTO schedule (schedule_id, employee_id, first_name, last_name, start_date, end_date)                     
    VALUES ('', '$eid', '$fn', '$ln', '$std', '$edt')
;

/* 3 records together */    
INSERT INTO schedule (schedule_id, employee_id, first_name, last_name, start_date, end_date)                     
    VALUES 
    ('', '$eid', '$fn', '$ln', '$std', '$edt'),
    ('', '$eid', '$fn', '$ln', '$std', '$edt'),
    ('', '$eid', '$fn', '$ln', '$std', '$edt')
;

Open in new window

note, there is no trailing comma on line 11
obviously 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.
0
Chris StanyonWebDevCommented:
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.
0
Chris StanyonWebDevCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Chris StanyonWebDevCommented:
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;
?>

Open in new window

0
Chris StanyonWebDevCommented:
And here's the HTML script.

<form action="add_schedule.php" method="post">
<h3>DRIVERS::</h3>

<?php
//prepare the query. It's best practice to SELECT the specific columns, rather than relying on SELECT *
$stmt = $pdo->prepare("SELECT employee_id, first_name, last_name, driver_class_a, driver_class_b FROM employees");

//run the query
$stmt->execute();

//loop through the results
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) : ?>
	<div>
		<!--
		Keep on eye on how the form inputs are named. We're using an array. We're naming the fields like this:
			driver[employeeid][ticked]
			driver[employeeid][firstname]
			driver[employeeid][lastname]
		This allows us to easily loop through the POSTed data in the database script.
		-->
		
		<!-- create the tickbox -->
		<p><?php echo $row->first_name ?> <?php echo $row->last_name ?>::<input type="checkbox" name="driver[<?php echo $row->employee_id ?>][ticked]"/></p>
		
		<!-- add some driver info -->
		<?php
			$name = $row->first_name . " " . $row->last_name;
			if ($row->driver_class_a == "yes") echo "<p>$name is a Class A Driver<p>";
			if ($row->driver_class_b == "yes") echo "<p>$name is a Class B Driver</p>";
		?>
		
		<!-- add some hidden inputs for the name -->
		<input type="hidden" name="driver[<?php echo $row->employee_id ?>][firstname]" value="<?php echo $row->first_name ?>" />
		<input type="hidden" name="driver[<?php echo $row->employee_id ?>][lastname]" value="<?php echo $row->last_name ?>" />
	</div>
<?php endwhile; ?>

<div>
	<label for="start">Start Date:</label>
	<input id="start" type="text" name="start_date" />
</div>

<div>
	<label for="End">End Date:</label>
	<input id="end" type="text" name="end_date" />
</div>

<p><input type="submit" value="Continue &rarr;"/></p>
</form>

Open in new window

A lot of what I've posted will vary hugely from what you originally had, both in logic and syntax. Take some time reading through the comments to fully understand WHAT we're doing, and then look through the code to see HOW we're doing it. Keep the PHP.net website open in a tab. It will explain all the functions we've used here.

If something doesn't make sense, just ask :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
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:
// 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>';
}

Open in new window

This will allow the client to enter "F. U. AZZHOLE" for the start date, but will reject many perfectly useful date representations, including "+3 weeks" or "10/12/2013" or "September 15, 2013."  You don't want to become known for creating work products that do that.  What your action script should be doing with the date fields is determining if they are useful dates within reasonable boundaries, converting them to the ISO-8601 standard representation, and storing them if they make sense.  That process is not done with regular expressions; it's done with strtotime() and date() functions or with the DATETIME class.

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.
http://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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.