Updating a MySQL entry

General PHP Help
#1

Hey everyone,

I created a very simple MySQL database that houses a flat file table with less than 10 fields. I am able to add and remove contacts, as well as search for them. Once I made a mistake in adding a contact I realized that I needed to edit contacts as well.

So I pieced together these three pages to try and get the job done. When I go through the editing process, it appears that everything worked. But when I check the database the contact is not receiving the edit. I think it might have something to do with the query ran in postupdate.php?

The database name is “nanette_database”, username is “nanette_database”, and the table is “people”.

Update.html

<!DOCTYPE html>                    
<html lang="en">
<html>
<head>
     <meta charset="utf - 8">
     <title>Select Contact to Edit</title>
     <script src="modernizr.min.js"></script>
     <!--[if lt IE 9]>
  <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>   For Internet Explorer error detection 
  <![endif]-->
</head>
<body>
<body bgcolor="white">
<form method="POST" action="update.php">
<table>
<col span="1" align="right">
<tr>
<td>Enter User Id to Update:</td>
<td><input type="text" name="id" size=50></td>
</tr>
<tr>
<td><input type="submit" value="Submit"></td>
</tr>
</table>
</form>
</body>
</html>

Update.php
[php]

<?php $username="nanette_database"; $password="******"; foreach($HTTP_POST_VARS as $varname => $value) $formVars[$varname]=$value; $db1=mysql_connect($server, $user_name, $password); mysql_select_db("nanette_database"); $query="SELECT * FROM people WHERE id = \"".$formVars["id"]."\""; $result=mysql_query($query); $row=mysql_fetch_array($result); $formVars = array(); $formVars["industry"]=$row["industry"]; $formVars["publication"]=$row["publication"]; $formVars["editor"]=$row["editor"]; $formVars["website"]=$row["website"]; $formVars["email"]=$row["email"]; $formVars["phone"]=$row["phone"]; $formVars["notes"]=$row["notes"]; $formVars["id"]=$row["id"]; mysql_close($db1); ?> 
 <meta charset="utf - 8">
 <title>Edit Contact</title>
 <script src="modernizr.min.js"></script>
 <!--[if lt IE 9]>
For Internet Explorer error detection

<![endif]–>

Marine Plastics Oil Food Oem Transporation Energy Water Packaging Automotive Telecom Construction Electrical Defense value="<? echo $formVars["industry"]; ?>"> [/php]

Postupdate.php
[php]

 <meta charset="utf - 8">
 <title>Edit Sucessful</title>
 <script src="modernizr.min.js"></script>
 <!--[if lt IE 9]>
For Internet Explorer error detection

<![endif]–>

<?php $username="nanette_database"; $password="******"; foreach($HTTP_POST_VARS as $varname => $value) $formVars[$varname]=$value; $db1=mysql_connect($server, $user_name, $password); mysql_select_db("nanette_database"); echo "Record updated
click here to update another record

click here to return home
"; $query="UPDATE people SET ". "publication= \"".$formVars["publication"]."\",". "editor= \"".$formVars["editor"]."\",". "website= \"".$formVars["website"]."\",". "email= \"".$formVars["email"]."\",". "phone= \"".$formVars["phone"]."\",". "notes= \"".$formVars["notes"]."\",". "\" WHERE id = \"".$formVars["id"]."\""; mysql_query($query); mysql_close($db1); ?> [/php]
Industry:
Publication: " size=100>
Editor: " size=100>
Website: " size=100>
Email: " size=100>
Phone: " size=100>
Notes: " size=100>
id: " size=100>
#2

This is what your UPDATE query currently looks like. Do you see the problem?

UPDATE people SET publication= "",editor= "",website= "",email= "",phone= "",notes= ""," WHERE id = ""
#3

You should change your mysql_query lines to include the die function. It will be much easier to see the problem.

For example:

[php]
mysql_query($query)or die(mysql_error());
[/php]

#4

m@tt, thanks for taking the time to respond and helping to teach me instead of responding with working code.

When you put my query like this, it appears that I am using the SET command incorrectly.
[php]
UPDATE people SET publication= “”,editor= “”,website= “”,email= “”,phone= “”,notes= “”," WHERE id = “”
[/php]

It looks like each field still needs to be SET to point somewhere.
My best guess would be something like:

[php]
UPDATE people SET publication= “publication”,editor= “editor”,website= “website”,email= “email”,phone= “phone”,notes= “notes”," WHERE id = “”
[/php]

But then what happens to the information entered in the HTML form on the previous page? I thought that was where the $formVars came in to play?

I imagined (perhaps incorrectly) that when I do this:
[php]
SET “.
“publication= “”.$formVars[“publication”].””,".
[/php]

It sets the publication field to equal the text box designated as publication that was entered on the previous page (update.html)

So I tried this:

[php]
$query=“UPDATE people SET “.
“publication= “publication”.$formVars[“publication”].””,”.
“editor= “editor”.$formVars[“editor”].”",".
“website= “website”.$formVars[“website”].”",".
“email= “email”.$formVars[“email”].”",".
“phone= “phone”.$formVars[“phone”].”",".
“notes= “notes”.$formVars[“notes”].”",".
“” WHERE id = “id”.$formVars[“id”].""";
[/php]

…and still nothing. I am pretty new to PHP and this error has just become a guessing game to me. Apologies in advanced for my butchering of the PHP language and lingo.

#5

I was typing my response as you posted this… will go try it now and read the error returned.

#6

Your SET is correct. I simply removed the values to show the full query. Look closely, the problem is just before your WHERE.

#7

LOL look closer.

#8

Adding the die function returned with 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 '" WHERE id = "9"' at line 1

Which tells me there is an error on my WHERE line, which you guys obviously figured out long long ago. :stuck_out_tongue:

I’m trying. Before my WHERE:
[php]""[/php]

Does this have something to do with magic quotes? I am trying to read up on those but the concept keeps going over my head. I’ve tried every combination I can possibly think of: quotes, no quotes, no slash, extra slash, but obviously nothing is fixing this for me.

I studied W3 Schools short tutorial on the UPDATE statement and they gave this example: (which is what I based my original code from)

[php]UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value [/php]

When I laid mine out this way (no quotes no slashes) it still didn’t work out.

#9

Right here

notes= ""," WHERE

You have an additional ," which should not be there.

notes= "" WHERE

So your code should be:

[php]
$query=“UPDATE people SET “.
“publication= “”.$formVars[“publication”].””,”.
“editor= “”.$formVars[“editor”].”",".
“website= “”.$formVars[“website”].”",".
“email= “”.$formVars[“email”].”",".
“phone= “”.$formVars[“phone”].”",".
“notes= “”.$formVars[“notes”].”"".
" WHERE id = “”.$formVars[“id”].""";
[/php]

#10

Works perfectly, Thanks!

So please clarify this for me…

I didn’t need a comma there because it was the end of a list within the statement?

Also, I tried it without the comma but put the “” in front of the WHERE and it didn’t work. What’s up with that?

#11

Correct

Because that would put two quotes at the end of your value. For example

SET field="value"" WHERE
Sponsor our Newsletter | Privacy Policy | Terms of Service