How to overwrite an existing table record

[jllav03]

I am new to PHP.

 

Goal: I want to create a php function that will override an existing table record

Condition: Only if the text in the id input box is identical to what is already in the database.

Currently, it just creates a new record.

 

The id in question is a Title field within an html Form. So if the title which has an id of dmname is already in the database, the new entry should override/replace what is there.

 

I'm using Joomla as my cms system which has an area for the PHP code and uses MySQL.

 

HTML snipet:

 

<div class="form_item">

  <div class="form_element cf_textbox">

    <label class="cf_label" style="width: 150px;">Title:</label>

    <input class="cf_inputbox required" maxlength="150" size="30" title=""id="dmname" name="dmname" type="text" />

  </div>

 

Please let me know if I need to clarify more. Thank you

[xyph]

Make the column unique. Use

 

INSERT... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

[jllav03]

Thanks Xyph, this is good, but I already have a primary key in that table. Can I have more than one unique id per table? Thanks

[xyph]

Of course you can. The manual will be a faster answer to questions like this... or even just trying it.

[jllav03]

Thanks for all your help, im on it!

[jllav03]

I'm getting closer. I was able to mark my field TITLE as Unique, (im using myPHPAdmin), and it works. But when I create a new record with the same TITLE name, it errors out, instead of overwriting the existing record. The article below perfectly addresses my problem, but in phpMyAdmin, how do I set the field to NOT NULL and change INSERT to INSERT IGNORE? I can't even find an option for that.

 

*********

What I found:

 

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it's also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values:

 

 

CREATE TABLE person_tbl

(

  first_name CHAR(20) NOT NULL,

  last_name CHAR(20) NOT NULL,

  sex CHAR(10)

  PRIMARY KEY (last_name, first_name)

);

 

The presence of a unique index in a table normally causes an error to occur if you insert a record into the table that duplicates an existing record in the column or columns that define the index.

 

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

 

*********

[Pikachu2000]

INSERT . . . ON DUPLICATE KEY UPDATE syntax may be what you're looking for.

[jllav03]

Thanks Pikachu. Would this be the correct syntax for the SQL statement then? I'm only using one unique field.

 

UPDATE `jos_docman` SET `dmname`=[value-3], WHERE `dmname`=1

 

jose_docman being the table,

dmname being the field name,

[value-3] being the column's value,

and WHERE dmname=1, meaning only one entry is allowed?

[jllav03]

.

[Pikachu2000]

The link I posted is to the MySQL manual page and it explains the syntax.