All Activity

These two are useful for showing us your table structures: SHOW CREATE TABLE order_item; CREATE TABLE `order_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `product_id` int(11) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 or, using the mysql CLI, mysql> DESCRIBE order_item; +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | order_id | int(11) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+

I'm guessing that yes, it's the number of queries that at to load, and retrieving extra days is still better than an extra query. As for the diagrams, awesome of you. I thought there was a sql clause like SHOW (which I have not gotten to work) that could be used.

NOTE: In the script I posted earlier I used oi.qty * p.price as amount in the query to produce the invoice. As the invoice is being produced immediately in my script, the values product.price and order_item.price will be the same. However, if there is a delay before producing the invoices, or if you subsequently use the data to produce an end-of-quarter or end-of-year sales report, use the price from the order_item table - this is the price at which is was sold. The product price could have moved on since the sale.

no, it seems like you think that the only way to produce the quantity * price for each row of data is if you hard-code the calculation for each row? you are missing the point, and probably not examining the posted information, of a computer program using variable/dynamic values at run-time. the term in the posted invoice sql query - oi.qty * p.price as amount, calculates the quantity * price for each row in the result set. the where clause - WHERE oi.order_id = ? determines which rows are in the result set, matching the current $order_id value.

The code I posted shows you how to pull the current price from the product table into the order_detail table . This bit... $quantities = array_filter($_POST['qty']); # remove 0 qtys try { $pdo->beginTransaction(); // write order record $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)"); $stmt->execute([ $_SESSION['customer'] ]); // get the id of the order $order_id = $pdo->lastInsertId(); // now write the order item records, also storing the current prices frmm product table $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price) SELECT ?, id, ?, price FROM product WHERE id = ? "); foreach ($quantities as $id => $qty) { $stmt->execute([ $order_id, $qty, $id ]); } $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); $order_id = null; throw $e; }

I type them out in my editor then paste them as code to retain the monospaced font.

I wrote the POST data to the db, it was simpler then I thought it was going to be. But now I have to create a dynamic sql select statement based on user input. I'll explain why. My order_details table looks like this: order_item_id (PK) order_id (FK) product_id qty price 1 1 2 4 2 1 6 8 3 2 3 1 4 3 4 7 5 3 1 10 6 3 9 6 Now the price must be the total price for that row. So in order_item_id 1, that person ordered 4 units of product_id 2. Let's say product_id 2 is £10. Then the price would be £40. I have to do that for each row. I am not storing the price of the total invoice because that would be a derived attribute. I Know I can use WHERE and AND in select but it's a bit different if you don't know the id before hand. Thanks

This illustrates the mechanics of the process. It processes the posted quantities, writes them to an order and order item file then generates the invoice html. The invoice is written to orders/order_xxx.html where xxx is the order number Example invoice output The tables used are customer order +-------------+-------------+ +-------------+----------+ | Field | Type | | Field | Type | +-------------+-------------+ +-------------+----------+ | customer_id | int(11) | | id | int(11) | | fname | varchar(45) | | customer_id | int(11) | | lname | varchar(45) | | order_date | datetime | +-------------+-------------+ +-------------+----------+ product order_item +-------------+---------------+ +------------+---------------+ | Field | Type | | Field | Type | +-------------+---------------+ +------------+---------------+ | id | int(11) | | id | int(11) | | description | varchar(50) | | order_id | int(11) | | price | decimal(10,2) | | product_id | int(11) | +-------------+---------------+ | qty | int(11) | | price | decimal(10,2) | +------------+---------------+ ` Code <?php include '../db_inc.php'; ## use your own $pdo = pdoConnect(); ## connecction code session_start(); $_SESSION['customer'] = 125; ## I am assuming this would be set when the customer signed in $order_id = null; if ($_SERVER['REQUEST_METHOD'] == 'POST') { $quantities = array_filter($_POST['qty']); # remove 0 qtys try { $pdo->beginTransaction(); // write order record $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)"); $stmt->execute([ $_SESSION['customer'] ]); // get the id of the order $order_id = $pdo->lastInsertId(); // now write the order item records, also storing the current prices frmm product table $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price) SELECT ?, id, ?, price FROM product WHERE id = ? "); foreach ($quantities as $id => $qty) { $stmt->execute([ $order_id, $qty, $id ]); } $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); $order_id = null; throw $e; } // prepare the invoice output $res = $pdo->prepare("SELECT oi.order_id , DATE_FORMAT(o.order_date, '%M %e %Y') as order_date , concat(c.fname, ' ', c.lname) as customer , p.description as product , oi.qty , oi.qty * p.price as amount FROM order_item oi JOIN `order` o ON o.id = oi.order_id JOIN customer c ON c.customer_id = o.customer_id JOIN product p ON oi.product_id = p.id WHERE oi.order_id = ? "); $res->execute([ $order_id ]); $row = $res->fetch(); $inv_total = 0; $inv_output = "<!DOCTYPE html> <html lang='en'> <head> <title>Sample order/invoice</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <table style = 'width: 400px;'><tr><td><b>Customer<b></td><td>{$row['customer']}</td></tr> <tr><td><b>Order No<b></td><td>" . sprintf('%06d', $order_id) . "</td></tr> <tr><td><b>Order Date<b></td><td>{$row['order_date']}</td></tr> </table><br><br> <table style='width: 600px; border-collapse: collapse;'> <tr style='background-color:#000; color: #FFF'> <td><b>Product</b></td> <td><b>Quantity</b></td> <td><b>Amount</b></td> <tr> "; do { $inv_output .= "<tr><td>{$row['product']}</td> <td>{$row['qty']}</td> <td>{$row['amount']}</td> </tr>"; $inv_total += $row['amount']; } while ($row = $res->fetch()); $inv_output .= "<tr><td colspan='3'>&nbsp;</td></tr> <tr><td colspan='2'><b>TOTAL</b></td><td>$inv_total</td><?tr> </table> </body> <?html>"; // WRITE INVOICE OUTPUT TO HTML FILE file_put_contents("orders/order_{$order_id}.html", $inv_output); header("Refresh: 0"); exit; } else { $results = $pdo->query("SELECT id , description , price FROM product"); $form_output = " <form method='POST'> <table style='width:500px'> <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td><td>Price</td></tr> "; foreach($results as $r) { $form_output .= "<tr><td>{$r['id']}</td> <td>{$r['description']}</td> <td> <input type='number' value='0' name='qty[{$r['id']}]'> </td> <td style='text-align: right;'>{$r['price']}</td> <tr> "; } $form_output .= " </table> <br> <button>Submit</button> </form> "; } ?> <!DOCTYPE html> <html lang='en'> <head> <title>Sample order/invoice</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <?php if ($order_id) { // was a new order created? echo $inv_output; } else { // if not, dsplay form echo $form_output; } ?> </body> </html>

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system. So it's the number of queries that actually cause extra load? Is it preferable to gather all my data and then evaluate sql = "SELECT X FROM TABLE.... JOIN... JOIN.... JOIN if ( evaluate results to determine action... Or cascade through IFs that essentially create extra query trips $sql = "SELECT x from table1 WHERE if ( $x .... condition TRUE $sql2 = SELECT.. JOIN.. another condition... but has the potential to reduce the bed to access resources. Or does it not matter at all? PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum? _____ | | ----- ? Thanks.

