SQL Cronjob Question

[ShiBDiB]

I'm trying to figure out the best way to do a cronjob on a mysql table.

I have a column called CharID and I need to SELECT that for each row and use it as a variable in an XML query. The xml query is an API call that I'm using to get an attribute from the CharID. Depending on this attribute I want to update another row in the table.

My issue is I'm not sure how to get it to loop thru every row. I know how to do it for one row on its own but not for all the rows.

I'm most comfortable with PHP so that's preferred but willing to look at other options.

 

[brandonwh64]

Try creating a shell script like the one below:

#!/bin/bash

mysql --user=[username]--password=[password]--database=[db name]--execute="DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) , timestamp ) >=7"

You can then add this to the cron

 

[Disparia]

What does that one row query look like?

 

[W1zzard]

I'm most comfortable with PHP so that's preferred but willing to look at other options.

Write a PHP script that does what you need and then call that from cron (php can run from the command line just fine)

 

[ShiBDiB]

<?php
function makeApiRequest($url) {

    // Initialize a new request for this URL
    $ch = curl_init($url);

    // Set the options for this request
    curl_setopt_array($ch, array(
        CURLOPT_FOLLOWLOCATION => true, // Yes, we want to follow a redirect
        CURLOPT_RETURNTRANSFER => true, // Yes, we want that curl_exec returns the fetched data
        CURLOPT_SSL_VERIFYPEER => false, // Do not verify the SSL certificate
    ));

    // Fetch the data from the URL
    $data = curl_exec($ch);

    // Close the connection
    curl_close($ch);

    // Return a new SimpleXMLElement based upon the received data
    try {
        return new SimpleXMLElement($data);
    }
    // In case of failure, simulate an error document to get a
    // SimpleXMLElement object in any case
    catch (Exception $e) {
        $time = gmdate('Y-m-d H:i:s');
        return new SimpleXMLElement("<?xml version='1.0' encoding='UTF-8'?>
            <eveapi version=\"2\">
             <currentTime>$time</currentTime>
             <error code=\"{$e->getCode()}\">{$e->getMessage()}</error>
             <cachedUntil>$time</cachedUntil>
            </eveapi>");
    }
}

$con=mysqli_connect("localhost","root","redacted","services");

$query = 'SELECT charid FROM members ORDER BY updatedAt DESC;';

$result = mysqli_query($con,$query);

// Corp Check

while( $row = mysqli_fetch_row($result) ) {
 
                                        $url = 'https://api.eveonline.com/eve/CharacterInfo.xml.aspx';
                                        $url .= '?characterID=' . $row[0];
                                     
                                        $xml = makeApiRequest($url);
                                        $corpid = $xml->xpath('//result/corporationID/text()')[0];
                                     
                                        $query2 = "UPDATE members SET corpid = $corpid WHERE charid = $row[0] LIMIT 1";

                                        $result2 = mysqli_query($con,$query2);

  if (!$result2) {
    die('Invalid query: ' . mysqli_error($con));
}

}
?>

works just fine when I call it in a terminal or as a webpage.. the issue is when I put it in crontab -e as..

*/30 * * * * /var/www/auth/includes/cron/accessupdate.php

when it runs (which it does according to the logs) it doesn't do anything.

UPDATE

fixed this, needed to include the path to my php install