Hello:

I need some assistance with the following script configuration. The goal is to run a cronjob with my initial script which would run a mysql query and pull some account ids, it then would create an array of those values and through a for() loop execute a second script with each individual value (account id ) passed on to the secondary script. The secondary script needs to perform specific tasks which are totally dependent upon the account id. Below is what I have.

first Script

<?php
//*
 $link = mysql_connect('****', '****', '*****');
    if(!$link) {
        die('Failed to connect to the server: ' . mysql_error());
    }

    //Select database
    $db = mysql_select_db('*****');
    if(!$db) {
        die("Sorry I am Unable to select database");
    }

$get_acctids="select accountid from company_info"; 
$results = mysql_query($get_acctids) or die(mysql_error());

$new_array[] = $row;
while( $row = mysql_fetch_assoc( $results)){
$acct_data[] = $row['accountid'];
//print_r($acct_data);

}

$key = $acct_data;
//echo "<br /><br />";
//print_r($key);

foreach($key as $value){
    $dr[] = "accountid = '$value' "; // Build array of strings
}
/* for testing purpose, uncomment
echo "<br /><br />";
print "<pre>";
print_r($dr);
print "</pre>";
echo "<br /><br />";
*/

function include_get_params($file) {
  $parts = explode('?', $file);
  if (isset($parts[1])) {
    parse_str($parts[1], $output);
    foreach ($output as $key => $value) {
      $_GET[$key] = $value;
    }
  }
  include($parts[0]);
}

$sql = "select * from company_info WHERE ";
$sql .= implode(' OR ', $dr); // convert to string joined by ' OR ' and add to end of $sql
// Instead of using a loop and running a query for 'Cable Guy'
// and another query for 'John Doe', the following query fetches both rows.
$result = mysql_query($sql);
 $numrows=mysql_num_rows($result);
//echo $numrows;
while($row = mysql_fetch_array($result))
  {
      echo "<br /><br />";

      //echo $row['co_name']."--".$row['accountid']. "--".$row['co_website']."<br />";
        // echo $row[0]."--".$row[5]. "--".$row['co_website']."<br />";


      for ($i=0; $i<1; $i++) {
  if (isset($row['accountid']) ) {
//including the secondary script
   include_get_params('purge_queueList_daily.php?accountid='.$row['accountid']);
 } else {
   break;
 }
}

 //mysql_close($db);

}


?>

Now, secondary script function would be to use each account id and query a corresponding database with the id as the required variable and thus purge a specified table within the script.

Secondary script:

<?php
$club_id =$_GET['accountid'];// This is from the first script and will be required in the included file below (datalogin.php
include 'datalogin.php';
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) {
        die('Failed to connect to server: ' . mysql_error());
    }
    //Select database
    $db = mysql_select_db(DB_DATABASE);
    if(!$db) {
        die("Unable to select database");
    }
    echo $db." <br />";
    //echo $db;
$todayis=date("Y-m-d");//date and time
$qry = "DELETE FROM queue_sys where todays_date NOT LIKE '%$todayis%'";// remove players from the list from previous days
$result=mysql_query($qry);      

if($result)
{
$message= "<br /><br /><p><font size=5 color=blue>Queue table Has been Purged and Updated!</font>";
}
else
{
                $message="Purge failed!";       
         }
   // echo $message;
?>

I'm afraid my logic may be wrong. I'm unable to complete the task of purging the specified table in the secondary script. I would really appreciate some assistance in getting this resolved!

Best,
Mossa

Member Avatar for diafol

Are these scripts on different servers or even accessing different DBs?

I can't see what you're trying to do. By running a cron job, the last thing you need to do is echo anything. Who's there to see it? You should log any errors though.

If you can pass an array of data (account_id) to a script, well that's better that looping and calling an url so many times. Doesn't make sense to me. The include_get_params??

All you need is a way to retrieve all relevant account_ids

Pass them to the delete query - OR better still just use the delete query - as you're not doing anything useful with the select as far as I can see.

