Drop-down Menu not updating form

[SalientAnimal]

Hi All,

 

I've been working on this page for quite some time now and I have gotten it to almost work exactly as needed. The error I was getting was 

 

 

Fatal error: Call to a member function fetchAll() on a non-object in C:\htdocs\page1.php on line 85

 Line 85 in the code was $result = $query -> fetchAll (PDO::FETCH_ASSOC);

 

I think the reason for the error was because I had multiple queries firing off at the same time when the page loaded and there fore there was no initial selection made in the drop-down menu. To see the page working I forced the URL to a selection eg original URL = page1.php, forced URL page1.php?shift=7. This made the page display and work as desired. Selecting the drop-down menu would change the check boxes as desired. 

 

However, I need to get around the error mention, and so changed the code to have a default value. The problem now however is that my drop-down menu no longer causes the check boxes to populate. Not errors are being returned either. Pleae see my code below.

<?php
include_once 'includes/db_connect.php';
include_once 'includes/functions.php';
include_once 'includes/session_management.php';
include_once 'includes/formatting.php';
include_once 'includes/panel.php';
include_once '/nav/menu.html';
// Page1.php
// This page will simply go to the database and give us a list of agents along with their associated IDs
// The user will tick checkboxes for each agent they wish to enter the status of


//FUNCTION TO CREATE HTML FOR OPTIONS LIST
function createOptions($optionList, $selectedValue)
{
    $options = '';
    foreach ($optionList as $option)
    {
        $selected = ($option['value']==$selectedValue) ? ' selected="selected"' : '';
        $options .= "<option value='{$option['value']}'{$selected}>{$option['label']}</option>\n";
    }
    return $options;
}

function db_result_to_array($result)
{
    for ($count=0; $row = $result->fetch_assoc(); $count++) {
        $res_array[$count]=$row;
    }
    return $res_array;
}	


?>

<script language="javascript">
function getSelectValue(selectID)
{
    var optionObj = document.getElementById(selectID);
    return optionObj.options[optionObj.selectedIndex].value;
}

function reload(form)
{
    //Adding the unselected options should work fine
    var locationURL = 'page1.php';
    locationURL += '?shift='   + getSelectValue('shift');
    //Perform the reload
    self.location = locationURL;
}

</script>

<?php


if ( isset ( $_GET['shift'] ) && is_numeric ( $_GET['shift'] ) ){ 
  // There's a shift=xxx in the querystring so we use that as the select's default value 
  $default_value = $_GET['shift']; 
} else { 
  // There's no shift=xxx in the query string so a default value is assigned here like so 
  $default_value = 7; 
} 

$shift   = isset($default_value)   ? intval($default_value)   : false;


//DETERMINE SELECTED OPTIONS PASSED ON QUERY STRING



// CREATE THE DROP-DOWN MENU FROM THE SELECTED DATE
    $sql_1 = "
			SELECT 
					DISTINCT(shift) as value
			,		title as label
			FROM 
					schedule  
			WHERE 
					DATE(start) = '2014-05-07' //THIS DATE IS ONLY FOR TESTING
			AND		title NOT LIKE '%1st%'
			AND		title NOT LIKE '%2nd%'
			AND		title NOT LIKE '%Lunch%'	
			AND		agent_id NOT LIKE '0'						
			ORDER BY 
					title ASC";
	$optionList = $db->query($sql_1);
	$shift_options = createOptions($optionList, $shift);	
	
		
	
	
	

// CREATE THE SQL TO GRAB ALL USERS FROM THE DB & EXECUTE QUERY - ADDED $SHIFT VARIABLE WHICH IS CREATED BY THE OPTIONS MENU
    $sql = "
			SELECT 
					DISTINCT(agent)
			, 		agent_id 
			FROM 
					schedule  
			WHERE 
					DATE(start) = '2014-05-07' 
			AND 	shift = $shift
			ORDER BY 
					agent ASC";
    $query = $db -> query ($sql);
    $result = $query -> fetchAll (PDO::FETCH_ASSOC);
	

// $result should now hold an array of agents. Here, a check is done to make sure and die if not
    if ( count ($result) == 0 ) die ("No agents found");
    
// At this point, there's at least 1 record in the $result array and we can loop through without an error



