ID a row in a datatable - mysql-PDO

[shaung]

Hi, I am a PHP noob.

 

I have a table in which I am showing data from a mysql database.  I'm using PDO to do the database stuff.  Thus far, all good.

 

In the table I have a hidden field that stores my unique identifier for that row.  Each row in the table has an 'Edit' button.  I am showing the row in a DataTable.  I am displaying the data using form text elements so I can make it editable in place.   I have all that working. 

 

The problem 'I think' is that the form elements in every row have the same name as in every other row.  If I show a single record I can change something and click edit and it works fine.  If I fill the table with records, then change something in a row and click edit, it doesn't work.  I have tried to concatenate an index number onto the names of each field, thus making them all unique, but it still isn't working.

 

Here is some code:

 

        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=:hidden";

            $stmt = $dbh->prepare($updatedQuery);

            $stmt->bindParam(':fname', $_POST['firstName'], PDO::PARAM_STR);
            $stmt->bindParam(':lname', $_POST['lastName'], PDO::PARAM_STR);
            $stmt->bindParam(':height', $_POST['height'], PDO::PARAM_STR);
            $stmt->bindParam(':cap', $_POST['cap'], PDO::PARAM_STR);
            $stmt->bindParam(':color', $_POST['color'], PDO::PARAM_STR);
            $stmt->bindParam(':hidden', $_POST['hidden'], PDO::PARAM_STR);

            $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());
        }

  echo "<form action=facultyPage.php method= post>";
     echo '<table id="recordTable">';
          echo '<thead>';
               echo '<tr>';
                    echo "<th>First Name";
                    echo "<th>Last Name";
                    echo "<th>Height</th>";
                    echo "<th>Cap</th>";
                    echo "<th>Color</th>";
                    echo "<th>Editable BUTONNNS</th>";
               echo '</tr>';
           echo '</thead>';
        echo '<tbody>';
        
        $num = 0;
        foreach ($arrValues as $row){

            echo "<tr>";
            echo "<td>" . "<input type=text name=firstName value=" . $row['first_name'] . " </ td>";
            echo "<td>" . "<input type=text name=lastName value=" . $row['last_name'] . " </ td>";
            echo "<td>" . "<input type=text name=height value=" . $row['height'] . " </ td>";
            echo "<td>" . "<input type=text name=cap value=" . $row['cap'] . " </ td>";
            echo "<td>" . "<input type=text name=color value=" . $row['colors'] . " </ td>";
            echo "<td>" . "<input type=hidden name=hidden value='" . $row['employee_id'] . "' /> ";


            //TODO dropdowns
            //echo '<td><select name="degree"></td>';
            //echo '    <option></option>';
            //echo '    <option></option>';
            //echo '</select> ';
            //echo '<td><select name="school" disabled="disabled"></td>';
            //echo    '<option></option>';
            //echo    '<option></option>';
            //echo '</select>';

            echo '<input type="submit" name= "update" value="Update" /></td>';
            echo '</tr>';
            $num++;
            
        }   
                          
            echo '</tbody>';
        echo '</table>';
        echo "</form>";

I am showing it in a datable, I have that working fine.

Each rows form elements have the same names.  I think thats where the problem lies.  Anyone know an approach or trick I can implement so the submit button knows which row it's on?

 

Thanks in advance...

Edited December 5, 2013 by shaung

[Ch0cu3r]

If you are allowing for multiple records to be edited at the same time then name the fields the same but add square brackets to it so the form is submitted as an array. Then loop over the $_POST data and update the records individually. 

 

This is how I'd setup the edit form

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

I am setting up form to be handled as an array when it is submitted, This will cause $_POST['record'] to become a multidimensional array, the employee_id field is used as the key.

 

Here is an example of how the $_POST will be constructed

 

 

Array
(
    [record] => Array
        (
            [1] => Array
                (
                    [firstName] => "firstname for employee id of 1"
                    [lastName] => "lastname for employee id of 1"
                    [height] =>  "height for employee id of 1"
                    [cap] => "cap for employee id of 1"
                    [color] => "color for employee id of 1"
                )

            [2] => Array
                (
                    [firstName] => "firstname for employee id of 2"
                    [lastName] => "lastname for employee id of 2"
                    [height] =>  "height for employee id of 2"
                    [cap] => "cap for employee id of 2"
                    [color] => "color for employee id of 2"
                )

        )

    [update] => update
)

 

 

 

To update the records we'll loop over the $_POST['record'] array, updating each record individually.

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();
    }
}

Edited December 5, 2013 by Ch0cu3r

[shaung]

Thank you for your reply.  The end user will be editing a single record at a time, but having the option to update multiple in one shot may also be handy.  I will tinker with it a bit and let you know how it worked.  THanks.

 

If anyone else has anything to add, would appreciate. it.

 

Thanks,

 

Shaun

[shaung]

Hmmmmm, I just realized something.  It is only updating the last record in the table.  Any other record that I attempt to update, nothing happens.  The last record in the table, which is the one that I added for testing purposes, if I edit it, the edit updates.

 

So the Submit button for the last record is the only one that works.  What is going on here?  What would cause that?

Edited December 7, 2013 by shaung

[Ch0cu3r]

Have you changed my code? As I have tested it and it updates all records.

[shaung]

Sorry I haven't had a lot of time to work on this.

 

What do you mean by this part  (bolded)

 

