SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating Multiple Rows

    I am having some trouble updating multiple rows at once. Below is the code. Any help is greatly appreciated.

    PHP Code:
    <?php
    $host
    ="12.34.56.78"// Host name
    $username="username"// Mysql username
    $password="password"// Mysql password
    $db_name="arcola399"// Database name
    $tbl_name="availability"// 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);
    ?>
    <br /><br />
    <form name="form1" method="post" action="">
    <table cellspacing="0" cellpadding="0" class="chart" align="center">
                <tr class="row_head">
                    <td>ID</td>
                    <td>Suite No.</td>
                    <td>Level</td>
                    <td>Square Ft.</td>
                    <td>Availabilty</td>
                    <td>Extras</td>
                </tr>    
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr class="row">
    <td><? $id[]=$rows['ID']; ?><? echo $rows['ID']; ?></td>
    <td><? echo $rows['Suite']; ?></td>
    <td><input name="Level[]" type="text" id="Level" value="<? echo $rows['Level']; ?>"></td>
    <td><input name="Sqft[]" type="text" id="Sqft" value="<? echo $rows['Sqft']; ?>"></td>
    <td><input name="Available[]" type="text" id="Available" value="<? echo $rows['Available']; ?>"></td>
    <td><input name="Extras[]" type="text" id="Extras" value="<? echo $rows['Extras']; ?>"></td>
    </tr>
    <?php
    }
    ?>
    </table><br /><br />
    <div class="align-center"><input type="submit" name="Submit" value="Submit"></div>
    </form>
    <?php
    // Check if button name "Submit" is active, do this
    if($Submit){
    for(
    $i=0;$i<$count;$i++){
    $sql1="UPDATE $tbl_name SET Level='$Level[$i]', Sqft='$Sqft[$i]', Available='$Available[$i]', Extras='$Extras[$i]' WHERE ID='$id[$i]'";
    $result1=mysql_query($sql1) or die("Cannot Update");
    }
    }

    if(
    $result1){
    header("location:index.php");
    }
    mysql_close();
    ?>

  2. #2
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the problem you are experiencing? Are you getting any error messages. While you are debugging I would get rid if the header() redirect at the end of the script.

    I am not totally sure if the header redirect might be causing the mysql query to stop early when the redirect is applied.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have removed the header redirect but it is still not updating the database. I am not receiving any errors it just seems to refresh the page.

    Here is a link to the page.

  4. #4
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to do a little debugging here.

    Try this:

    Code PHP:
    if($Submit){
    echo "we have submitted";

    If you see the text then you know the loop is being executed.

    I suspect the line may need modifying to this:
    Code PHP:
    if($_POST['Submit']){

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That seems to get me closer. Now the issue seems to be with my update statement, it clears all data from my table on submit it must not be getting the values from the form.

  6. #6
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try echoing out the values you are putting into the sql query. Since you are using the values of a submitted form there should be $_POST in there somewhere.

    Also, the form displays before the query has executed, so once you submit the form, the form will be populated with the old data.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not sure what is going on. If I echo out the values, everything looks right, but it still doesn't get entered in the database. There is some issue with the update statement that I can't figure out. I am just starting to learn PHP, I develop in ASP typically.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    echo out the entire UPDATE statement, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. After messing around trying to echo the Update statement, I figured it out. Everything is now working.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    mind sharing what the problem was?

    you had several people take time out of their day to try to help you, so it's only fair
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •