SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast Ckeren's Avatar
    Join Date
    Jun 2001
    Location
    AU
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can MySQL update ...

    ... the auto increment

    here is my table
    create table banned_list
    (
    date_log date not null,
    ip varchar(30) not null,
    count tinyint default 1 not null
    );

    okay here's the problem

    lets say inside this table has value 2001-07-05, 123.123.123.0, 1 respectively with the above attributes(in tbl banned_list).

    Can MySQL update only the the count increase by 1 whenever we try to insert the same date and ip ?

    or maybe some other ???

    Thanx

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check out MySQLs REPLACE function http://www.mysql.com/doc/R/E/REPLACE.html

    I reckon this might work (but can't guarantee it ):

    create table banned_list
    (
    ip varchar(30) not null,
    date_log date not null,
    count tinyint default 0 not null,
    PRIMARY KEY(ip, date_log)
    );

    REPLACE checks whether a unique indexed value does not already exist to determine whether to update or whether to insert (thus I defined what is the logical primary key for your table above).

    and to insert/update a record, the sql would be:
    Code:
    REPLACE INTO banned_list
    SET ip = whatever,
        date_log = CURDATE(),
        count = count + 1;
    Last edited by freakysid; Jul 10, 2001 at 02:06.

  3. #3
    SitePoint Enthusiast Ckeren's Avatar
    Join Date
    Jun 2001
    Location
    AU
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll give it a shot then
    Thanx mate


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •