from insert into to update

[skoff]

Hi everyone!

I have this code :
<?php

$where_form_is="http://".$_SERVER['SERVER_NAME'].strrev(strstr(strrev($_SERVER['PHP_SELF']),"/"));

include("config.inc.php");
$link = mysql_connect($db_host,$db_user,$db_pass);
if(!$link) die ('Could not connect to database: '.mysql_error());
mysql_select_db($db_name,$link);
$query = "INSERT into`".$db_table."` (field_1,field_2,field_3,field_4,field_5,field_6) VALUES ('" . $_POST['field_1'] . "','" . $_POST['field_2'] . "','" . $_POST['field_3'] . "','" . $_POST['field_4'] . "','" . $_POST['field_5'] . "','" . $_POST['field_6'] . "')";
mysql_query($query);
mysql_close($link);

include("confirm.html");

?>

and what i would like to do is not to insert data in fields but to update it. How can I do that?

Thanks a lot!

[blang]

Are you simply asking for the UPDATE statement syntax [dev.mysql.com]?

BTW, it's an incredibly Bad Idea to allow POST data (or any REQUEST data for that matter) to interact with your database. Be sure you properly filter and escape all incoming data before you even make a connection to the database.

[skoff]

i dont really care for security right now its just a test. What i want to know is what do i have to modify in this code to update data not to insert data in fields.

[LifeinAsia]

As blang pointed out, you need to use UPDATE instead of INSERT.

[rocknbil]

First, choose some unique field from your input as a handle to see if the record exists. If it's an email address,

select id from table where email='$email';

if ($id > 0) { $select = "update table set.... where id='$id'"; }
else { $select = "insert into table (fields...) values (values....)"; }

Then use the variable $select to perform your query.

Funny thing about security, bad habits have a way of sticking and getting forgotten once something works . . . . take heed. :-)

[blang]

rocknbil> IMHO, it's A Bad Idea in more ways than one to perform a SELECT just so you can perform an INSERT or UPDATE.

Typically there are two ways you can go here:

1) Force a UNIQUE constraint on a column and perform the INSERT statement. If it fails you know the value exists and you can alert the user to try something else (e.g. in username registrations, etc).

2) Perform an INSERT...ON DUPLICATE KEY UPDATE [dev.mysql.com] or REPLACE [dev.mysql.com] statement instead. This is likely what the OP needs to do in this situation (with the script example given).

In any event, all of these are detailed in the MySQL manual section on Data Manipulation Statements [dev.mysql.com].

[rocknbil]

to perform a SELECT just so you can perform an INSERT or UPDATE.

There are **lots** of reasons you'd want to do this from a usability standpoint.

- Instead of throwing an error, you can present a lost login screen for a user who's already registered, using the data you captured in the initial select.

- In a register scenario, you can get the user name instead of ID and present alternative choices to the user (see Yahoo, eBay, etc.)

- If you're adding a record as in a product for a shopping cart, you'd be checking the product ID. Instead of an error screen, you can a) provide a link to edit the existing item, and b) present the error that it already exists and to choose a different product id. You wouldn't have that product ID "in hand" without looking for it first.

- If none of the conditions above throw an error, you have both selects in hand to do either/or, and this gives you two select statements, easier to debug. For example, in an insert you'd create a unique id (never the auto increment id, that should only be used for program functions, and then only rarely). If it's an update, you need to skip that field. Other perks: for an insert, created and lastmod are the same, but for an update, you want to only update lastmod.

Those are just a few off the top of my head, an extra select to decide whether to insert or update can often be a better choice all around, but it's your programming, choose as ye may.

[blang]

rocknbil> You're right, there are some reasons to perform the SELECT ... INSERT combo, but I stick to what I said about this situation. A couple of the methods you mentioned are usually performed via XMLHttpRequest (a BUNCH of SELECT statements), but they're performed before the submission has even happened, which is arguably better / worse on server performance depending on how you look at it. At any rate, I concede. ;)