By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,143 Members | 1,855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,143 IT Pros & Developers. It's quick & easy.

Problems Updating Records In MySQL with PHP

P: n/a
Hello,
Sorry to ask what is probably a simple answer, but I am having problems
updating a table/database from a PHP/

PHTML file. I can Read From the Table, I can Insert into Table/Database, But
not update. Here is a copy of the

script I am using. I do not know what version of MySQL my host is running
nor do I have Shell Access to it. I

would like to setup a script so my website users can update thier records
without my intervention. Thank You In

Advance.

Also you may send replies to st**********@yahoo.com

Begin Script

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("databasename",$db);

if ($id) {
if ($submit) {
$sql = "UPDATE tablename SET
EMail='$EMail',Username='$Username',Password='$Pas sword',Item=

'$Item',Price='$Price',URL='$URL' WHERE Username='$Username'";
$result = mysql_query($sql);
echo "table Updated.";
} else {
// query the DB
$sql = "SELECT * FROM tablename WHERE Username='$Username'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
e-Mail Address:<input type="Text" name="EMail" value="<?php echo
$myrow["EMail"] ?>"><br>
Username:<input type="Text" name="Username" value="<?php echo
$myrow["Username"] ?>"><br>
Password:<input type="Text" name="Password" value="<?php echo
$myrow["Password"] ?>"><br>
Item:<input type="Text" name="Item" value="<?php echo $myrow["Item"]
?>"><br>
Price:<input type="Text" name="Price" value="<?php echo $myrow["Price"]
?>"><br>
Item URL:<input type="Text" name="URL" value="<?php echo $myrow["URL"]
?>"><br>
<input type="Submit" name="Update" value="Update table">
</form>
<?php
}
} else {
// display list of articles
$result = mysql_query("SELECT * FROM tablename",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?Username=%s\">%s %s</a><br>\n", $PHP_SELF,
$myrow["Username"], $myrow["Item"], $myrow

["URL"]);
}
}
// Close DB Connection
mysql_close()
?>

End Script
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
st**********@earthlink.net wrote:

Hello,
Sorry to ask what is probably a simple answer, but I am having problems
updating a table/database from a PHP/

PHTML file. I can Read From the Table, I can Insert into Table/Database, But
not update. Here is a copy of the

script I am using. I do not know what version of MySQL my host is running
nor do I have Shell Access to it. I

would like to setup a script so my website users can update thier records
without my intervention. Thank You In

Advance.

Also you may send replies to st**********@yahoo.com

Begin Script

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("databasename",$db);

if ($id) {
if ($submit) {
$sql = "UPDATE tablename SET
EMail='$EMail',Username='$Username',Password='$Pas sword',Item=

'$Item',Price='$Price',URL='$URL' WHERE Username='$Username'";
$result = mysql_query($sql);
echo "table Updated.";
} else {
// query the DB
$sql = "SELECT * FROM tablename WHERE Username='$Username'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
e-Mail Address:<input type="Text" name="EMail" value="<?php echo
$myrow["EMail"] ?>"><br>
Username:<input type="Text" name="Username" value="<?php echo
$myrow["Username"] ?>"><br>
Password:<input type="Text" name="Password" value="<?php echo
$myrow["Password"] ?>"><br>
Item:<input type="Text" name="Item" value="<?php echo $myrow["Item"]
?>"><br>
Price:<input type="Text" name="Price" value="<?php echo $myrow["Price"]
?>"><br>
Item URL:<input type="Text" name="URL" value="<?php echo $myrow["URL"]
?>"><br>
<input type="Submit" name="Update" value="Update table">
</form>
<?php
}
} else {
// display list of articles
$result = mysql_query("SELECT * FROM tablename",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?Username=%s\">%s %s</a><br>\n", $PHP_SELF,
$myrow["Username"], $myrow["Item"], $myrow

["URL"]);
}
}
// Close DB Connection
mysql_close()
?>

End Script


I don't see $id or $submit in your form. Where are they coming from? I suspect
this is the problem. If so, you'll have to put them in as hidden fields and/or
rename the submit button to "submit".

And you may want to consider using $_GET, $_POST, $_REQUEST, $_SESSION, etc.
If that doesn't clear up the problem:

Is register_globals on or off?
What error(s) are you getting? If none, try error_reporting(E_ALL); at the top
of the script.
Does that username have update privileges?

Regards,
Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com
Jul 17 '05 #2

P: n/a

On 18-Dec-2003, <st**********@earthlink.net> wrote:
Hello,
Sorry to ask what is probably a simple answer, but I am having problems
updating a table/database from a PHP/

PHTML file. I can Read From the Table, I can Insert into Table/Database,
But
not update. Here is a copy of the

script I am using. I do not know what version of MySQL my host is running
nor do I have Shell Access to it. I

would like to setup a script so my website users can update thier records
without my intervention. Thank You In

