How to pick values from an array after a set number of records repeatedly
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Hi friends

I need help to come up with a PHP function to pick specific values from a dataset at set intervals for purposes of graphing. Here's the scenario:

  • Every 6 minutes, data is inserted into my MySQL table with a timestamp from an Iot device.
  • I need help designing a simple function that can pick not randomly, but after set different intervals, e.g. Say If the input duration is an hour, pick a sample record inserted at minute 1, record inserted at minute 6, record 12, record 24, record 30, etc If the input duration is a day, only pick 1 sample entry every hour If the input is a month, only pick 1 sample entry every day

I've tried studying array_walk, callbacks, etc I can't seem to see how to do it...

Help a brother, thanks

@jobokoth if the duration is an hour or day, should we select the records of the current date only? for month, should we select records for the current month only?
mashtullah 11 months ago

Crowdsource coding tasks.

2 Solutions


I have created a sample table for testing your solution.
Run the sql below to create and populate the table in your mysql database.

DROP TABLE IF EXISTS iotdata;

CREATE TABLE iotdata (
  id INT(11) NOT NULL AUTO_INCREMENT,
 label VARCHAR(69) DEFAULT NULL,
 jina VARCHAR(69) DEFAULT NULL,
 addedtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=INNODB;

INSERT  INTO iotdata(id,label,jina,addedtime) VALUES (1,'test1','test1','2018-01-10 07:01:32'),(2,'test2','test2','2018-01-10 07:07:32'),(3,'test3','test3','2018-04-09 08:00:19'),(4,'test4','test4','2018-04-08 09:00:19'),(5,'test6','test6','2018-04-10 08:07:16'),(6,'test7','test7','2018-04-10 08:13:16'),(7,'test8','TEST8','2018-04-10 08:19:16');`

You will use the function below called getdata($duration) having duration as the parameter.

function getdata($duration)
{

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'mtuwatest', '1234');

} catch(PDOException $err) {
    die($err->getMessage());
}
switch ($duration)
{
    case 'hour':
        $sql="SELECT * FROM iotdata WHERE HOUR(addedtime)=HOUR(NOW())";
        break;
    case 'day':
        $sql="SELECT * FROM iotdata WHERE DATE(addedtime)=DATE(NOW()) GROUP BY HOUR(addedtime)";
        break;
    case 'month':
        $sql="SELECT * FROM iotdata WHERE MONTH(addedtime)=MONTH(NOW()) GROUP BY DATE(addedtime)";
        break;      
    default:
        $sql="SELECT * FROM iotdata WHERE addedtime=1";
}
$stmt = $pdo->prepare($sql);
$result = $stmt->execute();
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
echo"<p>";
}

you can use the function like this..

echo "Using The monthly duration...<p>";

getdata("month");


This is a sample scenario. I have not used array_walk or callback. I hope this works

<?php

$driver = 'mysql';

$database = "dbname=CODINGGROUND";

$dsn = "$driver:host=localhost;$database";

$username = 'root';

$password = 'root';

try {

$conn = new PDO($dsn, $username, $password);

echo "Database CODINGGROUND Connected\n";

}catch(PDOException $e){

echo $e->getMessage();

}

$input_duration = "hour";

//$input_duration = "day";

//$input_duration = "month";

$mysqli = new mysqli("localhost", "root", "root", "CODINGGROUND");

/* check connection */

if (mysqli_connect_errno()) {

printf("Connect failed: %s\n", mysqli_connect_error());

exit();

}

$mysqli->query("DROP TABLE TestTable");

$mysqli->query("DROP TABLE AddTable");

$mysqli->query("CREATE TABLE TestTable (Id varchar(20) Primary Key, Name varchar(20), Adddatetime Datetime);");

if ($mysqli->error)

printf( "createtable failed: %s\n", $mysqli->error);

$mysqli->query("CREATE TABLE AddTable (Id varchar(20) Primary Key, Name varchar(20), Adddatetime Datetime);");

if ($mysqli->error)
printf( "createtable failed: %s\n", $mysqli->error);

/* Prepare an insert statement */

$query = "INSERT INTO TestTable (Id, Name, Adddatetime) VALUES (?,?,?)";

$stmt = $mysqli->prepare($query);

$stmt->bind_param("sss", $val1, $val2, $val3);

$val1 = '01';
$val2 = 'Test1';
$val3 = '2017-01-01 00:00:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '02';
$val2 = 'Test2';
$val3 = '2017-01-01 00:01:0';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '03';
$val2 = 'Test3';
$val3 = '2017-01-01 01:07:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '04';
$val2 = 'Test4';
$val3 = '2017-01-01 02:13:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '05';
$val2 = 'Test5';
$val3 = '2017-01-01 03:00:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '06';
$val2 = 'Test6';
$val3 = '2017-01-02 00:00:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '07';
$val2 = 'Test7';
$val3 = '2017-01-02 00:00:01';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '08';
$val2 = 'Test8';
$val3 = '2017-01-03 00:00:00';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

$val1 = '09';
$val2 = 'Test9';
$val3 = '2017-01-03 00:00:01';

/* Execute the statement */

$stmt->execute();

if ($mysqli->error)

printf( "Insert failed: %s\n", $mysqli->error);

/* close statement */

$stmt->close();

// retrive all rows first

echo "all rows to begin with \n";

$query = "SELECT Id, Name, Adddatetime FROM TestTable";

if ($result = $mysqli->query($query)) {

while ($row = $result->fetch_row()) {

    printf("%s (%s,%s)\n", $row[0], $row[1], $row[2]);

}

}

else

printf( "select all rows failed: %s\n %s", $mysqli->error, $query);

/* retrieve rows from TestTable */

echo "\n retrive the result set\n";

// Build query

if ($input_duration == "hour")

{

 $query = "SELECT Id, Name, Adddatetime FROM TestTable WHERE DATE(Adddatetime) = '2017-01-01' and MOD(TIME_TO_SEC(TIME(Adddatetime)) , 360) = 60;";

}

else if ($input_duration == "day")

{

$query = "SELECT Id, Name, Adddatetime FROM TestTable WHERE DATE(Adddatetime) = '2017-01-01' AND MINUTE(TIME(Adddatetime)) = 0 AND SECOND(TIME(Adddatetime)) = 0;"; 

}

else if ($input_duration == "month")

{

$query = "SELECT Id, Name, Adddatetime FROM TestTable WHERE DATE_FORMAT(Adddatetime, '%Y-%m') = '2017-01' and TIME_TO_SEC(TIME(Adddatetime) = '00');";

}

// if result successful add date to second table

if ($result = $mysqli->query($query)) {

$query2 = "INSERT INTO AddTable (Id, Name, Adddatetime) VALUES (?,?,?)";

$stmt2 = $mysqli->prepare($query2);


$stmt2->bind_param("sss", $val1, $val2, $val3);



while ($row = $result->fetch_row()) {


    $val1 = $row[0];

        $val2 = $row[1];

        $val3 = $row[2];



        $stmt2->execute();


       if ($mysqli->error)

            printf( "Insert to add table failed: %s\n", $mysqli->error);

else

            printf("%s (%s,%s)\n", $row[0], $row[1], $row[2]);


}

}
else

printf( "select result failed: %s\n %s", $mysqli->error, $query);

/* free result set */

$result->close();

//retieve rows from Addtable

echo "\n result from add table\n";

$query = "SELECT Id, Name, Adddatetime FROM AddTable";

if ($result = $mysqli->query($query)) {

while ($row = $result->fetch_row()) {

    printf("%s (%s,%s)\n", $row[0], $row[1], $row[2]);

}

}
else

printf( "select add rows failed: %s\n %s", $mysqli->error, $query);

/* remove table */

$mysqli->query("DROP TABLE TestTable");

$mysqli->query("DROP TABLE AddTable");

/* close connection */

$mysqli->close();

?>

I have update the hour query to start at minute 1 and and pick at every 6 minute interval
Nagarathna Mandagere 11 months ago
View Timeline