Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Feb 2009
    Location
    Indore (INDIA)
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Multiple rows update

    Hello all,

    I want to update multiple rows of my table in database with one query.

    Looking for a way to update multiple rows of a db I found a tutorial on first page of google... .

    http://www.phpeasystep.com/mysql/10.html

    I then followed the tutorial which is quite simple

    so I modified it according to suit, but then couldn't get it to work.

    Can anyone spot any obvious problems with this tutorial.

    Please Suggest me any solutions regarding these or any other similar tutorial as quite simple as these.

    Thanks & regards.

  • #2
    New Coder
    Join Date
    Mar 2008
    Posts
    88
    Thanks
    12
    Thanked 0 Times in 0 Posts
    show your code

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    There's nothing wrong with that tutorial, apart from the lack of error checking on the queries, which is a noob mistake. However, it doesn't use a single query to update the entire form, it simply uses a loop to update each row, one at a time. This is really the only way to do it, if you are updating different data for each row and the rows don't share a common value (which is the case in the tutorial you pointed to).

    So either get more specific what you are trying to update or live with multiple queries to get all the updates done.

  • #4
    New Coder
    Join Date
    Feb 2009
    Location
    Indore (INDIA)
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Reply to multiple row update

    Hello all,

    Thanks for your precious suggestions.

    Here is my code

    Code:
     
    <?php
    
    $host="localhost"; // Host name 
    $username="myusername"; // Mysql username 
    $password="mypassword"; // Mysql password 
    
    
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("test3")or die("cannot select DB");
    
    
    // Retrieve data from database 
    $sql="SELECT * FROM table3 ";
    
    $result=mysql_query($sql);
    
    ?>
    
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    <form name="form1" method="post" action="">
    <tr> 
    <td>
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    
    <tr>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    </tr>
    <tr>
    <td align="center"><strong>Id</strong></td>
    <td align="center"><strong>Client</strong></td>
    <td align="center"><strong>Project</strong></td>
    <td align="center"><strong>Module</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><? $id[]=$rows['id']; ?> <? echo $rows['id']; ?> </td>
    <td align="center"><input name="client[]"  type="text" id="client" value="<? echo $rows['client']; ?>"> </td>
    <td align="center"><input name="project[]" type="text" id="project" value="<? echo $rows['project']; ?>"></td>
    <td align="center"><input name="module[]" type="text" id="module" value="<? echo $rows['module']; ?>"></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit new Details"></td>
    </tr>
    </table>
    </td>
    </tr>
    </form>
    </table>
    
    
    <?php
    
    if($Submit){ 
     
    for($i=0;$i<$count;$i++){
    
    $query = "UPDATE table3 SET client='$client[$i]', project='$project[$i]', module='$module[$i]'  WHERE id='$id[$i]'"; 
     
    $result2 = mysql_query($query);
    
    }
    } 
    
    if ($result2) {
    header("location:mytable3preview.php");
    }
    
    mysql_close();
    
    ?>
    As presently it retrieve data from DB & show their values

    in related text boxes of the table perfactly.

    But on Updating new values in the textboxes & submitting

    them it only Refresh the page,

    and does not send new data & does not Update rows in the DBase.

    Please suggest me the possible corrections that can be

    applied to work it correctly .


    Thanks & Regards.
    Last edited by MSK7; 05-14-2009 at 06:39 AM.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Your variable $count isn't defined anywhere but it's used as the condition for your update loop.

  • #6
    New Coder
    Join Date
    Feb 2009
    Location
    Indore (INDIA)
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Smile Reply to multiple row update

    Hello Fumigator ,

    Thanks for reminding me the missed variable defination row in my code.

    after defining the variable($count) also one problem arises that the query

    started to delete the whole data in the table rather than updating.

    I don't know, but i think that it was

    due to the statement " <? $id[]=$rows['id']; ?> "

    because after removal of which & modifying id row like same as other

    below rows it worked ahead .

    I expanded some new more field rows in the table according to my requirement .

    & also defined the variables $id, $client,... etc. clearly that was not

    defined before & also used " if(isset($_POST['Submit']))" in the place of

    " if($Submit) " .



    Any way finally it worked Successfully .

    & Here is my code which finally worked .

    Hope this code will also help a little bit to other users who required the

    similar multiple row updates results following the same tutorial .

    Code:
    <?php
    
    $host="localhost"; // Host name 
    $username="myusername"; // Mysql username 
    $password="mypassword"; // Mysql password 
    
    
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("test3")or die("cannot select DB");
    
    
    
    // Retrieve data from database 
    $sql="SELECT * FROM table3 ";
    
    $result=mysql_query($sql);
    
    // Count table rows
    $count=mysql_num_rows($result);
    
    ?>
    
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    <form name="form1" method="post" action="" >
    <tr> 
    <td>
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    </tr>
    <tr>
    <td align="center"><strong>Id</strong></td>
    <td align="center"><strong>Client</strong></td>
    <td align="center"><strong>Project</strong></td>
    <td align="center"><strong>Module</strong></td>
    <td align="center"><strong>Activity</strong></td>
    <td align="center"><strong>Hours</strong></td>
    <td align="center"><strong>Billable</strong></td>
    <td align="center"><strong>Description</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><input name="id[]"  type="text" id="id" size="3" value="<? echo $rows['id']; ?>" > </td> 
    <td align="center"><input name="client[]"  type="text" id="client" value="<? echo $rows['client']; ?>"> </td>
    <td align="center"><input name="project[]" type="text" id="project" value="<? echo $rows['project']; ?>"></td>
    <td align="center"><input name="module[]" type="text" id="module" value="<? echo $rows['module']; ?>"></td>
    <td align="center"><input name="activity[]" type="text" id="activity" value="<? echo $rows['activity']; ?>"></td>
    <td align="center"><input name="hours[]" type="text" id="hours" value="<? echo $rows['hours']; ?>"></td>
    <td align="center"><input name="billable[]" type="text" id="billable" value="<? echo $rows['billable']; ?>"></td>
    <td align="center"><input name="description[]" type="text" id="description" value="<? echo $rows['description']; ?>"></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit new Details"></td>
    </tr>
    </table>
    </td>
    </tr>
    </form>
    </table>
    
    
    <?php
    
    // Variables defined
    
    $id = $_POST['id'] ; 
    $client = $_POST['client'] ;
    $project = $_POST['project'];
    $module = $_POST['module'] ;
    $activity = $_POST['activity'] ;
    $hours = $_POST['hours'];
    $billable = $_POST['billable'] ;
    $description = $_POST['description'];  
     
     
    if(isset($_POST['Submit'])) {   
    
    for($i=0;$i<$count;$i++){
    
    $sql1 = "UPDATE `test3`.`table3` SET `client`='$client[$i]', `project`='$project[$i]', `module`='$module[$i]', `activity`='$activity[$i]', `hours`='$hours[$i]', `billable`='$billable[$i]',
     `description`='$description[$i]' WHERE `table3`.`id`='$id[$i]'"; 
     
    $result1 = mysql_query($sql1);
    
    }
    } 
    
    if($result1){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=mytable3preview.php\">";
    }
    
    
    mysql_close();
     
    ?>

    Thanks & Regards to all .
    Last edited by MSK7; 05-14-2009 at 11:06 AM.


  •  

    Posting Permissions

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