[SOLVED] Need compare code and send email code

[bulgin]

This should be simple enough but I'm areal newbie and could use some help.

 

table_a contains a list of unique_ids

table_b contains a list of unique_ids and email_address, name, message fields.

 

The unique_ids in table_a may or may not be the same as the unique_ids in table b.

 

I need a php script that I can run from a cron job that will:

 

enumerate through the unique_ids in table_a that have the field sent=0 and find their equal match in table_b.

 

If found:

 

mark the record in table_a as sent=1, pull the corresponding unique_id, email_address, name and message field from table_b that matched against unique_id from table_a and construct an email message from those fields and send it out the door.

 

If someone is quick at hand and sharp of mind perhaps they can shoot me the code I need to do this or point me in the right direction.

 

Thank you!

[corbin]

Hate to be blunt, but have you tried anything?

 

Doubt anyone is going to just give you the code.

 

 

 

Anyway, you will want to use a JOIN clause in a SELECT statement to pull data from both of the tables.

 

 

Then, you will want to process the email and then do an UPDATE statement.

[bulgin]

Thanks.  Okay to be blunt.  blunt is good.  That can start me in the right direction. It's the enumerate part that puzzles me...

[corbin]

With a JOIN, only 1 query will be required.  The JOIN is the "matching the field in tablea to tableb."

 

 

As for actually processing the query results, you would do:

 

$q = mysql_query("SELECT......");

while($r = mysql_fetch_assoc($q)) {

    //do something with the row.

}

 

 

This is kinda random, but you aren't new to PHP are you?  You've used PHP with MySQL before, yes?

[bulgin]

$q = mysql_query("SELECT......");

while($r = mysql_fetch_assoc($q)) {

    //do something with the row.

}

 

I'm very new but learning thanks to tutors and gracious posters like yourself.

 

Okay, so the select statement above will retrieve my results into the $q variable, right?

 

Then I can enumerate them with the while statement, right?

 

I just need the part that sends out the email and that would follow the while statement, correct?

 

Thank you for your time!

[corbin]

Errrr....  If you're new, you should probably read a MySQL-PHP tutorial x.x.

 

 

 

 

That specific SELECT statement will do nothing, but that is where one could go ;p.

 

 

 

You would also need to make  database connection and probably select a database to use that code.

 

 

http://php.net/mysql_connect

http://php.net/mysql_select_db

http://php.net/mysql_query

http://php.net/mysql_fetch_assoc

 

http://php.net/mail

[bulgin]

yes, I know that specific select statement will do nothing, and I know how to connect to a db and connect to a database, and yes I need to read up on php,mysql.  All agreed.  But specifically would the join query you proposed be included in the select statement?

 

thank you.

 

just looking for pointers -- not someone to write it all out for me.

[corbin]

A JOIN clause is a... hrmm don't know how to word it... it is basically a construct to join two tables.

 

 

 

For example, for a moment, pretend we have the following schema/data:

 

users

user_id|user_name

1|corbin

2|bob

3|john

 

user_posts

post_id|user_id|post_content

1|1|hi

2|1|hello

3|3|I'm John

 

 

SELECT u.user_id, u.user_name, up.post_id, up.post_content FROM users u JOIN user_posts up ON up.user_id = u.user_id;

 

Would select:

 

user_id|user_name|post_id|post_content

1|corbin|1|hi

1|corbin|2|hello

3|john|3|I'm John

[bulgin]

Thanks Corbin.  I actually think my select statements would be simpler because there would never be duplicates of any value in either table.  So, continuing with your example, I only need to get the post_id and post_content from the user_posts table when the user_id from the users table, matches the user_id in the user_posts table.

 

my schema/data will be:

 

tablea

unique_id

 

tableb

unique_id

name

email

message

 

SELECT tablea.unique_id, tableb.name, tableb.email, tableb.message FROM tablea JOIN tableb ON tablea.unique_id=tableb.unique_id;

 

would that work?

 

then I need to get those results into an array and enumerate through the array and for each row, send out an email.

[corbin]

Yes, that would work.

 

 

 

You don't actually have just 1 column in tablea do you?

[bulgin]

I only have 1 column in tablea of any value for the query.  It's actually a field from the mysql table of the apache logs.  So there are also time stamp fields, URL fields, IP field, browser info field, etc.

 

Are you sure my query would work?

 

I eliminated one parameter from my query that was in yours, look carefully.  I eliminated the part below marked between the carrot symbols because I didn't know why it was in there and it confused my logic.

 

Yours:

 

SELECT u.user_id, u.user_name, up.post_id, up.post_content FROM users ^u^ JOIN user_posts up ON up.user_id = u.user_id;

 

Mine:

SELECT tablea.unique_id, tableb.name, tableb.email, tableb.message FROM tablea JOIN tableb ON tablea.unique_id=tableb.unique_id;

[corbin]

Oh that's short syntax for aliases.

 

 

it could have been "users AS u"

 

 

I just didn't want to type out users a lot ;p.

[bulgin]

but would the lack of it my my query stop it from working? 

 

so this is what we have then, or am I way, way off base? 

 

$q = mysql_query("SELECT tablea.unique_id, tableb.name, tableb.email, tableb.message FROM tablea JOIN tableb ON tablea.unique_id=tableb.unique_id;");

while($r = mysql_fetch_assoc($q)) {

    //do something with the row.

}

[corbin]

That query should be fine.

[bulgin]

Well thanks so much for your help -- I really appreciate it.