Barand

An alternative approach... storage_unit: date unit rented, price per month storage_payment: record of payments made for each unit TABLE: storage_unit TABLE: storage_payment +---------+-------------+-------+ +------------+---------+--------------+--------+ | unit_id | rental_date | price | | payment_id | unit_id | payment_date | amount | +---------+-------------+-------+ +------------+---------+--------------+--------+ | 1 | 2019-02-28 | 20.00 | | 1 | 1 | 2019-03-28 | 20.00 | | 2 | 2019-04-30 | 20.00 | | 2 | 1 | 2019-04-28 | 20.00 | | 3 | 2019-05-01 | 20.00 | | 3 | 1 | 2019-05-31 | 20.00 | | 4 | 2019-05-15 | 20.00 | | 4 | 2 | 2019-05-30 | 20.00 | | 5 | 2019-05-30 | 20.00 | | 5 | 2 | 2019-06-30 | 20.00 | +---------+-------------+-------+ | 6 | 2 | 2019-07-30 | 20.00 | | 7 | 3 | 2019-06-01 | 20.00 | | 8 | 4 | 2019-06-19 | 20.00 | | 9 | 3 | 2019-07-01 | 20.00 | +------------+---------+--------------+--------+ use a query to calculate payments and late fees SELECT u.unit_id , u.rental_date , COALESCE(MAX(p.payment_date), u.rental_date) as `last_paid` , timestampdiff(MONTH, COALESCE(MAX(p.payment_date), u.rental_date), CURDATE()) as mths_since_last , @ld := u.rental_date + INTERVAL timestampdiff(MONTH, u.rental_date, CURDATE()) MONTH as last_due , SUM(IFNULL(p.amount, 0)) as paid_to_date , u.price * timestampdiff(MONTH, u.rental_date, CURDATE()) as due_to_date , u.price * timestampdiff(MONTH, u.rental_date, CURDATE()) - SUM(IFNULL(p.amount, 0)) as rental_owed , CASE WHEN ( timestampdiff(MONTH, COALESCE(MAX(p.payment_date), u.rental_date), CURDATE()) = 1 ) AND ( timestampdiff(DAY, @ld, CURDATE()) <= 5 ) THEN 0 ELSE timestampdiff(MONTH, COALESCE(MAX(p.payment_date), u.rental_date), CURDATE()) * 15 END as late_fees FROM storage_unit u LEFT JOIN storage_payment p USING (unit_id) GROUP BY unit_id; +---------+-------------+------------+-----------------+------------+--------------+-------------+-------------+-----------+ | unit_id | rental_date | last_paid | mths_since_last | last_due | paid_to_date | due_to_date | rental_owed | late_fees | +---------+-------------+------------+-----------------+------------+--------------+-------------+-------------+-----------+ | 1 | 2019-02-28 | 2019-05-31 | 2 | 2019-07-28 | 60.00 | 100.00 | 40.00 | 30 | | 2 | 2019-04-30 | 2019-07-30 | 0 | 2019-07-30 | 60.00 | 60.00 | 0.00 | 0 | | 3 | 2019-05-01 | 2019-07-01 | 1 | 2019-08-01 | 40.00 | 60.00 | 20.00 | 15 | | 4 | 2019-05-15 | 2019-06-19 | 1 | 2019-07-15 | 20.00 | 40.00 | 20.00 | 0 | | 5 | 2019-05-30 | 2019-05-30 | 2 | 2019-07-30 | 0.00 | 40.00 | 40.00 | 30 | +---------+-------------+------------+-----------------+------------+--------------+-------------+-------------+-----------+

$ddate is already a DateTime object!. It looks like the whole of the code you posted could be replaced by $paymentDate = new DateTime('last day of next month'); $paymentDate->add(new DateInterval('P5D')); // add grace days $daysToPay = $paymentDate->diff(new DateTime())->days;

What format are your dates? My example showed you the correct format to use. EDIT: If they are in a different format you could use this (eg m/d/Y format) $xmas = DateTime::createFromFormat('m/d/Y', '12/25/2019');

try $xmas = new DateTime('2019-12-25'); $today = new DateTime(); $daysToGo = $xmas->diff($today)->days; // 141

