Updating a each single row using php code

Question

Makara 0 Light Poster

9 Years Ago

Hello pals. I have a code here that is updating only the last column of my sql database table. how can I upgrade it to work for the rest upper rows please. my code is as follows

<?php
error_reporting (0);
// Connect to server and select databse.
$mysql_hostname = "localhost";
$mysql_user     = "root";
$mysql_password = "";
$mysql_database = "ison";
$con=mysql_connect("localhost", "root", "")or die("cannot connect");
mysql_select_db("ison",$con)or die("cannot select DB");

$result = mysql_query("SELECT * FROM customer");
?>
<html><head>
<meta http-equiv="content-type" content="text/html; charset=windows-1252">
<title>Pending Report</title>
</head>
<body>
<form method="post" action="#">
<table width="1200" border="1" cellspacing="1" cellpadding="0">
<tr>

<th>S.No</th><th>MSISDN</th><th>CUSTOMERNAME</th><th>TIME</th><th>COMPANY</th><th>REASON</th><th>CLOSED</th><th>COMMENT</th> <th>Update</th>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><?php echo $rows['S_NO']; ?></td>
<td><input name="MSISDN" type="text" id="MSISDN"value="<?php echo $rows['MSISDN']; ?>"/></td>
<td><input name="CUSTOMERNAME" type="text" id="CUSTOMERNAME"value="<?php echo $rows['CUSTOMERNAME']; ?>"/></td>
<td><input name="TIME" type="text" id="TIME"value="<?php echo $rows['TIME']; ?>"/></td>
<td><input name="COMPANY" type="text" id="COMPANY"value="<?php echo $rows['COMPANY']; ?>"/></td>
<td><input name="REASON" type="text" id="REASON"value="<?php echo $rows['REASON']; ?>"/></td>
<td><input name="CLOSED" type="checkbox" id="CLOSED"value=" <?php echo $rows['CLOSED']; ?> "checked />Closed</td>
<td><input name="COMMENT" type="text" id="COMMENT" value="<?php echo $rows['COMMENT']; ?>" size=""/>
</td>
<td><input type="submit" name="submit"  value="update" />
</td></form>
<?php

if(isset($_POST["submit"]))
{
$MSISDN=$_POST['MSISDN'];
$CUSTOMERNAME=$_POST['CUSTOMERNAME'];
$TIME=$_POST['TIME'];
$COMPANY=$_POST['COMPANY'];
$REASON=$_POST['REASON'];
$CLOSED=$_POST['CLOSED'];
$COMMENT=$_POST['COMMENT'];
$query = "SELECT * FROM `customer` WHERE `MSISDN` = '$_POST[MSISDN]'";
$result = mysql_query($query);
$sql = array(); 

foreach($_POST as $field => $value) {  
    $sql[] = '("'.mysql_real_escape_string($row['S_NO']).'")';  
}

$sql1="UPDATE customer SET COMMENT='$_POST[COMMENT]' WHERE MSISDN='$_POST[MSISDN]'";
$result=mysql_query($sql1);
header('Location: index.php');
      echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY...</span>';
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  else
  {    header('Location: index.php');
      echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY....</span>';

  }
}
}

?> 
</table>






<?php
$page = $_SERVER['PHP_SELF'];
$sec = "20";
?>
<html>
<head>
<meta http-equiv="refresh" content="<?php echo $sec?>;URL='<?php echo $page?>'">
</head>
<body>
<?php
echo "Page is refreshing...!";
?>
</body></html>

php

3 Contributors
15 Replies
2K Views
3 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by Makara

DaveAmour 160 Mmmmmm beer

9 Years Ago

Just add them seperated with commas eg:

$sql1="UPDATE customer SET COMMENT='$_POST[COMMENT]', COMPANY = '$_POST[COMPANY]' WHERE MSISDN='$_POST[MSISDN]'";

lps 71 Posting Pro in Training

9 Years Ago

First of all mysql_query and mysql_fetch_array has been depreciated. Please look into http://php.net/manual/en/function.mysql-query.php for details and replacement.

What is the purpose if this line of code $query = "SELECT * FROMcustomerWHEREMSISDN= '$_POST[MSISDN]'"; since you never used the result queried.

How is you database structure? Is MSISDN field is unique?

As from you code, please do not assume that if you put the if(isset($_POST["submit"])) inside your while loop and it will run correctly, and the html with same id and name will made it can read the last data only.