How does this work?

DELETE FROM queue_sys where todays_date NOT LIKE '%$todayis%'

Why LIKE?

DELETE FROM `queue_sys` WHERE `todays_date` != '$today'

Doesn't the delete query just work anyway, without fussing with the select queries?

Using include to run a script, doesn't use the GET params AFAIK. I think it's more suitable to use file_get_contents if you want to pass parameters.

I appreciate the responses!

I'm afraid my intended purpose is not being explained correctly.

Firstly, these scripts are all on the same server and there are different databases.

For better explanation, let me annotated the files structure a bit. I'm dealing with three files (or scripts)

file 1: determines total accounts with databases and gets all accountids and arrange them into an array

file 2: gets each account ids (in a for() loop) and contacts datalogin.php (file 3) for the appropriate database and connections strings. Once the appropriate information is retrieved it performs a purge.

file 3: is the datalogin.php. This file holds all database information and conntection strings. Using if and else conditional statements, it assigns the appropriate variables based on the account id.

It became apparent that in order to make multiple runs of file 2 looping through all possible accountid and thus databases, the best approach was "curl" --thanks in part to pritaeas for the suggestion in earlier post. I'm able to achieve the desired goal: A cron Job is executed on file 1 (gets all available account ids); calls file 2 (the purging script, which calls file 3 to determine which database to use that correspond with each accountid in the loop. Here is the full code:

<?php
//* grabbing all available accountids
 $link = mysql_connect('***', '*****', '*****');
    if(!$link) {
        die('Failed to connect to the server: ' . mysql_error());
    }

    //Select database
    $db = mysql_select_db('******');
    if(!$db) {
        die("Sorry I am Unable to select database");
    }
$get_acctids="select accountid from company_info"; 
$results = mysql_query($get_acctids) or die(mysql_error());

$new_array[] = $row;
while( $row = mysql_fetch_assoc( $results)){
$acct_data[] = $row['accountid'];

}

$key = $acct_data;

foreach($key as $value){
    $dr[] = "accountid = '$value' "; // Build array of strings
}

$sql = "select * from company_info WHERE ";
$sql .= implode(' OR ', $dr); // convert to string joined by ' OR ' and add to end of $sql
// Instead of using a loop and running a query for 'Cable Guy'
// and another query for 'John Doe', the following query fetches both rows.
$result = mysql_query($sql);
 $numrows=mysql_num_rows($result);
//echo $numrows;
while($row = mysql_fetch_assoc($result))
  {
$url = 'https://*****/*****/purge_queueList_daily.php?accountid='.$row['accountid'].'';
  call_curl($url);


}
function call_curl($url){
     $curl = curl_init();
     curl_setopt_array($curl, array(
     CURLOPT_URL => $url,
     CURLOPT_TIMEOUT => '5'
     ));
     $resp = curl_exec($curl);
     curl_close($curl);
    }

?>

My secondary script for purging task same as before with a modification in connection link

<?php
$club_id =$_GET['accountid'];// This is from the first script and will be required in the included file below (datalogin.php)
include 'datalogin.php';
$link = mysql_connect('localhost',$account_user, $account_pass);
    if(!$link) {
        die('Failed to connect to server: ' . mysql_error());
    }

    //Select database
    $db = mysql_select_db($account_db);
    if(!$db) {
        die("Unable to select database");
    }
    echo $db." <br />";
    //echo $db;
$todayis=date("Y-m-d");//date and time
$qry = "DELETE FROM queue_sys where todays_date NOT LIKE '%$todayis%'";// remove players from the list from previous days
$result=mysql_query($qry);        

if($result)
{
$message= "<br /><br /><p><font size=5 color=blue>Queue table Has been Purged and Updated!</font>";
}
else
{
                $message="Purge failed!";        
         }
   // echo $message;
?>

Works as conceptualized! If however, there are suggestions for improvement, I'm certainly opened to it!

Thanks,
Mossa

Member Avatar for diafol

I'm really lost with regard to why this is so contrived or convoluted.

