[SOLVED] Breaking Up Querys

[johng]

I have a page that updates a table every workday. (these are standard updates, like if someone has moved, or has a different phone number or something.)  It is too big of a query to run the entire table all at once, so it is split out. 

 

The split is grouped by the first letter of a first name field.  Right now it is broken up into 5 parts, and each part has a group of letters (i.e. "a" to "d" is one group, "e" to "k" is the next, and so on.  As you can see, these do not have an even number of letters, but that is because some letters have a lot more entries, and need to be in smaller groups.) 

 

The reason it is split out into 5 groups is that I can run one group on each workday (Monday through Friday), and I can have the PHP check what day of the week it is.  (I use a case statement, the first case is for Monday, the second is for Tuesday, etc.)

 

Maybe it will be easier if I add some code:

 

<?php

    $today = date("w");  //This checks to see what day of the week it is and puts it as a numerical value.

    switch($today){
      case 1:                //This case runs on Mondays
      $criteria = " where fname between 'a%' and 'd%' OR fname like 'd%'";
      break;
      case 2:                //This case runs on Tuesdays
      $criteria = " where fname between 'e%' and 'k%' OR fname like 'k%'";
      break;
      case 3:                //This case runs on Wednesdays
      $criteria = " where fname between 'l%' and 'n%' OR fname like 'n%'";
      break;
      case 4:                //This case runs on Thursdays
      $criteria = " where fname between 'o%' and 'r%' OR fname like 'r%'";
      break;
      case 5:                //This case runs on Fridays
      $criteria = " where fname between 's%' and 'z%' OR fname like 'z%'";
      break;
?>

 

Now the table is getting too big, and I am going to have to change it so that it will go through the whole table in a month (about 20-21 work days) 

 

My problem is that I don't know how I would have the code divide the groups into 20 parts for the case statement.  I need it to break out so that it will hit the next group on the next business day, so I can't just use the day of the month, because then on weekends, it will not update those groupings. 

 

I don't have to use the first letter of the first name, it was just easier to do that for now.  I could potentially use an automatic row id, in which case it would be much easier to make even groupings, but that will not be too difficult. 

 

So if anyone can help me out with this, that would be great.  Hopefully it isn't too confusing, and the code helped.  If you need me to clarify something, please let me know. 

Thanks.

[cooldude832]

Why not do your queries as

<?php
$query[0] = "Update `Users` Set `phone` = `$newphone` where id=`$id`";

foreach($query as $value)
{ $executed[] = mysql_query($value) or die(mysql_error());}
print_r($exectued);
?>

Since its ideally a cron job sotra deal you don't need to worry about executing mass queries since it happens only once a day

[Wildbug]

I think you're going to find some well-meaning skepticism/solutions that attempt to answer your method of updating before you get answers about splitting a month of workdays into first letters of names.  It just seems like the update shouldn't be so hard.

 

These questions come to mind:

 

How big is your table/database?  How are you getting new data and how are you updating?  Can I see the whole updating code?  Why can't any updates be done on the weekend?  Can't this be automated?  Can you use the LOW_PRIORITY modifier to reduce the load on the server?

[johng]

Well the problem comes when I add in the complexity of what it does before updating. I first have to go to a website where this information is held, and yes, this is the only way to do it, unfortunately. I don't have access to the database where it is being stored. Then I have to parse the source code for that website and look for specific lines of code to get the small amount of information I need. Then it has to update the row, and I have over 1000 rows in the table that need to get updated. It is a terrible system, but it is the simplest way of doing it for now. I think I am going to just divide it up into 30 groups, and just have it go off the date. It won't hit everything every month, but in two months, in theory it should go through every record. And if someone needs something immediately, they will let me know, and I'll do it manually, which is what they do now anyway.

 

Anyway, Thanks for the help, I'll consider this topic "solved" because, for the most part, it is.

 

Edit:  1000 records may not seem like much, but when you consider that you have to go to a different website (or part of the same website) and get the source code and parse it for each record, it becomes pretty cumbersome.