Barand
-
Posts
24,615 -
Joined
-
Last visited
-
Days Won
835
Everything posted by Barand
-
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;
-
Why do you feel this urge to duplicate the country, area,city data? You only need it in the ip_lookup table. Join the two tables in a query whenever you want that info for access table records.
-
Php script to receive http request from a software?
Barand replied to ARAD's topic in PHP Coding Help
One of the reasons for using an http request (AJAX) is to stay on the same page. If you want output to go to another page use a link or a form with GET or POST and send to the other page. -
The var_dump() output has now shown you why - the values are not the same. You search for "charlie" but array contains "charlie\n"
-
No. PHP knows nothing about where the page should appear, it is running remotely on the server. Things like opening new tabs need to be handled on the client, using HTML or JavaScript.
- 1 reply
-
- 1
-
-
Does a var_dump() of $word and $text[2] reveal any differences?
-
Start by following those two links that @requinix included for you in his reply.
-
Unlikely Quotes need removing... $query = "UPDATE `greencard` SET `comments`= '$comments', 'sent' = '$sent' WHERE `hospitalnumber`= '$hospitalnumber' and `PIN`= '$PIN'"; ^ ^ and it's easier just to use ... sent = NOW() WHERE ...
-
The best place to set the default timezone is in your php.ini file. Then you don't have to set the default in every script.
