Need some simple mysql help - GoFuckYourself.com

Myst · 02-10-2005, 06:55 PM

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 · 02-10-2005, 07:14 PM

come on boys this is elementary

calmlikeabomb · 02-10-2005, 08:36 PM

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 · 02-10-2005, 08:42 PM

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 · 02-10-2005, 08:55 PM

im trying to append it

but i dont know much about mysql, so i thought i need the update command..

calmlikeabomb · 02-10-2005, 09:08 PM

Quote:

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

You can do something to this effect....

Code:

$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 · 02-10-2005, 09:26 PM

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 · 02-10-2005, 09:39 PM

yeah you could do that.

calmlikeabomb · 02-10-2005, 09:46 PM

Code:

$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 = "UPDATE members set chattext = 'chattext{$_POST['chattext']}' WHERE username='$username'";
$username = @mysql_query($usernamequery);
$numusername = mysql_affected_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');
}

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

Note the bold.

Dragon Curve · 02-10-2005, 09:48 PM

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 · 02-10-2005, 09:53 PM

Nice thanks for that

Dragon Curve · 02-10-2005, 10:06 PM

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 · 02-10-2005, 10:27 PM

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

Myst · 02-10-2005, 11:19 PM

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 · 02-10-2005, 11:35 PM

nm i figured it out
silly me

02-10-2005, 06:55 PM	#1
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	Need some simple mysql help 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? __________________ ICQ: 298-523-037

02-10-2005, 07:14 PM	#2
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	come on boys this is elementary __________________ ICQ: 298-523-037

02-10-2005, 08:36 PM	#3
calmlikeabomb Confirmed User Join Date: May 2004 Location: SW Palm Bay, Florida Posts: 1,323	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 ? __________________ subarus.

02-10-2005, 08:42 PM	#4
calmlikeabomb Confirmed User Join Date: May 2004 Location: SW Palm Bay, Florida Posts: 1,323	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... __________________ subarus.

02-10-2005, 08:55 PM	#5
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	im trying to append it but i dont know much about mysql, so i thought i need the update command.. __________________ ICQ: 298-523-037

02-10-2005, 09:26 PM	#7
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	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'); __________________ ICQ: 298-523-037

02-10-2005, 09:39 PM	#8
calmlikeabomb Confirmed User Join Date: May 2004 Location: SW Palm Bay, Florida Posts: 1,323	yeah you could do that. __________________ subarus.

02-10-2005, 09:48 PM	#10
Dragon Curve Confirmed User Join Date: Oct 2002 Posts: 252	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.

02-10-2005, 09:53 PM	#11
calmlikeabomb Confirmed User Join Date: May 2004 Location: SW Palm Bay, Florida Posts: 1,323	Nice thanks for that __________________ subarus.

02-10-2005, 10:06 PM	#12
Dragon Curve Confirmed User Join Date: Oct 2002 Posts: 252	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

02-10-2005, 10:27 PM	#13
Cogitator Confirmed User Join Date: Feb 2002 Location: Florida Posts: 672	fuckin' A! you learn something new about this shit every day. __________________ - this space intentionally left blank -

02-10-2005, 11:19 PM	#14
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	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? __________________ ICQ: 298-523-037

02-10-2005, 11:35 PM	#15
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,707	nm i figured it out silly me __________________ ICQ: 298-523-037