[SOLVED] SIMPLE PHP SQL DATABASE UPDATE SCRIPT NOT WORKING CORRECTLY>

cmbcorp · July 25, 2007

Hi Guys,

I am having some issues with updating my sql table with a simple script.

The table name is members, i have 3 fields in the table which are:

ID username password

I created the table as follows:

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

I then:

Dumped the data for table `members`

INSERT INTO `members` VALUES (1, 'jason', 'jason'');

I have 3 php files:

list_records.php

code is as follows:

<?php
$host="localhost"; // Host name 
$username="greenpos_admin1"; // Mysql username 
$password="carlo"; // Mysql password 
$db_name="greenpos_test"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from user details from database. </strong> </td>
</tr>

<tr>
<td align="center"><strong>ID</strong></td>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Password</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['id']; ?></td>
<td><? echo $rows['username']; ?></td>
<td><? echo $rows['password']; ?></td>


<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

The second file (update.php):

<?php
$host="localhost"; // Host name 
$username="greenpos_admin1"; // Mysql username 
$password="carlo"; // Mysql password 
$db_name="greenpos_test"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>ID</strong></td>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Password</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="id" type="text" id="id" value="<? echo $rows['id']; ?>"></td>
<td align="center"><input name="username" type="text" id="myusername" value="<? echo $rows['username']; ?>" size="15"></td>
<td><input name="password" type="text" id="mypassword" value="<? echo $rows['password']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection 
mysql_close();

?>

The third file (update_ac.php):

<?php
$host="localhost"; // Host name 
$username="greenpos_admin1"; // Mysql username 
$password="carlo"; // Mysql password 
$db_name="greenpos_test"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database 
$sql="UPDATE $tbl_name SET id='$id', username='$myusername', password='$mypassword' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

Basically i went to update the the username and password and it works, but when i list the table contents there is no username and password under ID 1 in the table.

Any thoughts guys?

btw you guys rock! i really appreciate your help.

Jason.

trq · July 25, 2007

Notice how your the only person around here using caps in there subjects? Please don't.

Now, can you explain where the variables $id, $myusername and $mypassword are defined? They are used in your query...

$sql="UPDATE $tbl_name SET id='$id', username='$myusername', password='$mypassword' WHERE id='$id'";

Also (side note)... variables do not need to be surrounded by quotes. This...

mysql_connect("$host", "$username", "$password")or die("cannot connect");

only need be....

mysql_connect($host, $username, $password)or die("cannot connect");

trq · July 25, 2007

Your query should actually be failing as at least password and I'm pretty sure username are reserved words in sql. You'll either need to change the filed names or escape the reserved words using `backticks`.

$sql="UPDATE $tbl_name SET id='$id', `username`='$myusername', `password`='$mypassword' WHERE id='$id'";

cmbcorp · July 25, 2007

Notice how your the only person around here using caps in there subjects? Please don't.

Now, can you explain where the variables $id, $myusername and $mypassword are defined? They are used in your query...
$sql="UPDATE $tbl_name SET id='$id', username='$myusername', password='$mypassword' WHERE id='$id'";
Also (side note)... variables do not need to be surrounded by quotes. This...
mysql_connect("$host", "$username", "$password")or die("cannot connect");
only need be....
mysql_connect($host, $username, $password)or die("cannot connect");

cheers for getting back to me, i really appreciate it...

im very new to this.. but im getting the hang of it..

a answer to your question:

Now, can you explain where the variables $id, $myusername and $mypassword are defined? They are used in your query...

im using my simple login script that im using and thats where those variables came from, im gathering that they didnt pass through to my current script?

let me know your thoughts.

cheers,

jason.

cmbcorp · July 25, 2007

Hi,

Thanks for the reply.

Ignore the above quote...

In the update_ac.php i removed the following line:

$sql="UPDATE $tbl_name SET id='$id', username='$myusername', password='$mypassword' WHERE id='$id'";

And replaced it with:

$sql="UPDATE $tbl_name SET id='$id', `username`='$myusername', `password`='$mypassword' WHEREid='$id'";

I went to list my table via list records.php and went to update.

I entered in ID 1 and put in a new username and password and went to update.

It said it was successfull.

I went to list the table contents again and in the id 1, there is no username and password.

Any ideas?

Cheers,

Jason.

BTW... Thank you soooooooooo much for your help.

cmbcorp · July 26, 2007

Now, can you explain where the variables $id, $myusername and $mypassword are defined? They are used in your query...

An answer to that question.. Hope im not repeating my self.

I guess they arnt defined, how would i define them? they are the fields in my table..

Please let me know what i should do.

Cheers,

Jason.

teng84 · July 26, 2007

to define them you have to use this thing

$mypassword ='put a value';

if the value will come from the form then its gonna be this way

$mypassword =$_POST['the name of the forms'];

cmbcorp · July 26, 2007

Hi,

Thanks for the reply.

I have put

$myusername =$_POST['update.php'];
$mypassword =$_POST['update.php'];

In the update_ac.php

And i went to update the details via update_ac.php

And i went to list the table contents and it still hasnt got any values in the fields username and password.

Did i do something wrong?

Cheers.

Thanks.

Jason.

teng84 · July 26, 2007

no no cheers your very wrong i think we still have to walk far

but ill try to find ways to explain

teng84 · July 26, 2007

<?php
$host="localhost"; // Host name 
$username="greenpos_admin1"; // Mysql username 
$password="carlo"; // Mysql password 
$db_name="greenpos_test"; // Database name 
$tbl_name="members"; // Table name 

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// i dont know here you will get the id but for this to work you can set <<<<----------------------------------read
// default value to see how it run
//$id='put anything'; find an id from your db to have this work
$mypassword = $_POST['name'];
$myusername =$_POST['pword'];

$sql="UPDATE $tbl_name SET id='$id', username='$myusername', password='$mypassword' WHERE id='$id'";
$result=mysql_query($sql);

if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}
else {
echo "ERROR";
}
//not i use php self but you can use any page base on your needs
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="<?=$_SERVER['PHP_SELF']?>">
  <label></label>
  <table width="200" border="1">
    <tr>
      <td colspan="2">sample forms </td>
    </tr>
    <tr>
      <td>Name</td>
      <td><input type="text" name="name" /></td>
    </tr>
    <tr>
      <td>password</td>
      <td><input type="text" name="pword" /></td>
    </tr>
  </table>
  <label></label>
</form>
</body>
</html>

this will show you how post work

cmbcorp · July 26, 2007

Hi,

Thanks again for your help.

You guys are great!

I tried your code and still the same thing..

I listed the table contents and the fields are empty still...

any ideas?

Cheers,

Jason.

redarrow · July 26, 2007

there no id you need to add the $id="what ever the is is" to the page before the update.

DeadEvil · July 26, 2007

No problem with your first and second code above however "update_ac.php" no defined variable so i edit the query.

Now try this code...


<?php
$host="localhost"; // Host name 
$username="greenpos_admin1"; // Mysql username 
$password="carlo"; // Mysql password 
$db_name="greenpos_test"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database 
$sql="UPDATE $tbl_name SET id='{$_POST['id']}', username='{$_POST['username']}', password='{$_POST['password']}' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

cmbcorp · July 26, 2007

mate! your the best!!..

that works great!, thanks for all your help.

cheers

jase.

Sign In

[SOLVED] SIMPLE PHP SQL DATABASE UPDATE SCRIPT NOT WORKING CORRECTLY>

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information