'Twas the night before Christmas, and mySQL won't accept my Update query.

[Mavent]

I've messed around with this for three hours now, and it's driving me batty.  I'm running the following Query:

 

mysql_select_db("myDB", $con);

$query = "UPDATE mytable SET Name = '$Name', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Phone = '$Phone', Website = '$Website', Type = '$Type' WHERE id = '$ID'";

 

The problem is that it's not updating my database.  I've tried everything.  I've forced the page to Echo out the variables so that I know they're right.  I've run (successful) Delete queries to make sure I'm connecting to the right table.  I've forced it to spit out the variables at the end of the Update.  Everything I've tried works beautifully... except the Update itself.  It doesn't error out or anything- it acts like it's Updating, but the data never changes.  I can add to the database and delete from it, but it refuses to let me Update anything. 

 

I've looked all over the net, and tried every variation I can find.  Nothing works.  I also tried this:

 

$query = "UPDATE `testbed` SET `Name`=[$Name],`Address`=[$Address],`City`=[$City],`State`=[$State],`Zip`=[$Zip],`Type`=[$Type],`Phone`=[&Phone],`website`=[$Website] WHERE `ID`=[$ID]";

 

And

 

$result = mysql_query("UPDATE mytable SET Name = '$Name', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Phone = '$Phone', Website = '$Website', Type = '$Type' WHERE id = '$ID'");

 

None of them do anything at all.

[PFMaBiSmAd]

What does var_dump($result); show for the version where you assign the returned value to $result = mysql_query(....

[Mavent]

What does var_dump($result); show for the version where you assign the returned value to $result = mysql_query(....

 

Hi, and thanks for the response!

 

It shows:

bool(false) var_dump

 

Which I don't understand, unfortunately.

[PFMaBiSmAd]

A false value means your query is failing due to an error of some kind.

 

echo mysql_error(); right after the mysql_query() line to find out why the query is failing.

[Mavent]

A false value means your query is failing due to an error of some kind.

 

echo mysql_error(); right after the mysql_query() line to find out why the query is failing.

 

Ah.  It appears that one of my Variables isn't coming across from the Edit Form properly.  As you can tell, this is my first large-scale database project.    I'm not sure why the Variable isn't loading correctly, but I'm at least now I know where to look.  Thank you very much!

[PFMaBiSmAd]

If the mysql_error message is referring to a point in the query where your external data is at, it is likely that you are not escaping the data before putting it into the query and the data contains special sql characters.

 

See this link - mysql_real_escape_string

[Mavent]

Hola! I tried your suggestion.  The same thing happened.  No update. 

 

Here's the entirety of my current code:

 

<?php

$con = mysql_connect(localhost,myDB,myPass);

if (!$con)

  die('Could not connect: ' . mysql_error());

 

mysql_select_db("myDB", $con);

 

$query="UPDATE testbed SET Name=\"$_POST[Name]\", Address=\"$_POST[Address]\", State=\"$_POST[state]\" WHERE ID=\"$_POST[iD]\"";

echo mysql_error();

echo "<br>";

echo $query;

echo "<br>";

var_dump($result);

 

mysql_close($con);

?>

 

and here's what the tests return:

mysql error:

SQuery:UPDATE testbed SET Name="Test Name", Address="18501 W. Stanford Road", State="California" WHERE ID="96"

NULL

 

I don't see any errors, and yet the Table refuses to Update.  It just boggles my mind that it could be this hard to update a database entry. 

[PFMaBiSmAd]

It's not that hard to form and execute any kind of query.

 

Where's your mysql_query() statement?

[ignace]

I LOL'ed when I saw your code. For your database to actually ever receive the query you need to call mysql_query() with your actual query as a parameter.

 

Something like this:

$result = mysql_query($query) or trigger_error('ERROR: ' . mysql_error() . '<br>QUERY: ' . $query, E_USER_ERROR);
if ($result && mysql_num_rows($result)) {
    // query executed successful AND it returned a result.
}

[Mavent]

My original version, shown at the bottom of my first post, was:

$result = mysql_query("UPDATE mytable SET Name = '$Name', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Phone = '$Phone', Website = '$Website', Type = '$Type' WHERE id = '$ID'");

The version that's in there now is what a guy from another board suggested I try.    Since I'm not above any suggestion/ridicule, I put his code in and hoped for the best. 

 

After reading the posts here, I realized that the problem was that I was half right the first time, and the guy who gave me my current code was also half-right.  So I took the right halves, and made this:

 

$result = mysql_query("UPDATE testbed SET Name=\"$_POST[Name]\", Address=\"$_POST[Address]\", City=\"$_POST[City]\" WHERE ID=\"$_POST[iD]\"");

 

And now it seems to work. 

 

THANK YOU to PFMaBiSmAD and ignace!  Your advice was the best Christmas Present ever!  My form is now updating!

 

*Edited to show the functioning code.