Unable to UPDATE, INSERT is fine (PHP5 w/MySQL)

Question

TommyRay 0 Light Poster

15 Years Ago

Hi all...

I need your help, there must be something I am not seeing here when trying to update a table of information in a database.

Here is the code I am currently using.

In my program I have this call...

if (($_POST['changeAddress'] != "") || ($_POST['changeAddress'] != null)) {
	// Change the Address1
	echo "Change Address...<br />";
	$userID = $_SESSION['uid'];
	$theUID = (int)$userID;
	$theValue = $_POST['changeAddress'];
	$updateDB = "UPDATE userinfo (memberAddress) VALUE ('{$theValue}') where (memberID = {$theUID})";
	InfoUpdate($updateDB);
}

And in an include file I have this function being called...

function InfoUpdate($updateThis) {
	// Inserts/Injects Data into database using the query passed along...

	$db = mysql_connect('dbserver', 'dbname', 'dbpassword');
	mysql_select_db('dbname', $db);

	$queryResult = mysql_query('$updateThis');

	if ($queryResult) {
		echo 'Success.';
	}
	else {
		echo '<br />Insertion failed. Please try again.';
	}	
}

I am able to use INSERT just fine, however doing an UPDATE with a WHERE clause seems to not affect anything, and gave errors as to the syntax "near 'WHERE...' but then nothing at all here but the obvious "Insertion Failed" error being echoed out. Echoed out it shows:

UPDATE userinfo (memberAddress) VALUE ('2112 Rush Ave.') where (memberID = 4)

The variable $updateThis shows everything is there and should work correctly. I'd tried several variations of syntax but no success. I am using PHP 5.+ and an updated MySQL server as well insofar as it was just updated a few months ago.

THANKS for any assistance in advance!

-Tom

PS: I'd been reading and 'trying' to learn security measures but am more interested in it simply working. I'll then back it all up and start working with the .htaccess since I'm on a more public/shared server as well as other security methods/aspects. I'm a bit in over my head for this project but determined.

mysql php

3 Contributors
6 Replies
204 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by jrotunda85

Dani AI

Generated 7 Months Ago

Quick summary and practical fix:

The thread shows two common causes for an UPDATE that appears to "do nothing": (1) malformed SQL or missing quotes around string literals, and (2) passing the query text incorrectly to the DB API so the SQL never actually runs. 's early UPDATE syntax and the InfoUpdate function reveal both problems: an UPDATE needs the SET form (not INSERT-like syntax), and passing the query as a literal string (for example mysql_query('$updateThis')) sends the text "$updateThis" instead of the built query.

Recommended, robust approach (use PDO with prepared statements and error mode set):

$pdo = new PDO('mysql:host=DBHOST;dbname=DBNAME;charset=utf8mb4', DBUSER, DBPASS, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);
$stmt = $pdo->prepare('UPDATE `user` SET memberAddress = :addr WHERE memberID = :id');
$stmt->execute([':addr' => $address, ':id' => (int)$id]);
echo $stmt->rowCount() . ' row(s) updated.';

Practical troubleshooting checklist (quick wins shown by and ):

Enable DB errors: set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION (silent failures are common without it).
Verify SQL syntax: use UPDATE table SET col = 'value' WHERE .... Strings must be quoted or, better, bound as parameters.
Confirm the correct database/table is selected and that the DB user has UPDATE rights. The identifier user can clash with system names; wrap identifiers in backticks or rename the table.
Check affected rows: rowCount() (PDO) or mysqli_affected_rows() — a return of 0 can mean "no matching row" or "value unchanged", not necessarily an error.
For legacy mysql_* code, remove quotes around the variable in the call (use mysql_query($sql) not mysql_query('$sql')) and inspect mysql_error().

The immediate fix in this thread was quoting/variable handling as noted by ; migrating to prepared statements removes those pitfalls and improves security.

All 6 Replies

jrotunda85 9 Junior Poster

