Code won't update database

[Janj]

I created a script that is supposed to update some values in a database. However, it won't change anything.

This is my code:

//Edit article
	mysql_query("UPDATE articles WHERE artnum=\"".$_POST['ID']."\" SET art_title=\"".$art_title."\", art_image=\"".$art_image."\", image_caption=\"".$image_caption."\", image_src=\"".$image_src."\", image_src_location=\"".$image_src_location."\", article =\"".$article."\"");
		echo "Article edited.";

I know that it is executed because it is in an if statement and I can see the "Article edited." But I've tried altering the actual query various times, changing the order of the WHERE and SET, etc, using mysqli_query, and nothing has worked so far.

I've checked all my variables, they all exist and I can echo each one of them. I have no idea what to do.

[Jessica]

You're not checking to see if the query failed (which it clearly did)

See my signature.

 

Also look up the syntax for mysql update because you have at least one major error.

Edited May 7, 2013 by Jessica

[computermax2328]

1. Don't concatenate inside of MYSQL queries. Assign variables to your $_POST['ID']

You don't have to escape all of those double quotes. Only use quotes for stings and use single quotes inside of double quotes.

[Janj]

Well I knew the query failed because nothing happened, but I don't know how to check it...

Looked for an article on update syntax. Found one from MySQL and a million questions about it.

I changed all the \"s to 's, put the WHERE after the SET, and removed the quotes around $_POST['ID']

I'm on my iPad right now so testing isn't as easy as it is on the computer so I'll wait for tomorrow to continue this.

[Jessica]

Read the link in my signature on debugging SQL. You need to use mysql_error()

[Janj]

Here it is, my attempt:

$sql = "UPDATE articles SET art_title='".$art_title."', art_image='".$art_image."', image_caption='".$image_caption."', image_src='".$image_src."', image_src_location='".$image_src_location."', article ='".$article."' WHERE artnum=".$_POST['ID']."";
$result=mysqli_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);

	//mysql_query("UPDATE articles SET art_title='".$art_title."', art_image='".$art_image."', image_caption='".$image_caption."', image_src='".$image_src."', image_src_location='".$image_src_location."', article ='".$article."' WHERE artnum=".$_POST['ID']."");
		echo "Article edited.";

But the page just doesn't load it. Not even "Article edited"

[computermax2328]

Take the double quotes out like I said. You don't need to concatenate your variables.

$sql = "UPDATE articles SET art_title='$art_title'........

Also make your POST id a variable

$id = $_POST['ID'];

[Jessica]

You were using mysql before. Did you switch everything to mysqli?

 

Enable error reporting.

[DavidAM]

Take the double quotes out like I said. You don't need to concatenate your variables.

$sql = "UPDATE articles SET art_title='$art_title'........

Also make your POST id a variable

$id = $_POST['ID'];

 

There is no real reason to create a new variable for one that already exists. $_POST[id'] is perfectly valid and usable in this situation. It is IN FACT a variable -- a super-global array variable -- but a variable nonetheless.

 

There is also no real reason to put the variables in a string over using concatenation. Neither of these "answers" address the problem he is having. They are both PREFERENCES of one programmer over another.

 

Here it is, my attempt:

$sql = "UPDATE articles SET art_title='".$art_title."', art_image='".$art_image."', image_caption='".$image_caption."', image_src='".$image_src."', image_src_location='".$image_src_location."', article ='".$article."' WHERE artnum=".$_POST['ID']."";
$result=mysqli_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);

	//mysql_query("UPDATE articles SET art_title='".$art_title."', art_image='".$art_image."', image_caption='".$image_caption."', image_src='".$image_src."', image_src_location='".$image_src_location."', article ='".$article."' WHERE artnum=".$_POST['ID']."");
		echo "Article edited.";

But the page just doesn't load it. Not even "Article edited"

 

If you are getting a "white page", use the "View Source" feature of the browser to see if there is output that is not rendered.

 

Turn on error reporting. A white page indicates a fatal error. Probably a syntax error, that prevents PHP from doing anything.

 

You have not shown enough of your code for us to really help. You can NOT mix mysql_* functions with mysqli_* functions. You are either using the standard (now deprecated) mysql module, or you are using the "improved" (hence the "i") module. Whichever one you connect with, is the one you MUST use for the rest of the script.

 

I find it difficult, if not impossible to maintain code that has such long SQL lines built in. You are free to breakup these lines, and I recommend you do so:

 

$sql = "UPDATE articles SET art_title='" . $art_title . 
	"', art_image='" . $art_image .
	"', image_caption='" . $image_caption .
	"', image_src='" . $image_src . 
	"', image_src_location='" . $image_src_location . 
	"', article ='" . $article . 
	"' WHERE artnum=" . $_POST['ID'] . "";

Since that leaves the "closing" single-quote for each field on the line after the field, and because I find it difficult to pick out the variables, I generally use sprintf to build my SQL statements:

 

$sql = sprintf("UPDATE articles 
		SET art_title='%s', art_image='%s', 
		  image_caption='%s', image_src='%s', 
		  image_src_location='%s', article ='%s' 
		WHERE artnum=%d", 
		mysql_real_escape_string($art_title), mysql_real_escape_string($art_image), 
		mysql_real_escape_string($image_caption), mysql_real_escape_string($image_src), 
		mysql_real_escape_string($image_src_location), mysql_real_escape_string($article),
		intval($_POST['ID']));

