update where v1=$val1 AND v2=$val2: not working

andersstudio

I need to update one record where two separate values are true in the same table and this statement does not provide an error, but it does not update.
Is there something wrong with the statement below?

$sql = "UPDATE program_entries
            SET `program_entry_info`=\"$entry\"
            WHERE program_id = \"$program_id\"
	        AND
	        program_entry_label_id = \"$program_entry_label_id\"";

Is there a better way to write this statement?

Thx.

NogDog

Basically it looks OK to me. For debugging you might try the following, mainly to make sure the variables have the values you think they do (I'm assuming MySQL for the DB here):

$sql = "UPDATE program_entries
            SET `program_entry_info`=\"$entry\"
            WHERE program_id = \"$program_id\"
            AND
            program_entry_label_id = \"$program_entry_label_id\"";
$result = mysql_query($sql) or die(mysql_error() . " <br> $sql");
if(mysql_affected_rows() == 0)
{
   user_error("DEBUG: No rows updated. Query: $sql", E_USER_WARNING);
}

andersstudio

I like the debug function. I'll definitely use that more often...

I've never had this issue before because we update tables directly from a text editor to the db. This time, we are pulling data from one table, storing it, and updating another. This is obviously the problem. It appears as though the syntax is throwing it off.

For example,

<span style="font-weight:bold;"> is being replaced with &lt;span style=&quot;font-weight: bold;&quot;&gt;

What special charReplace can I use to make this work?

The error is below:

You have an error in your SQL syntax near 'font-weight: bold;">test this update to master.
Required for B.A. in Comput' at line 2
UPDATE program_entries SET program_entry_info = "test this update to master.
Required for B.A. in Computer Science: (18 courses)

* CSIT 220
* CSIT 301
* CSC 230
* CSC 240

NogDog

This might help:

$sql = sprintf("UPDATE program_entries SET `program_entry_info`='%s'
            WHERE program_id = '%s' AND program_entry_label_id = '%s'",
            mysql_real_escape_string($entry),
            mysql_real_escape_string($program_id),
            mysql_real_escape_string($program_entry_label_id));

andersstudio

awesome. It worked.
I researched the mysql_real_escape_string and sprintf but am having a tough time making sense of the whole statement.

Would you mind explaining what is happening? I'd appreciate it.

Thx.

NogDog

The sprintf() function allows you to put placeholders in the string that is the first argument, denoted by the "%" followed by a letter (and possibly with other special instruction characters in between). In this case I'm just using the "%s" place-holder for each of the three values where you want a variable value, as %s just means "treat the value as a string". Then for each such place-holder, you need an additional argument for the value that goes there. So we're just using the mysql_real_escape_string() function with the desired variable for each value, in sequence. The mysql_real_escape_string() function is basically the replace function you need to "escape" any characters that would otherwise be problematic within a MySQL query.

andersstudio

Thanks a lot.

andersstudio

I thought this had corrected the problem, but there is some inconsistency.

$sql = sprintf("UPDATE program_entries SET `program_entry_info`=\"%s\"
				WHERE program_id = \"%s\" AND program_entry_label_id = \"%s\"",
				mysql_real_escape_string($entry),
				mysql_real_escape_string($program_id),
				mysql_real_escape_string($program_entry_label_id));

$result = mysql_query($sql,$dbc) or die(mysql_error() . " <br /> $sql");
		//	GREAT FOR DEBUGGING
		if(mysql_affected_rows() == 0){
		   	user_error("<em>DEBUG: No rows updated. </em><br />Query: $sql", E_USER_WARNING);
		} 
		//print("<p>$sql</p>");
		mysql_close($dbc);

Warning: DEBUG: No rows updated.
Query: UPDATE program_entries SET program_entry_info="

* HIS 300

" WHERE program_id = "40" AND program_entry_label_id = "18" in /db/www/html/cms/catalog/forms/web/update_program_entry_db.php on line 45

Any ideas?