As in I am getting something into product_id and qty , but not what I was expecting

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system. PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum? _____ | | ----- ? Thanks.

You apparently do not communicate at the same level as most IT professionals, whom you are dealing with here. What is being said is to not only clarify what you are doing incorrectly but also the background as to why you need to change the things you are doing. I have read some pretty detailed explanations on this topic from very talented and extremely dedicated Pros about what you need to do but apparently you are not ready to get into this business that deeply. Your responses tell me that you do not understand this business. That's not a condescending answer but simply the assumption that I (and perhaps others) are getting from your repeatedly abusive responses. You have chosen a topic that is very complex for many long-time users of PHP and you are simply not ready for that it seems. Despite the attempts from a couple of our best and brightest to involve you and educate you you insist on being hurtful and dismissive of the time and effort that has been put into trying to help you. Good luck.

I provide a TXT file that is in fact HTML, because HTML can not be directly uploaded - I get that Where this was posted, it for some reason was removed, but not by me ?? I have taken on board the suggestions given - And while I take a level of exception to the language used. It is unfortunate that what started as a simple request, becomes so patronising I may not as a newbie to this site, know its every rule or requirement, but I least expected to be playing mind games - Is this the norm here - to mob newbies and basically abuse them? It must make you feel so superior - My hubby was right Freaks IS APPROPRIATE for this site

