please help with query

[lingo5]

Hi,

I have a MySQL query that inserts some values like so:

 

  $insertSQL = sprintf("INSERT INTO t_propiedades (regimen, localidad, isla, tipopropiedad, zona, amueblado, habitaciones, estado, baños, cocina, salon, terraza, aseos, metros, descripcion, precio, precio_oferta, oferta, novedad, destacada) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['regimen'], "text"),
                       GetSQLValueString($_POST['localidad'], "text"),
                       GetSQLValueString($_POST['isla'], "text"),
                       GetSQLValueString($_POST['tipopropiedad'], "text"),
                       GetSQLValueString($_POST['zona'], "text"),
                       GetSQLValueString(isset($_POST['amueblado']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString($_POST['habitaciones'], "text"),
                       GetSQLValueString($_POST['estado'], "text"),
                       GetSQLValueString($_POST['baos'], "text"),
                       GetSQLValueString($_POST['cocina'], "text"),
                       GetSQLValueString($_POST['salon'], "text"),
                       GetSQLValueString($_POST['terraza'], "text"),
                       GetSQLValueString($_POST['aseos'], "text"),
                       GetSQLValueString($_POST['metros'], "text"),
                       GetSQLValueString($_POST['descripcion'], "text"),
                       GetSQLValueString($_POST['precio'], "double"),
                       GetSQLValueString($_POST['precio_oferta'], "double"),
                       GetSQLValueString(isset($_POST['oferta']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString(isset($_POST['novedad']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString(isset($_POST['destacada']) ? "true" : "", "defined","1","0"));

  mysql_select_db($database_MySQLconnect, $MySQLconnect);
  $Result1 = mysql_query($insertSQL, $MySQLconnect) or die(mysql_error());

 

what I want to do is when the $_POST['precio_oferta'] is left blank in the insert form, the value that is already on the DB remains instead of being deleted as it is at the moment.

 

My database sets the valur to 0 by default, but when a record is inserted, the 0 gets deleted and set to blank...which sucks.

 

Thanks

[smoseley]

If it's blank, you can set the php value to "NULL", which will tell the DB to use the default value (if it's a NOT NULL field)

[lingo5]

thanks, but how do I do that?

[Kays]

You want to only update values that are modified right? If so, smoseley may have misunderstood. In that case, you'll have to check each field and update each one accordingly.

[lingo5]

thanks kays,

yes that's right, I want to do that. I need to check if the "precio_oferta" field is empty. If so, I want the column "precio_oferta" to stay as it is.

How can I do this?...any code eamples?

Thanks

[Pikachu2000]

If you're editing an existing entry, why not load the existing data into the form to begin with? Then only values that are modified will be updated.

[Kays]

It's easy. Just add a ON DUPLICATE KEY UPDATE to your INSERT. But you will need a unique identifier to the record in the DB. Otherwise, it wouldn't be a duplicate entry.

[lingo5]

OK, here's how I did it and it works....

 

GetSQLValueString($_POST['precio_oferta'] ? "'{$_POST['precio_oferta']}'" : "precio_oferta"),

Is this a correct way of doing this?

[smoseley]

OK, here's how I did it and it works....

 

GetSQLValueString($_POST['precio_oferta'] ? "'{$_POST['precio_oferta']}'" : "precio_oferta"),

Is this a correct way of doing this?

 

Nope, that'll put the string "precio_oferta" as the value you're setting it to.  Try this:

 

strlen($_POST['precio_oferta']) ? GetSQLValueString($_POST['precio_oferta']) : "`precio_oferta`",

[lingo5]

thanks smoseley!!!