Need some simple mysql help

[Myst]

Alright
I have some chatbots, and id like to save conversations for each user that my bots talk to

What would be the best way of doing this? My table has 2 columns, 'user' and 'chattext'. For every message received, what would be the best way of updaitng the table? Should i go <Select chattext from myTable where user = 'some_user'>.. If theres no results, then add the user.. if there is, then do <update mytable set chattext = mysql_result(chattext) & 'new text' where user='some_user'>??

it seems wrong cause im searching for user='some_user' two times.. once to retrieve chattext (if there is any - i do this to 1) check if the user exists in the table and 2)to store chattext for later when im updating the table). and the other to update the table where user='some_user'.

Can this be done in a better way? heres what im thinking - pseudo mysql code
"update mytable append(new_chat_text) to chattext where user='some_user'.... on error insert into table mytable(user,chattext) values some_user, new_chat_text"

but i dont know enough sql to do this right

any suggestions?

[Myst]

come on boys this is elementary

[calmlikeabomb]

So you want 1 unique row for each user and you are going to use the chattext column to store the last message the user typed ?

[calmlikeabomb]

This question really doesn't make much sense. You are asking about updating the chattext column, but your codes looks like you are trying to append to it...

[Myst]

im trying to append it
but i dont know much about mysql, so i thought i need the update command..

[calmlikeabomb]

For every message received, what would be the best way of updaitng the table?

You can do something to this effect....

$username = $_COOKIE['username']; //What user are we updating ?
$chattext = $_POST['chattext']; //What text did this user submit ?

//Let's check to see if this username is in the table.
$usernamequery = "SELECT username FROM members WHERE username='$username';
$username = @mysql_query($usernamequery);
$numusername = mysql_num_rows($username);//Does this username exist ?

if ($numusername = 0) {//If username doesn't exist add to table.
$adduserquery = "INSERT INTO members (username,chattext) VALUES ('$username', '$chattext');
}
elseif ($numusername = 1) {//If the username already exists update chattext.
$updatechat = "UPDATE members SET chattext='chattext{$_POST['chattext']}'"
}

In this example a form is being used to pass the values to the variables.

[Myst]

so theres no way of only doing one query??

ie
can you simply do something like
$updatechat = "UPDATE members SET chattext='chattext{$_POST['chattext']}' WHERE user='$username'"
and if it fails, execute
$adduserquery = "INSERT INTO members (username,chattext) VALUES ('$username', '$chattext');

[calmlikeabomb]

yeah you could do that.

[calmlikeabomb]

$username = $_COOKIE['username']; //What user are we updating ?
$chattext = $_POST['chattext']; //What text did this user submit ?

//Let's check to see if this username is in the table.
$usernamequery = "[b]UPDATE members set chattext = 'chattext{$_POST['chattext']}' WHERE username='$username'[/b]";
$username = @mysql_query($usernamequery);
$numusername = [b]mysql_affected_rows[/b]($username);//Does this username exist ?

if ($numusername = 0) {//If username doesn't exist add to table.
$adduserquery = "INSERT INTO members (username,chattext) VALUES ('$username', '$chattext');
}

It's pretty much the same on system resources needed.....

Note the bold.

[Dragon Curve]

Use MySQL 4.1 and you can do it all in one query. I use this for time costly reporting tasks.

INSERT INTO table VALUES ('user', 'text') ON DUPLICATE KEY UPDATE SET text = 'text';

Make sure you have a PRIMARY KEY or UNIQUE KEY on the user field.

Anything under MySQL 4.1 isn't capable of this so you'll have to use multiple queries.

[calmlikeabomb]

Nice thanks for that

[Dragon Curve]

Btw, as of MySQL 4.1.1 you can also use VALUES(Column) in the ON DUPLICATE KEY UPDATE .. i.e.

INSERT INTO Reports VALUES (DATE(NOW()), 1) ON DUPLICATE KEY UPDATE ReportValue = VALUES(ReportValue) + 1;

very useful - not to mention fast

[Cogitator]

fuckin' A! you learn something new about this shit every day.

[Myst]

awesome

but i get an error..

INSERT INTO GUser VALUES (Myst, hello) ON DUPLICATE KEY UPDATE SET chattext = CONCAT(chattext,hello);
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET chattext = CONCAT(chattext,hello)' at line 1

any ideas?

[Myst]

nm i figured it out
silly me