Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,566

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    You need to match aginst the subquery on id and time. Table subqueries require an alias (as virtual table name) try SELECT pda.* FROM production_data_archive pda INNER JOIN ( SELECT order_id MAX(insert_time) as insert_time FROM production_status_archive GROUP BY order_id ) pda2 USING (order_id, inser_time)
  2. Barand
    You have this situation if (....) { // define row row = something } echo row; // here you attempt to use row whether it was defined or not
  3. Barand
    Also, your data table design is wrong. Don't store data (particularly IDs) in comma separated lists.
  4. Barand
    Yes, you are - the answer was given to you ... The answer to the above question, BTW, is NO.
  5. Barand
    1) do not double post. 2) we don't like being shouted at.
  6. Barand

    Yona

    Barand replied to Yona's topic in PHP Coding Help

    https://developer.mozilla.org/en-US/docs/Web/HTML/Attributes/required
  7. Barand

    Yona

    Barand replied to Yona's topic in PHP Coding Help

    Adding a "required" attribute to your input fields will do it for you on the client side.. Don't forget you still need to check within PHP when you process the posted data.
  8. Barand
    Your form needs name="formpay" for document.formpay to work. It also doesn't like a varaiab;e called "sum" and a function called "Sum". Try <script type="text/javascript"> function calcSum () { var Pay = document.formpay.Pay.value; var Charge = document.formpay.Charge.value; var sum = (Charge - (Pay * 1.295)); document.getElementById('Total').value = sum; } </script> <form name="formpay" action="" method="post"> <br> <br> <table> <tr> <td><input type="text" id="Pay" value="" name="Pay" placeholder="Pay"></td> </tr> <tr> <td><input type="text" id="Charge" value="" name="Charge" placeholder="Charge"></td> </tr> <tr> <td><input type="text" id="Total" value="" name="Total" placeholder="Total"></td> </tr> </table> <input type="button" name="button" Value="Calculate" onClick="calcSum()"> </form>
  9. Barand
    Does it help if you force it to use numeric values? function Sum () { var Pay = parseFloat(document.formpay.Pay.value); var Charge = parseFloat(document.formpay.Charge.value); var sum = (Charge - (Pay * 1.295)); document.GetElementByid('Total').value = Sum; }
  10. Barand
    if you "echo $oak" you should see that it has a value of "Cut a tree", which has a numeric value of zero. Therefore, setting the value of your column called "oak" to $oak+1 will always result in 1. What does this "users" table of yours look like?
  11. Barand
    There is nothing in your form that has the name "alltree" so $_POST['alltree'] will not exist. (Turn php error reporting on. It would have told you that) The line "$oak = $_POST['oak']" is a waste of space. the column identifiers (puud, sire1) should not have the $s. mysqli_query() requires 2 parameters (see manual link provided)
  12. Barand
    Use JOINs with a single query to get the information you want instead of running multiple queries If you are only retrieving a single record, don't use a while loop - a single fetch will suffice Use prepared statements - don't put variables directly into the SQL string <?php /* MK_users MK_role_access MK_baccounts +-----+---------------+ +----------+----------+ +-----+------------------------+ | uid | fullname | | role_uid | role_bid | | bid | businessname | +-----+---------------+ +----------+----------+ +-----+------------------------+ | 1 | Laura Norder | | 1 | 10 | | 10 | Amazon | | 2 | Peter Dowt | | 2 | 20 | | 20 | Apple | | 3 | Tom Di Canari | | 3 | 30 | | 30 | Paula's Poodle Parlour | +-----+---------------+ +----------+----------+ +-----+------------------------+ */ $_SESSION['id'] = 2; // provide a value $res = $conn->prepare("SELECT ba.bid , ba.businessname FROM MK_role_access ra JOIN MK_baccounts ba ON ra.role_bid = ba.bid WHERE ra.role_uid = ? "); $res->bind_param('i', $_SESSION['id']); $res->execute(); $res->bind_result($bid, $business); $res->fetch(); echo "$bid - $business <br>"; //==> 20 - Apple ?>
  13. Barand
    Find a better way to store the ids than a comma-separated list.
  14. Barand
    Just put a link in the head section of the HTML <!DOCTYPE html> <html lang="en"> <head> <link rel="stylesheet" href="path/to/my.css"> </head>
  15. Barand
    As the string is JSON, use json_decode(); EG $str = '{ "success": true, "delivered": true, "contactDetailsRequired": false, "message": "Signed For by: D ANDERSON ", "signature": "https://webservices.thedx.co.uk/PodImage/ImageHandler.ashx?tn=906732192165", "date": "21-07-2020", "serviceLevelName": "Consigned", "time": "13:33:19", "trackedProductName": "DataExchange" }'; $data = json_decode($str); echo $data->message . '<br>'; //--> Signed For by: D ANDERSON echo $data->date . '<br>'; //--> 21-07-2020
  16. Barand

    PHP update

    Barand replied to Ronel's topic in PHP Coding Help

    Easiest way is a DATETIME type column in your table. Give it a value NOW() when updating. This can also be automated by specifying the column default value of CURRENT_TIMESTAMP when updated.
  17. Barand
    If you had used a data model like the one I suggested to you in May 2019 ... ... the coached_from and coached_until dates would give you who the coach was/is at any point in time.
  18. Barand
    It isn't calling the addPostCode() function, it is just echoing its name ??? echo 'addPostCode("'.$mig_postcode.'")';
  19. Barand
    Ignore what I said about the join in this case. The fastest way I've found so far is to incorporate the code which gets the id into a MYSQL function, EG CREATE FUNCTION `getLookupId`(ip VARCHAR(60)) RETURNS int(11) BEGIN DECLARE lookupid INT; SELECT t.id INTO lookupid FROM ( SELECT g.* FROM ip_lookup AS g WHERE g.start <= INET6_ATON(ip) ORDER BY g.start DESC, g.end DESC LIMIT 1 ) AS t WHERE t.end >= INET6_ATON(ip); RETURN lookupid; END TEST... mysql> select * from download; +----+-----------------+------+ | id | address | ref | +----+-----------------+------+ | 1 | 140.50.228.2 | NULL | | 2 | 178.225.157.177 | NULL | | 3 | 102.125.142.27 | NULL | | 4 | 66.75.44.127 | NULL | | 5 | 33.25.97.27 | NULL | | 6 | 145.50.228.2 | NULL | | 7 | 183.225.157.177 | NULL | | 8 | 107.125.142.27 | NULL | | 9 | 71.75.44.127 | NULL | | 10 | 38.25.97.27 | NULL | | 11 | 44.153.111.171 | NULL | | 12 | 88.0.224.154 | NULL | | 13 | 49.153.111.171 | NULL | | 14 | 93.0.224.154 | NULL | | 15 | 218.255.51.103 | NULL | +----+-----------------+------+ 15 rows in set (0.00 sec) mysql> update download set ref = getLookupId(address); -- update using the function Query OK, 15 rows affected (0.11 sec) Rows matched: 15 Changed: 15 Warnings: 0 mysql> select d.id -> , d.address -> , l.country -> , l.city -> FROM download d -> JOIN -> ip_lookup l ON d.ref = l.id; +----+-----------------+---------------+----------------------------+ | id | address | country | city | +----+-----------------+---------------+----------------------------+ | 1 | 140.50.228.2 | United States | "Columbus (East Columbus)" | | 2 | 178.225.157.177 | Netherlands | "The Hague" | | 3 | 102.125.142.27 | Sudan | Khartoum | | 4 | 66.75.44.127 | United States | "San Diego" | | 5 | 33.25.97.27 | United States | "Columbus (East Columbus)" | | 6 | 145.50.228.2 | Netherlands | "Delft (Tanthof-West)" | | 7 | 183.225.157.177 | China | Dongguan | | 8 | 107.125.142.27 | United States | "Washington D.C." | | 9 | 71.75.44.127 | United States | Salisbury | | 10 | 38.25.97.27 | United States | Newark | | 11 | 44.153.111.171 | United States | "San Diego (Clairemont)" | | 12 | 88.0.224.154 | Spain | Madrid | | 13 | 49.153.111.171 | China | "Beijing (Chaoyang Qu)" | | 14 | 93.0.224.154 | France | Paris | | 15 | 218.255.51.103 | Hong Kong | "Kowloon City" | +----+-----------------+---------------+----------------------------+ 15 rows in set (0.00 sec)
  20. Barand
    No. ip_lookup.start and ip_lookup.end are varbinary(16) so the ip_address you are comparing also needs to be varbinary(16). We have been here before! I gave you a clue in the code I posted above. Read it. Once you fix that you need to work on the syntax for joining tables to do an update.
  21. Barand
    Given this data, for example +---------+----------+------------+ +---------+----------+------------+ | user_id | username | created | | user_id | username | created | +---------+----------+------------+ +---------+----------+------------+ | 1 | Kim | 2020-08-12 | | 21 | Emma | 2020-08-10 | | 2 | Gerald | 2020-08-12 | | 22 | Fenella | 2020-08-13 | | 3 | Irene | 2020-08-11 | | 23 | Charles | 2020-08-12 | | 4 | Naomi | 2020-08-11 | | 24 | Martin | 2020-08-10 | | 5 | Sally | 2020-08-12 | | 25 | Roy | 2020-08-13 | | 6 | Olivia | 2020-08-11 | | 26 | Carol | 2020-08-11 | | 7 | Kate | 2020-08-10 | | 27 | Graham | 2020-08-10 | | 8 | Wanda | 2020-08-13 | | 28 | Keith | 2020-08-13 | | 9 | Debbie | 2020-08-12 | | 29 | Jenny | 2020-08-10 | | 10 | Cuthbert | 2020-08-11 | | 30 | Barbara | 2020-08-13 | | 11 | David | 2020-08-13 | | 31 | Len | 2020-08-10 | | 12 | Emily | 2020-08-11 | | 32 | Ken | 2020-08-10 | | 13 | Kevin | 2020-08-12 | | 33 | Andrew | 2020-08-13 | | 14 | John | 2020-08-12 | | 34 | William | 2020-08-10 | | 15 | Adam | 2020-08-12 | | 35 | Oscar | 2020-08-12 | | 16 | Henry | 2020-08-11 | | 36 | Neil | 2020-08-10 | | 17 | Liz | 2020-08-13 | | 37 | Daniel | 2020-08-12 | | 18 | Glynn | 2020-08-12 | | 38 | Victor | 2020-08-11 | | 19 | Georgina | 2020-08-13 | | 39 | Amanda | 2020-08-13 | | 20 | Harry | 2020-08-10 | | 40 | Sarah | 2020-08-10 | +---------+----------+------------+ +---------+----------+------------+ ... we can get the daily totals with this query mysql> SELECT created -> , COUNT(*) as total_joined -> FROM user -> GROUP BY created; +------------+--------------+ | created | total_joined | +------------+--------------+ | 2020-08-10 | 11 | | 2020-08-11 | 8 | | 2020-08-12 | 11 | | 2020-08-13 | 10 | +------------+--------------+ To get the average, we make this query a table subquery (so it behaves like a temporary table), so... mysql> SELECT AVG(total_joined) as av_per_day -> FROM ( -> SELECT created -> , COUNT(*) as total_joined -> FROM user -> GROUP BY created -> ) tots; +------------+ | av_per_day | +------------+ | 10.0000 | +------------+
  22. Barand
    Your third item is suffering from a surfeit of ids
  23. Barand
    mysql> EXPLAIN SELECT id, country, area, city -> FROM ip_lookup -> WHERE INET6_ATON('72.168.144.181') BETWEEN start AND end;EXPLAIN SELECT id, country, area, city +----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | ip_lookup | NULL | ALL | idx_iplookup_ip,idx_ip_lookup_end | NULL | NULL | NULL | 4774295 | 25.00 | Using where | +----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+ and mysql> EXPLAIN SELECT t.id, country, area, city FROM -> ( SELECT g.* -> FROM ip_lookup AS g -> WHERE g.start <= INET6_ATON('72.168.144.181') -> ORDER BY g.start DESC, g.end DESC -> LIMIT 1 -> ) AS t -> WHERE t.end >= INET6_ATON('72.168.144.181'); +----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | g | NULL | range | idx_iplookup_ip | idx_iplookup_ip | 19 | NULL | 2387147 | 100.00 | Using index condition | +----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+
  24. Barand
    I have a similar table (ip_lookup) with 5.2 million rows. CREATE TABLE `ip_lookup` ( `id` int(11) NOT NULL AUTO_INCREMENT, `start` varbinary(16) DEFAULT NULL, `end` varbinary(16) DEFAULT NULL, `continent` varchar(45) DEFAULT NULL, `country` varchar(45) DEFAULT NULL, `area` varchar(45) DEFAULT NULL, `city` varchar(145) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_iplookup_ip` (`start`,`end`), KEY `idx_ip_lookup_end` (`end`) ) ENGINE=InnoDB AUTO_INCREMENT=5277083 DEFAULT CHARSET=utf8; FYI, here's a benchmark comparison of those two queries mysql> SELECT id, country, area, city -> FROM ip_lookup -> WHERE INET6_ATON('72.168.144.181') BETWEEN start AND end; +--------+---------------+------------+------------+ | id | country | area | city | +--------+---------------+------------+------------+ | 806584 | United States | California | Littlerock | +--------+---------------+------------+------------+ 1 row in set (3.49 sec) mysql> SELECT t.id, country, area, city FROM -> ( SELECT g.* -> FROM ip_lookup AS g -> WHERE g.start <= INET6_ATON('72.168.144.181') -> ORDER BY g.start DESC, g.end DESC -> LIMIT 1 -> ) AS t -> WHERE t.end >= INET6_ATON('72.168.144.181'); +--------+---------------+------------+------------+ | id | country | area | city | +--------+---------------+------------+------------+ | 806584 | United States | California | Littlerock | +--------+---------------+------------+------------+ 1 row in set (0.07 sec)
  25. Barand
    inet_aton() does not produce varbinary(16). The manual is available online should you ever want to consult it.
×
×
  • 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.