What you can do:
1. Close the while loop before the submit button so that only one submit button in one table since it is only one form with one one kind of behavior.
2. Change the name and id of the elements to array like name="MSISDN[]" id="MSISDN[]" for example.
3. When receive the $_POST, use a foreach loop to get each line of data and update the database in the loop.

foreach($_POST['MSISDN'] as $index => $value){
    $sql1="UPDATE customer SET COMMENT='$_POST[COMMENT][$index]' WHERE MSISDN='$_POST[MSISDN][$index]'";
    if (!mysql_query($sql,$con)){
        die('Error: ' . mysql_error());
    }else{
        header('Location: index.php');//the code after this line is ignored as the page already redirected
    }
}

Edited 9 Years Ago by lps because: insert example code

lps 71 Posting Pro in Training

9 Years Ago

wonder what you need is just update one row of data only? if it is, the submit button name also have to be changed into array.
I modified and restructure your code:

<?php
    error_reporting (0);
    // Connect to server and select databse.
    $mysql_hostname = "localhost";
    $mysql_user     = "root";
    $mysql_password = "";
    $mysql_database = "ison";
    $con = mysql_connect("localhost", "root", "")or die("cannot connect");
    mysql_select_db("ison",$con)or die("cannot select DB");
    $result = mysql_query("SELECT * FROM customer");
    $page = $_SERVER['PHP_SELF'];
    $sec = "20";
?>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=windows-1252">
        <meta http-equiv="refresh" content="<?php echo $sec?>;URL='<?php echo $page?>'">
        <title>Pending Report</title>
    </head>
    <body>
    <form method="post" action="#">
        <table width="1200" border="1" cellspacing="1" cellpadding="0">
            <tr>
                <th>S.No</th><th>MSISDN</th><th>CUSTOMERNAME</th><th>TIME</th><th>COMPANY</th><th>REASON</th><th>CLOSED</th><th>COMMENT</th> <th>Update</th>
            </tr>
<?php
    $row_num = 0;
    while($rows=mysql_fetch_array($result)){
?>
        <tr>
            <td><?php echo $rows['S_NO']; ?></td>
            <td><input name="MSISDN[$row_num]" type="text" id="MSISDN[$row_num]"value="<?php echo $rows['MSISDN']; ?>"/></td>
            <td><input name="CUSTOMERNAME[$row_num]" type="text" id="CUSTOMERNAME[$row_num]"value="<?php echo $rows['CUSTOMERNAME']; ?>"/></td>
            <td><input name="TIME[$row_num]" type="text" id="TIME[$row_num]"value="<?php echo $rows['TIME']; ?>"/></td>
            <td><input name="COMPANY[$row_num]" type="text" id="COMPANY[$row_num]"value="<?php echo $rows['COMPANY']; ?>"/></td>
            <td><input name="REASON[$row_num]" type="text" id="REASON[$row_num]"value="<?php echo $rows['REASON']; ?>"/></td>
            <td><input name="CLOSED[$row_num]" type="checkbox" id="CLOSED[$row_num]"value=" <?php echo $rows['CLOSED']; ?> "checked />Closed</td>
            <td><input name="COMMENT[$row_num]" type="text" id="COMMENT[$row_num]" value="<?php echo $rows['COMMENT']; ?>" size=""/></td>
            <td><input type="submit" name="submit[$row_num]"  value="update" /></td>
        </tr>
<?php
    }
?>
        </table>
    </form>
<?php
if(isset($_POST["submit"])){
    foreach($_POST["submit"] as $index => $value){
        if(isset($value)){
            $MSISDN = $_POST['MSISDN'][$index];
            $CUSTOMERNAME=$_POST['CUSTOMERNAME'][$index];
            $TIME=$_POST['TIME'][$index];
            $COMPANY=$_POST['COMPANY'][$index];
            $REASON=$_POST['REASON'][$index];
            $CLOSED=$_POST['CLOSED'][$index];
            $COMMENT=$_POST['COMMENT'][$index];
            $sql1="UPDATE customer SET COMMENT='$COMMENT' WHERE MSISDN='$MSISDN'";
            $result=mysql_query($sql1);
            if (!mysql_query($sql,$con)){
                die('Error: ' . mysql_error());
            }else{
                echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY...</span>';
                header('Location: index.php');
            }
        }
    }
}
echo "Page is refreshing...!";
?>
    </body>
</html>

DaveAmour 160 Mmmmmm beer

9 Years Ago

