Barand
-
Posts
24,566 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
As it's a query() and not a prepare() the code is the same. Just create a mysqli connection instead.
-
try <?php require 'db_inc.php'; $db = pdoConnect('test'); $res = $db->query("SELECT oracleid , name , des , clockingindate as clockin , clockingoutdate as clockout , timediff(clockingoutdate, clockingindate) as duration , total FROM attendance_record JOIN ( SELECT oracleid , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total FROM attendance_record GROUP BY oracleid ) tots USING (oracleid) ORDER BY oracleid, clockingindate "); ?> <html> <body> <table border='1' style='border-collapse:collapse; width: 700px;'> <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr> <?php $previd = 0; foreach ($res as $row) { if ($row['oracleid'] != $previd) { // id changed so output total if ($previd != 0) { echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; } $previd = $row['oracleid']; } echo "<tr><td>{$row['oracleid']}</td> <td>{$row['name']}</td> <td>{$row['des']}</td> <td>{$row['clockin']}</td> <td>{$row['clockout']}</td> <td>{$row['duration']}</td> </tr> "; $total = $row['total']; } echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; // dont forget total for last id ?> </table> </body> </html>
-
mysql> SELECT login_time, logout_time FROM login; +---------------------+---------------------+ | login_time | logout_time | +---------------------+---------------------+ | 2020-03-09 09:15:00 | 2020-03-09 15:00:00 | | 2020-03-10 09:30:00 | 2020-03-10 16:00:00 | | 2020-03-11 09:00:00 | 2020-03-11 13:30:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT sec_to_time(SUM(timestampdiff(SECOND, login_time, logout_time))) as total -> FROM login; +----------+ | total | +----------+ | 16:45:00 | +----------+
-
Sounds like you are comparing datetimes against date only values, in which case you need to compare just the date portions of the datetime values ... WHERE DATE(ClockingInDate) <= '$edate1' AND DATE(ClockingOutDate) >= '$sdate1'
-
String literals need to be in single quotes ... WHERE `district`= '{$_SESSION['g_district']}' ^ ^
-
Better still, use prepared statements and don't embed values in the query at all.
-
Why are you even attempting to store that duration. You can get it any time you need it with a query. Rule of DB design - don't store derived data. If you really insist on storing it, why do need two queries? UPDATE attendance_records SET duration = timediff(...) WHERE ... - a single update would do the job
-
mysql> select login_time -> , logout_time -> , timediff(logout_time, login_time) as diff -> FROM login; +---------------------+---------------------+----------+ | login_time | logout_time | diff | +---------------------+---------------------+----------+ | 2020-03-12 12:30:00 | 2020-03-13 15:02:30 | 26:32:30 | +---------------------+---------------------+----------+
-
Does it accept "\t"?
-
Welcome.
-
$date = 'Sun, Feb 9<br />3:00 PM ET'; $date = str_replace('<br />', ' ', $date); $dtobj = DateTime::createFromFormat('D, M j g:i A *', $date); echo $dtobj->format('Y-m-d H:i:s'); // 2020-02-09 15:00:00
-
By far the fastest way is to use the SQL statement LOAD DATA INFILE Specify FIELDS TERMINATED BY '\t'
-
SELECT whatever FROM mytable WHERE DATE(timestamp) = CURDATE()
-
What does this SQL query output? DESCRIBE staff;
-
Why are you doing a select query then checking the date and updating if not today. All you need is single update similar to this UPDATE staff SET clockedIn = 0, clockedOut = 0 WHERE indate <> CURDATE()
-
If you don't use table3 you need to remove these bits... The resulting query gives +---------------+--------------------------+ | payment_total | items | +---------------+--------------------------+ | 88 | RH Beer | | 125 | RH Beer, Nokia3210 | | 115 | 22Wine, IPhone4, RH Beer | +---------------+--------------------------+ Of course you can't tell to whom each set of items belong as the name came from table3
-
"distinct" is a keyword, not a function. You cannot apply it to a single column.
-
What you mean is "will I do all your thinking for you?"
-
I posted it an hour ago
-
Remove references to table3 from the query.
-
Tell the creator to Google "database normalization" and try an couple of tutorials. If you use a subquery which effectively puts table3 in the normalized form it should've had, which is Table3 +------+------------+ | name | payment_id | +------+------------+ | Lim | 11 | | Jhon | 2 | | Leo | 1 | +------+------------+ then it works. Without the subquery it doubles or trebles the totals and item lists. SELECT SUM(payment) as payment_total , name , GROUP_CONCAT(item_name SEPARATOR ', ') as items FROM table1 JOIN table2 USING (item_id) JOIN ( SELECT DISTINCT name , payment_id FROM table3 ) t3 USING (payment_id) GROUP BY payment_id; +---------------+------+--------------------------+ | payment_total | name | items | +---------------+------+--------------------------+ | 88 | Leo | RH Beer | | 125 | Jhon | Nokia3210, RH Beer | | 115 | Lim | 22Wine, RH Beer, IPhone4 | +---------------+------+--------------------------+
-
The table1 - table2 relationship is fine but each row in table1 matches several rows in table3 instead of just one. As this is hypothetical case (don't know what it represents) there is no way to know why the weird structure of table3 and the purpose of the payment_id columns in tables 1 and 3. Those relationships won't give what you want without a messy query.
-
You forgot to show your query where "group_concat is not working" EDIT: That peculiar data model that you have needs work.
-
Seems OK except for You need to call session_start() at beginning of every page that uses $_SESSION. As $_SESSION is always set, your code never calls it. $logoutAction - a lot of messing to set a variable that never gets used. Why not just check if $_GET['logout'] is set and equal to "true" mysql_* functions have been deprecated for years and now (since 7.0) no longer exist. Use PDO functions instead. Used prepared statements instead of putting variables directly into the query. You don't appear to be connecting to a database server anywhere. The only session variable you should be storing is the username. You set $logoutGoTo then immediately check it has a value - why? You should have error reporting turned on.
-
Firefox: right click ==> Select "Desktop" folder, name file and save. So you can - with Firefox and Chrome anyhow. (not Edge, surprise, surprise)
