DeX

Oh wow, that was it! I didn't know what a table index was so I did some research yesterday and applied indexes to 2 of the tables based on the quote_id. Loading the quote details dropped from 104 seconds to 4 seconds and loading a quote to edit dropped from 28 seconds to 0 seconds. This is amazing, thanks a lot! I haven't tested inserting the records, I hope that hasn't increased much as a result.

I'm back from vacation and hoping to get this resolved now, thanks for being patient while I was away. Height, width, length and quantity are attributes of doors and windows being added to the building. Because the use is able to continuously add as many doors and windows as they like, I store each set of attributes in a JSON string, that's why they're varchar. For example, if the user enters a 3x4 window and a 3x5 window, the heights column will get the following data: ["4","5"] and width will get: ["#","3"] I may be doing this wrong but it's the best way I've found so far. The reason I have a purchase_order_id for each product is because the administrators control which products show up on which purchase orders in the system. I need to store which purchase order the item was on when the quote was made in case it changes later on. I want the item to be put on its original purchase order, now the one it was changed to, if that makes sense. I'm already aware there is a better way for this, I plan on storing all edits in the main purchase order table and then just referencing back to the purchase order that item appeared on at the time period the quote was done. It's on the to do list. I very much appreciate you supplying me with the new SQL, thanks a lot, however it didn't quite work. I made some edits to yours and got the following: SELECT mqp.product_id, mqp.price_per_k, mqp.board_feet, mqp.location, mqp.power, mqp.locking_handle_side, mqp.unit_price, mqp.total_price_calculation_id, mqp.quantity, width, length, height, c.name, total_price FROM match_quote_product mqp JOIN building_products bp ON mqp.product_id = bp.id JOIN match_quote_colour mqc ON mqp.colour_type_id = mqc.colour_type_id AND mqp.quote_id = mqc.quote_id JOIN colours c ON mqc.colour_id = c.id WHERE mqp.quote_id = 50000 AND bp.purchase_order_id = 5 ORDER BY purchase_order_order ASC I had to: - add a comma at the end of line 1 - change building_products to bp in the where clause - change colours to c in the select statement - change match_quote_product to mqp in the where clause The query now works but returns 0 rows, I'm not sure why. I have to go through it and try to understand what it's doing but something must not match another column in the JOIN.

| match_quote_product | CREATE TABLE `match_quote_product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `quote_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `width` varchar(255) NOT NULL, `length` varchar(255) NOT NULL, `height` varchar(255) NOT NULL, `location` varchar(100) NOT NULL, `quantity` varchar(255) NOT NULL, `locking_handle_side` varchar(100) NOT NULL, `power` varchar(100) NOT NULL, `insulated` tinyint(4) NOT NULL, `customer_supplied` tinyint(4) NOT NULL, `storey` varchar(100) NOT NULL, `window_inserts` int(11) NOT NULL, `board_feet` double NOT NULL, `price_per_k` double NOT NULL, `unit_price` double NOT NULL, `total_price_calculation_id` int(11) NOT NULL, `purchase_order_id` int(11) NOT NULL, `colour_type_id` int(11) NOT NULL, `total_price` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3248453 DEFAULT CHARSET=latin1 | mysql> explain select product_id, match_quote_product.price_per_k, match_quote_product.board_feet, match_quote_product.location, match_quote_product.power, match_quote_product.locking_handle_side, match_quote_product.quantity, width, length, height, colours.name, match_quote_product.unit_price, total_price, match_quote_product.total_price_calculation_id from building_products, match_quote_product, match_quote_colour, colours where building_products.id = product_id && match_quote_product.colour_type_id = match_quote_colour.colour_type_id && match_quote_colour.colour_id = colours.id && match_quote_product.quote_id = 50000 && match_quote_product.quote_id = match_quote_colour.quote_id && building_products.purchase_order_id = 5 order by purchase_order_order asc; +----+-------------+---------------------+------------+--------+---------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+--------+---------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------------+ | 1 | SIMPLE | match_quote_product | NULL | ALL | NULL | NULL | NULL | NULL | 2846461 | 10.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | building_products | NULL | eq_ref | PRIMARY | PRIMARY | 4 | integrity_development.match_quote_product.product_id | 1 | 10.00 | Using where | | 1 | SIMPLE | match_quote_colour | NULL | ALL | NULL | NULL | NULL | NULL | 573276 | 1.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | colours | NULL | eq_ref | PRIMARY | PRIMARY | 4 | integrity_development.match_quote_colour.colour_id | 1 | 100.00 | NULL | +----+-------------+---------------------+------------+--------+---------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------------+ 4 rows in set, 1 warning (0.03 sec)

It's a simple table with an ID, a quote_id (to match up to the quote), a product_id (to match up to the product) and then each other column is an attribute like length, width, height, quantity and price. When loading a purchase order to view or edit, I need to pull out each material of a certain type (lumber, metal, screws) and display the relevant materials on that purchase order with their attributes. The attributes are also building specific because the lumber and metal lengths / quantities will change per building.

