[RESOLVED] I can't see the problem - quick fix - I'm sure.

Aurasia

I'm sorry to drop such a dumb question in here, but every single time I tried this code or any variation I've been able to come up with, I get an error ...

$updateSQL2 = sprintf("UPDATE polls SET starting='%s', ending='%s' WHERE id='%s'",
	   mysql_real_escape_string($fstart_timestamp),
	   mysql_real_escape_string($fend_timestamp), 
	   mysql_real_escape_string($_POST['id']));
   $Result2 = mysql_query($updateSQL2, $DB_Con) or die(mysql_error());

Please shoot me back a correction - I cannot - for the life of me figure out what I'm doing wrong.

mystrymaster

maybe I'm missing something but what is %s equal to?

Aurasia

As I understand the "sprintf" function - it's treating them as strings ... so each %s means look to the following of the initial statement and instert the value of the variable.

But what do I know ... I can't even get this simple little thing to work.

Aurasia

Ok - I figured it out ...

$updateSQL2 = sprintf("UPDATE polls SET starting='%s', ending='%s' WHERE id='%s'", 
       mysql_real_escape_string($fstart_timestamp), 
       mysql_real_escape_string($fend_timestamp),  

       mysql_real_escape_string($_POST['id'])); 
   $Result2 = mysql_query($updateSQL2, $DB_Con) or die(mysql_error());

Had to become:

  $updateSQL2 = sprintf("UPDATE polls SET `starting`=%s, `ending`=%s WHERE `id`=%s",
	   mysql_real_escape_string($fstart_timestamp),
	   mysql_real_escape_string($fend_timestamp), 
	   mysql_real_escape_string($_POST['id']));
   $Result2 = mysql_query($updateSQL2, $DB_Con) or die(mysql_error());

I don't suppose anyone out there can tell me when the little "tilde" character is required to be set around the names?

mystrymaster

congrats good job

Aurasia

Thanks for lookin' at this mystrymaster ... I had a couple functions that calculated the $fstart_timestamp and $fend_timestamps above ... the error ... seems to be that the SQL won't find the correct table column due to the name ... I've seen DreamWeaver do it before when I had columns named "user" - it would always put it down like user ... leaving other columns like "profile" untouched ... they would look like this ...

user='%s', profile='%s', ... etc ...

I hope someone out there can explain to me when this special syntax is required in a query.

Sxooter

Yep, I'll tell ya...

The SQL spec says fold all identifiers (like table and field names) to upper case. Some databases, like PostgreSQL, fold to lower case because it's easier to read. Anyway...

In order to make the database preserve your case, you enclose the identifiers in quotes, like so:

create table "MyTABLE" (id int primary key, "Info" text);

This would create a table with mixed case names, and an field with a capitalized I for info.

MySQL, for reasons known only the people who at the very first started coding it, decided against a " mark, and went with a ` marke for the identifier quote character.

If you so a "show tables;" what do you see? (without the quotes of course)

csn

Why can't postgres just not fold instead of requiring uppercase table names to be in quotes? I can't remember if it has something to do with function names or something. I'm surprised the otherwise SQL-adherent Postgres broke spec in this case, but I do prefer using lowercase (lazy typer).

Sxooter

Well, the key is, that while postgresql DOES break the spec by folding to lower instead of upper, it follows the spec in that it DOES fold. so, generally, the only time a problem shows up is when someone quotes SOME of the identifiers but not all. Which creates problems for using Oracle, and most other SQL database servers that also fold case (just to upper not lower...)