Solved

PHP Query Syntax

Posted on 2013-10-23
5
282 Views
Last Modified: 2013-10-25
This is a followup question that has to do with a question I have already asked. The following script does a number of things.
1. Query schedule _table to find Employee_id numbers who have something schedules on a specific date.

2. Query Employees Table with employee_id and select every employees that does not have that Employee_id then print out a list of those employees.

What happens is, if the 1st query returns 2 - 3 - 4 etc..  It will repeat the second query that many times, giving me the same list, 2 -3 -4 -5 etc... times.

BUT::: The second part of the first query if(!$results) {  That part works 100% and does not repeat.
I know it is something simple but I'm not sure what and my brain feels like mush.

	// CREATE A QUERY FOR USE WITH BINDPARAM()
			$sql = "SELECT employee_id, asset_id, first_name, last_name FROM schedule WHERE start_date BETWEEN :start_date AND :end_date 
    				OR end_date BETWEEN :start_date AND :end_date";
	// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
			$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

	// BIND THE VARIABLE AND TRY THE QUERY
			$pdos->bindParam(':start_date', $start_date, PDO::PARAM_STR);
			$pdos->bindParam(':end_date', $end_date, PDO::PARAM_STR);
	

	//  START FIRST QUERY TRY GRAB EMPLOYEE ID FROM SCHEDULE			
			try {
    			$pdos->execute();
	
	//fetch the query results into an array
				$results = $pdos->fetchAll();
				
				if($results) { 
	//  START FOREACH OF THE EMPLOYEE ID FROM SCHEDULE
				foreach ($results as $row) {
					$eid_a = $row['employee_id'];
//  START RUN QUERY EMPLOYEE ID FROM EMPLOYEES
												
	//  CREATE QUERY TO LIST ALL EMPLOYEES AND THE EMPLOYEE ID
					$stmt = $pdo->prepare("SELECT employee_id, first_name, last_name, driver_class_a, driver_class_b, driver_class_c
							FROM employees
							WHERE NOT employee_id = '$eid_a' ORDER BY driver_class_c DESC, driver_class_b, driver_class_a DESC");
	//  PREPARE PDO QUERY
					try {
    					$stmt->execute();
	//  RUN A FOREACH FOR EACH EMPLOYEE ID AGAIST SCHEDULE
						if ($stmt) {
	//loop through the results
							while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
								if($row->driver_class_a == "yes" || $row->driver_class_b == "yes" || $row->driver_class_c == "yes") {	 
								echo '<tr>
									<td width="25"><input type="checkbox" name="driver['.$row->employee_id.'][ticked]"/>
									<input type="hidden" name="driver['.$row->employee_id.'][first_name]" value="'.$row->first_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][last_name]" value="'.$row->last_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][start_date]" value="'.$start_date.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][end_date]" value="'.$end_date.'" />
									</td>
									<td width="100">'.$row->first_name. ' ' . $row->last_name.'</td>';
									$name = $row->first_name . " " . $row->last_name;
									if ($row->driver_class_a == "yes") echo "<td width='200'>Class A Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
									if ($row->driver_class_b == "yes") echo "<td width='200'>Class B Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
									if ($row->driver_class_c == "yes") echo "<td width='200'>Class C Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
								echo '</tr>';
								}
								
								if($row->driver_class_a == "" && $row->driver_class_b == "" && $row->driver_class_c == "") {	
								echo '<tr>
									<td width="25"><input type="checkbox" name="driver['.$row->employee_id.'][ticked]"/>
									<input type="hidden" name="driver['.$row->employee_id.'][first_name]" value="'.$row->first_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][last_name]"value="'.$row->last_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][start_date]" value="'.$start_date.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][end_date]" value="'.$end_date.'" />
									</td>
									<td width="100">'.$row->first_name. ' ' . $row->last_name.'</td>
									<td width="200"><strong>Helper</strong></td><td width="250"><span class="available">AVAILABLE</span></td>';
								echo '</tr>';
								}		
							}  //  END MAIN WHILE
			 			}  //  END MAIN IF
			 
			 		}  //  END TRY
					catch(PDOException $exca) {
   					var_dump($exca);
    				trigger_error($exca->getMessage(), E_USER_ERROR);
					}
				}  //  END FOREACH
				}  //  END IF RESULTS	
					
//  START IF NO RESULTS  
				
			if (!$results) {
  				$eid_a = '0';
				
				$stmtb = $pdo->prepare("SELECT employee_id, first_name, last_name, driver_class_a, driver_class_b, driver_class_c
							FROM employees
							WHERE NOT employee_id = '$eid_a' ORDER BY driver_class_c DESC, driver_class_b, driver_class_a DESC");
				
				try {
					$stmtb->execute();
					
						if ($stmtb) {
	//loop through the results
							while ($row = $stmtb->fetch(PDO::FETCH_OBJ)) {
								if($row->driver_class_a == "yes" || $row->driver_class_b == "yes" || $row->driver_class_c == "yes" && $row->employee_id != '$eid_a') {	 
								echo '<tr>
									<td width="25"><input type="checkbox" name="driver['.$row->employee_id.'][ticked]"/>
									<input type="hidden" name="driver['.$row->employee_id.'][first_name]" value="'.$row->first_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][last_name]" value="'.$row->last_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][start_date]" value="'.$start_date.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][end_date]" value="'.$end_date.'" />
									</td>
									<td width="100">'.$row->first_name. ' ' . $row->last_name.'</td>';
									$name = $row->first_name . " " . $row->last_name;
									if ($row->driver_class_a == "yes") echo "<td width='200'>Class A Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
									if ($row->driver_class_b == "yes") echo "<td width='200'>Class B Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
									if ($row->driver_class_c == "yes") echo "<td width='200'>Class C Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
								echo '</tr>';
								}
								
								if($row->driver_class_a == "" && $row->driver_class_b == "" && $row->driver_class_c == "" && $row->employee_id != '$eid_a') {	
								echo '<tr>
									<td width="25"><input type="checkbox" name="driver['.$row->employee_id.'][ticked]"/>
									<input type="hidden" name="driver['.$row->employee_id.'][first_name]" value="'.$row->first_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][last_name]"value="'.$row->last_name.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][start_date]" value="'.$start_date.'" />
									<input type="hidden" name="driver['.$row->employee_id.'][end_date]" value="'.$end_date.'" />
									</td>
									<td width="100">'.$row->first_name. ' ' . $row->last_name.'</td>
									<td width="200"><strong>Helper</strong></td><td width="250"><span class="available">AVAILABLE</span></td>';
								echo '</tr>';
								}		
							}  //  END MAIN WHILE
						}  //  END MAIN IF
			 		}  //  END TRY
					catch(PDOException $exca) {
   					var_dump($exca);
    				trigger_error($exca->getMessage(), E_USER_ERROR);
					}
				}
					
//  END IF NO RESULTS  
					
			
		}  //  END TRY
		catch(PDOException $exca) {
   		var_dump($exca);
    	trigger_error($exca->getMessage(), E_USER_ERROR);
		}

Open in new window

0
Comment
Question by:rgranlund
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39595853
You seem to have taken a very linear approach to this and kept adding code as you go, so I think you've lost site of what you're trying to achieve. It looks to me like you're creating the same form 4 times in your code. You also seem to 'preparing' your query each time you run the loop. The idea of preparing it is that you do that once, and then execute it within the loop. You are running a loop based on returned employee ids, and then running a separate loop by setting the employee id to 0. A better approach isto check whether employees where returned. If not, create the array with an ID of zero and then just carry on with your code.

I haven't tested the code, but have a look through this logic and see if it makes sense:

<?php
// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT employee_id FROM schedule WHERE start_date BETWEEN :start_date AND :end_date OR end_date BETWEEN :start_date AND :end_date";
$schedule = $pdo->prepare($sql);

// BIND THE VARIABLE AND TRY THE QUERY
$schedule->bindParam(':start_date', $start_date, PDO::PARAM_STR);
$schedule->bindParam(':end_date', $end_date, PDO::PARAM_STR);
	
//  START FIRST QUERY TRY GRAB EMPLOYEE ID FROM SCHEDULE			
$schedule->execute();
	
//fetch the query results into an array
$results = $schedule->fetchAll();
				
//if no results, then create the array with an employee ID of 0
if (!count($results)) {
	$results[] = array('employee_id' => 0 );
};

//prepare the employees query
$employees = $pdo->prepare("SELECT employee_id, first_name, last_name, driver_class_a, driver_class_b, driver_class_c
FROM employees WHERE NOT employee_id = :employeeId ORDER BY driver_class_c DESC, driver_class_b, driver_class_a DESC");

//  START FOREACH OF THE EMPLOYEE ID FROM SCHEDULE
foreach ($results as $row) {
	$employees->execute(array('employeeId' => $row['employee_id']));

	//loop through the results
	while ($row = $employees->fetch(PDO::FETCH_OBJ)) {
		echo '<tr>
		<td width="25"><input type="checkbox" name="driver['.$row->employee_id.'][ticked]"/>
		<input type="hidden" name="driver['.$row->employee_id.'][first_name]" value="'.$row->first_name.'" />
		<input type="hidden" name="driver['.$row->employee_id.'][last_name]" value="'.$row->last_name.'" />
		<input type="hidden" name="driver['.$row->employee_id.'][start_date]" value="'.$start_date.'" />
		<input type="hidden" name="driver['.$row->employee_id.'][end_date]" value="'.$end_date.'" />
		</td>
		<td width="100">'.$row->first_name. ' ' . $row->last_name.'</td>';
									
		if($row->driver_class_a == "yes" || $row->driver_class_b == "yes" || $row->driver_class_c == "yes") {	 
			$name = $row->first_name . " " . $row->last_name;
			if ($row->driver_class_a == "yes") echo "<td width='200'>Class A Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
			if ($row->driver_class_b == "yes") echo "<td width='200'>Class B Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
			if ($row->driver_class_c == "yes") echo "<td width='200'>Class C Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
		} elseif ($row->driver_class_a == "" && $row->driver_class_b == "" && $row->driver_class_c == "") {
			echo '<td width="200"><strong>Helper</strong></td><td width="250"><span class="available">AVAILABLE</span></td>';					
		}
							
		echo '</tr>';
	}
}							
?>

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 39595911
I changed it just a little.  Needless to say, I had not taken into consideration turning the Return 0 into an array.  Newbie, learning all the time.

I changed your code just a little to reflect me growing needs.  However, it is still basically what you sent me.  However, It still loops through as many times as the the first return:

Does it have anything to do with the foreach statement?
	// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT employee_id, asset_id FROM schedule WHERE start_date BETWEEN :start_date AND :end_date OR end_date BETWEEN :start_date AND :end_date";
$schedule = $pdo->prepare($sql);

// BIND THE VARIABLE AND TRY THE QUERY
$schedule->bindParam(':start_date', $start_date, PDO::PARAM_STR);
$schedule->bindParam(':end_date', $end_date, PDO::PARAM_STR);
	
//  START FIRST QUERY TRY GRAB EMPLOYEE ID FROM SCHEDULE			
$schedule->execute();
	
//fetch the query results into an array
$results = $schedule->fetchAll();
				
//if no results, then create the array with an employee ID of 0
if (!count($results)) {
	$results[] = array('employee_id' => 0 );
};

//prepare the employees query
$employees = $pdo->prepare("SELECT employee_id, first_name, last_name, driver_class_a, driver_class_b, driver_class_c
FROM employees WHERE NOT employee_id = :employee_id ORDER BY driver_class_c DESC, driver_class_b, driver_class_a DESC");

//  START FOREACH OF THE EMPLOYEE ID FROM SCHEDULE
foreach ($results as $row) {
	$employees->execute(array('employee_id' => $row['employee_id']));

	//loop through the results
	while ($rows = $employees->fetch(PDO::FETCH_OBJ)) {
		echo '<tr>
		<td width="25"><input type="checkbox" name="driver['.$rows->employee_id.'][ticked]"/>
		<input type="hidden" name="driver['.$rows->employee_id.'][first_name]" value="'.$rows->first_name.'" />
		<input type="hidden" name="driver['.$rows->employee_id.'][last_name]" value="'.$rows->last_name.'" />
		<input type="hidden" name="driver['.$rows->employee_id.'][start_date]" value="'.$start_date.'" />
		<input type="hidden" name="driver['.$rows->employee_id.'][end_date]" value="'.$end_date.'" />
		</td>
		<td width="100">'.$rows->first_name. ' ' . $rows->last_name.'</td>';
									
		if($rows->driver_class_a == "yes" || $rows->driver_class_b == "yes" || $rows->driver_class_c == "yes") {	 
			$name = $rows->first_name . " " . $rows->last_name;
			if ($rows->driver_class_a == "yes") echo "<td width='200'>Class A Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
			if ($rows->driver_class_b == "yes") echo "<td width='200'>Class B Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
			if ($rows->driver_class_c == "yes") echo "<td width='200'>Class C Driver</td><td width='250'><span class='available'>AVAILABLE</span></td>";
		} elseif ($rows->driver_class_a == "" && $rows->driver_class_b == "" && $rows->driver_class_c == "") {
			echo '<td width="200"><strong>Helper</strong></td><td width="250"><span class="available">AVAILABLE</span></td>';					
		}
							
		echo '</tr>';
	}
}

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39595942
Not sure I follow.

Your code will select all the employee_ids from the schedule table and then loop through them (using foreach). On each loop through, it will call another query, this time selecting all records from the employees table that don't match the employee record from the schedule table [WHERE NOT employee_id] (don't quite understand why you do this). It then does another internal loop through these records (while{}) to create your <tr> data.

If that's not what you intended, maybe explain what your are trying to achieve and we can work on the logic
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39595965
I have two table:
1. Schedule
2. Employees

I want to query the Schedule table looking for employee_id's that are scheduled
on a specific date.  If they are scheduled on that date I want to exclude them from the next query.

2nd Query, I want to query the Employee table for all of the employee_id's  that are not in the list (Array) from above (the first query)
Then I want to print out a list of all the employees that are not already scheduled.

This is what I am trying to do and am not 100% clear on how to get there the easiest way.
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39596050
Right. The best approach here is to run one query. It gets a little more complicated than your existing queries because you need to include a sub-query, but this way you can grab all the employees not scheduled for particular dates all in one go. Your code will need to look something like this:

<?php
//create the query string
$sql = <<<EOT
SELECT  employee_id, first_name, last_name, driver_class_a, driver_class_b, driver_class_c
FROM    employees e
WHERE   NOT EXISTS (
        SELECT  s.employee_id
        FROM    schedule s
        WHERE   (e.employee_id = s.employee_id)
		AND (s.start_date BETWEEN :start_date AND :end_date
		OR s.end_date BETWEEN :start_date AND :end_date)
        )
ORDER BY driver_class_c DESC, driver_class_b, driver_class_a DESC
EOT;

//set the named parameters
$params = array(
	'start_date' => $start_date,
	'end_date' => $end_date
);

//prepare the query
$employees = $conn->prepare($sql);

//execute the query
$employees->execute($params);

//loop through the results
while ($employee = $employees->fetch(PDO::FETCH_OBJ)):
	//now you can echo out your table rows
	echo $employee->first_name . ' ' . $employee->last_name;
endwhile;
?>

Open in new window

It looks more complicated than it really is if you've not seen that type of query before but basically is says 'select the data from employees where a record in schedule doesn't exist for the specific dates. Give it a try and come back if you get stuck
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
WooCommerce Sort by Date 4 10
Creating a slider 12 34
How Can I Use otf Custom Font with TCPDF 7 11
sql query Help 12 33
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now