15 Years Ago

You don't need to concatenate your Update statement, try something like this:

$updateThis = "UPDATE user SET memberAddress = '$theValue' WHERE memberID = '$theUserID'";

It also seems, based on what you have in the first post, you may be mis-labeling some variables? I'm not sure if this was just something you were messing around with but I left your variables consistent with the latest post. Figured I would point that out just in case.

If that still doesn't work, you might want to try just executing the query via PHP without including the function. So do something like this:

$db = mysql_connect('dbserver', 'dbname', 'dbpassword');
mysql_select_db('dbname', $db);

$updateThis = "UPDATE user SET memberAddress = '$theValue' WHERE memberID = '$theUserID'";

$queryResult = mysql_query($updateThis);

if ($queryResult) {
	echo "Success.";
}
else {
	echo "<br />Insertion failed. Please try again.";
}

On that note, you also don't need quotes (single or otherwise) in the mysql_query part when calling a variable which has already been declared. :)

Edited 15 Years Ago by jrotunda85 because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TommyRay 0 Light Poster · Answer 1 · 2011-03-20T03:24:52+00:00

AN UPDATE: TRIED PDO But no Go

I thought it may be something in the method I was using, so I tried PDO...

The setup and call...

$updateThis = "UPDATE user SET memberAddress=".$theValue." WHERE memberID=".$theUserID;
	InfoUpdate($updateThis);

And the function itself...

/*** mysql hostname ***/
	$hostname = 'dbserver';

	/*** mysql username ***/
	$username = 'dbusername';

	/*** mysql password ***/
	$password = 'dbpassword';

	try {
		$dbh = new PDO("mysql:host=$hostname;dbname=dbname", $username, $password);
		/*** echo a message saying we have connected ***/
		echo 'Connected to database<br />';
		echo 'Attempting Insert with: '.$updateThis.'<br /><br />';
		/*** INSERT data ***/
		$count = $dbh->exec($updateThis);

		/*** echo the number of affected rows ***/
		echo $count;

		/*** close the database connection ***/
		$dbh = null;
	}
	catch(PDOException $e)	{
		echo $e->getMessage();
	}

When ran, I echoed out the variable and can see it fine...

Change Address...
Connecting to the Database...Connected to database
Attempting Insert with: UPDATE user SET memberAddress=2112 Brown Ave. WHERE memberID=4

So WHY it is NOT executing the actual update I have NO idea.
I get NO Error whatsoever. It is quite unnerving LOL!

TommyRay 0 Light Poster · Answer 2 · 2011-03-20T10:46:12+00:00

I did as you suggested and BAM it worked.
I just need to place it back into the include file again now.
Funny how a simply syntax error like the quotes can mess you up.
I spent like 2 days trying to figure this out, I THANK YOU very much the help!!

-Tom

jrotunda85 9 Junior Poster · Answer 3 · 2011-03-21T03:55:05+00:00

No problem, glad to help. Sometimes PHP can indeed be finicky with the quotes :)

tomato.pgn -6 Posting Whiz in Training · Answer 4 · 2011-03-21T08:09:47+00:00

the syntax u used is wrong...

$updateThis = "UPDATE user SET memberAddress = '$theValue' WHERE memberID = '$theUserID'";

it should be.....-

$updateThis = "UPDATE user SET memberAddress = '".$theValue."' WHERE memberID = '".$theUserID."'";

jrotunda85 9 Junior Poster · Answer 5 · 2011-03-21T18:54:12+00:00

the syntax u used is wrong...

$updateThis = "UPDATE user SET memberAddress = '$theValue' WHERE memberID = '$theUserID'";

it should be.....-

$updateThis = "UPDATE user SET memberAddress = '".$theValue."' WHERE memberID = '".$theUserID."'";

Again, you don't need to concatenate an Update statement ;)

Unable to UPDATE, INSERT is fine (PHP5 w/MySQL)

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers