[SOLVED] Mass INSERT and grabbing ID from another table

[acctman]

I'm going to be using two tables (see below). Basically I need to access the r_members table and foreach m_id in that table, create and insert into r_messages applying the 'r_members.m_id' into r_messages.msg_recip allow with all the present field info. this is all i've come up with so far INSERT INTO r_messages ... IN ( SELECT m_id FROM r_members )

 

Table: r_members

Field: m_id (userid)

 

Table: r_messages

Fields:

msg_sender : 39

msg_recip : (userid from r_members.m_id)

msg_subj : Test

msg_text : Test Subject

msg_ip : 127.0.0.1

msg_status : 1

[rhodesa]

INSERT INTO r_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_ip,msg_status) SELECT 39,m_id,'Test','Test Subject','127.0.0.1',1 FROM r_members

[acctman]

INSERT INTO r_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_ip,msg_status) SELECT 39,m_id,'Test','Test Subject','127.0.0.1',1 FROM r_members

 

thanks. quick question how do I handle ' quotes in the msg_text?

[rhodesa]

if it's coming from a variable, just use mysql_real_escape_string() on it first. it will properly escape it

[acctman]

if it's coming from a variable, just use mysql_real_escape_string() on it first. it will properly escape it

 

its being inserted directly into the database via phpmyadmin. i figureed the query would process a lot faster than going through http

[rhodesa]

Ah, in that case, you just need to add a \ before it:

INSERT INTO r_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_ip,msg_status) SELECT 39,m_id,'Test Subject','This isn\'t a good test message','127.0.0.1',1 FROM r_members

[acctman]

thanks