Solved

PHP Query Syntax

Posted on 2013-10-23
5
284 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 43

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 43

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 43

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

920 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

13 Experts available now in Live!

Get 1:1 Help Now