updating form in sql not working

[amplexus]

hi,

 

I've looked around and not seen an answer that fits my situation, so here goes.

 

I've got a database and I have a script that displays the values from that database in editable fields and has an update button

 

this is that script:

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM ($newdbname) ";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
//$real_name=mysql_result($result,$i,"real_name");
echo "$username, $password, $real_name";
?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Username: <input type="text" name="username" value="<? echo "$username"?>"><br>
Password: <input type="text" name="password" value="<? echo "$password"?>"><br>

<input type="Submit" value="Update">
</form>

<?php
++$i;
} 
?>

 

it passes the "updated" values to this script:

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);

$query="INSERT $newdbname SET username (user_id, username, password)='$ud_username', password= PASSWORD('$ud_password') WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";
mysql_close();
?>

 

I get no errors, and everything runs just fine.  except it doesn't update any thing.  I'm sure it's a simple thing I'm missing, stupid lack of experience hindering me.

 

any help would be greatly appreciated!

 

[PFMaBiSmAd]

Update queries, as you might imagine use the UPDATE keyword in them, not the INSERT keyword.

 

Your code does not have any error checking or error reporting logic to test the result of the mysql_query() statement, so it would be a little hard for your code to tell you if the query did fail. mysql_query() returns a FALSE value if the query failed due to an error. You would also need to check mysql_affected_rows to determine if the query actually updated the row (after you change the INSERT to UPDATE in the query.)

 

If this is a real application (not just something in a programming class), you must both check in the form processing code that the current visitor is authorized to update the record with the id that was passed to the code and you must also validate/escape the data values being put into the query.

 

Also, in looking at the query, the red part in the following makes no sense and is invalid-

 

username (user_id, username, password)='$ud_username'

[amplexus]

okay, so that tells me that I've got a few things wrong, actually, the things wrong were my attempts to fix a non-functioning script.

 

I've added in

printf ("Updated records: %d\n", mysql_affected_rows());

and it's telling me that -1(none) records were updated, which I was pretty much able to figure out before by checking the database contents.

 

so, how do I make it work?

 

again, and revised

first script:

 

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM ($newdbname) ";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
//$real_name=mysql_result($result,$i,"real_name");
echo "$username, $password, $real_name";
?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Username: <input type="text" name="username" value="<? echo "$username"?>"><br>
Password: <input type="text" name="password" value="<? echo "$password"?>"><br>

<input type="Submit" value="Update">
</form>

<?php
++$i;
} 
?>

 

second script:

<?
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);

$query="update $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
printf ("Updated records: %d\n", mysql_affected_rows());
echo "Record Updated";
mysql_close();
?>

[PFMaBiSmAd]

I don't see any code in your code that is setting the $ud_username, $ud_password, and $ud_id variables.

 

Are you developing and debugging your php code on a system with error_reporting set to E_ALL and display_errors set to ON so that all the php detected errors will be reported and displayed? You should be receiving undefined NOTICE error messages for each of those variables.

 

You would want to assign a value to each of those from the correct $_GET['...'] variable from your form.

 

Edit: Also, your first piece of code implies that the passwords are stored as plain text in the database, while your second piece of code is applying the mysql PASSWORD() function to the entered password while updating it.

 

Also, you should not use the mysql PASSWORD() function in your application.  Instead use either md5() or sha1().

[amplexus]

Okay,  I've added in a GET for each of the variables, and set it up, still no update.  does this have anything to do with sessions? also, the end of the url becomes:

"updated.php?ud_id=&username=test125&password=*22A99BA288DB55E8E230679259740873101CD636"

 

here's the new code

<?php
//session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);


  if(isset($_GET['id']))
         {
         $ud_id = $_GET['id'];
         }
  if(isset($_GET['username']))
         {
         $ud_username = $_GET['username'];
         }
  if(isset($_GET['password']))
         {
         $ud_password = $_GET['password'];
         }
       
     


$query="update $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
printf ("Updated records: %d\n", mysql_affected_rows());
//echo "Record Updated";
mysql_close();
?>

 

I do love this problem solving part,  I've likened it to trying to solve a crossword in another language...

 

[PFMaBiSmAd]

From the URL, ud_id is empty (because your first piece of code is not setting $id) and the password is apparently stored as a hash value in your table (why are you putting it into a form field for editing?)

[amplexus]

the idea is that the site admin can modify a user name and password.  the hash is left in place just in case anyone gains access to the page, but is also applied if you change the password.

 

I've changed the original script to this

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM ($newdbname) ";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result, $i, "user_id");
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
//$real_name=mysql_result($result,$i,"real_name");
echo "$username, $password, $real_name";
?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Username: <input type="text" name="username" value="<? echo "$username"?>"><br>
Password: <input type="text" name="password" value="<? echo "$password"?>"><br>