// STARTING THE HTML PAGE

    echo "<body>";	
    echo 	"<div id='container'>";
    echo 		"<div id='content' style='margin-top:-45px;'>";
    echo 		"<img src='images/logo.png' alt=''></img>";
    echo 		"<h1>Auxilium</h1>";
    echo 	"<div id='stylized' class='form'>";
	

// Start the HTML form	
    echo "<form name='page1_form' action='page2.php' method='post'>";

	
    echo 		"<h1>Shift Roster</h1>";


	

		
    echo 		"<input type='hidden' name='username' id='username' value='$username' readonly style='background-color: #C9C9C9'>";	
	echo	"</div>";



	echo "<div id='shift' class='form3'>";	
	echo "<table border='0' class='shift'><tbody>";	


	

	echo 	"<tr>";
	echo 		"<td>";
	echo 			"<label>Shift :";
	echo 			"<span class='small'>Shift to update / edit</span>";
	echo 			"</label>";
	echo 		"</td>";
	
	echo 		"<td>";	
	echo 			"<select name='shift' id='shift' onChange='reload(this.form)'>";
	echo 			"<option value='99'>--- Select a Shift ---</option>";
	echo 			$shift_options;
	echo 			"</select>";
	echo 		"</td>";
	echo 	"</tr>";	

	echo 	"<tr>";
	echo 			"<td colspan='2'>";
	echo 			"<p class='shift_highlight'>Select the agents whom's attendance status you wish to update by checking the tick boxes below.";
	echo 			"</td>";
	echo 	"</tr>";	
	
    
// Loop through all the Agents returned from the query above so that the user can select which one(s) they 
// want to enter the status data for
    foreach ( $result as $agent_info ) {
    
        // We now have an array called $agent_info which holds two keys:
        //        agent_id
        //        agent_name

	echo 	"<tr>";
	echo 		"<td>";	       
	echo 			"<input type='checkbox' name='agent[]' value='" . $agent_info['agent_id'] . "' id='" . $agent_info['agent_id'] . "'>";
    echo 			" ";
	echo 		"</td>";
	echo 		"<td>";				
    echo 			"<for='" . $agent_info['agent_id'] . "'>";
	echo			"<span class='buttonlabel'>";
    echo 			$agent_info['agent'];
	echo			"</span>";
    echo 			"<br>";
	echo 		"</td>";		
	echo 	"</tr>";
   
    }
    
	echo 	"<tr>";	
	echo 		"<td colspan='1'>";	
	echo		"<br>";  	
// Give the user a submit button
    echo 			"<input type='submit' name='submit' value='Submit'>";
	echo 		"</td>"; 
	echo 	"</tr>";
	
			   
// Close the HTML form
    echo "</form>";

Any help will be appreciated here.

Edited May 2, 2014 by SalientAnimal

[mac_gyver]

your form is using method='post', so $_GET['shift'] will never exist.

[SalientAnimal]

Can I simply replace the GET with post?

 

I tried replacing 

 

$_GET['shift'] with $_POST['shift'], but no success.

Edited May 2, 2014 by SalientAnimal

[bsmither]

Fatal error: Call to a member function fetchAll() on a non-object

 

The call to fetchAll() in this statement:

// Does a space between the function name and the argument list in parentheses cause a problem?
$query -> fetchAll (PDO::FETCH_ASSOC);

is based on $query being an object in the same "scope" as that of the statement. If $query was assigned in the root, or global scope, then you might not be able to see it inside functions.

 

However, I have learned (the hard way) that variables assigned in the global scope can be seen anywhere because PHP makes $GLOBALS['query'] from $query.

 

Just FYI.

 

I also think this can be looked at closer. I am of the opinion DISTINCT is not a SQL function.

DISTINCT(shift) as value

 

This will cause problems. PHP comments do not work as comments inside a string. Here is the MySQL comment format.

DATE(start) = '2014-05-07' //THIS DATE IS ONLY FOR TESTING

 

 

And I generally don't like separating the function name from the argument list, although I do not see why PHP would complain:

Was:
if ( count ($result) == 0 ) die ("No agents found");
Now:
if( count($result) == 0 ) die("No agents found");

Edited May 2, 2014 by bsmither

[SalientAnimal]

This will cause problems. PHP comments do not work as comments inside a string. Here is the MySQL comment format.

DATE(start) = '2014-05-07' //THIS DATE IS ONLY FOR TESTING

 

 

That comment is not in my actual code. I just added it in for the sake of the forum post.