It would still only be a single query (with a few joins). What is your problem with that?

I'm getting accustomed to spreading my data over multiple tables, and trying to do it right. Assuming that several small tables i are more optimum than one big table (since there are less rows to touch - even if they are being ignored), is touching a table for a single column advisable? I know repetition is a no-no, but should oft needed data be stored together (even if unrelated)? Example: On login there is a check for user and password (from USERS). But then I want to make sure the account is active (it will expire after 30 days and days is stored with other timestamps) so check ACCOUNT activity. And that the user logged in within the last 5 days (check INFO) or used a PROMO table code etc, etc. Each of these tables needs to be visited to compare data. Is this the correct/best approach? Or is there an alternative way to make 'regularly required info' more accessible?

Yes! I have done all that (quote selection part) and now I'm on to the order_details table. I am trying to use my $_POST['qyt'] and write the values to the database. order_item_id is fine as thats Auto-increment, order_id is fine as thats the last insert id. I am getting product_id and qty into the database but it's not the expected output. As for price in the order_details table, I am just passing a fixed variable (e.g $price = 15;) for development purposes at the moment whilst trying to figure out how to get my POST array into the db, correctly. When I've done that, I will do the price.

Your UPDATE query should be an INSERT .. ON DUPLICATE KEY query. If there is a duplicate you should be updating the city, not the id. INSERT INTO user_city (id_city, city) VALUES ('$id_city', '$city') ON DUPLICATE KEY UPDATE city = '$city' Better still, use prepared statements.

You're reading from an XML file but you haven't posted it, and you're getting errors but you haven't said what they are. Does that about sum it up?

Good morning, I ask you why I have a problem with a script in php to update the data of a mysql table. I have a table with the following values: id, name, city, city_id and an xml file with the data to be updated: id_city, city I tried to make a script to update the data in the table, but unfortunately I always get errors. My script is as follows: <?php $conn = mysqli_connect("localhost", "root", "", "dbtest001"); $affectedRow = 0; $xml = simplexml_load_file("city.xml") or die("Error: Cannot create object"); foreach ($xml->children() as $row) { $id_city = mysqli_real_escape_string($conn, ($row-> id_city)); $city = mysqli_real_escape_string($conn, ($row-> city)); $sql = "UPDATE user_city SET (id_city, city) VALUES ('" . $id_city . "', '" . $city . "') ON DUPLICATE KEY UPDATE id_city=$id_city " ; $result = mysqli_query($conn, $sql); if (! empty($result)) { $affectedRow ++; } else { $error_message = mysqli_error($conn) . "\n"; } } ?>