I have a quoting system which calculates the quantity and other attributes of 360 different building products every time a quote is generated in order to calculate a final cost. When the quote is performed, I'm saving each individual product as a row in a database table with each attribute (length, width, quantity, price) as columns in that table. Right now I'm just about to cross over 3,000,000 records in the table and it takes quite a while to perform queries each time a quote retrieval is requested by a user. I'm wondering if there would be any performance increase if I were to start storing all products in the table in a single JSON string taking up only one row and then parsing the string once it is retrieved.

Thank you. One more question, if I'm getting the price of the quote, how do I know if that's already in the model object or if I need to get it from the database? For example, the first time I need to fetch the price, I would go to the database, get it, then set it in the quote model. The next 10 times I need to get that price while displaying the same page (in various places), I don't want to keep going back to the database for the price, how do I know if I can retrieve it from the model object using the getter? Do I have a piece of code in the getter that checks to see if the local variable is set and return if so?

No not at all, I very much appreciate the guidance. If using an ORM library is the proper way to go for medium scaled PHP implementations, then that's what I will use. Just as long as it's better long term and not just short term. I want the best long term solution.

I wasn't necessarily going to build setters and getters for every single database column. For example, if I have a quote I need to display on the page, I would build a customer object and set all the customer details in that so the getters can retrieve it to display on the page, it's only name, address and phone number. Then I would have a quote object which would set things like the quote price, the quote expiry date and a list of all the upgrade options to show on the quote, along with their attributes like width, height and quantity. Then when doing purchase orders, I would build a bill of materials (BOM) object that would contain all individual building products and their attributes. Is this wrong? I'm mostly wondering how to separate the model into multiple models because I mainly just have one right now with all the methods in it.

I've been working with what I thought was MVC for quite a while but now after researching it some more, it appears I may have been wrong all along. I think I have been using the model incorrectly. Is my new way of thinking now correct? Model: These are like objects that hold data during the session (page load). It can also dump the data to the database but is mainly used to hold data related to a specific object for use throughout the current page load. Controller: There will usually only be one as it is like a gatekeeper that directs requests to the proper model. View: These will usually be the .php pages themselves but for views that are reused, it can be an isolated class which generates HTML code (like loading a select element with user names). I have built a program where a quote is requested by the user and the program has to go to the database in order to get the details of the quote to display them on screen. Right now my view is making the request through the controller to the model and the model is fetching each piece of requested information from the database, then passing it back through to the view as needed. Because the page can show the same information in various places, the same database call by the model can be made more than once for that specific information. Should I be making a request for the entire quote, building a quote object with the model and then passing that entire object back to the view to be used instead? This will introduce a lot of getter/setts methods of which I currently have none. I'm assuming I would have a customer model which would build a customer object and return that. Then a quote model would build a quote object and return that. Same for purchase orders and users, all which will have information appearing on the quote or purchase order. Thanks a lot, I'm really trying to research this as much as I can and get it correct.

At times, yes. Some SELECT elements will be duplicated on more than one screen, like the list of users. That can be used to assign a quote to a salesman or it can be used on the user management screen in the admin section.

Does this mean I should just have the foreach loop in the webpage itself and build the option elements right there? This would mean I can avoid having a view class file at all, the view would simply be the webpage.

Currently I have a page where a select element resides and it shows a list of all users in the system. Here is my logic for populating the select box: 1. That page makes a call to the view to be populated. 2. The view runs a foreach loop and builds a string containing a lot of options elements holding the user names, then returns the long string back to the page to be displayed. 3. The foreach from the view makes a call to the controller to get all the names. 4. The controller passes the request straight through to the model. 5. The model gets all the names from the database, adds them to an array and returns them back to the controller. 6. The controller passes them back to the view. 7. The view iterates through them and builds the list (in step 2 above). 8. Element with all options is displayed on page. I'm confused if the view is for building objects which contain HTML coding, or if the view is the actual webpage being displayed, and the building should be handed off to the controller. Basically I'm wondering if the view should be pages or classes.

By repairing the schema, do you mean adding default data values to all those columns?

I upgraded my server to Ubuntu 16.04 which also installed MySQL 5.7 with it and now every time I run an insert statement, it errors and tells me there is no default data specified for that column. If I go into PHPMyAdmin and specify default data for all columns, the insert will run without error. How do I turn this off? It's affecting my production sites, I just want it to go back the way it was of inserting empty strings or zeroes (or null) for any column I don't specify.

You were right, that worked perfectly. Thank you!

Wow that worked perfectly, thanks! I wonder why it does that.

7. I have other websites running on the same webserver and their code can find their respective javascript folders fine. 8. I didn't customize Apache2 at all, I just installed the default setup.

