mashtullah's solution to "How to pick values from an array after a set number of records repeatedly"

I have created a sample table for testing your solution.
\nRun the sql below to create and populate the table in your mysql database.<\/p>\n

DROP<\/span> <\/span>TABLE<\/span> <\/span>IF<\/span> <\/span>EXISTS<\/span> <\/span>iotdata<\/span>;<\/span>\n\nCREATE<\/span> <\/span>TABLE<\/span> <\/span>iotdata<\/span> <\/span>(<\/span>\n  <\/span>id<\/span> <\/span>INT<\/span>(<\/span>11<\/span>)<\/span> <\/span>NOT<\/span> <\/span>NULL<\/span> <\/span>AUTO_INCREMENT<\/span>,<\/span>\n <\/span>label<\/span> <\/span>VARCHAR<\/span>(<\/span>69<\/span>)<\/span> <\/span>DEFAULT<\/span> <\/span>NULL<\/span>,<\/span>\n <\/span>jina<\/span> <\/span>VARCHAR<\/span>(<\/span>69<\/span>)<\/span> <\/span>DEFAULT<\/span> <\/span>NULL<\/span>,<\/span>\n <\/span>addedtime<\/span> <\/span>DATETIME<\/span> <\/span>NOT<\/span> <\/span>NULL<\/span> <\/span>DEFAULT<\/span> <\/span>CURRENT_TIMESTAMP<\/span>,<\/span>\nPRIMARY<\/span> <\/span>KEY<\/span> <\/span>(<\/span>id<\/span>)<\/span>\n)<\/span> <\/span>ENGINE<\/span>=<\/span>INNODB<\/span>;<\/span>\n\nINSERT<\/span>  <\/span>INTO<\/span> <\/span>iotdata<\/span>(<\/span>id<\/span>,<\/span>label<\/span>,<\/span>jina<\/span>,<\/span>addedtime<\/span>)<\/span> <\/span>VALUES<\/span> <\/span>(<\/span>1<\/span>,<\/span>'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');`<\/span>\n<\/pre><\/div>\n

You will use the function below called getdata($duration)<\/strong> having duration as the parameter.<\/p>\n

function<\/span> <\/span>getdata<\/span>(<\/span>$<\/span>duration<\/span>)<\/span>\n{<\/span>\n\ntry<\/span> <\/span>{<\/span>\n    <\/span>$<\/span>pdo<\/span> <\/span>=<\/span> <\/span>new<\/span> <\/span>PDO<\/span>(<\/span>'mysql:host=localhost;dbname=testdb', 'mtuwatest', '1234');<\/span>\n\n}<\/span> <\/span>catch<\/span>(<\/span>PDOException<\/span> <\/span>$<\/span>err<\/span>)<\/span> <\/span>{<\/span>\n    <\/span>die<\/span>(<\/span>$<\/span>err<\/span>-><\/span>getMessage<\/span>());<\/span>\n}<\/span>\nswitch<\/span> <\/span>(<\/span>$<\/span>duration<\/span>)<\/span>\n{<\/span>\n    <\/span>case<\/span> <\/span>'hour':<\/span>\n        <\/span>$<\/span>sql<\/span>=&<\/span>quot<\/span>;<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>iotdata<\/span> <\/span>WHERE<\/span> <\/span>HOUR<\/span>(<\/span>addedtime<\/span>)<\/span>=<\/span>HOUR<\/span>(<\/span>NOW<\/span>())<\/span>&<\/span>quot<\/span>;;<\/span>\n        <\/span>break<\/span>;<\/span>\n    <\/span>case<\/span> <\/span>'day':<\/span>\n        <\/span>$<\/span>msg<\/span>=&<\/span>quot<\/span>;<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>iotdata<\/span> <\/span>WHERE<\/span> <\/span>DATE<\/span>(<\/span>addedtime<\/span>)<\/span>=<\/span>DATE<\/span>(<\/span>NOW<\/span>())<\/span> <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>HOUR<\/span>(<\/span>addedtime<\/span>)<\/span>&<\/span>quot<\/span>;;<\/span>\n        <\/span>break<\/span>;<\/span>\n    <\/span>case<\/span> <\/span>'month':<\/span>\n        <\/span>$<\/span>msg<\/span>=&<\/span>quot<\/span>;<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>iotdata<\/span> <\/span>WHERE<\/span> <\/span>MONTH<\/span>(<\/span>addedtime<\/span>)<\/span>=<\/span>MONTH<\/span>(<\/span>NOW<\/span>())<\/span> <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>DATE<\/span>(<\/span>addedtime<\/span>)<\/span>&<\/span>quot<\/span>;;<\/span>\n        <\/span>break<\/span>;<\/span>      <\/span>\n    <\/span>default:<\/span>\n        <\/span>$<\/span>msg<\/span>=&<\/span>quot<\/span>;<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>iotdata<\/span> <\/span>WHERE<\/span> <\/span>addedtime<\/span>=<\/span>1<\/span>&<\/span>quot<\/span>;;<\/span>\n}<\/span>\n$<\/span>stmt<\/span> <\/span>=<\/span> <\/span>$<\/span>pdo<\/span>-><\/span>prepare<\/span>(<\/span>&<\/span>quot<\/span>;<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>iotdata<\/span> <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>HOUR<\/span>(<\/span>addedtime<\/span>)<\/span>&<\/span>quot<\/span>;);<\/span>\n$<\/span>result<\/span> <\/span>=<\/span> <\/span>$<\/span>stmt<\/span>-><\/span>execute<\/span>();<\/span>\nprint_r<\/span>(<\/span>$<\/span>stmt<\/span>-><\/span>fetchAll<\/span>(<\/span>PDO:<\/span>:<\/span>FETCH_ASSOC<\/span>));<\/span>\necho&quot<\/span>;<\/span><<\/span>p<\/span>>&<\/span>quot<\/span>;;<\/span>\n}<\/span>\n<\/pre><\/div>\n

you can use the function like this..<\/p>\n\n

echo &quot;Using The monthly duration...<p>&quot;;<\/code><\/p>\n\n

getdata(&quot;month&quot;);<\/code><\/p>\n

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': $msgsql="SELECT * FROM iotdata WHERE DATE(addedtime)=DATE(NOW()) GROUP BY HOUR(addedtime)"; break; case 'month': $msgsql="SELECT * FROM iotdata WHERE MONTH(addedtime)=MONTH(NOW()) GROUP BY DATE(addedtime)"; break; default: $msgsql="SELECT * FROM iotdata WHERE addedtime=1"; } $stmt = $pdo->prepare("SELECT * FROM iotdata GROUP BY HOUR(addedtime)"); $result = $stmt->execute(); print_r($sql); $result = $stmt->execute(); print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); echo"

"; } you can use the function like this.. `echo "Using The monthly duration...

";` `getdata("month");`

User: mashtullah

Question: How to pick values from an array after a set number of records repeatedly

Back to question