$id = $row['employee_id'];
echo <<<FORM_FIELDS
<tr>
<td><input type="text" name="record[$id][firstName]" value="{$row['first_name']}" /></td>
<td><input type="text" name="record[$id][lastName]" value="{$row['last_name']}" /></td>
<td><input type="text" name="record[$id][height]" value="{$row['height']}" /></td>
<td><input type="text" name="record[$id][cap]" value="{$row['cap']}" /></ d>
<td><input type="text" name="record[$id]" value="{$row['colors']}" /></td>
        <td><input type="submit" name="update" value="Update" /></td>
</tr>
FORM_FIELDS;

 

I tried putting the "<form action=facultyPage.php method= post>" part in the loop and it made a really whacked out looking 'table'.  Not sure what you mean here.

 

Sorry if I am a bit lost here.  I understand your code otherwise.

 

Thanks

Edited December 8, 2013 by shaung

[Ch0cu3r]

The bit in bold is heredoc syntax. You don't replace it with anything. I tend to use heredoc for echoing large amounts of HTML, saves having to escape quotes

 

This is the code I used for testing

<?php

$dbh = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'pass');

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());
}

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>Editable BUTONNNS</th>
            </tr>
        </thead>
        <tbody>
HTML;


        foreach ($arrValues as $row)
	{
	    $id = $row['employee_id'];
	    echo <<<FORM_FIELDS
    	<tr>
            <td><input type="text" name="record[$id][firstName]" value="{$row['first_name']}" /></td>
            <td><input type="text" name="record[$id][lastName]" value="{$row['last_name']}" /></td>
            <td><input type="text" name="record[$id][height]" value="{$row['height']}" /></td>
            <td><input type="text" name="record[$id][cap]" value="{$row['cap']}" /></ d>
            <td><input type="text" name="record[$id][color]" value="{$row['colors']}" /></td>
            <td><input type="submit" name="update" value="Update" /></td>
        </tr>
FORM_FIELDS;
	}
                          
echo <<<HTML
        </tbody>
    </table>
</form>
HTML;

?>

Edited December 8, 2013 by Ch0cu3r

[shaung]

So heredoc syntax allows HTML to run within PHP without all the echo and escape char hell?  OK, thanks, thats good to know.  With my limited knowledge of web programming and PHP this saves me a lot of headache.

 

I work in a college IT dept.  This app is for our campus bookstore to order graduation gowns for faculty members based on where they went to college.

 

Because of your insight, I now have to make very few pages for this web app.  The index page directs users to this page based on group membership.  If a faculty member logs in, it directs them to the page, then shows his/her single record based on employee ID number - and they can edit their info as necessary.  If they belong to the school bookstore group (who orders gowns based on the info in each record), they see the full table and can edit, delete or add new records.   I had been making separate pages for each task, but now I can implement them on one page in a nice pretty datatable with rows editable in line without having to use javascript.  I could find no examples online that do this, so hopefully this helps others wishing to do the same.  Im lovin' PHP now!

 

Your example works like a champ.  I learned a lot from your help.  Thanks immeasurably!

Edited December 8, 2013 by shaung

[shaung]

I have one more question.  I am now attempting to populate a dropdown using an inner join.

 

Here is the sql statement:  

$sql = "SELECT * FROM fac_detail INNER JOIN institution ON fac_detail.institution_id = institution.id";

 

The 'institution' table has( id mediumInt  and name varchar(255) ).  The 'name' are the colleges the records are associated with...eg.  MIT, UW, USC, Milton College, UCLA....etc.

 

I am trying to show the institution associated with each record in a dropdown.  Just the one that matches the record for that row.

Here is my code:

 

       //the headers for the two new columns added (degree and institution)     
            <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>


...

                //attempting to show the result of the inner join in the dropdown
                <td><select name="record[$id][school]" </td> 
                <option>{$row['institution_id']}</option> 

My dropdown for the institution is showing the ID number rather than the name of the institution from the other table.  I think the problem is in the bolded part of the code.  If I can figure out how to show the school name (institution) in the dropdown, I can figure out everything else I need to do from there.

 

Thanks again, 

 

Shaun

[Ch0cu3r]

$row['institution_id'] holds the institution id. I guess you need to use $row['institution_name'] instead?

<option value="{$row['institution_id']}">{$row['institution_name']}</option>

[shaung]

Here are the tables of the Database:  fac_detail,  institution,  degree

 

fac_detail

detail_id mediumint

employee_id varchar(11)

first_name varchar(25)

last_name varchar(50)

institution_id mediumint

height varchar(5)

cap varchar(15)

degree varchar(4)

colors varchar(25)

 

institution

id mediumint

name varchar(255)

 

degree

degree_type varchar(4)

 

I wish to pull out the name for my particular row from the institution table using an inner join 

 

I am not sure which {$row['??????'] would hold the name of the institution (school) after doing the join.  Would it be something like  {$row['institution.name'] ??

 

Thanks loads,

 

Shaun

Edited December 10, 2013 by shaung

[shaung]

I figured it out.   I use the 'name' field from the table that I'm joining fac_details to.

 

I changed this:

 

                <td><select name="record[$id][school]" </td> 
                <option>{$row['institution_id']}</option> 

 

to this:

 

                <td><select name="record[$id][school]" </td> 
                <option>{$row['name']}</option> 

 

and it worked.

 

 

Thanks!