<input type="Submit" value="Update">
</form>

<?php
++$i;
} 
?>

 

still no change in the records updated.  also, I un-commented the session_start() in the second script.

 

to no avail.

 

 

[PFMaBiSmAd]

So, after you made that change, did you check if the HTML of your form was what you expected it to be?

 

Writing code to accomplish any task involves breaking that task down into a series of steps and writing code to perform each step. Debugging code involves checking at each step if the results are what you expect or not. When you find the point where the results are not what you expect, I can guarantee that the problem lies before that point in the code.

[amplexus]

while I would in no way like to discount the high value of the logic, wisdom and approach of PFMaBiSmAd, is there anyone out there who can give me a bit more help?  I understand that you, PFMaBiSmAd, are getting me to learn more on my own, and as a teacher, I can certainly appreciate it.  However, I am very frustrated right now, since I don't know which parts of my code to fix.  I look up answers, and I change things.  no avail. there is very little of my code that I have NOT changed at this point, and I don't know how to fix it, since I don't know what isn't happening. 

 

anybody feeling kind?

 

first script(which includes the form):

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM ($newdbname) ";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$user_id=mysql_result($result, $i, "user_id");
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
echo "$username, $password, $user_id";
?>
<form action="updated.php">
<input type="hidden" name="user_id" value="<?php echo "$user_id"; ?>">
Username: <input type="text" name="username" value="<?php echo "$username"?>"><br>
Password: <input type="text" name="password" value="<?php echo "$password"?>"><br>
<input type="Submit" value="Update">
</form>
<?php
++$i;
} 
?>

 

aaaand the second script, designed to GET the input passed by the form via POST and UPDATE the database:

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
if(isset($_GET['user_id']))
         {
         $ud_id = $_GET['user_id'];
         }
if(isset($_GET['username']))
         {
         $ud_username = $_GET['username'];
         }
if(isset($_GET['password']))
         {
         $ud_password = $_GET['password'];
         }
$query="UPDATE * FROM $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
printf ("Updated records: %d\n", mysql_affected_rows());
echo '</br><a href="update.php"> Click here to update more records</a>';
mysql_close();
?>

 

[amplexus]

here is what i see happening:

 

in the first script,

 

database is connected to, and

 

$query is set as an action to pull all the info from the database

$result is set to actually pull the info using $query

$num is set as the total number of rows

 

a while loop is set to make sure all results are returned and filled into the form.

 

$user_id, $username, and $password are set as the corresponding values pulled form the database, and are displayed.

 

the form allows for those values to be changed, and passes the values via the submit button to the next script.

 

in the second script,

 

database is connected to, and

 

the values of $ud_id, $ud_username, and $ud_password are set to the values returned by the GET action.

 

$query is set to UPDATE the values in the table WHERE the id equals the value returned by that GET action ($ud_id)

 

database is connected to and $query is run

 

the number of affected rows is displayed, along with a link to return and change more records.

what am I missing?  Is it just syntax?  I'm not getting any errors, and I've already changed everything I can think of.

 

 

help.  me.  please.

 

 

 

 

[amplexus]

So, after you made that change, did you check if the HTML of your form was what you expected it to be?

 

Writing code to accomplish any task involves breaking that task down into a series of steps and writing code to perform each step. Debugging code involves checking at each step if the results are what you expect or not. When you find the point where the results are not what you expect, I can guarantee that the problem lies before that point in the code.

 

I got this as for my form, which is what I expected:

 test123, *22A99BA288DB55E8E230679259740873101CD636, 11<form action="updated.php">
<input type="hidden" name="user_id" value="11">
Username: <input type="text" name="username" value="test123"><br>
Password: <input type="text" name="password" value="*22A99BA288DB55E8E230679259740873101CD636"><br>
<input type="Submit" value="Update">
</form>
BobD, *4CF4CFD025BD3576D2697FC15E11DAEA09C4652B, 10<form action="updated.php">
<input type="hidden" name="user_id" value="10">
Username: <input type="text" name="username" value="BobD"><br>
Password: <input type="text" name="password" value="*4CF4CFD025BD3576D2697FC15E11DAEA09C4652B"><br>
<input type="Submit" value="Update">
</form>
chris, *23AE809DDACAF96AF0FD78ED04B6A265E05AA257, 9<form action="updated.php">
<input type="hidden" name="user_id" value="9">
Username: <input type="text" name="username" value="chris"><br>
Password: <input type="text" name="password" value="*23AE809DDACAF96AF0FD78ED04B6A265E05AA257"><br>
<input type="Submit" value="Update">
</form>

 

