Barand

The user privileges defined in the mysql database for whichever username you are connecting with.

If you aren't storing the file you need to load it from the tmp_file. Make sure you have the privileges required to run a LOAD DATA query.

$newData = JSON.parse(data); Try commenting out the above line - it shouldn't be necessary as you specified a data-type: json

Storing reservations only, as you have done, is the efficient way to do it. However, as you have discovered, finding those not booked is like asking "Hands up up everyone who isn't here?". Create a fourth table to define time periods (a record for 30 minute slot between 8am and 5pm) Booking_periods : period_id (PK), start_time, end_time Now you have something you can match against to find available slots. Whether you use it in a query or use it to define a PHP array is up to you. You may find this useful

WHERE's don't work correctly on table that you LEFT JOIN to. You need to put the condition in the JOIN Try SELECT [PROD SCHD].[WORK ORDER], [PROD SCHD].[ENTERPRISE], [PROD SCHD].[PART NUMBER], [PPBOMFCB].[ITEM_NUMBER_BOM], [PPBOMFCB].[CATALOGUE_NUMBER_BOM], [PPBOMFCB].[DESCRIPTION_BOM] FROM [PROD SCHD] LEFT JOIN [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] AND [PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA%' WHERE [PROD SCHD].[WORK ORDER] IS NOT NULL

Not sure how to say it with any more clarity. Your definition of the function has 4 parameters, namely $fields, $data, $rows and $page When you call the function you provide only the "$fields" parameter. There are no values provided for the other three.

When you call your "display_table()" function you are passing a single array parameter ($fields). Your function also expects $data, $rows and $page parameters. What happened to those?

You say you get the error on the fetch, but that is the bit of the code you decided not to show us!

As mac_gyver suggested, have two table viz. stock_receipts, stock_issues. CREATE TABLE `stock_receipt` ( | CREATE TABLE `stock_issue` ( `batch_id` int(11) NOT NULL AUTO_INCREMENT, | `stock_issue_id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) DEFAULT NULL, | `product_id` int(11) DEFAULT NULL, `receipt_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | `issue_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `quantity` int(11) DEFAULT NULL, | `job_no` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, | `quantity` int(11) DEFAULT NULL, PRIMARY KEY (`batch_id`), | `batch_id` int(11) DEFAULT NULL, KEY `index2` (`product_id`,`receipt_date`) | PRIMARY KEY (`stock_issue_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | KEY `index2` (`product_id`, `issue_date`), | KEY `index3` (`batch_id`) | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | When a new issue is entered, get the stock records for that product which have unissued stock remaining SELECT r.batch_id , r.quantity , SUM(i.quantity) as issued , CASE WHEN SUM(i.quantity) IS NULL THEN r.quantity ELSE r.quantity - SUM(i.quantity) END as remaining FROM stock_receipt r LEFT JOIN stock_issue i USING (batch_id) WHERE r.product_id = 9 AND receipt_date < NOW() GROUP BY r.batch_id HAVING remaining > 0; +----------+----------+--------+-----------+ | batch_id | quantity | issued | remaining | +----------+----------+--------+-----------+ | 1 | 15 | 10 | 5 | | 2 | 10 | NULL | 10 | +----------+----------+--------+-----------+ Loop through these records and allocate remaining quantities, creating issue records containing the batch and quantity. If there are more required issues than remain in stock, write a pending issue for the outstanding quantity without a batch_id. Therefore if 20 are issued, the issue records written to the stock_issues table would be Batch Qty ------+------ 1 5 2 10 5 When a new receipt is entered, the process is similar but reversed. I.E. Batch Qty ------+------ 1 5 2 10 3 2 3 get the pending issues for that product (no batch_id). Loop through them allocating the new batch_id where they can be fulfilled from the new receipt. If 2 are received then 2 of the pending 5 items can come from batch 3 (new batch) there would then be 3 pending. Below is the code for a small application to illustrate the process

Is this what you want to achieve Receipts Batch | a | b | c | d | Date | 2020-01-01 | 2020-01-02 | 2020-01-03 | 2020-01-04 | Qty | 15 | 10 | 23 | 30 | ------------------------+--------------+--------------+--------------+--------------+ Issues | | | | | | | | | | 2020-01-01 10 | 10 | | | | 10 from batch a 2020-01-02 7 | 5 | 2 | | | 5 from batch a, 2 from batch b 2020-01-03 25 | | 8 | 17 | | 8 from batch b, 17 from batch c 2020-01-04 16 | | | 6 | 10 | 6 from batch c, 10 from batch d

Still not totally clear. If, for the time being, we ignore which batch to issue from then you have received 78 and issued 58. That leaves 20, not 8 +------------+-----+----+-----+-------+ | Date | ID | In | Out | Level | +------------+-----+----+-----+-------+ | 2020-10-01 | 9 | 15 | 10 | 5 | | 2020-10-02 | 9 | 10 | 7 | | | 2020-10-03 | 9 | 23 | 25 | | | 2020-10-04 | 9 | 30 | 16 | | +------------+-----+----+-----+-------+ | 78 | 58 | +----+-----+

I will be waiting for paulvz to answer requinix's questions regarding the data, instead of just reposting it.

Here's another solution <?php include 'db_inc.php'; $db = pdoConnect(); ### ### PROCESS POSTED DATA ### if ($_SERVER['REQUEST_METHOD'] == 'POST') { $stmt = $db->prepare("UPDATE sales SET confirmed = 1 WHERE id = ? "); foreach ($_POST['confirm'] as $id => $v) { $stmt->execute([ $id ]); } header("Location: #"); // reload the page and refresh html table exit; } ### ### GET THE DATA TO DISPLAY ### $res = $db->query("SELECT DATE_FORMAT(s.sales_date, '%b %d, %Y') as date , CONCAT(u.firstname, ' ', u.lastname) as name , CONCAT_WS(', ', u.country, u.city, u.address) as address , FORMAT(SUM(d.quantity * p.price), 2) as total , s.id as salesid FROM sales s INNER JOIN users u ON s.user_id = u.id INNER JOIN details d ON s.id = d.sales_id INNER JOIN products p ON d.product_id = p.id WHERE s.confirmed = 0 GROUP BY s.id "); $tdata = ''; foreach ($res as $r) { $tdata .= "<tr><td>{$r['date']}</td> <td>{$r['name']}</td> <td>{$r['address']}</td> <td class='ra'>{$r['total']}</td> <td><input type='checkbox' name='confirm[{$r['salesid']}]' value='1'></td> "; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <style type='text/css'> body, table { font-family: verdana, sans-serif; font-size: 10pt; } table { border-collapse: collapse; width: 80%; margin: 0 auto; } #header { background-color: indigo; color: white; margin-bottom: 32px; padding: 16px; } th { padding: 8px; background: blue; color: white; } td { padding: 4px 8px; } td.ra { text-align: right; } </style> </head> <body> <div id='header'> <h1>Example Sales Confirmation</h1> </div> <form method='POST'> <table border='1'> <tr><th>Date</th> <th>Name</th> <th>Address</th> <th>Total</th> <th>Confirm</th> </tr> <?=$tdata?> <tr><td colspan='4'>&nbsp;</td> <td><button type='submit'>Submit</button></td> </tr> </table> </form> </body> </html> [edit] PS It assumes your data looks like this users id ----+ firstname | lastname | address | city | country | | | sales products | id -----+ +-------- id +----< user_id | | price sales_date | | confirmed | | | details | | id | +----------< sales_id | product_id >-------+ quantity

I have no idea. Your continued use of "SELECT * " completely obfuscates your table structures. Also, that "solution" that you found is far from optimal.

You use a WHERE clause, just like you have in your UPDATE query. There is an SQL tutorial link in my signature.

Do not run queries in a loop like that where you are using each row from one query to provide the value to do another select query. You apparently are aware of joins, so just do a single query using another join. If you are concerned with rows where confirmed == 0, put that condition in your query instead of retrieving every row and the ignoring those you don't want. Also, don't use select *. Specifiy the columns you require. (i would rewrite the query for you but, because you used "*", I don't know what you are selecting from which tables.) Now to your question... Put the output in a form so it can be submitted to the server to update the records.

Illegible.

You're probably right - I hadn't considered it might be a substitute for DATE(date_added)

@Gandalf64 for the above function to work, date_added needs to be in Y-m-d format, so why reformat it to Y-m-d format?

Then you ain't doing it right. My background pattern... My code... <?php // texture $im1 = imagecreatefromjpeg('images/stripes.jpg'); list ($w, $h) = getimagesize('images/stripes.jpg'); // mask $im2 = imagecreatetruecolor($w, $h); $bg2 = imagecolorallocatealpha($im2, 255, 255, 255, 127); $txcolor = imagecolorallocate($im2, 0,0,0); imagefill($im2, 0, 0, $bg2); imagettftext($im2, 96, 0, 20, $h-20, $txcolor, 'c:/windows/fonts/arlrdbd.ttf', 'TEXT'); // target $im3 = imagecreatetruecolor($w, $h); $bg3 = imagecolorallocatealpha($im3, 80, 80, 255, 127); imagefill($im3, 0, 0, $bg3); imagesavealpha($im3, 1); // set pixels for ($y = 0; $y<$h; $y++) { for ($x = 0; $x<$w; $x++) { $c = imagecolorat($im2, $x, $y); if ($c == 0) imagesetpixel($im3, $x, $y, imagecolorat($im1, $x, $y) ); } } header("Content-Type: image/png"); imagepng($im3); imagedestroy($im1); imagedestroy($im2); imagedestroy($im3); ?> My output...

A more efficient way to write the code is not to check first if the username exists but to define a UNIQUE key on username column. The just add the record and trap any duplicate key exceptions. Like this... <?php if (!empty($_POST["username"]) && !empty($_POST["password"])) { $DBHOST = "localhost"; $DBUSER = "tim"; $DBPWD = "nineteen1985"; $DBNAME = "customs_auction"; mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $conn = new mysqli($DBHOST, $DBUSER, $DBPWD, $DBNAME); $hashed = password_hash($_POST["password"], PASSWORD_DEFAULT); try { $statement = "INSERT INTO buyer(username,password) VALUES(?, ?)"; $stmt = $conn->prepare($statement); $stmt->bind_param("ss", $_POST["username"], $hashed); $stmt->execute(); header("Location: add_buyer.php?buyer=successful"); } catch (mysqli_sql_exception $e) { if ($e->getCode() == 1062) { header("Location: add_buyer.php?buyer=duplicate"); } else throw $e; } } else { header("Location: add_buyer.php"); } ?>

There is no data because no code executes to add them. You have a $POST instead of $_POST so the first if() fails. You use "execute" instead of "execute()" You need to remove the html code from the start of check_buyer.php otherwise the header() calls will fail (you can't sent output before a header() call.) There is no need for any html in that file.

So what are the symptoms that lead you to say it isn't updating?

Check for db errors. Put this line mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); before you create the mysqli connection

Hints Put the numbers 0 - 99 in an array and use array_chunk() to get the rows. Loop through each chunk to get the cells in each row. when checking for N is prime, it is necessary only to check for factors <= sqrt(N)