[SOLVED] Insert data but if theres a dup username just update a column?

[Teck]

What I'm doing is first counting the number of times unique usernames

appear in the "usernames" column, then I'm echoing it out and then later

inserting that data into another table...

 

The Problem:

When the script is run a second time, the data is reinserted, I would

prefer that it update the row if the username exists already & if not

add it in... How can i get it to do that?

 

 

$query = "SELECT username, COUNT(*) AS `col1` FROM test_table GROUP BY username ORDER BY username";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$username = $row['username'];
$col1 = $row['col1'];

echo "$username | $occurs <br>"

$query2 = "INSERT INTO a_test (username, col1) VALUES ('$username', $col1)";
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
}

 

 

Heres an overview of what the table looks like in case it helps

------------------------------------------------

| table: "a_test"                                          |

----------------------------------------------------

| username    | col1    | col2    | col3    | col4    |

----------------------------------------------------

| Aquila          | 42      | 521      | 46321    | 323    |

| Addison      | 35      | 854      | 15145    | 856      |

| Gareth      | 25      | 321      | 87618    | 516      |

| Finn        | 34      | 651      | 81641    | 261      |

| Jesse        | 76      | 452      | 84361    | 369    |

| Bradley    | 39      | 672      | 81651    | 814      |

| Victor      | 65      | 541      | 68932    | 675      |

| Cain        | 20      | 111      | 81118    | 548      |

---------------------------------------------------

 

 

[Illusion]

This thread could be helpful to you.

http://www.phpfreaks.com/forums/index.php/topic,152686.msg659586.html#msg659586

[fenway]

Sounds like the job for insert on duplicate update.

[Teck]

Sounds like the job for insert on duplicate update.

Good Idea!

 

I just had to set the "username" col as the pri key and use ON DUPLICATE and bang! Its doing exactly what i wanted...