Are all DBs supposed to be purged? I'm assuming so, or at least you have a list of DBs in an array. If this is a cron_job, then there's no user to pick DBs to purge.

Can't just this code be used?

$link = mysql_connect('localhost', 'root', '');
if(!$link) logMe('Failed to connect to the server: ' . mysql_error());

function logMe($str)
{
    //this should log error to log file, but for testing purposes...
    echo $str;  
}

$dbArray = array('cable', 'cook', 'removalmen');

foreach($dbArray as $db)
{
    if(!mysql_query("DELETE FROM $db.queue_sys WHERE $db.queue_sys.todays_date NOT LIKE '%$todayis%'")) logMe("Couldn't run delete query on DB $db");
}

Suppose you have multiple accounts each with its own unique db, tables and db connection info. All these accounts are using one file system. Now, one of these files is tasked via performing a cron job of purging a specified table which exist in each account's database. Now, how does one is able construct such a task?

With your suggested code, the db connection would present a problem because each account has its own connection values, such connection values are attained depending on the accountid in a file called datalogin.php(see below). So my thinking was to first get all account ids and make them available to a db connection script called datalogin.php the content of which looks something like below (NOTE: This file is dynmaically populate every time a new account is created, the account numbers and db information automatically created, never hard-coded):

if($accountid_var =="425786" || $club_id=="425786")//SMARTSPIN -- $club_id variable is passed 
{
                                                                     /*------------------*/

                                                                //CHANGE THE VARIABLES HERE FOR PASSWORD AND DATABASE USER NAME
                                                                $account_pass ="****";
                                                                $account_user ="****";
                                                                $account_mster_db ="****";
                                                                $account_db ="****";
                                                                $club_accountid_raw =""; //club account id needs to be hard coded for auto membership reminder
                                                                $cal_user_name = "****";
                                                                $countries_db ="****";
                                                                $countries_db_user = $cal_user_name;
                                                                //FAQ
                                                                $admin_username ="****";
                                                                $admin_password ="*****";

                                                                /*------------------*/
define("DB_HOST", "localhost");
define("DB_USER", $account_user);
define("DB_PASSWORD", $account_pass);
define("DB_DATABASE", $account_db);
}

elseif
{
//different database info representing each accounts
}

elseif
{
 //different database info representing each accounts
}

Once the account id is matched using the if and else statements, this file determines which database connection to provide my purging script with. It performs the same task for the entire application, thus making it possible for all accounts to run the same application folder and files while using different dbs)

I know this may not be the most effecient way, but this is what I'm trying to achieve. It seems to be working with the scripts structure earlier posted. I'm just not sure if your simplified code would achieve the same outcome.

Your thoughts!

Member Avatar for diafol

I don't think that's too difficult...

function connect($user, $pw)
{
    $link = mysql_connect('localhost', 'root', '');
    if(!$link) {
        return false;
    }
    return $link;
}

function logMe($str)
{
    //for testing - write a proper logging function
    echo $str;  
}

if($link = connect('root','')) //main DB credentials
{
    //change fieldnames/table to the ones you need
    $result = mysql_query("SELECT db_username, db_password, db_name FROM company_info");
    $dbArray = array();
    while($data = mysql_fetch_assoc($result))
    {
        //store connection and DB name details
        $dbArray[] = array($data['db_username'], $data['db_password'], $data['db_name']); 
    }
    mysql_free_result($result);
    mysql_close($link);

    foreach($dbArray as $db){
        if($link = connect($db['db_username'], $db['db_password'])){
            //either do this or use mysql_select_db and drop the variables from the SQL
            if(!mysql_query("DELETE FROM {$db['db_name']}.queue_sys WHERE {$db['db_name']}.queue_sys.todays_date NOT LIKE '%$todayis%'")){
                logMe("Couldn't run delete query on DB {$db['db_name']}");  
            }
            mysql_close($link);
        }
    }
}else{
    logMe("Couldn't connect to the main DB");
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.