Advance.

Also you may send replies to st**********@yahoo.com

Begin Script

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("databasename",$db);

if ($id) {
if ($submit) {
$sql = "UPDATE tablename SET
EMail='$EMail',Username='$Username',Password='$Pas sword',Item=

'$Item',Price='$Price',URL='$URL' WHERE Username='$Username'";
$result = mysql_query($sql);
echo "table Updated.";
} else {
// query the DB
$sql = "SELECT * FROM tablename WHERE Username='$Username'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
e-Mail Address:<input type="Text" name="EMail" value="<?php echo
$myrow["EMail"] ?>"><br>
Username:<input type="Text" name="Username" value="<?php echo
$myrow["Username"] ?>"><br>
Password:<input type="Text" name="Password" value="<?php echo
$myrow["Password"] ?>"><br>
Item:<input type="Text" name="Item" value="<?php echo $myrow["Item"]
?>"><br>
Price:<input type="Text" name="Price" value="<?php echo $myrow["Price"]
?>"><br>
Item URL:<input type="Text" name="URL" value="<?php echo $myrow["URL"]
?>"><br>
<input type="Submit" name="Update" value="Update table">
</form>
<?php
}
} else {
// display list of articles
$result = mysql_query("SELECT * FROM tablename",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?Username=%s\">%s %s</a><br>\n", $PHP_SELF,
$myrow["Username"], $myrow["Item"], $myrow

["URL"]);
}
}
// Close DB Connection
mysql_close()
?>

End Script


You have several problems

1) assuming you are running with register_globals off, you still need to use
addslashes() on the variables you receive from your form
2) you aren't doing any error checking. You echo "table updated" after the
query without checking the result. You should have some way of displaying
the MySQL error when the query fails-- like 'mysql_query($sql) or die($sql.'
failed because '.mysql_error());'
3) where does $id come from?

Not being able to update is usually caused by
1) where clause failed
2) no update permission
3) syntax error in SQL (bad column name, etc.)
4) not connected to DB

If you add the 'or die()' to your code you will probably get your answer.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #3

P: n/a
To All,
Thank You For the Help. The $id was mis named (Wrong Field/Colum) but to
answer the question that fild is a passed argument from another page. To be
honest I do little PHP/SQL Programing where is do Error Trapping, I know
this is bad but the Manual that I have (From PHP.net) does not explain very
well how to error trap.

Thanks
Steven Y.

"Shawn Wilson" <sh***@glassgiant.com> wrote in message
news:3F***************@glassgiant.com...
st**********@earthlink.net wrote:

Hello,
Sorry to ask what is probably a simple answer, but I am having problems
updating a table/database from a PHP/

PHTML file. I can Read From the Table, I can Insert into Table/Database, But not update. Here is a copy of the

script I am using. I do not know what version of MySQL my host is running nor do I have Shell Access to it. I

would like to setup a script so my website users can update thier records without my intervention. Thank You In

Advance.

Also you may send replies to st**********@yahoo.com

Begin Script

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("databasename",$db);

if ($id) {
if ($submit) {
$sql = "UPDATE tablename SET
EMail='$EMail',Username='$Username',Password='$Pas sword',Item=

'$Item',Price='$Price',URL='$URL' WHERE Username='$Username'";
$result = mysql_query($sql);
echo "table Updated.";
} else {
// query the DB
$sql = "SELECT * FROM tablename WHERE Username='$Username'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
e-Mail Address:<input type="Text" name="EMail" value="<?php echo
$myrow["EMail"] ?>"><br>
Username:<input type="Text" name="Username" value="<?php echo
$myrow["Username"] ?>"><br>
Password:<input type="Text" name="Password" value="<?php echo
$myrow["Password"] ?>"><br>
Item:<input type="Text" name="Item" value="<?php echo $myrow["Item"]
?>"><br>
Price:<input type="Text" name="Price" value="<?php echo $myrow["Price"]
?>"><br>
Item URL:<input type="Text" name="URL" value="<?php echo $myrow["URL"]
?>"><br>
<input type="Submit" name="Update" value="Update table">
</form>
<?php
}
} else {
// display list of articles
$result = mysql_query("SELECT * FROM tablename",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?Username=%s\">%s %s</a><br>\n", $PHP_SELF,
$myrow["Username"], $myrow["Item"], $myrow

["URL"]);
}
}
// Close DB Connection
mysql_close()
?>

End Script
I don't see $id or $submit in your form. Where are they coming from? I

suspect this is the problem. If so, you'll have to put them in as hidden fields and/or rename the submit button to "submit".

And you may want to consider using $_GET, $_POST, $_REQUEST, $_SESSION, etc.

If that doesn't clear up the problem:

Is register_globals on or off?
What error(s) are you getting? If none, try error_reporting(E_ALL); at the top of the script.
Does that username have update privileges?

Regards,
Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com

Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.