Unable to update database from textbox

Question

silentkill89 0 Newbie Poster

11 Years Ago

I am trying to update whatever content in the textbox that has been edited and post to database. However, only the second record is update but the first record is not. I think should be the while loop problem but I don't what is the mistake. Here's my edit page code: viewadmindb.php

<?php
session_start();
include('adminconfig.php');


$sql = "SELECT * FROM admin ORDER BY ID"; 
$result = mysql_query($sql);  
?>


<body>
<div id="wrap">
<div id="status"></div>
<form method="POST" action="adminsave.php" onSubmit="return validate(this);">
   <table class="viewdb"  contentEditable="true">
   <tr><td id='fcolor' style='border:2px solid black' align=center> ID </td>
   <td id='fcolor' style='border:2px solid black' align=center> Name </td>
   <td id='fcolor' style='border:2px solid black' align=center> Password </td>
   <td id='fcolor' style='border:2px solid black; width:auto;' align=center> Department </td>
   <td id='fcolor' style='border:2px solid black' align=center> Email </td></tr>
    <div id="content">
    <?php

 while($row = mysql_fetch_array($result)){ ?>
    <tr>
    <td style='border:2px solid black; width:auto' align=center><?php echo $row['ID'] ?> </td>
    <td style='border:2px solid black' align=center> <?php echo  $row['name']  ?> </td>
    <td style='border:2px solid black' align=center> <?php echo $row['password']  ?> </td>
    <td style='border:2px solid black; width:200px' align=center> <?php echo $row['department'] ?> </td>
    <td style='border:2px solid black' align=center> <?php echo $row['email'] ?> </td>

    <tr>
<td><input id='edit' type = 'text' name="ID[]" value='<?php echo ($row['ID']); ?>' maxlength="50"/></td>
<td><input id='edit' type = 'text' name="name[]" value='<?php echo $row['name'] ?>' maxlength="50"></td>
<td><input id='edit' type = 'text' name="password[]" value='<?php echo $row['password'] ?>' maxlength=50"></td>
<td><input id='edit' type = 'text' name="department[]" value='<?php echo $row['department'] ?>' maxlength="50"></td>
<td><input id='edit' type = 'text' name="email[]" value='<?php echo $row['email'] ?>' style='width:300px' " maxlength="50"></td>
<?php } ?>
<td><input id='edit' type='submit' name='submit' value='Submit' /></td></tr>
    </table>
    </form>
<?php 
    $ID=$row['ID'];
    $name=$row['name'];
    $password=$row['password'];
    $department=$row['department'];
    $email=$row['email'];
    ?>

adminsave.php

<?php
session_start();
include('adminconfig.php');

$ID=$_POST['ID'];
$name=$_POST['name'];
$password=$_POST['password'];
$department=$_POST['department'];
$email=$_POST['email'];


    $sql = "UPDATE `admin` SET `name` = '{$name}', `password` = '{$password}', `department` = '{$department}', `email` = '{$email}' WHERE `ID` = '{$ID}'";

    $result = mysql_query($sql);
    if(!$result){
       die('invalid query:'.mysql_error());
     }
 else
echo ("<tr><td>" . "Data updated succesfully..." . "</td></tr>");
header('Refresh:5; url=viewadmindb.php');
die;

?>

php

2 Contributors
4 Replies
215 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by F-3000

F-3000 0 Newbie Poster

11 Years Ago

First of all, your query in adminsave.php is begging for sql-injection attack. Even if it's a page meant for admins, do not rely blindly on the input, even if you have javascript validation (javascript is ran on clientside, easily modified). A tiny bug somewhere in the code could mean compromise of the whole database. Or, what if the logged in admin is using compromised PC?

Secondly, I would suggest using mysqli (or DPO) extension instead of mysql, as latter is deprecated.

If your javascript function validate() does affect the data somehow, it could be the culprit for the problem.

If not, then...

I'm rather suprised that you get correct input even for first entry, since $ID should be an array, and you're putting it 'as is' into the query.

If the query for first entry is actually correct (for example, $_POST['id'] containing a string with numeric value, etc), I'd suspect that true problem is with that your PHP does not get the post values as an (sub-)arrays. Since if it would, you would be getting MySQL error stating that there is no row with id "Array", because you would be trying to put array into a string. I think that the problem with the post values not being arrays is related to either Apache or PHP settings, I don't remember which, or which setting is it.

In current update-query, those {} do basically nothing, as far as I'm aware.

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.

F-3000 0 Newbie Poster · Answer 1 · 2013-08-06T13:11:53+00:00

Oh, also, in HTML you should use certain value in id-attribute only once per document.

Sorry if I sound offensive, not my intend.

silentkill89 0 Newbie Poster · Answer 2 · 2013-08-07T00:47:42+00:00

F-3000, no offense, thanks for your guidance, but can show me how your code works? I'm not very clear in understanding by words, can you map me your concept?

F-3000 0 Newbie Poster · Answer 3 · 2013-08-07T08:34:34+00:00

These changes I suggest probably will not fix the problem, but it is a worth to try?

I have not tested any of these, so it might not work 'as is'.

Also, do not save passwords as plain text into database. Encrypt them.

Do not show passwords after saving into database.

adminsave.php

<?php
session_start();
include('adminconfig.php');

$ID=validate_post($_POST['ID']); // ALWAYS validate or sanitize input!
$name=validate_post($_POST['name']); // validate_post represents a custom function
$password=validate_post($_POST['password']);
$department=validate_post($_POST['department']);
$email=validate_post($_POST['email']);

for($a=0;$a<count($ID);$a++) // you need to loop through array
{
    $sql = "UPDATE `admin` SET `name` = '{$name[$a]}', `password` = '{$password[$a]}', `department` = '{$department[$a]}', `email` = '{$email[$a]}' WHERE `ID` = '{$ID[$a]}' LIMIT 1"; // With LIMIT 1 we prevent UPDATE going through whole index/table

    $result = mysqli_query($link, $sql); // use mysqli_ instead of mysql_, mysql_ is going to be deprecated
    if(!$result){
        die('invalid query:'.mysqli_error($link));
    }
}

echo ("<tr><td>" . "Data updated succesfully..." . "</td></tr>");
header('Refresh:5; url=viewadmindb.php');
die;

?>

Some changes to HTML of viewadmindb.php

<tr id="fcolor" class="foo">
<td> ID </td>
<td> Name </td>
<td> Password </td>
<td class="foo2"> Department </td>
<td> Email </td>
</tr>

<tr class="foo">
<td class="foo2"><?php echo $row['ID'] ?> </td>
<td> <?php echo $row['name'] ?> </td>
<td> <?php echo $row['password'] ?> </td>
<td class="foo3"> <?php echo $row['department'] ?> </td>
<td> <?php echo $row['email'] ?> </td>
</tr>

<tr id="edit"> <!-- use ID only once per document -->
<td><input name="ID[]" value='<?php echo ($row['ID']); ?>' maxlength="50"/></td>
<td><input name="name[]" value='<?php echo $row['name'] ?>' maxlength="50"></td>

Some CSS you might want to add, if you utilize my changes to HTML

tr.foo>td {
    border:2px solid black;text-align:center;
}

td.foo2{
    width:auto;
}

td.foo3{
    width:200px;
}