[fortnox007]

I hope this is what your looking for...

your submit button doesn't have a name:

<input type="Submit" value="Update">

does this work for you?

<input type="Submit" value="Update" name="submit" />

 

I would also add an if isset submit statement like:

if (isset($_POST['submit'])){
// add your code here that needs to be executed when one presses submit here.
}else{
echo 'press submit';
}

 

[amplexus]

 

tried that fortnox.  no dice.

 

I wonder is it something in the second script that's not getting the info?

 

the url of the second script ends like this:

updated.php?user_id=11&username=test12y&password=*22A99BA288DB55E8E230679259740873101CD636&submit=Update

 

so, the values are coming in, no?

[PFMaBiSmAd]

Based on your first piece of code (the form), you have a field named user_id.

 

The WHERE clause in your update query would therefore need to have user_id, not id.

[amplexus]

so, I've changed it to be:

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
if(isset($_GET['user_id']))
         {
         $ud_id = $_GET['user_id'];
         }
if(isset($_GET['username']))
         {
         $ud_username = $_GET['username'];
         }
if(isset($_GET['password']))
         {
         $ud_password = $_GET['password'];
         }
$query="UPDATE * FROM $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE user_id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
printf ("Updated records: %d\n", mysql_affected_rows());
echo '</br><a href="update.php"> Click here to update more records</a>';
mysql_close();
?>

 

and I still get the same url, and the same lack of updating...

[PFMaBiSmAd]

If you echo mysql_error(); on the next line after the line with your mysql_query() statement, it will tell you why your query is failing (assuming the query is being executed.)

[amplexus]

okay, so the problem is in this line

 

$query="UPDATE  FROM $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE user_id='$ud_id'";

 

I get this error

 

right syntax to use near 'FROM users2 SET username ='test124', password= PASSWORD('*22A99BA288DB55E8E23067' at line 1

 

I tried removing the asterix and got exactly same result.  does this mean it is unnecessary?

 

[PFMaBiSmAd]

In a way that is kind of funny, because your UPDATE query didn't have an incorrect * FROM in it, which you have since taken the * out of, until your Reply #8 in this thread. Immediately before reply #8, that part of the query was correct. Why did you change it?

[amplexus]

well, I put it in because I thought (apparently incorrectly) that it would make it look at all the rows.

 

I think i was using the $query="SELECT * FROM ($newdbname) "; part from the first script as a referencing point on that, in terms of when to use an asterix

[mikosiko]

same answer than in the other forum

 

 

- In your $query definition... what is the value of $newdbname ? do you have that variable defined in your dbinfo.inc.php file?

- Suppress the "@" before your @mysql_select_db if there are an error you are suppressing the display of it.

- write the line mysql_query($query) at least in this way

 

 

mysql_query($query) or die("Query Error: " . mysql_error());

 

that will show that you have an error in your UPDATE sentence

 

$query="UPDATE * FROM $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE id='$ud_id'"

 

is not a valid syntax for the UPDATE clause

http://dev.mysql.com/doc/refman/5.0/en/update.html

 

[amplexus]

 

 

is not a valid syntax for the UPDATE clause

http://dev.mysql.com/doc/refman/5.0/en/update.html

 

okay, I removed the @ symbol.

 

I changed the code to the following, base on what I found in the link you posted me.

 

$query="UPDATE [LOW_PRIORITY] [iGNORE] $newdbname SET username = {'$ud_username'}, {password= PASSWORD('$ud_password')} [WHERE user_id='$ud_id']";
mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);

 