Please ignore my suggestion - I misread the quetion and read column instead of row for some reason!

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Makara 0 Light Poster · Answer 1 · 2015-03-26T07:03:54+00:00

I need to update each row by its own.When I click on update button, am only able to update only the last row. this table is fetched from the sql table in phpmyadmin and displayed as a html table. my screen shot of the code is

Makara 0 Light Poster · Answer 2 · 2015-03-26T07:45:35+00:00

It is not updating. It is returning an error message 'Query is empty'

lps 71 Posting Pro in Training · Answer 3 · 2015-03-26T07:49:52+00:00

My bad, change if (!mysql_query($sql,$con)){ into if (!$result){

Makara 0 Light Poster · Answer 4 · 2015-03-26T07:53:24+00:00

The following code is the one that is updating the table after changing the $sql1 to $sql. but still it is only updating the last row only. other rows it doesnt work for them.

    <?php
    error_reporting (0);
    // Connect to server and select databse.
    $mysql_hostname = "localhost";
    $mysql_user = "root";
    $mysql_password = "";
    $mysql_database = "ison";
    $con = mysql_connect("localhost", "root", "")or die("cannot connect");
    mysql_select_db("ison",$con)or die("cannot select DB");
    $result = mysql_query("SELECT * FROM customer");
    $page = $_SERVER['PHP_SELF'];
    $sec = "20";
    ?>
    <html>
    <head>
    <meta http-equiv="content-type" content="text/html; charset=windows-1252">
    <meta http-equiv="refresh" content="<?php echo $sec?>;URL='<?php echo $page?>'">
    <title>Pending Report</title>
    </head>
    <body>
    <form method="post" action="#">
    <table width="1200" border="1" cellspacing="1" cellpadding="0">
    <tr>
    <th>S.No</th><th>MSISDN</th><th>CUSTOMERNAME</th><th>TIME</th><th>COMPANY</th><th>REASON</th><th>CLOSED</th><th>COMMENT</th> <th>Update</th>
    </tr>
    <?php
    $row_num = 0;
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td><?php echo $rows['S_NO']; ?></td>
    <td><input name="MSISDN[$row_num]" type="text" id="MSISDN[$row_num]"value="<?php echo $rows['MSISDN']; ?>"/></td>
    <td><input name="CUSTOMERNAME[$row_num]" type="text" id="CUSTOMERNAME[$row_num]"value="<?php echo $rows['CUSTOMERNAME']; ?>"/></td>
    <td><input name="TIME[$row_num]" type="text" id="TIME[$row_num]"value="<?php echo $rows['TIME']; ?>"/></td>
    <td><input name="COMPANY[$row_num]" type="text" id="COMPANY[$row_num]"value="<?php echo $rows['COMPANY']; ?>"/></td>
    <td><input name="REASON[$row_num]" type="text" id="REASON[$row_num]"value="<?php echo $rows['REASON']; ?>"/></td>
    <td><input name="CLOSED[$row_num]" type="checkbox" id="CLOSED[$row_num]"value=" <?php echo $rows['CLOSED']; ?> "checked />Closed</td>
    <td><input name="COMMENT[$row_num]" type="text" id="COMMENT[$row_num]" value="<?php echo $rows['COMMENT']; ?>" size=""/></td>
    <td><input type="submit" name="submit[$row_num]" value="update" /></td>
    </tr>
    <?php
    }
    ?>
    </table>
    </form>
    <?php
    if(isset($_POST["submit"])){
    foreach($_POST["submit"] as $index => $value){
    if(isset($value)){
    $MSISDN = $_POST['MSISDN'][$index];
    $CUSTOMERNAME=$_POST['CUSTOMERNAME'][$index];
    $TIME=$_POST['TIME'][$index];
    $COMPANY=$_POST['COMPANY'][$index];
    $REASON=$_POST['REASON'][$index];
    $CLOSED=$_POST['CLOSED'][$index];
    $COMMENT=$_POST['COMMENT'][$index];
    $sql="UPDATE customer SET COMMENT='$COMMENT' WHERE MSISDN='$MSISDN'";
    $result=mysql_query($sql);
    if (!mysql_query($sql,$con)){
    die('Error: ' . mysql_error());
    }else{
    echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY...</span>';
    header('Location: index.php');
    }
    }
    }
    }
    echo "Page is refreshing...!";
    ?>
    </body>
    </html>

Makara 0 Light Poster · Answer 5 · 2015-03-26T07:56:18+00:00

Makara 0 Light Poster

9 Years Ago

I have checked the live chat lps

lps 71 Posting Pro in Training · Answer 6 · 2015-03-26T08:11:25+00:00

Try to update the row number 2 data to sg and click on the submit button at row number 2 with this code:

<?php
    error_reporting (0);
    // Connect to server and select databse.
    $mysql_hostname = "localhost";
    $mysql_user     = "root";
    $mysql_password = "";
    $mysql_database = "ison";
    $con = mysql_connect("localhost", "root", "")or die("cannot connect");
    mysql_select_db("ison",$con)or die("cannot select DB");
    $result = mysql_query("SELECT * FROM customer");
    $page = $_SERVER['PHP_SELF'];
    $sec = "20";
?>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=windows-1252">
        <meta http-equiv="refresh" content="<?php echo $sec?>;URL='<?php echo $page?>'">
        <title>Pending Report</title>
    </head>
    <body>
    <form method="post" action="#">
        <table width="1200" border="1" cellspacing="1" cellpadding="0">
            <tr>
                <th>S.No</th><th>MSISDN</th><th>CUSTOMERNAME</th><th>TIME</th><th>COMPANY</th><th>REASON</th><th>CLOSED</th><th>COMMENT</th> <th>Update</th>
            </tr>
<?php
    $row_num = 0;
    while($rows=mysql_fetch_array($result)){
?>
        <tr>
            <td><?php echo $rows['S_NO']; ?></td>
            <td><input name="MSISDN[$row_num]" type="text" id="MSISDN[$row_num]"value="<?php echo $rows['MSISDN']; ?>"/></td>
            <td><input name="CUSTOMERNAME[$row_num]" type="text" id="CUSTOMERNAME[$row_num]"value="<?php echo $rows['CUSTOMERNAME']; ?>"/></td>
            <td><input name="TIME[$row_num]" type="text" id="TIME[$row_num]"value="<?php echo $rows['TIME']; ?>"/></td>
            <td><input name="COMPANY[$row_num]" type="text" id="COMPANY[$row_num]"value="<?php echo $rows['COMPANY']; ?>"/></td>
            <td><input name="REASON[$row_num]" type="text" id="REASON[$row_num]"value="<?php echo $rows['REASON']; ?>"/></td>
            <td><input name="CLOSED[$row_num]" type="checkbox" id="CLOSED[$row_num]"value=" <?php echo $rows['CLOSED']; ?> "checked />Closed</td>
            <td><input name="COMMENT[$row_num]" type="text" id="COMMENT[$row_num]" value="<?php echo $rows['COMMENT']; ?>" size=""/></td>
            <td><input type="submit" name="submit[$row_num]"  value="update" /></td>
        </tr>
<?php
    }
?>
        </table>
    </form>
<?php
if(isset($_POST["submit"])){
    foreach($_POST["submit"] as $index => $value){
        if(!empty($value)){
            echo $index;
            echo $MSISDN = $_POST['MSISDN'][$index];
            $CUSTOMERNAME=$_POST['CUSTOMERNAME'][$index];
            $TIME=$_POST['TIME'][$index];
            $COMPANY=$_POST['COMPANY'][$index];
            $REASON=$_POST['REASON'][$index];
            $CLOSED=$_POST['CLOSED'][$index];
            echo $COMMENT=$_POST['COMMENT'][$index];
            $sql1="UPDATE customer SET COMMENT='$COMMENT' WHERE MSISDN='$MSISDN'";
            $result=mysql_query($sql1);
            if (!$result){
                die('Error: ' . mysql_error());
            }else{
                echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY...</span>';
                header('Location: index.php');
            }
        }
    }
}
echo "Page is refreshing...!";
?>
    </body>
</html>

lps 71 Posting Pro in Training · Answer 7 · 2015-03-26T08:29:27+00:00

lps 71 Posting Pro in Training

9 Years Ago

Paste me the echoed result please?

lps 71 Posting Pro in Training · Answer 8 · 2015-03-26T08:37:01+00:00

Sorry, my fault again....the code <td><input name="MSISDN[$row_num]" type="text" id="MSISDN[$row_num]"value="<?php echo $rows['MSISDN']; ?>"/></td should be <td><input name="MSISDN[<?php echo $row_num; ?>]" type="text" id="MSISDN[<?php echo $row_num; ?>]"value="<?php echo $rows['MSISDN']; ?>"/></td>. I missed out the php tag and echo. Please do implement to all similar structure.

Makara 0 Light Poster · Answer 9 · 2015-03-26T08:43:39+00:00

its returning same results. Only updating the last row only for any update button

lps 71 Posting Pro in Training · Answer 10 · 2015-03-26T08:55:25+00:00

Forgot to add in the $row_num++; before the closing of while loop when generating the elements.

Makara 0 Light Poster · Answer 11 · 2015-03-26T08:59:33+00:00

Thank you lps. this is the working code now

    <?php
    error_reporting (0);
    // Connect to server and select databse.
    $mysql_hostname = "localhost";
    $mysql_user = "root";
    $mysql_password = "";
    $mysql_database = "ison";
    $con = mysql_connect("localhost", "root", "")or die("cannot connect");
    mysql_select_db("ison",$con)or die("cannot select DB");
    $result = mysql_query("SELECT * FROM customer");
    $page = $_SERVER['PHP_SELF'];
    $sec = "20";
    ?>
    <html>
    <head>
    <meta http-equiv="content-type" content="text/html; charset=windows-1252">
    <meta http-equiv="refresh" content="<?php echo $sec?>;URL='<?php echo $page?>'">
    <title>Pending Report</title>
    </head>
    <body>
    <form method="post" action="#">
    <table width="1200" border="1" cellspacing="1" cellpadding="0">
    <tr>
    <th>S.No</th><th>MSISDN</th><th>CUSTOMERNAME</th><th>TIME</th><th>COMPANY</th><th>REASON</th><th>CLOSED</th><th>COMMENT</th> <th>Update</th>
    </tr>
    <?php
    $row_num = 1;
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td><?php echo $rows['S_NO']; ?></td>
    <td><input name="MSISDN[<?php echo $row_num; ?>]" type="text" id="MSISDN[<?php echo $row_num; ?>]"value="<?php echo $rows['MSISDN']; ?>"/></td>
    <td><input name="CUSTOMERNAME[<?php echo $row_num; ?>]" type="text" id="CUSTOMERNAME[<?php echo $row_num; ?>]"value="<?php echo $rows['CUSTOMERNAME']; ?>"/></td>
    <td><input name="TIME[<?php echo $row_num; ?>]" type="text" id="TIME[<?php echo $row_num; ?>]"value="<?php echo $rows['TIME']; ?>"/></td>
    <td><input name="COMPANY[<?php echo $row_num; ?>]" type="text" id="COMPANY[<?php echo $row_num; ?>]"value="<?php echo $rows['COMPANY']; ?>"/></td>
    <td><input name="REASON[<?php echo $row_num; ?>]" type="text" id="REASON[<?php echo $row_num; ?>]"value="<?php echo $rows['REASON']; ?>"/></td>
    <td><input name="CLOSED[<?php echo $row_num; ?>]" type="checkbox" id="CLOSED[<?php echo $row_num; ?>]"value=" <?php echo $rows['CLOSED']; ?> "checked />Closed</td>
    <td><input name="COMMENT[<?php echo $row_num; ?>]" type="text" id="COMMENT[<?php echo $row_num; ?>]" value="<?php echo $rows['COMMENT']; ?>" size=""/></td>
    <td><input type="submit" name="submit[<?php echo $row_num; ?>]" value="update" /></td>
    </tr>
    <?php
    $row_num++; 
    }
    ?>
    </table>
    </form>
    <?php
    if(isset($_POST["submit"])){
    foreach($_POST["submit"] as $index => $value){
    if(!empty($value)){
    echo $index; 
    echo $MSISDN = $_POST['MSISDN'][$index];
    $CUSTOMERNAME=$_POST['CUSTOMERNAME'][$index];
    $TIME=$_POST['TIME'][$index];
    $COMPANY=$_POST['COMPANY'][$index];
    $REASON=$_POST['REASON'][$index];
    $CLOSED=$_POST['CLOSED'][$index];
    echo $COMMENT=$_POST['COMMENT'][$index];
    $sql1="UPDATE customer SET COMMENT='$COMMENT' WHERE MSISDN='$MSISDN'";
    $result=mysql_query($sql1);
    if (!$result){
    die('Error: ' . mysql_error());
    }else{
    echo '<span style="color:#6f892c;">RECORD INSERTED SUCCESSFULLY...</span>';
    header('Location: index.php');
    }
    }
    }
    }
    echo "Page is refreshing...!";
    ?>
    </body>
    </html>