I have an Amazon web server which I host maybe 5 websites on and one of them is a portal I'm building for a client. Everything works great except when I load a page that uses JavaScript files, the console complains that it can't find any of the files, so therefore none of the JavaScript code on the page works. Here is a comprehensive list of things I have tried: 1. I put a test.html file in the JavaScript directory and tried to load it in my browser. My browser told me it didn't exist, 404 error. 2. I created a javascript2 folder and put the test.html file in there, it loaded no problem. 3. I moved all of my JavaScript files out into the javascript2 folder and changed all the reference links in the HTML to point to the new folder. The page and all JavaScript functions worked perfectly. 4. I did a "locate .htaccess" on my server (Linux) and it didn't return anything inside the /var/www/X directory for the website. There were 1 or 2 inside some other /var/www/X2 or /var/www/X3 directories but none of them specified anything for a javascript directory. 5. I restarted Apache2 and restarted the server many times. 6. When I visit X/javascript/test.html in my browser, watch it fail, then check my Apache2 logs, nothing was added. For some reason my web server can find every file except for anything inside that javascript directory, I have no idea why.

I have a job management portal which I built and it allows my client to create PDF files of quotes for their customers. They send their customer a link (token based URL) so their customers can click the link and view the quote. I want to serve up the PDF file on screen for their customer to view, how can I do this? Each time I attempt it, Chrome asks me if I would like to download the file. I just want to view it without the customer having a direct link to it.

Great ideas, guys, I'm going to do this for sure, the other added benefit of your suggestions is that Google can no longer spider the PDF and show them to anyone Googling a name. I think I already avoided this with my robots.txt but it has been a problem in the past. I think I will still move the PDF folder outside the web directory just to make it easier to roll out updates, I no longer have to skip the PDF folder when moving all folders into production. I can just use a symlink to get the same functionality or just serve it up with PHP like someone suggested.

I have a portal system I built that allows users to generate a quote PDF and save it onto the server so they can send the link to a customer for review. Is it common practice to leave the PDF directory web accessible or should it be one level above the web directory? These quotes do need to be viewed by the public since the salesmen simply send the URL to the customer for review but I've seen it done both ways. Thanks.

That's odd, I wrote some quick sample code to post here for you but my example code works. I'll have to go back and work through my code to see where the problem is with that then. Oh well, I'll leave the example code here for someone else in case they're looking for one. <html> <head> <title>TODO supply a title</title> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> <script type ="text/javascript" > function test(element) { alert($(element).find(":selected").text()); } </script> </head> <body> <select onchange ="test(this);" > <option value ="1" >One</option> <option value ="2" >Two</option> </select> </body> </html>

I've seen many solutions on various searches using :selected or options:selected or .text(). I've tried all of these in both JavaScript and JQuery, all of them give me the same result. When I change the selected option of a SELECT element and then run the JavaScript statement, it returns the original SELECT value before I changed it, not the current value. I've tried both .text() and .val(), both give me the same result. I realize I'm not posting the code but it's literally just a SELECT element with 20 options and some JavaScript to get the text from that element.

You mean create a class object and use getters / setters?

I'm only using the first array element of overallQuantities for most elements but like the wallMetal example I posted above, some items have more than one quantity group because there are different quantities at different lengths. Like overhead doors, you might have 3 at 10x12 and then another 5 at 12x12. So then the quantities array would have 2 elements in it. How would this work for getting the total price? If the building costs $300,000 and part of that is corner moulding, what is the new price when the quantity of corner moulding has changed? Am I keeping another array of the total prices for each product and tallying the total price of the building based on adding them all together? Then I would have to re-add them if one of the quantities change. Is that right? So now I'm trying to understand what you're doing with the BOM because I really want to implement this in the system. So let me try and walk through an example here to see if I can figure out how this works. overallQuantities[main_item_id] = quantity overallQuantities[sub_item_id][main_item_id] = quantity overallQuantities[sub_sub_item_id][sub_item_id][main_item_id] = quantity This is the example you posted, if I'm making a change which will update the quantity of corner moulding, would the corner moulding be the sub-item or main-item in this example? Going from the beginning: - an edit is done on the web page (building length changed) - AJAX function is called - AJAX function passes in the name of the changed element as well as the value of that element - corner moulding quantity requires updating based on the changed element and its value - overallQuantities[cornerMoulding] is updated using the huge quantity calculation assigned to corner mouldings - overQuantities[cornerMoulding][whiteScrews] is updated using the quantity calulation assigned to white screws which uses corner mouldings. Is that right? Did I use the main and sub items correctly there? - somehow the total price is updated and the new quote is displayed to the user. There are also other things on the quote which change based in some inputs. For example, if the sales guy changes the post spacing (distance between posts) from 6 feet to 4 feet, that's going to have an effect on the quantity of posts but it's also going to show up in an area at the top of the quote which states what the post spacing is. This isn't for every element, just for a couple, but post spacing is one of them. So this text would need to change to reflect the new post spacing, as well as show the new total price of the quote.