Barand
-
Posts
24,605 -
Joined
-
Last visited
-
Days Won
830
Everything posted by Barand
-
That's because there is an "entry_id" column in both tables. You need to specify which one (as I did) SELECT e.entry_id ^
-
Yes SELECT e.entry_id , field_id , value FROM wp_wpforms_entry_fields f JOIN wp_wpforms_entries e ON f.entry_id = e.entry_id WHERE field_id IN (5, 13, 16, 18) AND e.status='completed' ORDER BY e.entry_id
-
What is the structure of the table you want to write this data to? That could well have an influence on the best way to structure the data. What does the raw data coming from JQuery look like?
-
DATA +----------+----------+-------+ | entry_id | field_id | value | +----------+----------+-------+ | 1 | 5 | Curly | | 1 | 13 | bbb | | 1 | 16 | ccc | | 1 | 18 | ddd | | 2 | 5 | Larry | | 2 | 13 | eee | | 2 | 16 | fff | | 2 | 18 | ggg | | 2 | 43 | hhh | | 3 | 5 | Mo | | 3 | 13 | kkk | | 3 | 16 | mmm | | 3 | 18 | nnn | | 3 | 43 | ooo | | 4 | 5 | Tom | | 4 | 13 | ppp | | 4 | 16 | qqq | | 4 | 18 | rrr | | 4 | 43 | sss | +----------+----------+-------+ CODE $res = $conn->query("SELECT entry_id , field_id , value FROM wp_wpforms_entry_fields WHERE field_id IN (5, 13, 16, 18) ORDER BY entry_id "); $headings = [ 5 => 'Name' , 16 => 'Belt' , 13 => 'School', 18 => 'Events' ]; $temp_array = array_fill_keys(array_keys($headings), ''); // array for each attendee to be filled in from query results // process query results and place in array with attendee as the key $data = []; foreach ($res as $r) { if ( !isset($data[$r['entry_id']])) { $data[$r['entry_id']] = $temp_array ; } $data[$r['entry_id']][$r['field_id']] = $r['value'] ; // store answer in its array position } $theads = "<tr><th>" . join('</th><th>', $headings) . "</th></tr>\n" ; $tdata = ''; foreach ($data as $d) { $tdata .= "<tr><td>" . join('</td><td>', $d) . "</td></tr>\n"; } OUTPUT
-
I disagree, it ignores the entry_id. That is the field which you need to group the other values.
-
Looks like your approach to process name just like you do with school, belts, events is correct.
-
You probably have an error that stops the page executing. Turn on error reporting in your php.ini file.
-
Sounds like a job for the dynamic duo, Ajax and Javascript.
-
Sounds like there are no matching schools records - perhaps if you had CONCAT(s.city,s.school) instead of CONCAT(s.school,s.school) it may find matches. or ...LEFT JOIN a_schools USING (city, school)
-
get data from two tables for specific ticket id and username
Barand replied to ianhaney's topic in MySQL Help
you don't specify a specific ticket id in the query -
Converting and Calculating Numbers (decimal & rounding)
Barand replied to martinspire's topic in PHP Coding Help
To round $x up to the nearest $y mm $x = ceil($x / $y) * $y; -
Use a prepared statement with placeholders and bound parameters
-
Typing it is a good way. Put it after the FROM section and before the ORDER BY.
-
pdoConnect() is my custom function which does what you have already done in the lines above it. Remove that line and replace $db in my code with $conn (as that is the connection you are creating) .
-
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