To add a bit to this, Namespaces solve one major problem, which is class and function name conflicts. Here's a simple example: namespace Gizmola; function substr($string) { return \substr($string, 0, 3); } $test = '12345678910'; echo substr($test); With a namespace, I'm able to redefine a built in function, as well as use the built in function in my customized version of substr. I can then include my function and use it as a library in other scripts: use function Gizmola\substr as substr; echo substr('Now is the time for us to use namespaces'); This becomes even more valuable when you are dealing with class libraries. Without namespaces, the use of a library would mean that every single class across all the libraries you might want to be using, would need to have a unique name. Namespaces solve this issue, and allow you to organize your code, and refer to other classes in an unambiguous way. I can reference any class definition by including its namespace. // Logger.php namespace Gizmola; class Logger { public function log() { // ... } } Some other code that wants to use the Logger class. namespace MyApp; require_once('path/to/gizmola/Logger.php'); use Gizmola\Logger; $logger = new Logger(); $logger->log(); If for some reason I end up using a class library that also has a Logger class, like Seldaek\Monolog, I can do that via the ability to alias a class or function when I use it: namespace MyApp; use Gizmola\Logger; use Monolog\Logger as AppLogger; $logger = new Logger(); $appLogger = new AppLogger('main'); $logger->log(); $appLogger->warning('Problem with MyApp'); Alternatively, I could reference it directly via it's namespace to get around the conflict: namespace MyApp; $logger = new Gizmola\Logger(); $logger->log(); $appLogger = new Monolog\Logger('main'); $appLogger->warning('Problem with MyApp'); The other benefit of namespaces, is that by applying a convention to the way you map a namespace to a directory structure, a class autoloader can determine the location of a class and load it at runtime. This is where PSR-0 and now PSR-4 come into play. Library authors who conform the directory structure and location of their code to these standards will allow their library to be easily integrated into any project. These standards were designed to incorporate the organizational structure of as many pre-existing libraries as possible, so there is some interesting code in there, but for most people, creating your own class is as simple as putting it into a directory structure that more or less maps to the namespace. Composer is able to install a component library, and make that available to your app with its own libraries, and provide an autoloader and static class map for you to use if you want it, relieving you of having to require classes or be concerned about placing them in specific includable directories on your server, as you had to do in the olden days before namespaces were introduced.

The intent is to essentially create a single instance script, similar to single-instance applications on the desktop. If you try and launch a second instance, it detects the first instance and either doesn't run or does something different. One possible scenario for this is if you have some cron job that runs every minute. If for some reason it takes longer than a minute to run, sometimes it's easier to just prevent the second instance from starting than to write the scripts in such a way that they don't cause problems. @peter844, assuming that is an accurate description of your goal, file_exists is not really the correct way to solve the problem. If you actually ran both scripts at the exact same moment, it's possible for them both to pass the file_exists test before either of them has a chance to actually create the file. A better solution is to use fopen with mode x flag to create the file. In this mode, fopen will fail if the file already exists, or create the file if it does not. Your script can use that to determine if the other instance is already running. Example: $lockFile = __DIR__.'/monkey.tmp'; $fp = @fopen($lockFile, 'x'); if (!$fp){ die('Script already running.'); } register_shutdown_function(function() use ($fp, $lockFile){ fclose($fp); unlink($lockFile); }); An even better solution, is to use flock to obtain an exclusive lock on the file. If the lock cannot be obtained then that means there is a script still running holding the lock. Example: $lockFile = __DIR__.'/monkey.tmp'; $fp = @fopen($lockFile, 'c'); if (!$fp){ die('Unable to access lock file'); } if (!flock($fp, LOCK_EX|LOCK_NB)){ die('Script is already running.'); } register_shutdown_function(function() use ($fp, $lockFile){ flock($fp, LOCK_UN); fclose($fp); unlink($lockFile); });

Hi again, Apologies for the delayed response - I can't thank you enough for your help! This is a little embarrassing but you were exactly right, mac_gyver; I was using the wrong password. I was able to get this to work, and I will definitely implement your security recommendations for this login / site. Thank you again everybody for your help in solving this!!

aren't you at the point of inserting data in to the order table - member_id and date columns, get the last insert id from that query, get the current prices for the product ids from the product table, then insert the data in to the order_item table - order_id, product_id, qty, and price columns that you defined in the ERD?

Make sure that your regex is correctly matching the end of the output. Sounds like your connection is stalling out because it doesn't detect the end of the output and ends up looping until it hits the script timeout. In addition to the regex, you could add another test that $line contains some data. That should break the loop if fgets times out due to no data to read. do { $line = fgets($this->fp); $r .= $line; } while ($line && !preg_match('/\d{1,4} Bridge Interfaces displayed/', $line));

Update - new class not connecting for me, still looking at that one. Current class I am also trying to modify still produces a Gateway Timeout with the newly modified code addition; //edited code to attempt to read the eof to allow all data to pass before close of socket function GetResponse(&$r) { $r = ''; do { $line = fgets($this->fp); $r .= $line; } while (!preg_match('/\d{1,4} Bridge Interfaces displayed/', $line)); } timeout is occurring at exactly 1min. **From tailing the log file I am also seeing that the Fatal Error is due to the Maximum execution time of 300 seconds exceeded (used php_ini to set this).