and I get the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[LOW_PRIORITY] [iGNORE] users2 SET username = {'test124'}, {password= PASSWORD('' at line 1Updated records: -1

 

[Pikachu2000]

I think you'll want to use the following syntax. Note that the table and field names are enclosed in backticks (not single-quotes), while any values that are expected to be of the string type are in single-quotes (not backticks).

 

$query = "UPDATE `table_name` SET `field_name_1` = '$value_1', `field_name_2 = '$value_2' WHERE `some_field`= '$some_value'";
$result = mysql_query( $query ) or die( mysql_error() );

[amplexus]

utterly defeated.

 

I cannot make this work.  I cannot understand what is wrong.  I cannot seem to do anything that ever makes this better, only breaks it.  I've read tutorials.  I've read the manual.  I've done a logical breakdown of what should be happening.  I've followed every suggestion you have all given me.

 

I am lost.  and disheartened.

 

here's my code. read it, and have a good laugh at me.

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
if(isset($_GET['user_id']))
         {
         $ud_id = $_GET['user_id'];
         }
if(isset($_GET['username']))
         {
         $ud_username = $_GET['username'];
         }
if(isset($_GET['password']))
         {
         $ud_password = $_GET['password'];
         }

//$query="UPDATE FROM $newdbname SET username ='$ud_username', password= PASSWORD('$ud_password') WHERE user_id='$ud_id'";

$query = "UPDATE FROM `$newdbname` SET `user_name` = '$ud_username', `password` = 'PASSWORD('$ud_password')' WHERE `user_id`= '$ud_id'";

$result = mysql_query( $query ) or die( mysql_error() );

mysql_select_db($database) or die( "Unable to select database");

mysql_query($query) or die("Query Error: " . mysql_error());

echo mysql_error();

printf ("Updated records: %d\n", mysql_affected_rows());
echo '</br><a href="update.php"> Click here to update more records</a>';

mysql_close();
?>

 

could someone with a heart please at least tell me if this is a simple problem?  am I WAY off the mark, or really close, because, honestly, I can't tell anymore.

 

James

 

[wildteen88]

Okay, Your first script is fine. The problem is with your second script.

 

Lets go through the the steps we need to take for updating the records.

 

1. Connect to mysql and select a database

 

This you have done. Except I'd call mysql_select_db() directly after mysql_query(). Rather than placing mysql_select_db() half way through your script.

 

2. Check that the variables user_id, username and password exists within the $_GET superglobal array.

 

Again you have done this, except I'd write the code as

if(isset($_GET['user_id'], $_GET['username'], $_GET['password']))
{
     $ud_id        = $_GET['user_id'];
     $ud_username = $_GET['username'];
     $ud_password = $_GET['password'];
}

 

isset can check more than one variable. Ideally you should also perform some form of validation here but for now we'll keep it like it is.

 

3. If all the variables exist, we'll define the update query and populate it with the new values and update the query.

 

Although you have defined the query it sill isn't right, UPDATE statement doesn't have a FROM clause The correct syntax is

UPDATE table_name SET field1 = 'new value1' AND field2 = 'new value2' WHERE a_field='some value'

Or the alternative syntax is

UPDATE table_name SET (field1, field2) VALUES ('new value1', 'new value2') WHERE a_field='some value'

Both queries do the same, but written differently. Now that we know the correct syntax we'd write your update query as

$query = "UPDATE `$newdbname` SET `user_name` = '$ud_username', `password` = 'PASSWORD('$ud_password')' WHERE `user_id`= '$ud_id'";
$result = mysql_query($query);

 

I'd place these lines after the line that reads $ud_password = $_GET['password']; within the if statement we constructed in step 2 above.

 

4. Check that the query successfully ran without any errors, and check if any rows where updated.

 

Notice in the step above notice I added  '$result = ' before mysql_query. This is so I can check that the query executed correctly.

mysql_query will return false if there is an error, otherwise it'll return true. So we'd write this simple if/else statement to see if the query ran.

if($result)
{
     // query executed fine
}
else
{
     // query didn't execute! Most probably due to an error
}

 

We're half way through step 4. We now need to see if the query updated anything. As you have already been told you'd use the mysql_affected_rows. Again we'd write another if to see if it updated an errors.

if(mysql_affected_rows() == 1)
{
     echo 'Success! We have updated users username/password with the id of -' . $ud_id
}
else
{
    echo 'Sorry, now rows where affected!';
}

 

You'd place this if/else statement after this line // query executed fine. These two if statement we just wrote will go after the line that reads $result = mysql_query($query);.

 

So your complete code

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
mysql_select_db($database) or die( "Unable to select database");

if(isset($_GET['user_id'], $_GET['username'], $_GET['password']))
{
     $ud_id        = $_GET['user_id'];
     $ud_username = $_GET['username'];
     $ud_password = $_GET['password'];

    $query = "UPDATE `$newdbname` SET `user_name` = '$ud_username', `password` = 'PASSWORD('$ud_password')' WHERE `user_id`= '$ud_id'"
    $result = mysql_query( $query ) or die( mysql_error() );
    
    if($result)
    {
         if(mysql_affected_rows() == 1)
        {
             echo 'Success! We have updated the username/password for the users id of -' . $ud_id
             echo '<br /><a href="update.php"> Click here to update more records</a>';
        }
        else
        {
            echo 'Sorry, now rows where affected with the userid of - '. $ud_id;
        }
    }
}
else
{
    echo 'Invalid data provided!<br /><a href="update.php">Chose a record to update</a>';
}

?>

[PFMaBiSmAd]

Or the alternative syntax is

UPDATE table_name SET (field1, field2) VALUES ('new value1', 'new value2') WHERE a_field='some value'

 

^^^ Must be from some other SQL. That's not working mysql.