You need to learn the difference between arrays and strings. The first is an array of arrays, the second is an array of strings containing numbers and commas Array ( [0] => Array ( [0] => 1 [1] => 5 [2] => 6 [3] => 7 [4] => 8 [5] => 9 ) [1] => Array ( [0] => 2 [1] => 7 [2] => 6 [3] => 5 [4] => 4 [5] => 3 ) [2] => Array ( [0] => 3 [1] => 12 [2] => 13 [3] => 14 [4] => 15 [5] => 16 ) ) Array ( [0] => 1,5,6,7,8,9 [1] => 2,7,6,5,4,3 [2] => 3,12,13,14,15,16 )

Also note that you created (correctly) a mysqli connection but you have switched to a mysql_ library function that is now obsolete

@Techbriefers - Brilliant! The way you managed to repeat what Psyco posted 14 hours earlier, removing all structure and readability and fitting the query into a single line, shows sheer genius.

Psycho has already given you the JOIN solution so saves me doing it. A couple of questions though, Why is there a reply_day column in the message table (when it's clearly reply-related). Do the reply_day and post_day columns contain dates?

You mention questions and answers tables in the question and the code is about messages and replies!? Are the two even related? If you want help with a query it's always better to provide the structures of the relevant tables. Please post them. Don't loop through one query result and use that to query another table within the loop. That is very heavy on time and resources. Instead use a single query with a join to get all the data you need from both tables with a single query.

I'd prefer to "normalize" the array data instead of storing duplicate records ... $data Array ( [10] => Array ( [0] => 25 [1] => 31 [2] => Yes [3] => No ) [20] => Array ( [0] => 21 [1] => 18 [2] => No [3] => No ) [30] => Array ( [0] => 11 [1] => 47 [2] => No [3] => No ) [40] => Array ( [0] => 14 [1] => 60 [2] => No [3] => No ) ) and have a second array to point synonymous keys to the correct records ... $keys Array ( [10] => 10 [20] => 20 [30] => 30 [40] => 40 [Ten] => 10 [dix] => 10 [twenty] => 20 ) You would reference the data array via the keys array EG print_r( $data[ $keys['dix'] ] ); Code to normalize the original array... sort($dataAry); // ensure keys are in sequence with numeric keys first $data = $keys = []; foreach ($dataAry as $d) { $k = $d[0]; // key = first element $v = array_slice($d,1); // values = the rest $k1 = array_search($v, $data); // have we stored it yet? if ($k1 === false) { $data[$k] = $v; $keys[$k] = $k; } else { $keys[$k] = $k1; } } Database equivalent: +-------------+ | data | +-------------+ | data_id |-------+ | Field 1 | | | Field 2 | | +------------------+ | Field 3 | | | keys | | Field 4 | | +------------------+ +-------------+ | | pseudokey | +------<| data_id | +------------------+ SELECT field1 , field2 , field3 , field4 FROM data INNER JOIN keys USING (data_id) WHERE pseudokey = 'twenty'

The method I was suggesting with that link to the php manual was $sql="SELECT * FROM test LIMIT 10"; $result = mysqli_query($con,$sql); $data = $result->fetch_all(MYSQLI_ASSOC); echo json_encode($data); However you can traverse the $result object as you suggest $sql="SELECT * FROM test LIMIT 10"; $result = mysqli_query($con,$sql); $data = []; foreach ($result as $row) { $data[] = $row; } echo json_encode($data);

That would depend on how $services is being created. You failed to show that bit. It isn't in your original post and appears out of nowhere in this one..

Then why post it in the PHP Help forum?

You need https://www.php.net/manual/en/mysqli-result.fetch-all.php to fetch the data EDIT: Note that fetch_all() is not available in all inplementations of mysqli so you may have to loop through the resuts using fetch() and store the rows in an array (which is one of the reasons for using PDO instead of mysqli.)

It sounds like it would be more efficient to make [#][0] the key of sets of subarrays . Then you can go straight ot the ones you want instead of repeatedly searching the whole array. array ( [string] => array ( array (int, int, string, string), array (int, int, string, string), array (int, int, string, string), array (int, int, string, string) ), ... [string] => array ( array (int, int, string, string), array (int, int, string, string), array (int, int, string, string), array (int, int, string, string) ) );

Protecting a form field from what? htmlspecialchars() is for use when outputting user-supplied data data to a web page. mysql_real_escape string() is was used to protect input values to queries from SQL injection. This is now obsolete, replaced by mysqli_real_escape_string() or (better still) the use of prepared statements to completely separate the query code from the user-supplied data.

Then your first stop should have been the forum rules

So far you have posted this, or similar, in the Linux, Miscellaneous and PHP Coding forums. Stop spreading it all over the site - just append comments to your original topic (Linux forum) Moving this post.

Welcome to the community

With an array of data like the one you described $projects = [ [ 'job_no' => '101', 'job_name' => 'Job 101', 'job_due' => '2019-08-15' ], [ 'job_no' => '102', 'job_name' => 'Job 102', 'job_due' => '2019-07-31' ], [ 'job_no' => '103', 'job_name' => 'Job 103', 'job_due' => '2019-08-31' ], [ 'job_no' => '104', 'job_name' => 'Job 104', 'job_due' => '2019-07-29' ], [ 'job_no' => '105', 'job_name' => 'Job 105', 'job_due' => '2019-08-15' ] ]; First sort the array with a custom sort function comparing the due dates usort($projects, function($a,$b) { return $a['job_due'] <=> $b['job_due']; } ); then you can loop through the array elements outputting each in its own row echo "<table style='width:500px;' > <tr> <td>Job No</td><td>Name</td><td>Date Due</td> </tr>\n"; foreach ($projects as $proj) { $formatted = date('d/m/Y', strtotime($proj['job_due'])); echo "<tr><td>{$proj['job_no']}</td><td>{$proj['job_name']}</td><td>$formatted</td></tr>\n"; } echo "</table>\n";

Your form method is post, so the variables will be in $_POST array. As stated, your query needs to be inside double quotes to interpolate the variables. Don't run queries in loops, especially when one which correctly uses "IN()" will do the job. Don't rely on button values being POSTed (browser dependent) if ($_SERVER['REQUEST_METHOD']=='POST') { //data removal code will be going here $kuemid = array_map('intval',$_POST['kuemid']); // ensure all ids are integers $del_id = join(',', $kuemid); // put ids in a comma separated string $wpdb->query( "DELETE FROM '.$wpdb->prefix.'kudos_email WHERE kuemid IN($del_id)" ); // etc }

Another simple method is to add a version number column to the table ... CREATE TABLE `test_product` ( `prod_id` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(50) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `version` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`prod_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> select * from test_product; +---------+-------------+-------+---------+ | prod_id | description | price | version | +---------+-------------+-------+---------+ | 1 | Product A | 49.99 | 2 | +---------+-------------+-------+---------+ ... then, when updating, make sure the record still has the same version number that you retrieved. If the number is the same you can update and increment the version. Below is a sample page which demonstrates this. Open in two browser pages - both will show the same data change one field in the first page and update then go to second page and change second field and update <?php include 'db_inc.php'; // Substitute your own $db = pdoConnect('test'); // database connection code if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $db->prepare("UPDATE test_product SET description = ?, price = ?, version = version+1 WHERE prod_id = 1 AND version = ? "); $stmt->execute([ $_POST['descrip'], $_POST['price'], $_POST['version'] ] ); if ($stmt->rowCount()==0) { // update didn't happen echo "Concurrent update occurred, try again<br>" ; } } $res = $db->query("SELECT description , price , version FROM test_product WHERE prod_id = 1 "); $r = $res->fetch(); ?> <!DOCTYPE html> <html> <head> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> </script> <style type="text/css"> </style> </head> <body> <form method="POST"> <input type="text" name="descrip" value="<?=$r['description']?>"><br> <input type="text" name="price" value="<?=$r['price']?>"><br> <input type="hidden" name="version" value="<?=$r['version']?>"><br> <!-- hidden field --> <input type="submit" name="btnSub" value="Submit"> </form> </body> </html>

You evidently missed this "main line" code when you read the initial post.... <ul class="navbar-nav mr-auto"> <?=menu_builder1($db, 0)?> </ul>

Is your dbname really "dbname"?. You might want to make some useful enhancements to your pdo connection code $db = new PDO("mysql:host=$servername;dbname=dbname", $username, $password); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // error reporting auto handled $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // saves specifying every time $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // handle prepared queries correctly In the above query there are no parameters so, instead of prepare/execute, just use $db->query(); Just my 0.02 worth.