0

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>
3
Contributors
15
Replies
43
Views
2 Years
Discussion Span
Last Post by Makara
0

Just add them seperated with commas eg:

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

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 by lps: insert example code

0

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 coded_output.pngcoded_output.png

0

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>
0

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

0

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

Edited by lps

0

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>
0

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>

Edited by lps

0

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.

Edited by lps

0

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

0

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

0

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>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.