Problems Updating Records In MySQL with PHP

[Guest]

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 stevennyoung@ya hoo.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='$User name',Password= '$Password',Ite m=

'$Item',Price=' $Price',URL='$U RL' WHERE Username='$User name'";
$result = mysql_query($sq l);
echo "table Updated.";
} else {
// query the DB
$sql = "SELECT * FROM tablename WHERE Username='$User name'";
$result = mysql_query($sq l);
$myrow = mysql_fetch_arr ay($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("SE LECT * FROM tablename",$db) ;
while ($myrow = mysql_fetch_arr ay($result)) {
printf("<a href=\"%s?Usern ame=%s\">%s %s</a><br>\n", $PHP_SELF,
$myrow["Username"], $myrow["Item"], $myrow

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

End Script

[Shawn Wilson]

Re: Problems Updating Records In MySQL with PHP

stevennyoung@ea rthlink.net wrote:[color=blue]
>
> 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 stevennyoung@ya hoo.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='$User name',Password= '$Password',Ite m=
>
> '$Item',Price=' $Price',URL='$U RL' WHERE Username='$User name'";
> $result = mysql_query($sq l);
> echo "table Updated.";
> } else {
> // query the DB
> $sql = "SELECT * FROM tablename WHERE Username='$User name'";
> $result = mysql_query($sq l);
> $myrow = mysql_fetch_arr ay($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("SE LECT * FROM tablename",$db) ;
> while ($myrow = mysql_fetch_arr ay($result)) {
> printf("<a href=\"%s?Usern ame=%s\">%s %s</a><br>\n", $PHP_SELF,
> $myrow["Username"], $myrow["Item"], $myrow
>
> ["URL"]);
> }
> }
> // Close DB Connection
> mysql_close()
> ?>
>
> End Script[/color]

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_global s 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
shawn@glassgian t.com

http://www.glassgiant.com

[Tom Thackrey]

Re: Problems Updating Records In MySQL with PHP


On 18-Dec-2003, <stevennyoung@e arthlink.net> wrote:
[color=blue]
> 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 stevennyoung@ya hoo.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='$User name',Password= '$Password',Ite m=
>
> '$Item',Price=' $Price',URL='$U RL' WHERE Username='$User name'";
> $result = mysql_query($sq l);
> echo "table Updated.";
> } else {
> // query the DB
> $sql = "SELECT * FROM tablename WHERE Username='$User name'";
> $result = mysql_query($sq l);
> $myrow = mysql_fetch_arr ay($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("SE LECT * FROM tablename",$db) ;
> while ($myrow = mysql_fetch_arr ay($result)) {
> printf("<a href=\"%s?Usern ame=%s\">%s %s</a><br>\n", $PHP_SELF,
> $myrow["Username"], $myrow["Item"], $myrow
>
> ["URL"]);
> }
> }
> // Close DB Connection
> mysql_close()
> ?>
>
> End Script[/color]

You have several problems

1) assuming you are running with register_global s 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($s ql) 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

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Guest]

Re: Problems Updating Records In MySQL with PHP

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" <shawn@glassgia nt.com> wrote in message
news:3FE302E1.F 4D9848E@glassgi ant.com...[color=blue]
> stevennyoung@ea rthlink.net wrote:[color=green]
> >
> > 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,[/color][/color]
But[color=blue][color=green]
> > not update. Here is a copy of the
> >
> > script I am using. I do not know what version of MySQL my host is[/color][/color]
running[color=blue][color=green]
> > nor do I have Shell Access to it. I
> >
> > would like to setup a script so my website users can update thier[/color][/color]
records[color=blue][color=green]
> > without my intervention. Thank You In
> >
> > Advance.
> >
> > Also you may send replies to stevennyoung@ya hoo.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='$User name',Password= '$Password',Ite m=
> >
> > '$Item',Price=' $Price',URL='$U RL' WHERE Username='$User name'";
> > $result = mysql_query($sq l);
> > echo "table Updated.";
> > } else {
> > // query the DB
> > $sql = "SELECT * FROM tablename WHERE Username='$User name'";
> > $result = mysql_query($sq l);
> > $myrow = mysql_fetch_arr ay($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("SE LECT * FROM tablename",$db) ;
> > while ($myrow = mysql_fetch_arr ay($result)) {
> > printf("<a href=\"%s?Usern ame=%s\">%s %s</a><br>\n", $PHP_SELF,
> > $myrow["Username"], $myrow["Item"], $myrow
> >
> > ["URL"]);
> > }
> > }
> > // Close DB Connection
> > mysql_close()
> > ?>
> >
> > End Script[/color]
>
> I don't see $id or $submit in your form. Where are they coming from? I[/color]
suspect[color=blue]
> this is the problem. If so, you'll have to put them in as hidden fields[/color]
and/or[color=blue]
> rename the submit button to "submit".
>
> And you may want to consider using $_GET, $_POST, $_REQUEST, $_SESSION,[/color]
etc.[color=blue]
>
>
> If that doesn't clear up the problem:
>
> Is register_global s on or off?
> What error(s) are you getting? If none, try error_reporting (E_ALL); at the[/color]
top[color=blue]
> of the script.
> Does that username have update privileges?
>
> Regards,
> Shawn
> --
> Shawn Wilson
> shawn@glassgian t.com
> http://www.glassgiant.com[/color]