Using PHP and mySQL to update a Table of Data.

[mattsheets]

This is what I am tring to do...

I have a table with 4 items:

id (primary key)
group_name
employees
surveys

What I need to do is display all the data on a html table in input boxes, and allow a end user to update each field.

I have tried to use this code very hard and had no luck. http://www.phpeasystep.com/mysql/10.html

Anyone care to point me in the right direction?

[mattsheets]

This is a copy one of the options I have tried. It display's the data and looks like it works only the data in the DB never get's updated.

 <?php
        $host=""; // Host name
        $username=""; // Mysql username
        $password=""; // Mysql password
        $db_name="survey"; // Database name
        $tbl_name="viva_test"; // Table name


        // Connect to server and select databse.
        mysql_connect("$host", "$username", "$password")or die("cannot connect");
        mysql_select_db("$db_name")or die("cannot select DB");

        $sql="SELECT *
        FROM $tbl_name";
        $result=mysql_query($sql);

        // Count table rows
        $count=mysql_num_rows($result);
        ?>
    <table class="table">
                <form name="form1" method="post" action="">


    <thead>
        <tr>
            <th style='width: 5%'>ID</th>
            <th style='width: 75%'>Group</th>
            <th style='width: 10%'># Employees</th>
            <th style='width: 10%'># Completed</th>
        </tr>
     </thead>
        <tbody>

        <?php
        while($rows=mysql_fetch_array($result)){
        ?>
        <tr>
        <td ><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
        <td ><input name="group_name[]" class='input-xxlarge' type="text" id="group_name" value="<? echo $rows['group_name']; ?>"></td>
        <td ><input name="employees[]" class='input-mini' type="text" id="employees" value="<? echo $rows['employees']; ?>"></td>
        <td ><input name="surveys[]" class='input-mini'  type="text" id="surveys" value="<? echo $rows['surveys']; ?>"></td>
        </tr>
        <?php
        }
        ?>
       </tbody>

        </table>
    <input class="btn btn-primary" type="submit" name="Submit" value="Submit">
    <a href="#" onclick='location.reload(true); return false;' class="btn btn-danger">Cancel</a>
    <?php



    if($_POST['Submit']){
        for($i=0;$i<$count;$i++){ 
        $sql1 = "UPDATE $tbl_name SET group_name='{$_POST['group_name'][$i]}', employees='{$_POST['employees'][$i]}', surveys='{$_POST['surveys'][$i]}' WHERE id='{$_POST['id'][$i]}'";  
        $result1=mysql_query($sql1);
    }
    }

    if($result1){
    header("location:viva_form_4.php");
    }
    mysql_close();
    ?>
    </form>

[TonyG_cyprus]

Something along these lines covers it (much shortened code, of course)

<?php 

$query=("SELECT * FROM db")
    while($row=mysql_fetch_array($query)){
    $group=$row['groupname'];
    }

 echo" <form action='' method='post'>   
     Group<input type='text' name='group' value='".$group."'>
     <input type='submit' name='send' value='Send'>
     </form>";


     if(isset($_POST['send'])){
    $group=$_POST['group'];
    mysql_query("INSERT INTO db(group)
    VALUES('$group')");
    }
     ?>

[TonyG_cyprus]

Dang, you posted while I was typing, didn't think you'd got that far, ignore mine, I'll look at your code.

[TonyG_cyprus]

I think maybe it's the curly braces in your update sql.

[mattsheets]

Odd those actully are not on the code I am running either the copy and paste or daniweb added them...

I made a few more changes and now it just wipes out the data updating it with nothing (because the ID matches I am sure)

     <?php
        $host=""; // Host name
        $username=""; // Mysql username
        $password=""; // Mysql password
        $db_name="survey"; // Database name
        $tbl_name="viva_test"; // Table name


        // Connect to server and select databse.
        mysql_connect("$host", "$username", "$password")or die("cannot connect");
        mysql_select_db("$db_name")or die("cannot select DB");

        $sql="SELECT 
            id,
            group_name,
            employees,
            surveys
        FROM $tbl_name";
        $result=mysql_query($sql);

        // Count table rows
        $count=mysql_num_rows($result);
        ?>
    <table class="table">
                <form name="form1" method="post" action="">


    <thead>
        <tr>
            <th style='width: 5%'>ID</th>
            <th style='width: 75%'>Group</th>
            <th style='width: 10%'># Employees</th>
            <th style='width: 10%'># Completed</th>
        </tr>
     </thead>
        <tbody>

        <?php
        while($rows=mysql_fetch_array($result)){
        ?>
        <tr>
        <td ><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
        <td ><input name="group_name[]" class='input-xxlarge' type="text" id="group_name" value="<? echo $rows['group_name']; ?>"></td>
        <td ><input name="employees[]" class='input-mini' type="text" id="employees" value="<? echo $rows['employees']; ?>"></td>
        <td ><input name="surveys[]" class='input-mini'  type="text" id="surveys" value="<? echo $rows['surveys']; ?>"></td>
        </tr>
        <?php
        }
        ?>
       </tbody>

        </table>
<input type="submit" name="Submit" value="Submit">
    </form>

    <a href="#" onclick='location.reload(true); return false;' class="btn btn-danger">Cancel</a>
    <?php



    if(isset($_POST['Submit']) && $_POST['Submit'] == 'Submit'){
        for($i=0;$i<$count;$i++){
            $sql1="UPDATE $tbl_name SET group_name='$group_name[$i]', employees='$employees[$i]', surveys='$surveys[$i]'  WHERE id='$id[$i]'";
            $result1=mysql_query($sql1);
            echo $sql1; exit;
        }
    }

    if($result1){
    header("location:viva_form_4.php");
    }
    mysql_close();
    ?>
    </form>

[TonyG_cyprus]

I would comment out the line 70, and put in some echo's to test if it's the form sending, or the sql update. Then you're only looking at one thing.