Putting a DB column into an array

[shaung]

Hi, I want to put a column from mysql into an array.  Should I use fetch() or fetchAll() ?

 

            try
            {  
                $sth = $dbh->prepare("SELECT name FROM institution");
                $sth->execute();


                /* Fetch all of the institution names from table*/
                $institutions = $sth->fetchAll();
            }
            catch(PDOException $e) {
                    die($e->getMessage());
            }

I  then want to iterate through the array and populate a dropdown using the array.  

 

        foreach ($arrValues as $row)
        {
           $id = $row['employee_id'];
           echo '<tr>
                        <td><input type="text" name="record['.$id.'][firstName]" value="'.$row['first_name'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][lastName]" value="'.$row['last_name'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][height]" value="'.$row['height'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][cap]" value="'.$row['cap'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][color]" value="'.$row['colors'].'" . $readState . /></ td>


                                <td><select name="record['.$id.'][degree]"> </td>
                                <option> "Filler" </option>
                                </select> 




                                <td><select name="record['.$id.'][school]"> </td> '
                                   
                                    if($edit){
                                        foreach($institutions as $option){


                                           echo "<option value='{$option}' </option>";   


                                        }
                                    }
                                    #edit =!$edit;
                               echo '</select> 


                        <td><input type="submit" name="update" value="Update" /></td>
                    </tr>';

If the edit variable == true I want to fill the dropdowns.  The error says, "Unexpected if"

 

Can anyone explain this?

[trq]

Your missing the closing ; just before your if statement.

[shaung]

OK, thanks.  I still can't get the values from the database into the array then into the dropdown. 

 

Someone suggested I do this:

 

function _options($arr){
    $selected = isset($arr['selected']) ? " selected='selected'" : '';
    return "<option value='{$arr['id']}'$selected>{$arr['name']}</option>";
}
$current_options = $institutions;
$options = array_map('_options',$current_options);


echo implode("\n",$options);

...but I can't get that to work either.  

 

 I have like one day a week that I can look at this and I have been stuck on it for three weeks.  This sucks to no end.

 

Here is all the pertinent code using the above method.

 

 

        $_SESSION['sid'] = $sid;        
        $edit = false;
        /*-------------------------------------
        *Begin adding your stuff here 
        --------------------------------------*/
            /*-------------------------------------  */   
            
            if($_SESSION['edit'])
            {
               $sql = "SELECT * FROM fac_detail INNER JOIN institution ON fac_detail.institution_id = institution.id "
                . "INNER JOIN degree ON fac_detail.degree_id = degree.id WHERE employee_id = . '$_POST[hidden]'";
            } 
            else 
            {


                $sql = "SELECT * FROM fac_detail INNER JOIN institution ON fac_detail.institution_id = institution.id "
                . "INNER JOIN degree ON fac_detail.degree_id = degree.id"; 
            }
            
            try
            {  
                $sth = $dbh->prepare("SELECT name FROM institution");
                $sth->execute();


                /* Fetch all of the values of the first column */
                $institutions = $sth->fetch();
            }
            catch(PDOException $e) {
                    die($e->getMessage());
            }
            
            //try
            //{  
                //$sth = $dbh->prepare("SELECT degree_type FROM degree");
                //$sth->execute();


                /* Fetch all of the values of the column */
                //$degrees = $sth->fetch();
            //}
            //catch(PDOException $e) {
                    //die($e->getMessage());
            //}


            try
            {  
                    if(isset($_POST['update']))
                    {
                        $updatedQuery = "UPDATE fac_detail SET first_name=:fname, last_name=:lname, height=:height, cap=:cap,colors=:color WHERE employee_id=:id";
                        $stmt = $dbh->prepare($updatedQuery);


                        // loop over each record in $_POST['record'] 
                        // getting the employee_id and each fields value
                        foreach($_POST['record'] as $employee_id => $field)
                        {
                            $stmt->bindParam(':fname', $field['firstName'], PDO::PARAM_STR);
                            $stmt->bindParam(':lname', $field['lastName'], PDO::PARAM_STR);
                            $stmt->bindParam(':height', $field['height'], PDO::PARAM_STR);
                            $stmt->bindParam(':cap', $field['cap'], PDO::PARAM_STR);
                            $stmt->bindParam(':color', $field['color'], PDO::PARAM_STR);
                            $stmt->bindParam(':id', $employee_id, PDO::PARAM_INT);


                            $stmt->execute();
                        }
                    }


                //$sql = "SELECT * FROM fac_detail";
                $stmt = $dbh->prepare($sql);
                $stmt->execute();
                $arrValues = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $row = $stmt->fetch();
            }
            catch(PDOException $e) {
                    die($e->getMessage());
            }


    //Heredoc syntax for echoing out HTML
    echo <<<HTML
    <form method= post>
        <table id="recordTable">
            <thead>
                <tr>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Height</th>
                    <th>Cap</th>
                    <th>Color</th>
                    <th>Degree</th>
                    <th>Institution</th>
                    <th>Edit Record</th>
                </tr>
            </thead>
            
HTML;


        //Array with sid as key
    
    if($edit)
    {
        $readstate = "readonly";
    }
    else 
    {
        $readstate = "";
    }
               
            echo "<tbody>";
     
        foreach ($arrValues as $row)
        {
           $id = $row['employee_id'];
           echo '<tr>
                        <td><input type="text" name="record['.$id.'][firstName]" value="'.$row['first_name'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][lastName]" value="'.$row['last_name'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][height]" value="'.$row['height'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][cap]" value="'.$row['cap'].'" . $readState . /></ td>
                        <td><input type="text" name="record['.$id.'][color]" value="'.$row['colors'].'" . $readState . /></ td>


                                <td><select name="record['.$id.'][degree]"> </td>
                                <option> "Filler" </option>
                                </select> 




                                <td><select name="record['.$id.'][school]"> </td>';
                     
                                    if($edit){
                                        foreach($institutions as $val){
                                          echo "<option value='\$val\'>".$val."</option>";
                                        }
                                    }
                                    
                               echo '</select> 


                        <td><input type="submit" name="update" value="Update" /></td>
                    </tr>';
        }   
  


            echo "</tbody>";
        echo "</table>";
       echo "</form>";

       

       

?>

 

Everything above and below the code is done with a template and works fine.  Everything works on this page except the dropdowns.  Any hints as to how I can get this to work would be greatly appreciated.

 

When the page loads it shows a table full of form elements filled with data and an edit button for each row.  I want them to click the edit button then show the table again with only the single record they clicked with the dropdowns filled.  

[Ch0cu3r]

You the syntax for the dropdown options is wrong

echo "<option value='{$option}' </option>"; 

the above should be like

echo "<option value='{$option}'>$option</option>"; 

//OR Just use
echo "<option>$option</option>"; 

[shaung]

 

You the syntax for the dropdown options is wrong

echo "<option value='{$option}' </option>"; 

the above should be like

echo "<option value='{$option}'>$option</option>"; 

//OR Just use
echo "<option>$option</option>"; 

For some reason nothing I do will fill the options

 

                                <td><select name="record['.$id.'][school]"> </td>';
                                    if($edit){
                                        foreach($institutions as $option){
                                          echo "<option>$option</option>"; 
                                        }
                                    }
                                    
                               echo '</select> 

Thanks for taking your time with this.  I will look at this again tonight after work.