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)

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.

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 | +------------+

Your third item is suffering from a surfeit of ids

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 | +----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+

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)

inet_aton() does not produce varbinary(16). The manual is available online should you ever want to consult it.

start_id and end_id are varbinary(16) '1219006512' isn't.

Also, if continuing to use mysqli, place this line before connecting to the db server mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

You are missing the step to prepare the query before binding the parameters. I would strongly advise you use PDO rather than mysqli - much simpler.

Step 1 - read the error message Step 2 - look at the parameters in your code Step 3 - consult the reference manual to see what they should be Step 4 - adjust code accordingly

Just for the record... mysql> select * from datetest; +------------+------+ | unix | date | +------------+------+ | 1576972800 | NULL | | 1579651200 | NULL | | 1582329600 | NULL | +------------+------+ mysql> UPDATE datetest SET date = from_unixtime(unix); mysql> select * from datetest; +------------+------------+ | unix | date | +------------+------------+ | 1576972800 | 2019-12-22 | | 1579651200 | 2020-01-22 | | 1582329600 | 2020-02-22 | +------------+------------+

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime

try $product_queried = '09shjk1'; $xml=simplexml_load_string($xmlstr) or die("Error: Cannot make object"); $items = $xml->xpath("//PRODUCT[@ITEM='$product_queried']"); if ($items) echo $items[0]['ITEM'] . ' - ' . $items[0]->STOCK . '<br>'; else echo "Not found";

Include is not function. ( )s are not required.

then <?php echo $result2['user_convoy']; ?> and <td><?php echo $result2['name']; ?></td> <td><?php echo $result2['date']; ?></td> Where are those values (user_convoy, name and date) supposed to come from?

Do you have an input form yet where the user can specify their requirements and dimensions/quantities?

You can submit the form when the checkbox is clicked <input type="checkbox" name="display" value="1" onclick="this.form.submit()"> <!-- give it a value --> The thing to remember about about checkboxes (and radiobuttons) is that only checked values are submitted so your processing has to check if they were posted or not. Either $display = isset($_POST['display']) ? $_POST['display'] : 0; or (v7.0+) $display = $_POST['display'] ?? 0;

That's the equivalent of removing the bulb when a warning light comes on. It doesn't fix anything.

Looks like the query is failing. Try putting $Search in single quotes, or, better, use a prepared query.

That is one of the worse pieces of advice I have seen in these forums for a long time. @Endrick, ignore what @jodunno said in the above quote and specify the columns your query needs - don't use "SELECT * ". One day it will come back to bite you. Consider this scenario... Joe has written a script which creates a register of all student names, and uses "SELECT * FROM student" (with the excuse that he needs all the columns anyway) and iterates through the results. He then takes a holiday and, while he is away, things change. A colleague of his adds a new "dissertation" column and an image BLOB column to the student table. This contains the text of each student's 10,000 word final dissertation and a photo of the student. His register program still works but a query that took milliseconds now takes an age to run. The register doesn't need the dissertation or image data but, nevertheless, is dragging them down from the database for every student.

If they were empty, var_dump would show string(0) "" try echo bin2hex($exif1['GPS']['GPSAltitureRef']);

Where you have a function that desn't return a value (referred to in some languages as a void function and in other languages as a procedure) then, I agree, a return on the last line is pointless as it exits the function anyway. For functions that return a value, a return is necessary, and usually on the last line, although not necessarily. Another debugging tip... When outputting to the screen normally, use "echo" When outputting variables for debug puposes only, use "print". This makes it easy to search and remove stuff that shouldn't be in the production version.

I wouldn't split the clients into vendor and client tables - just have a client table. In one transaction, A might be the seller but in another transaction A might be the buyer (as in the examples in your initial post). Just record the buyer and seller ids in each sale transaction. Don't store derived data, such as total paid, balance outstanding etc. You get those by querying the transactions and payments.

Your items table may be a headache. Vendor B sells bikes and the item attributes are Now what if vendor D sells fridge/freezers whose attributes are width, height, depth, colour, fridge capacity, freezer capacity, ice dispenser(Y/N) and vendor E is selling concert tickets?