Jump to content

Barand

Moderators
  • Posts

    24,566
  • Joined

  • Last visited

  • Days Won

    822

Everything posted by Barand

  1. Sorry, missed the second question It's usually going to be either "TEXT" or "JSON", depending on whether the ajax response will contain text or json encoded data,
  2. I usually pass a variable called "ajax" in my ajax calls. This has two purposes if my ajax handling code is in the same file as my calling code (it often is) it enables me to check for ajax calls with if isset($_GET['ajax']) and handle them separately. if there are multiple ajax calls, the value of that variable tells me what to do with it. if (isset($_GET['ajax'])) { switch ($_GET['ajax']) { case 'region' : exit(getRegionSales($conn, $_GET['region'])); break; case 'area' : exit(getAreaSales($conn, $_GET['area'])); break; } } Example scenario - a sales report showing totals by region Clicking on a region name drills down to show that region's sales broken down by area Clicking on an area drills down again to show the area's sales broken down by product $(".regionname").click(function() { var rgn = $(this).data("id"); $.get ( "ajax_handler.php", {"ajax" : "region", "region" : rgn}, function(resp) { // output region's totals figures by area }, "TEXT" ) }) $(".areaname").click(function() { var area = $(this).data("id"); $.get ( "ajax_handler.php", {"ajax" : "area", "area" : area}, function(resp) { // output area's sales totals by product }, "TEXT" )
  3. I'd guess that satellite[j] does not exist for the current value of j. You need more satellites It's better to use foreach() when iterating through arrays instead of for() (which expects a satellite to always be present for every value of j)
  4. To pass the value from the client click to PHP requires AJAX. for example, javascript onclick handler would $.get( "ajax-handler.php", {"id" : $(this).data("id")}, function (response) { // handle reponse } , "TEXT" ) Then in "ajax-handler.php" <?php $result = myfunction($_GET['id']); function myfunction (id) { // do something with id // return result } ?>
  5. The method of counting the dates not present between the logout dates has a major flaw - it does not take into account those days when they should not be present (ie weekends and holidays) and just counts them as absent on those days. The method I usually use in these situations is to create a temporary table (workdays) which contains all the weekday dates for the required period. In the logout data I have also the data for several users during this period (Oct 1 to Oct 24 2019). CODE TO GENERATE workday DATES $startdate = '2019-10-01'; $enddate = '2019-10-25'; $interval = DateInterval::createFromDateString('next weekday'); $dateperiod = new DatePeriod( new DateTime($startdate), $interval, new DateTime($enddate)); $db->exec("CREATE TEMPORARY TABLE workday (day DATE not null primary key)"); foreach ($dateperiod as $d) { $data[] = "('{$d->format('Y-m-d')}')"; } $db->exec("INSERT INTO workday VALUES " . join(',', $data)); I also have a "holiday" table. In this example, Oct 14th is designated a holiday. So the data I now have is... TABLE: ajoo_log TABLE: ajoo_holiday +-------+---------------------+--------+ +------------+------------+-------------+ | recno | timeoflogout | userid | | holiday_id | hol_date | hol_name | +-------+---------------------+--------+ +------------+------------+-------------+ | 3 | 2019-09-30 13:13:15 | 3 | | 1 | 2019-10-14 | October Day | | 4 | 2019-09-30 13:13:15 | 4 | +------------+------------+-------------+ | 5 | 2019-09-30 13:13:15 | 5 | | 9 | 2019-10-01 07:47:35 | 4 | TABLE: ajoo_user TEMP TABLE: workday | 10 | 2019-10-01 07:47:35 | 5 | +--------+-------+ +------------+ | 14 | 2019-10-03 13:28:17 | 4 | | userid | name | | day | | 15 | 2019-10-03 13:28:17 | 5 | +--------+-------+ +------------+ | 18 | 2019-10-04 21:41:17 | 3 | | 3 | Curly | | 2019-10-01 | Tue | 19 | 2019-10-04 21:41:17 | 4 | | 4 | Larry | | 2019-10-02 | | 20 | 2019-10-04 21:41:17 | 5 | | 5 | Mo | | 2019-10-03 | | 23 | 2019-10-05 11:18:18 | 3 | +--------+-------+ | 2019-10-04 | | 24 | 2019-10-05 11:18:18 | 4 | | 2019-10-07 | Mon | 25 | 2019-10-05 11:18:18 | 5 | | 2019-10-08 | | 28 | 2019-10-08 16:56:55 | 3 | | 2019-10-09 | | 29 | 2019-10-10 16:56:55 | 4 | | 2019-10-10 | | 30 | 2019-10-16 16:56:55 | 5 | | 2019-10-11 | | 33 | 2019-10-18 16:37:43 | 3 | | 2019-10-14 | Mon | 34 | 2019-10-18 16:37:43 | 4 | | 2019-10-15 | | 35 | 2019-10-18 16:37:43 | 5 | | 2019-10-16 | | 38 | 2019-10-19 12:31:23 | 3 | | 2019-10-17 | | 40 | 2019-10-19 12:31:23 | 5 | | 2019-10-18 | | 43 | 2019-10-21 15:23:54 | 3 | | 2019-10-21 | Mon | 44 | 2019-10-21 15:23:54 | 4 | | 2019-10-22 | | 45 | 2019-10-21 15:23:54 | 5 | | 2019-10-23 | | 48 | 2019-10-24 15:37:35 | 3 | | 2019-10-24 | | 49 | 2019-10-24 16:37:35 | 4 | +------------+ | 50 | 2019-10-24 17:37:35 | 5 | +-------+---------------------+--------+ Now to calculate the days absent. A cross join of the user table with workday table gives me a date for each user when they were expected to attend. Left joining to the log table and the holiday tables tells me when they were absent (no matching logout and no matching holiday) SELECT u.name , SUM(ISNULL(timeoflogout) AND ISNULL(hol_date)) as total_absent FROM ajoo_user u CROSS JOIN workday w LEFT JOIN ajoo_log l ON u.userid = l.userid AND w.day = DATE(l.timeoflogout) LEFT JOIN ajoo_holiday h ON w.day = h.hol_date GROUP BY u.userid Comparison of original method with this new method ORIGINAL RESULTS NEW RESULTS +----------+--------------+ +-------+--------------+ | username | total_absent | | name | total_absent | +----------+--------------+ +-------+--------------+ | Curly | 14 | | Curly | 12 | | Larry | 16 | | Larry | 10 | | Mo | 15 | | Mo | 10 | +----------+--------------+ +-------+--------------+
  6. If what you really want is just the content of the latest row, then SELECT MAX(timeoflogout) as timeoflogout , SUM(absents)) as tot FROM ( SELECT recno , DATEDIFF(timeoflogout, @prevlog) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout ASC ) as sorted JOIN (SELECT @prevlog := NULL) as initialise ) recs; +---------------------+------+ | timeoflogout | tot | +---------------------+------+ | 2019-10-24 17:37:35 | 15 | +---------------------+------+ EDIT: P.S. Just curious - what is your next query that requires this data?
  7. Turn on your error checking.
  8. To do it properly the transaction require a timestamp +----+-------+--------+---------------------+ | id | name | amount | transaction_date | +----+-------+--------+---------------------+ | 1 | Curly | 10.00 | 2020-01-01 10:00:00 | Jan | 2 | Larry | 20.00 | 2020-01-15 12:30:00 | | 3 | Mo | 15.00 | 2020-02-01 09:00:00 | Feb | 4 | Peter | -5.00 | 2020-02-01 10:30:00 | | 5 | Paul | 10.00 | 2020-02-02 11:30:00 | | 6 | Mary | 5.00 | 2020-02-02 12:15:00 | +----+-------+--------+---------------------+ Suppose we want to list this month's transactions only but showing the correct running balance total. Our initialise subquery needs to get the opening balance as at the end of January (ie sum of amount column where the date is prior to start of Feb). We then list the records since the start of February SELECT name , transaction_date , amount , FORMAT(@balance := @balance + amount, 2) as balance FROM pup JOIN (SELECT @balance := (SELECT SUM(amount) FROM pup WHERE transaction_date < '2020-02-01 00:00:00') ) as init WHERE transaction_date >= '2020-02-01 00:00:00'; +-------+---------------------+--------+---------+ | name | transaction_date | amount | balance | +-------+---------------------+--------+---------+ | Mo | 2020-02-01 09:00:00 | 15.00 | 45.00 | | Peter | 2020-02-01 10:30:00 | -5.00 | 40.00 | | Paul | 2020-02-02 11:30:00 | 10.00 | 50.00 | | Mary | 2020-02-02 12:15:00 | 5.00 | 55.00 | +-------+---------------------+--------+---------+
  9. No, you haven't executed any query, and you haven't even defined a string variable correctly.
  10. All that would be required is to get the SUM(amount) for those transactions up to that one (as @requinix has already mentioned.)
  11. "absents" is a column alias. You can't reference an alias inside the SELECT or WHERE parts of the same query. If you want the cumulative then you'll need an extra subquery SELECT recno , timeoflogout , absents , @tot := @tot + IFNULL(absents,0) as total FROM ( SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise ) recs; +-------+---------------------+---------+-------+ | recno | timeoflogout | absents | total | +-------+---------------------+---------+-------+ | 30 | 2019-10-24 17:37:35 | NULL | 0 | | 29 | 2019-10-21 15:23:54 | 2 | 2 | | 28 | 2019-10-19 12:31:23 | 1 | 3 | | 27 | 2019-10-18 16:37:43 | 0 | 3 | | 26 | 2019-10-15 16:56:55 | 2 | 5 | | 25 | 2019-10-05 11:18:18 | 9 | 14 | | 24 | 2019-10-04 21:41:17 | 0 | 14 | | 23 | 2019-10-03 13:28:17 | 0 | 14 | | 21 | 2019-10-02 07:47:35 | 0 | 14 | | 4 | 2019-09-30 13:13:15 | 1 | 15 | +-------+---------------------+---------+-------+ "absents" is now a column in the subquery (a dynamic temporary table)
  12. I'd advise against storing any derived data values such as the balance. Calculate it when rerquired SELECT * FROM pup; +----+-------+--------+ | id | name | amount | +----+-------+--------+ | 1 | Curly | 10.00 | | 2 | Larry | 20.00 | | 3 | Mo | 15.00 | | 4 | Peter | -5.00 | | 5 | Paul | 10.00 | | 6 | Mary | 5.00 | +----+-------+--------+ SELECT name , amount , FORMAT(@balance := @balance + amount, 2) as balance FROM pup JOIN (SELECT @balance:=0) as init; +-------+--------+---------+ | name | amount | balance | +-------+--------+---------+ | Curly | 10.00 | 10.00 | | Larry | 20.00 | 30.00 | | Mo | 15.00 | 45.00 | | Peter | -5.00 | 40.00 | | Paul | 10.00 | 50.00 | | Mary | 5.00 | 55.00 | +-------+--------+---------+
  13. That code snippet without any context is as much use as a chocolate teapot. What is the table structure? How are attempting to get the balance?
  14. This will calculate the "absents" value between consecutive logouts SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog:=NULL) as initialise +-------+---------+---------------------+ | recno | absents | timeoflogout | +-------+---------+---------------------+ | 30 | | 2019-10-24 17:37:35 | | 29 | 2 | 2019-10-21 15:23:54 | | 28 | 1 | 2019-10-19 12:31:23 | | 27 | 0 | 2019-10-18 16:37:43 | | 26 | 2 | 2019-10-15 16:56:55 | | 25 | 9 | 2019-10-05 11:18:18 | | 24 | 0 | 2019-10-04 21:41:17 | | 23 | 0 | 2019-10-03 13:28:17 | | 21 | 0 | 2019-10-02 07:47:35 | | 4 | 1 | 2019-09-30 13:13:15 | +-------+---------+---------------------+ You can accumulate the total of "15" as you process the query results.
  15. I thought it was perhaps an "Illiteracy filter" service offered by the site. Would you hire someone who can't read simple instructions?
  16. Lose the output buffering (ob_ functions). Rename "EmailBut.html" to "EmailBut.php" so that php code is executed. The first code should end like this . . . if ($mail->Send() ) { $Message = "Email sent..!"; } else{ $Message = "Error..!"; } $mail->smtpClose(); $Message = urlencode($Message); header("Location:EmailBut.php?Message=$Message"); ?> EmailBut.php <?php if(isset($_GET['Message'])){ echo $_GET['Message']; } ?> <html> <body> <form action="index.php" method="POST"> <button type="submit">Send</button> </form> </body> </html>
  17. If you currently have that data in a string variable only, then file_put_contents()
  18. Step 1 - check error logs
  19. When you process the query and store in an array, make the array index the product name $list['Plesk'] = [ product data ] Since array keys must be unique you will end up with only one.
  20. There is only one auto_inc column. It's just that myisam will allow it to be placed as the second part of a multi-column key and thus give multiple auto-incremented ranges. Thus, in this case, assuring unique values of 1 to N for each raffle.
  21. Oh Joy! I love it when someone tells us what they want and you give up your time to tell them how, only to have them then tell us that it wasn't really what they want so the exercise was a waste of time. However, if you use a MyIsam table you can still do it. Just make the PRIMARY KEY (raffle_number, ticket_number) CREATE TABLE `raffle` ( `name` varchar(50) DEFAULT NULL, `raffle_number` int(11) NOT NULL, `ticket_number` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`raffle_number`,`ticket_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO raffle (name, raffle_number) VALUES ('Curly', 1), ('Curly', 1), ('Curly', 1), ('Larry', 2), ('Larry', 2), ('Larry', 2), ('Larry', 2), ('Larry', 2), ('Mo', 1), ('Mo', 1), ('Mo', 1), ('Mo', 2), ('Mo', 2), ('Mo', 2); SELECT name , raffle_number , ticket_number FROM raffle ORDER BY raffle_number, ticket_number; +-------+---------------+---------------+ | name | raffle_number | ticket_number | +-------+---------------+---------------+ | Curly | 1 | 1 | | Curly | 1 | 2 | | Curly | 1 | 3 | | Mo | 1 | 4 | | Mo | 1 | 5 | | Mo | 1 | 6 | | Larry | 2 | 1 | | Larry | 2 | 2 | | Larry | 2 | 3 | | Larry | 2 | 4 | | Larry | 2 | 5 | | Mo | 2 | 6 | | Mo | 2 | 7 | | Mo | 2 | 8 | +-------+---------------+---------------+
  22. Make ticket_number an auto_increment column CREATE TABLE raffle ( name VARCHAR(50) ticket_number INT NOT NULL auto_increment primary key ); INSERT INTO raffle (name) VALUES ('Curly'), ('Curly'), ('Curly'), ('Larry'), ('Larry'), ('Larry'), ('Larry'), ('Larry'), ('Mo'), ('Mo'), ('Mo'), ('Mo'), ('Mo'), ('Mo'); SELECT name, ticket_number FROM raffle; +-------+---------------+ | name | ticket_number | +-------+---------------+ | Curly | 1 | | Curly | 2 | | Curly | 3 | | Larry | 4 | | Larry | 5 | | Larry | 6 | | Larry | 7 | | Larry | 8 | | Mo | 9 | | Mo | 10 | | Mo | 11 | | Mo | 12 | | Mo | 13 | | Mo | 14 | +-------+---------------+
  23. It all depends on your requirements. Do you ever have a requirement for a reduced-size version that does not have a watermark? Do you want to store the original full-size image with or without the watermark? Note that as soon as you manipulate the uploaded image you lose any exif data (such as rotation). Do you require different sizes of images (thumbnail for product listings and medium when viewing individual product, say)?
  24. On second thoughts function getISP($str) { $a = explode('.', $str); return join('.', array_slice($a, -3)); }
×
×
  • 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.