Exporting new records

[clay1]

Trying to figure out the best way to set this up.

 

People register my site.

 

Their profile has the date they registered.

 

I've got another table called 'markets' which has some information that is specific to various cities that the people who register are in.

 

One of the columns in markets is a toggle to determine whether that market should be exported

 

ie: Austin - export: 1

Boston -export: 0

 

My goal is to grab all of the new profiles for all markets where export = 1

 

I added a datetime field to the markets table.

 

I'm confused how to proceed however though

 

I was originally thinking grab the datetime from markets and select the stuff that is newer than that, then remembered I am dealing with more than 1 market.

 

Then I thought 'well just have a table that stores the last export date and select the newer stuff than that' but I wasn't sure if that would work either.

 

Advice?

 

Added: This is using mysql

[clay1]

Also: Since my profiles only have a date, should I just use a date column for the other table and live with only being able to export once a day?

[clay1]

Or actually, should I just add a column to the profiles 'exported' and update it when the export script is run?

 

That would work for new ones but there are already thousands and thousands in the table

[clay1]

Would I be better off doing this based on ID#?

 

Anyone?

 

This seems like it should be a fairly common task, yet I can't find any answers for it whatsoever.

[clay1]

Anyone?

 

Should this be moved to the mysql forum?

[akitchin]

i take it your definition of "new" is simply "new registrations since last export"? if that's the case, then your best bet is indeed to have a `last_exported` column in your markets table with the format DATETIME and update it each time that market's users have been exported. then it's simply a matter of selecting WHERE `date_registered` > `last_exported`.

 

i'm not sure how you intend to run these exports, or what the `export` column's specific significance is (ie. does it mean "export on the next script run," or "export everytime the script runs").

[clay1]

Thanks for the reply.

 

It does mean 'export every time the script is run'. There are 30 markets but only 8 of them are being used right now so to say.

 

 

Right now I have this and it is dumping to an excel file the way I want:

 

$result = mysql_query("SELECT * FROM markets WHERE export=1 ORDER BY market", $recipes) 
or die(mysql_error()); 

$num = mysql_num_rows($result);
if($num == 0){
exit;
}

while ($row1 = mysql_fetch_assoc($result)) {
$result2 = mysql_query("SELECT * FROM free_for_all_payment WHERE market='{$row1['market']}' and age>= '{$row1['minage']}' and income>='{$row1['minincome']}' ORDER BY market", $recipes)
or die(mysql_error());

while ($row = mysql_fetch_array($result2)) {

DO EXCEL STUFF

}
}

 

Do I just need to add

mysql_query("UPDATE markets SET lasteexport = now()");

before the double braces at the end?

[akitchin]

actually, you would add it at the end - you don't want to update the lastexported column until the export has actually completed (which isn't until after those two closing braces). you may also want to adjust the query to only update the lastexported column for the markets with an export value of 1.

 

don't forget to adjust the SELECT query for the users to include the lastexported value - remember, you only want to select users that have registered since the date/time in that column.

[clay1]

Actually, obviously I would need to change the select statements and add something like regdate > lastexport

 

My dilemma is with regdate being a date field is there the potential for orphaned records not being exported?

 

So someone signs up an hour after the script is last run for example. Their regdate will be the same date is the last export-- will they not be exported the next time it is run?

[akitchin]

if you want greater than single-day resolution in the operations, you will need to change the user's registration date columns to a DATETIME format. in order to try to avoid orphaning, you may want to add a padding (say, maybe 5 minutes' worth) for the time it takes to actually run the excel export as well.

[clay1]

Thanks, this is helping a lot.

 

by padding do you mean something like lastexport +-5 minutes?

[akitchin]

exactly:

 

WHERE registration_datetime > DATE_SUB(lastexport, INTERVAL 3 MINUTE)

 

since you're starting to play with dates and times, be sure to have a look in the MySQL manual's section on Date and Time Functions, as there are a slew of very handy functions that you can use right in your queries.

[clay1]

RE: don't forget to adjust the SELECT query for the users to include the lastexported value

 

 

Do I need to dump the last exported value into a variable or can I use values from different tables directly in the one query?

[akitchin]

well, you're already grabbing the lastexport column's value in the query you use to get the markets in the first place:

 

SELECT * FROM markets WHERE export=1 ORDER BY market

 

unless i'm mistaken about where that column resides.

[clay1]

Oh right. Nevermind!