Note: I added escaping for the strings, and sanitation for the integer. I used the mysql version, if you are using mysqli it is a different function.

[jazzman1]

Note: I added escaping for the strings, and sanitation for the integer.

 

I think, it should be better to avoid from signed integers (nagative numbers)

WHERE artnum=%u

Edited May 7, 2013 by jazzman1

[Janj]

Back on a computer.

 

You were using mysql before. Did you switch everything to mysqli?

Enable error reporting.

 

Changed it back to mysql, still the same.

 

I also changed error reporting from E_ALL & ~E_NOTICE to E_ALL.  Nothing yet.

 

 

There is no real reason to create a new variable for one that already exists. $_POST[id'] is perfectly valid and usable in this situation. It is IN FACT a variable -- a super-global array variable -- but a variable nonetheless.

There is also no real reason to put the variables in a string over using concatenation. Neither of these "answers" address the problem he is having. They are both PREFERENCES of one programmer over another.
 
If you are getting a "white page", use the "View Source" feature of the browser to see if there is output that is not rendered.

Turn on error reporting. A white page indicates a fatal error. Probably a syntax error, that prevents PHP from doing anything.

You have not shown enough of your code for us to really help. You can NOT mix mysql_* functions with mysqli_* functions. You are either using the standard (now deprecated) mysql module, or you are using the "improved" (hence the "i") module. Whichever one you connect with, is the one you MUST use for the rest of the script.

I find it difficult, if not impossible to maintain code that has such long SQL lines built in. You are free to breakup these lines, and I recommend you do so:
 

$sql = "UPDATE articles SET art_title='" . $art_title . 
	"', art_image='" . $art_image .
	"', image_caption='" . $image_caption .
	"', image_src='" . $image_src . 
	"', image_src_location='" . $image_src_location . 
	"', article ='" . $article . 
	"' WHERE artnum=" . $_POST['ID'] . "";

Since that leaves the "closing" single-quote for each field on the line after the field, and because I find it difficult to pick out the variables, I generally use sprintf to build my SQL statements:
 

$sql = sprintf("UPDATE articles 
		SET art_title='%s', art_image='%s', 
		  image_caption='%s', image_src='%s', 
		  image_src_location='%s', article ='%s' 
		WHERE artnum=%d", 
		mysql_real_escape_string($art_title), mysql_real_escape_string($art_image), 
		mysql_real_escape_string($image_caption), mysql_real_escape_string($image_src), 
		mysql_real_escape_string($image_src_location), mysql_real_escape_string($article),
		intval($_POST['ID']));

Note: I added escaping for the strings, and sanitation for the integer. I used the mysql version, if you are using mysqli it is a different function.

 

 

 

 

If you are getting a "white page", use the "View Source" feature of the browser to see if there is output that is not rendered.

 

The bottom of the source shows this:

<div class="big">CHANGE ARTICLE</div>
<!--Do not insert anything before this line-->

Right after which comes the code, then comes the closing div, body, and html, all of which are not displayed.  So when 

$result=mysql_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysql_error(), E_USER_ERROR);

appears, it prevents anything past it from loading.

[Jessica]

So you are not actually displaying errors. Do a phpinfo() and check if display errors is on

[Janj]

So you are not actually displaying errors. Do a phpinfo() and check if display errors is on

Off, turned it on.  Now I see this:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /hermes/waloraweb018/b1141/as.jansaviation/acp/changearticle.php on line 42 Fatal error: Query Failed! SQL: UPDATE articles SET [here goes a looot of stuff, what was typed in] WHERE artnum='1' - Error: No database selected in /hermes/waloraweb018/b1141/as.jansaviation/acp/changearticle.php on line 127

 

$sql = "UPDATE articles SET art_title='".$art_title."', art_image='".$art_image."', image_caption='".$image_caption."', image_src='".$image_src."', image_src_location='".$image_src_location."', article ='".$article."' WHERE artnum='".$_POST['ID']."'";
$result=mysql_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysql_error(), E_USER_ERROR);


//mysql_query($sql);
echo "Article edited.";

If I comment out the $result line but uncomment the mysql_query line I get this:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /hermes/waloraweb018/b1141/as.jansaviation/acp/changearticle.php on line 42

Article edited.

 

Here's my document:

http://pastebin.com/Uk04mgui

[Jessica]

Well? The error is telling you the problem. Did you read it?

[Janj]

Turns out I was requesting the wrong page.I forgot to make sure that on the first time, it checked to see if status wasn't "done," fixed it.

 

Changed line 40 to 

if($_GET['id']=="" && $_GET['status']=="")

and line 95 to 

else if ($_GET['id']=="" && $_GET['status']=="done")

And I turned the $result line "back on."  My error is:  No database selected in /hermes/waloraweb018/b1141/as.jansaviation/acp/changearticle.php on line 126.  Which I only get from the result line.  At the top, I already put mysql_select_db("newsdb", $con); so I don't know why it's telling me 'no database selected.'  I tried this suggestion of prefixing the table with the database (changing UPDATE articles to UPDATE newsdb.articles)

 

Well, that just worked.  Thanks so much!