Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About hokletrain

  • Rank

Profile Information

  • Gender
    Not Telling
  1. Thanks for all the help again, Req. The issue is that the ID/SerialNumber is dynamic and changes a lot. I looked at your previous answer got kind of confused so just stuck with your first suggestion. Processed the date through PHP rounded to nearest 15 minutes and stored as proper date. Code works almost perfectly with what i have above. Now that the dates are rounded properly , I get a lot of looped results is that because I'm using date = date and so on? Example query: SELECT d.RH,b.AT,c.WSAV,d.date FROM test d, test b, test c WHERE d.RH IN (SELECT RH FROM test WHERE sn = 5165654) AND b.AT IN (SELECT AT FROM test WHERE sn = 5165655) AND c.WSAV IN (SELECT WSAV FROM test WHERE sn = 5165656) AND d.date = b.date AND d.date = c.date ORDER BY d.date; Output: So what i have done is LIMIT by 1, which gives me the result i need. Just curious why it returns so many results?
  2. I'm kind of confused in which part of the query i would apply the date_format part to. Would i select it at the start e.g SELECT d.$sensor,b.$sensor1,c.$sensor2,DATE_FORMAT(SEC_TO_TIME(ROUND(TIME_TO_SEC(d.date) / 900) * 900), "%Y-%m-%d %H:%i") as dd.date? Or could i just use it at the end as a group by DATE_FORMAT(SEC_TO_TIME(ROUND(TIME_TO_SEC(d.date) / 900) * 900), "%Y-%m-%d %H:%i") ? And even then how could i apply this to the other date fields like b.date and c.date? Sorry I'm quite new to this particular MySQL , thanks for all the help Req.
  3. I also still want to keep the original date's which is why i haven't rounded in my insert script. I only want to do this rounding part for calculations
  4. I'm using PHP to insert JSON data into the MySQL table, which gives me a date I'm just using the varchar to store it in the PHP formatted datetime. I can still use every MySQL date function with the varchar though aha? I pretty much just want to get records of data that are inside the period of 15 minutes, hence why I'm trying to round it. In my case could i use FLOOR? e.g SEC_TO_TIME(FLOOR((TIME_TO_SEC(CURTIME())+450)/900)*900).
  5. d.date is a varchar , which has been formatted!
  6. Hey guys, the data in my database gets inputted at dates within the hour, I'm trying to round each date to the nearest 15 minute interval. I have tried to use SEC_TO_TIME but wasn't working. I'm trying to calculate data for every 15 minutes. Example dates: 2017-09-05 09:01 , 2017-09-05 09:05 , 2017-09-05 09:17 , 2017-09-05 09:24 First two dates should 'round' to 9:00 and the second set should 'round' to 9:30. Current Query: "SELECT d.$sensor,b.$sensor1,c.$sensor2,d.date FROM test d, test b, test c WHERE d.$sensor IN (SELECT $sensor FROM test WHERE sn = $rtu) AND b.$sensor1 IN (SELECT $sensor1 FROM test WHERE sn = $rtu1) AND c.$sensor2 IN (SELECT $sensor2 FROM test WHERE sn = $rtu2) AND d.date = b.date AND d.date = c.date order by d.date" Any help would be great , thanks.
  7. The screenshot is just a snippet from PHPmyAdmin. Could i use the same kind of query loops that i have but nest the query loops like such: while($row = mysqli_fetch_array($query1)) { $x = $row['sensor']; while($row = mysqli_fetch_array($query2)) { $y = $row['sensor1']; while($row = mysqli_fetch_array($query3)) { $z = $row['sensor2']; $step1 = str_replace("x",$x,$formula); $step2 = str_replace("y",$y,$step1); $step3 = str_replace("z",$z,$step2); eval('$output = ('. $step3 .');'); var_dump($output); } } } The guy I'm working for wants the script to be easily changed via front end , e.g why i did the calc table in the first place. So anything that needs to be changed/added can be done so in the CRUD table. I am relatively new to this kind of work , so got chucked in the deep end.
  8. Thanks for the responses guys. Sorry Barand i can attach some data if it makes it easier. I would probably prefer using SQL to get the data as a whole anyway. I do like the idea of completing the process in MySQL. If i used joins inside of one query and then looped results could i still process this similar to above -e.g $x = $row['sensor]; . Also the sensors and unit_id's can change completely so the above MySQL statements only really works for the three sensors RH, AT and AVGWN? This is the major reason i use the calculation table , because of how much information can vary.
  9. Hey guys, i have been working on a script for a little while now and just can't seem to find a solution.The issue I'm having is that i can't seem to loop through multiple tables using a primary tables information. In the bottom case i have two records with the same title 'fire_index' which has the same formula, the only thing that differs is the sensors and unit_id's have changed. Breakdown of steps: 1.) Automatically loop through calculation table retrieve all records with desired title e.g 2 records , fire_index 2.) Use the information retrieved from the calculation table to build query's for getting data 3.) Loop through data queries to get results , apply results to formula (string replace x,y,z) and output to output table Firstly i have looped through the calculation table which contains pre-made formulas, types , company names, unit id's, sensors. The user chooses the sensor and the unit they want to apply the formula to. The script is based on the formula type e.g fire index. $query = mysqli_query($mysqli, "SELECT * FROM calculation where title = 'fire_index'"); while($row = mysqli_fetch_array($query)) { $title = $row['title']; // fire_index $time = $row['time']; // 2017-12-3 $formula = $row['formula']; // e.g 10^(0.009254 + (0.01201*x) + (0.2789*(SQRT(y)) - 0.09577*(SQRT(z)))) $unit= $row['unit_id']; //23223 $sensor = $row['sensor']; // AT - Air Temp $unit1 = $row['unit1_id']; //123424 $sensor1 = $row['sensor1']; //RH - Relative Humidity $unit2 = $row['unit2_id']; //123525 $sensor2 = $row['sensor2']; //AVGWN - Avg Winspeed $unit3 = $row['unit3_id']; // Etc.. $sensor3 = $row['sensor3']; //Etc... $unit4 = $row['unit4_id']; //Etc.. $sensor4 = $row['sensor4']; //Etc.. } The next query's rely on the calculation table to get the correct columns : $query1 = mysqli_query($mysqli,"SELECT $sensor as sensor from data where unit_id = '$unit'"); while($row = mysqli_fetch_array($query1)) { $x = $row['sensor']; /// GETS data from AT column } $query2 = mysqli_query($mysqli,"SELECT $sensor1 as sensor1 from test where unit_id = '$unit1'"); while($row = mysqli_fetch_array($query2)) { $y = $row['sensor1']; /// GETS data from RH column } $query3 = mysqli_query($mysqli,"SELECT $sensor2 as sensor2 from test where unit_id = '$unit2'"); while($row = mysqli_fetch_array($query3)) { $z = $row['sensor2']; /// GETS data from WNSAVG column } I have successfully made the script to work with looping individual units using one unit_id, but when it comes to multiple it doesn't work. Example output variable storing the finalised formula inside the end of the loop: $step1 = str_replace("x",$x,$formula); $step2 = str_replace("y",$y,$step1); $step3 = str_replace("z",$z,$step2); eval('$output = ('. $step3 .');'); After the task i need to insert the output into the output table. FULL CODE EXAMPLE : Here MySQL table Example: Here Thanks, any help at all would be amazing.
  10. The only issue with using an array location is that the the name changes so there can be multiple underlined parts before it reaches the serial number e.g Test_camera_DEMO_782300. I have been working on a different way which essentially works with one file at time operated by a CRON job ? In the front end when a sensor unit is made a folder is also created with the serial number. I'm getting the latest image chucking it into an array, then trying to match the serial number contained in the image file name with the folder name? If matched copy image to folder? My Current Code Which doesn't work properly: <?php $imagesDir = '/Pictures/irrigation/'; $images = glob($imagesDir . '*.{jpg,jpeg,png,gif}', GLOB_BRACE); array_multisort(array_map('filemtime', $images), SORT_NUMERIC, SORT_DESC, $images); $latestimage = $images[0]; print($latestimage); $path = 'images'; // '.' for current foreach (new DirectoryIterator($path) as $file) { if ($file->isDot()) continue; if ($file->isDir()) { $folder = $file->getFilename(); } } $pos = strpos($folder, $latestimage); $result = substr($folder, $pos); print($result); $file = basename($latestimage); $dest = "images/$result/$file"; if (copy($latestimage, $dest)){ echo '<strong>copied!!</strong>'; } else { echo "<strong>didn't copy <strong>"; } ?>
  11. I think i am too, i tried this piece I'm getting an error :
  12. Hey guys , I'm quite new to PHP and have been working on a piece of code for a while now and just can't seem to find a solution. I'm trying to make a script which copy's images from a folder to another folder on the web server. The folder names on the web server will be the serial number in the image name. So basically i want to get the serial number from the image name, create folder with 'serial number' as the name if folder exists don't make a new folder. And then copy the image to that folder? I can find loads on filtering arrays but nothing on working with the array elements. Is there a way to read the element and get INT that's length is 7? Any help or tips would be greatly appreciated The middle part of the filename "5165266" is the serial number which changes a lot. Each serial number represents a camera unit that automatically takes pictures. Output: Array ( [0] => Thumbs.db [1] => YDOC_CAMERA DEMO_5165266_170317_120008.004.jpg [2] => YDOC_CAMERA DEMO_5165266_170317_130006.005.jpg [3] => YDOC_CAMERA DEMO_5165266_170317_140002.006.jpg ) Reading Directory image names into array: $dir = "/Documents/PICTURECOLLECTOR/"; if (is_dir($dir)) { if ($dh = opendir($dir)) { $images = array(); while (($file = readdir($dh)) !== false) { if (!is_dir($dir.$file)) { $images[] = $file; } } closedir($dh); } } Copying images code: $srcPath = '/Documents/PICTURECOLLECTOR/'; $destPath = '/website/pictures/'; $srcDir = opendir($srcPath); while($readFile = readdir($srcDir)) { if($readFile != '.' && $readFile != '..') { if (!file_exists($destPath . $readFile)) { if(copy($srcPath . $readFile, $destPath . $readFile)) { echo "Copy file"; } else { echo "Canot Copy file"; } } } } closedir($srcDir); P.S Sorry if the title is misleading forgot to change it...
  13. Okay i'm gonna give that a crack. Is it bad to execute so many query's separately? Once again thanks for all the help Sep.
  14. Thanks for clearing up so much guys! Sepodati thanks for adding some code as well. As far as inserting each value into the database tables, whats the best way to go about that?
  15. Yes i gathered the $data['data']['RH'] part of the code isn't right , hence why i posted it just to see if i can find the proper way of doing it? And oh okay the fourth element, can you please explain that part? I haven't used JSON with PHP before so i'm quite new to how some of the code works. And yes that's correct sometimes there will be more of those: { "$ts": 170801170000, "RH": 67.15, "AT": 12.87, "MINVi": 3.81, "PTi": 23.4, "LWS": "0*T", "WSAV": 0, "WSMX": 0, "WSMN": 0, "PR_TOT": 156, "RAIN": 0, "FDI": 0.239, "DT": 2.881, "WD": "0*T", "P1": "0*T", "AVGCi": 175 } If the file contained more sets it would look like this : {"$ts":170725171400,"RH":94.43,"AT":11.418,"MINVi":3.74,"PTi":21,"LWS":"0*T","WSAV":3.73,"WSMX":5.74,"WSMN":2.02,"PR_TOT":132.8,"RAIN":0,"FDI":0.661,"DT":-0.039,"WD":"0*T","P1":"0*T","AVGCi":162}, {"$ts":170725173000,"$msg":"WDT;WV01"}, {"$ts":170725173000,"$msg":"WDT;SDI12"}, {"$ts":170725173000,"$msg":"WDT;LWS"}, {"$ts":170725173000,"RH":94.4,"AT":10.935,"MINVi":3.75,"PTi":20.2,"LWS":"0*T","WSAV":2,"WSMX":2.45,"WSMN":0,"PR_TOT":132.8,"RAIN":0,"FDI":0.449,"DT":-0.007,"WD":"0*T","P1":"0*T","AVGCi":167}, {"$ts":170725174400,"$msg":"WDT;LWS"}, {"$ts":170725174400,"$msg":"WDT;WV01"}, {"$ts":170725174400,"$msg":"WDT;SDI12"}, {"$ts":170725174400,"RH":94,"AT":10.681,"MINVi":3.74,"PTi":19.6,"LWS":"0*T","WSAV":1.93,"WSMX":2.51,"WSMN":0,"PR_TOT":132.8,"RAIN":0,"FDI":0.44,"DT":0.046,"WD":"0*T","P1":"0*T","AVGCi":168}, {"$ts":170725180000,"$msg":"WDT;WV01"}, {"$ts":170725180000,"SDB":"0*I"},
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.