Simple PHP & SQL weight tracker for multiple persons
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

weight.php 6.3KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251
  1. <?php
  2. /*
  3. * When calleld as is, return JSON object like this:
  4. * {
  5. * "users": [
  6. * {
  7. * "name": "User 1",
  8. * "id": 0,
  9. * "data": [
  10. * {
  11. * "x": "2018-10-29 20:30",
  12. * "y": "110.0"
  13. * },
  14. * {
  15. * "x": "2018-10-30 20:30",
  16. * "y": "110.5"
  17. * }
  18. * ]
  19. * },
  20. * {
  21. * "name": "User 2",
  22. * "id": 1,
  23. * "data": [
  24. * {
  25. * "x": "2018-10-29 20:30",
  26. * "y": "105.5"
  27. * },
  28. * {
  29. * "x": "2018-10-30 20:30",
  30. * "y": "105.0"
  31. * }
  32. * ]
  33. * }
  34. * ]
  35. * }
  36. * or
  37. * {
  38. * "error": "No configuration found"
  39. * }
  40. *
  41. * When called with POST parameters, add user or data-point to db
  42. * parameters: username, (date, time, weight)
  43. */
  44. function print_json_error($s) {
  45. echo '{';
  46. echo ' "error": "' . $s;
  47. echo '"';
  48. echo '}';
  49. }
  50. function print_html($s) {
  51. echo '<html lang="en">';
  52. echo ' <head>';
  53. echo ' <meta charset="utf-8" />';
  54. echo ' <title>Weight-Track DB</title>';
  55. echo ' </head>';
  56. echo ' <body>';
  57. echo ' <p>' . $s . '</p>';
  58. echo ' <a href="index.html">Back to Main-Page...</a>';
  59. echo ' </body>';
  60. echo '</html>';
  61. }
  62. function print_error($s) {
  63. if (isset($_POST['username'])) {
  64. return print_html($s);
  65. } else {
  66. return print_json_error($s);
  67. }
  68. }
  69. include('config.php');
  70. if ((!isset($sql_host))
  71. || (!isset($sql_port))
  72. || (!isset($sql_username))
  73. || (!isset($sql_password))
  74. || (!isset($sql_database))) {
  75. print_error('Configuration Error');
  76. exit(1);
  77. }
  78. $conn_string = 'host=' . $sql_host . ' port=' . $sql_port . ' dbname=' . $sql_database;
  79. $conn_string = $conn_string . ' user=' . $sql_username . ' password=' . $sql_password;
  80. $db = pg_connect($conn_string);
  81. if (!$db) {
  82. print_error('Database Error: ' . pg_last_error($db));
  83. exit(1);
  84. }
  85. $sql = 'CREATE TABLE IF NOT EXISTS weight_users (';
  86. $sql .= 'id SERIAL,';
  87. $sql .= 'name varchar';
  88. $sql .= ');';
  89. $result = pg_query($db, $sql);
  90. if (!$result) {
  91. print_error('Error (re-) creating database table for users: ' . pg_result_error($result));
  92. exit(1);
  93. }
  94. $sql = 'CREATE TABLE IF NOT EXISTS weight_data (';
  95. $sql .= 'id int NOT NULL,';
  96. $sql .= 'date TIMESTAMP,';
  97. $sql .= 'weight DECIMAL(5,2)';
  98. $sql .= ');';
  99. $result = pg_query($db, $sql);
  100. if (!$result) {
  101. print_error('Error (re-) creating database table for weights: ' . pg_result_error($result));
  102. exit(1);
  103. }
  104. if (isset($_POST['username'])
  105. && isset($_POST['date'])
  106. && isset($_POST['time'])
  107. && isset($_POST['weight'])) {
  108. $sql = 'INSERT INTO weight_data(id, date, weight) VALUES (\'';
  109. $sql .= pg_escape_string(str_replace('user_', '', $_POST['username']));
  110. $sql .= '\', ';
  111. $datetime = $_POST['date'] . $_POST['time'];
  112. $timestamp = strtotime($datetime);
  113. if (($timestamp == FALSE) || ($timestamp == -1)) {
  114. print_error('Error interpreting DateTime: "' . $datetime . '"');
  115. exit(1);
  116. }
  117. $mysqltime = date("Y-m-d H:i:s", $timestamp);
  118. $sql .= '\'' . $mysqltime . '\', \'';
  119. $sql .= pg_escape_string($_POST['weight']) . '\')';
  120. $result = pg_query($db, $sql);
  121. if (!$result) {
  122. print_error('Error adding new data for user "' . $_POST['username'] . '" to DB: ' . $pg_result_error($result));
  123. } else {
  124. print_error('Added new data for user "' . $_POST['username'] . '" to DB!');
  125. }
  126. } else if (isset($_POST['username'])) {
  127. $sql = 'INSERT INTO weight_users(name) VALUES (\'';
  128. $sql .= pg_escape_string($_POST['username']);
  129. $sql .= '\')';
  130. $result = pg_query($db, $sql);
  131. if (!$result) {
  132. print_error('Error adding new user "' . $_POST['username'] . '" to DB: ' . pg_result_error($result));
  133. } else {
  134. print_error('Added new user "' . $_POST['username'] . '" to DB!');
  135. }
  136. } else if (isset($_GET['debug'])) {
  137. echo <<<EOF
  138. {
  139. "users": [
  140. {
  141. "name": "User 1",
  142. "id": 0,
  143. "data": [
  144. {
  145. "x": "2018-10-27 20:30",
  146. "y": "110.0"
  147. },
  148. {
  149. "x": "2018-10-28 20:30",
  150. "y": "110.7"
  151. },
  152. {
  153. "x": "2018-10-29 20:30",
  154. "y": "110.2"
  155. },
  156. {
  157. "x": "2018-10-30 20:30",
  158. "y": "111.8"
  159. }
  160. ]
  161. },
  162. {
  163. "name": "User 2",
  164. "id": 1,
  165. "data": [
  166. {
  167. "x": "2018-10-27 20:30",
  168. "y": "103.5"
  169. },
  170. {
  171. "x": "2018-10-28 20:30",
  172. "y": "105.0"
  173. },
  174. {
  175. "x": "2018-10-29 20:30",
  176. "y": "105.5"
  177. },
  178. {
  179. "x": "2018-10-30 20:30",
  180. "y": "104.0"
  181. }
  182. ]
  183. }
  184. ]
  185. }
  186. EOF;
  187. } else {
  188. $sql = 'SELECT id, name FROM weight_users ORDER BY id ASC';
  189. $result = pg_query($db, $sql);
  190. if (!$result) {
  191. print_error('Error fetching users from database: ' . pg_result_error($result));
  192. exit(1);
  193. }
  194. $data = array();
  195. while ($row = pg_fetch_assoc($result)) {
  196. $sql2 = 'SELECT date, weight FROM weight_data ';
  197. $sql2 .= 'WHERE id = \'' . $row['id'] . '\' ORDER BY date ASC';
  198. $result2 = pg_query($db, $sql2);
  199. if (!$result2) {
  200. print_error('Error fetching data for user ' . $row['id'] . ' "' . $row['name'] . '": ' . pg_result_error($result2));
  201. exit(1);
  202. }
  203. $cur = array();
  204. $cur['name'] = $row['name'];
  205. $cur['id'] = $row['id'];
  206. $cur['data'] = array();
  207. while ($row2 = pg_fetch_assoc($result2)) {
  208. $elem = array();
  209. $elem['date'] = $row2['date'];
  210. $elem['weight'] = $row2['weight'];
  211. $cur['data'][] = $elem;
  212. }
  213. $data[] = $cur;
  214. }
  215. echo '{"users": [';
  216. foreach ($data as $data_key => $data_value) {
  217. if ($data_key > 0) {
  218. echo ',';
  219. }
  220. echo '{';
  221. echo '"name": "' . $data_value['name'] . '",';
  222. echo '"id": ' . $data_value['id'] . ',';
  223. echo '"data": [';
  224. foreach ($data_value['data'] as $row_key => $row_value) {
  225. if ($row_key > 0) {
  226. echo ',';
  227. }
  228. echo '{';
  229. echo '"x": "' . $row_value['date'] . '",';
  230. echo '"y": "' . $row_value['weight'] . '"';
  231. echo '}';
  232. }
  233. echo ']}';
  234. }
  235. echo ']}';
  236. }
  237. ?>