Barand
-
Posts
24,573 -
Joined
-
Last visited
-
Days Won
824
Everything posted by Barand
-
According to my manual...
-
Calculating Inbreeding on a 10 Generation Pedigree
Barand replied to Triple_Deuce's topic in PHP Coding Help
@Beluga Don't attribute things to me that I didn't say. Don't hijack other people's threads Don't resurrect old topics. -
mysql> CREATE TABLE `download` ( -> `download_id` int(11) NOT NULL AUTO_INCREMENT, -> `ip_address` varchar(50) DEFAULT NULL, -> `address` varchar(50) DEFAULT NULL, -> PRIMARY KEY (`download_id`) -> ); mysql> INSERT INTO download (address) VALUES ('66.249.75.215'); mysql> UPDATE `download` SET `ip_address` = hex(inet_aton(`ADDRESS`)) WHERE ip_address is null; mysql> SELECT address -> , ip_address -> FROM download; +---------------+------------+ | address | ip_address | +---------------+------------+ | 66.249.75.215 | 42F94BD7 | +---------------+------------+
-
I'd expect 1 & 4
-
... WHERE b.user_id = 1 AND a.user_id <> b.user_id; -- ADD THE CONDITION
-
mysql> CREATE TABLE `download` ( -> `download_id` int(11) NOT NULL AUTO_INCREMENT, -> `ip_address` int(10) unsigned DEFAULT NULL, -> `address` varchar(50) DEFAULT NULL, -> PRIMARY KEY (`download_id`) -> ); mysql> INSERT INTO download (address) VALUES ('66.249.75.215'); mysql> UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null; mysql> SELECT address -> , HEX(ip_address) as ip_address -> FROM download; +---------------+------------+ | address | ip_address | +---------------+------------+ | 66.249.75.215 | 42F94BD7 | +---------------+------------+
-
Of course it does - as already stated, inet_aton() is an SQL function not PHP
-
$stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;"); @gw1500se Those look like backticks to me
-
https://www.php.net/manual/en/mysqli.prepare.php
-
Selecting records without relations in another table
Barand replied to Strahan's topic in MySQL Help
Simple rule: if you are LEFT JOINing to a table, put any conditions on that table in the ON clause. (If you put them in the WHERE clause, the join behaves as an INNER JOIN) -
Don't run queries in side loops. Use a single query which joins the two tables. Use prepared statements. Use PDO, it's easier and better than mysqli. You could use a second query to group/sum the product totals but as you are listing all the products anyway, you may as well total them into an array as you go. <?php // assumes you have a PDO conection here $tdata = ''; $totdata = ''; $date_from = $_POST['date1'] ?? date('Y-m-d', strtotime('first day of this month')); $date_to = $_POST['date2'] ?? date('Y-m-d'); $res = $db->prepare("SELECT o.order_id , CONCAT(o.firstname, ' ', o.lastname) as full_name , DATE_FORMAT(o.date_added, '%M %e, %Y') as date , p.name as product , p.quantity as qty FROM oc_order o JOIN oc_order_product p USING (order_id) WHERE o.order_status_id <> 0 AND o.date_added BETWEEN ? AND ? ORDER BY o.order_id, product "); $res->execute( [ $date_from, $date_to ] ); $prevorder = 0; $totals = []; foreach ($res as $r) { if ($r['order_id'] != $prevorder) { $tdata .= "<tr><td>{$r['full_name']}</td><td>{$r['date']}</td><td colspan='2'> </td></tr>"; $prevorder = $r['order_id']; } $tdata .= "<tr><td colspan='2'> </td><td>{$r['product']}</td><td>{$r['qty']}</td></tr>"; // accumulate totals by product if (isset($totals[$r['product']])) { $totals[$r['product']] += $r['qty']; } else { $totals[$r['product']] = $r['qty']; } } arsort($totals); foreach ($totals as $p => $q) { $totdata .= "<tr><td>$p</td><td>$q</td></tr>"; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <style type="text/css"> table {width: 50%; margin: 16px 50px; border-collapse: collapse; } th {background-color: #000; color: #FFF; padding: 8px;} td {padding: 4px 8px;} </style> </head> <body> <h3>Orders</h3> <table> <tr><th>Customer</th><th>Date</th><th>Product</th><th>Quantity</th></tr> <?=$tdata?> </table> <h3>Product Totals</h3> <table> <tr><th>Product</th><th>Total Qty</th></tr> <?=$totdata?> </table> </body> </html> Giving Your data needs nomalizing. It should be something like this +----------------+ +---------------+ +------------------+ +-----------------+ | customer | | oc_order | | oc_order_product | | product | +----------------+ +---------------+ +------------------+ +-----------------+ | cust_id |----+ | order_id |----+ | order_product_id | +---| product_id | | firstname | | | date_added | +---| order_id | | | name | | lastname | +---| cust_id | | product_id |---+ | price | | email | | status_id | | quantity | +-----------------+ +----------------+ +---------------+ +------------------+
-
Fatal error: Allowed memory size of bytes exhausted
Barand replied to TrueMember's topic in PHP Coding Help
A common cause of memory exhaustion is infinite recursion. Are you sure about that callback? -
try SELECT DISTINCT u.firstname , u.lastname FROM ssm_chat_link a JOIN ssm_chat_link b USING (chat_id) JOIN user u ON a.user_id = u.user_id WHERE b.user_id = 1;
-
This class is excellent for working with AD
-
As a compromise, when you add a download record, get the id of the matching ip_lookup record and store that in the download record. That way you aren't duplicating the contry/area/city data and you get extremely efficient joins. SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city FROM download INNER JOIN ip_lookup ON download.ip_id = ip_lookup.id WHERE download.FILENAME is not null
-
convert unsigned int ip_address from db to string
Barand replied to larry29936's topic in PHP Coding Help
Were they originally saved using INET_ATON to convert from "w.x.y.z" to an integer? -
Without knowing what output you are expecting from that input, how can we say what's missing? The inputs are dodgy too. Each sub2 will overwrite the previous one, leaving you with 2-92 and 3-90 only.
-
convert unsigned int ip_address from db to string
Barand replied to larry29936's topic in PHP Coding Help
Are you sure that is line 205? -
PHP login system fetch username and redirect issue
Barand replied to etopal's topic in PHP Coding Help
try echo '<pre>' . print_r($_SESSION, true) . '</pre>'; That will show the contents of $_SESSION. If it's doesn't contain the expected values you need to trace backwards to where the values should be set and work out why it isn't happening. -
PHP login system fetch username and redirect issue
Barand replied to etopal's topic in PHP Coding Help
Have you checked if $_SESSION contains what you are expecting it to contain? -
Can you post the output from SHOW CREATE TABLE ip_lookup; and SHOW CREATE TABLE download;
-
Use json_decode() to create either an object or array. You can then access the individual elements.
-
You could replace $myfile = fopen("test.txt", "r") or die("Unable to open file!"); while(!feof($myfile)) { $text[] = fgets($myfile); } fclose($myfile); with $text = file('test.txt', FILE_IGNORE_NEW_LINES);
-
... and a meaningful topic title
-
Remove the ( ) from the SELECT clause. download +----+---------------------+------------+----------+ | ID | LOG_TIME | IP_ADDRESS | FILENAME | +----+---------------------+------------+----------+ | 1 | 2020-05-03 17:26:56 | 20 | NULL | | 2 | 2020-05-03 17:26:56 | 160 | a.txt | | 3 | 2020-05-03 17:26:56 | 205 | b.txt | +----+---------------------+------------+----------+ ip_lookup +-------+----------+--------+---------+--------+------------+ | IP_ID | start_ip | end_ip | country | area | city | +-------+----------+--------+---------+--------+------------+ | 1 | 1 | 100 | USA | NY | New York | | 2 | 101 | 200 | UK | N West | Manchester | | 3 | 201 | 300 | Spain | North | Barcelona | +-------+----------+--------+---------+--------+------------+ mysql> SELECT download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city -> FROM download, ip_lookup -> WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip; +----------+---------+--------+------------+ | FILENAME | country | area | city | +----------+---------+--------+------------+ | NULL | USA | NY | New York | | a.txt | UK | N West | Manchester | | b.txt | Spain | North | Barcelona | +----------+---------+--------+------------+ Also, use explicit join syntax and not "FROM A,B WHERE ..." SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city FROM download INNER JOIN ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;
