conditional statement for field update

asprinwizard

Hi There,

I wish to add a conditional statement to my update record form. Basically the form has an image upload feature and I wish to make it so that only if an image is specified should that field be updated, otherwise it should just ignore updating that field.

The update code is below:
$updateSQL = sprintf("UPDATE news SET Type=%s, Headline=%s, Poster=%s, Summary=%s, Content=%s, Image=%s, Date=%s, Disabled=%s WHERE Id=%s", GetSQLValueString($HTTP_POST_VARS['type'], "int"),
GetSQLValueString($HTTP_POST_VARS['headline'], "text"),
GetSQLValueString($HTTP_POST_VARS['poster'], "text"),
GetSQLValueString($HTTP_POST_VARS['summary'], "text"),
GetSQLValueString($HTTP_POST_VARS['content'], "text"),
GetSQLValueString($HTTP_POST_VARS['image'], "text"),
GetSQLValueString($HTTP_POST_VARS['date'], "date"),
GetSQLValueString(isset($HTTP_POST_VARS['disabled'
]) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($HTTP_POST_VARS['Id'], "int"));

I have tried inserting an if statement around the particular field but this caused the page to flip out and generate errors all over the place. Can someone point me in the right direction... again! many thanks.

bpat1434

well... not sure how it fits into the sprintf() function, but I'd do this:

$query = "UPDATE news
SET Type='".mysql_real_escape_string($_POST['type'])."',
Headline='".mysql_real_escape_string($_POST['headline'])."',
Poster='".mysql_real_escape_string($_POST['poster'])."',
Summary='".mysql_real_escape_string($_POST['summary'])."',
Content='".mysql_real_escape_string($_POST['content'])."',";
$query .= (!$_POST['image'])?"":"Image='".mysql_real_escape_string($_POST['image'])."',";
$query .= "Date='".mysql_real_escape_string($_POST['date'])."',
Disabled='".mysql_real_escape_string($_POST['disabled'])."'
WHERE id='".mysql_real_escale_string($_POST['id'])."'";

~Brett

asprinwizard

Right, I've done as you suggested, but am now getting syntax errors. I took out sprintf and npw the code reads as:

$updateSQL = $query = "UPDATE news
SET Type='".mysql_real_escape_string($POST['type'])."', Headline='".mysql_real_escape_string($POST['headline'])."', Poster='".mysql_real_escape_string($POST['poster'])."', Summary='".mysql_real_escape_string($POST['summary'])."', Content='".mysql_real_escape_string($POST['content'])."',";
$query .= (!$POST['image'])?"":"Image='".mysql_real_escape_string($POST['image'])."',";
$query .= "Date='".mysql_real_escape_string($POST['date'])."',
Disabled='".mysql_real_escape_string($POST['disabled'])."'
WHERE id='".mysql_real_escale_string($POST['id'])."'"
GetSQLValueString($HTTP_POST_VARS['type'], "int"),
GetSQLValueString($HTTP_POST_VARS['headline'], "text"),
GetSQLValueString($HTTP_POST_VARS['poster'], "text"),
GetSQLValueString($HTTP_POST_VARS['summary'], "text"),
GetSQLValueString($HTTP_POST_VARS['content'], "text"),
GetSQLValueString($HTTP_POST_VARS['image'], "text"),
GetSQLValueString($HTTP_POST_VARS['date'], "date"),
GetSQLValueString(isset($HTTP_POST_VARS['disabled']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($HTTP_POST_VARS['Id'], "int");

I get the error: Parse error: syntax error, unexpected T_STRING in C:\Documents and Settings\Richard Aspden\My Documents\My Webs\PHP\RLI\admin\news\edit_news_test.php on line 111

Line 111 is the GetSQLValueString($HTTP_POST_VARS['type'], "int"), line. Any suggestions?

bpat1434

Because you can't just mix & match arbitrarily. You have to be aware of what you're doing. When you remove the sprintf() function, that means you remove the GetSQLValueString() calls as well. Your query then just becomes a string. So, you don't need any of this:

GetSQLValueString($HTTP_POST_VARS['type'], "int"),
GetSQLValueString($HTTP_POST_VARS['headline'], "text"),
GetSQLValueString($HTTP_POST_VARS['poster'], "text"),
GetSQLValueString($HTTP_POST_VARS['summary'], "text"),
GetSQLValueString($HTTP_POST_VARS['content'], "text"),
GetSQLValueString($HTTP_POST_VARS['image'], "text"),
GetSQLValueString($HTTP_POST_VARS['date'], "date"),
GetSQLValueString(isset($HTTP_POST_VARS['disabled']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($HTTP_POST_VARS['Id'], "int")

And this line can be changed from

$updateSQL = $query = "UPDATE news

$updateSQL = "UPDATE news

$query = "UPDATE news

. There's no sense in defining both variables to hold the same query.

~Brett

asprinwizard

Oh I see, thanks Brett.

I have done as you suggested but now it won't update at all. The form submits but nothing is updated. I have posted the code again for you to look at (I changed the $_POST to $HTTP_POST_VARS).

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "edit_news")) {
  $query = "UPDATE news
			SET Type='".mysql_real_escape_string($HTTP_POST_VARS['type'])."', 
			Headline='".mysql_real_escape_string($HTTP_POST_VARS['headline'])."', 
			Poster='".mysql_real_escape_string($HTTP_POST_VARS['poster'])."', 
			Summary='".mysql_real_escape_string($HTTP_POST_VARS['summary'])."', 
			Content='".mysql_real_escape_string($HTTP_POST_VARS['content'])."',"; 
			$query .= (!$HTTP_POST_VARS['image'])?"":"Image='".mysql_real_escape_string($HTTP_POST_VARS['image'])."',"; 
			$query .= "Date='".mysql_real_escape_string($HTTP_POST_VARS['date'])."', 
			Disabled='".mysql_real_escape_string($HTTP_POST_VARS['disabled'])."' 
			WHERE id='".mysql_real_escape_string($HTTP_POST_VARS['id'])."'"; 
}

bpat1434

Okay, are you using "mysql_query($query)" or "mysql_query($updateSQL)"?

~Brett

asprinwizard

I'm using mysql_query($query). Is that wrong? The page won't load at all otherwise.

The following code is placed after the code above:

 mysql_select_db($database_c13_RLI_2006c, $c13_RLI_2006c);
  $Result1 = mysql_query($query, $c13_RLI_2006c) or die(mysql_error());

  $updateGoTo = "news_reports.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));

Also, I'm not sure its relevant but the following code which is placed above the code I posted previously describes the formaction:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if (isset($editFormAction)) {
  if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
	  if (!eregi("GP_upload=true", $HTTP_SERVER_VARS['QUERY_STRING'])) {
  	  $editFormAction .= "&GP_upload=true";
		}
  } else {
    $editFormAction .= "?GP_upload=true";
  }
}

bpat1434

Try echoing the query to see that it is precisely what you expect it to be. Might not be, and then you can narrow down your problem....

~Brett

asprinwizard

Can't seem to do this. I put

echo $query;

in the page and nothing was displayed. Put it in the form action and it didn't work either.

Is this syntax right? Should I be using

echo Query($query);

or something similar?