markputt Posted July 22, 2014 Share Posted July 22, 2014 (edited) Hi all. What connects and order to it's customized data in the database. Specifically what connects ps_orders and ps_customized_data. Thanks. Edited July 22, 2014 by markputt (see edit history) Link to comment Share on other sites More sharing options...
vekia Posted July 22, 2014 Share Posted July 22, 2014 prestashop database schema check this PDF you will see there connections between tables :-) Link to comment Share on other sites More sharing options...
markputt Posted July 22, 2014 Author Share Posted July 22, 2014 (edited) prestashop database schema check this PDF you will see there connections between tables :-) Thank you very much for that schema pdf. I know that it will come in handy in the future. I've been looking at it for about an hour and I'm having a lot of trouble reading it and at this point there if far too much information in it to decipher what I need to see. I can't find a connection between the areas I need. So, if there are any other suggestions or hints from anyone I'd sure appreciate it. Again, thanks for the pdf though. I know it will eventually prove useful. Mark. *** found it *** Thanks again!!!! Edited July 22, 2014 by markputt (see edit history) Link to comment Share on other sites More sharing options...
cjrhoads Posted July 22, 2014 Share Posted July 22, 2014 (edited) Believe it or not, I spent about 6 hours just yesterday trying to get the answer to this question. >>>>What connects an order to its customerized data in the database.<<< (oops - I just realized I misread the original quote - I thought customized was customer. Editing the quote so my post makes more sense. The basic premise still applies; does Prestashop use PHP functions instead of table relationships to generate invoice/product/customer data?) Because there are no Prestashop reports that provide the order number, the customer, and the product they ordered along with payment information, I finally decided that I had to bite the bullet and learn the schema in order to write my own SQL queries to get the information. What I found is not good. While the schema from 1.4x seems to be well documented, I could not find documentation for 1.6.06 (the version I have) and there are significant differences. Furthermore, according to the tool I used to download the scheme, there are several tables in the MySQL database that don't have primary keys - a real no-no. Since my Prestashop only has a handful of orders and I'm not using many of the features, I went through every single one of the tables and identified the ones with data. addresscustomermanufacturerorder_detailorder_invoiceorder_invoice_paymentordersproductproduct_langproduct_suppliersupplier The worst part was - after identifying every single table out of the 268 tables that had data (11 with data, 257 without), I could not find a single transaction table that contained the customer number, the order number or invoice number and the product number. There just didn't seem any way to join the tables to get that information. I reviewed the developers guidelines (I'm not a developer, but have a technical background so can recognize a lot) and it appeared to me (I could be wrong) that instead of storing the information in related tables (which would be the more common way of doing it) the Prestashop developers created many functions which would enable them to create screens and PDF documents dynamically to pull up the products, and the customers that ordered them (or, alternatively, the customers and the invoices associated with them). In other words, the only way to get the information OUT in a report is to use PHP commands. Since it is not stored in the database, it cannot be queried in the typical fashion. Am I wrong? Please tell me that I am. Please tell me that it IS possible to write a SQL query that will give me this information: SELECT ps_customer.id_customer, ps_customer.company, ps_customer.firstname, ps_customer.lastname, ps_customer.email, ps_address.phone, ps_address.phone_mobile, ps_order_invoice.id_order_invoice, ps_order_invoice.id_order, ps_order_invoice.delivery_date, ps_order_invoice.total_paid_tax_incl The joins I tried were FROM (((ps_customer INNER JOIN ps_customer_group ON ps_customer.id_customer = ps_customer_group.id_customer) INNER JOIN ps_order_slip ON ps_customer.id_customer = ps_order_slip.id_customer) INNER JOIN (ps_order_payment INNER JOIN (((ps_layered_price_index INNER JOIN (ps_order_detail INNER JOIN ((ps_manufacturer INNER JOIN ps_product ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer) INNER JOIN ps_product_comment ON ps_product.id_product = ps_product_comment.id_product) ON ps_order_detail.product_id = ps_product.id_product) ON ps_layered_price_index.id_product = ps_product.id_product) INNER JOIN ps_order_invoice ON ps_order_detail.id_order_invoice = ps_order_invoice.id_order_invoice) INNER JOIN ps_order_invoice_payment ON ps_order_invoice.id_order_invoice = ps_order_invoice_payment.id_order_invoice) ON ps_order_payment.id_order_payment = ps_order_invoice_payment.id_order_payment) ON ps_order_slip.id_order = ps_order_invoice.id_order) INNER JOIN ps_address ON (ps_customer.id_customer = ps_address.id_customer) AND (ps_manufacturer.id_manufacturer = ps_address.id_manufacturer); order_slip appeared to be the only table with both customer_id and order_id in it, but it turned out that this doesn't exist for all the orders. (My guess? It's the delivery slip for items that to be delivered by a certain shipping service). Any help would be greatly appreciated. I don't want to start over from scratch with a new shopping cart, but it appears to me that not having this information in a query-able table is a deal breaker. Regards CJ Rhoads Edited July 22, 2014 by cjrhoads (see edit history) Link to comment Share on other sites More sharing options...
markputt Posted July 22, 2014 Author Share Posted July 22, 2014 Re-opening this one because of other interested parties. Link to comment Share on other sites More sharing options...
markputt Posted July 22, 2014 Author Share Posted July 22, 2014 There has to be some query that prestashop uses to call up an order and it's accompanying customized fields. Does anyone know what that would be? Link to comment Share on other sites More sharing options...
tuk66 Posted August 18, 2014 Share Posted August 18, 2014 See functions in /classes/Customization.php. Link to comment Share on other sites More sharing options...
eec Posted January 26, 2016 Share Posted January 26, 2016 prestashop database schema check this PDF you will see there connections between tables :-) Hello Vekia Does prestashop define realtionships also in MySQL database schema? If so, please let me know how can I see those, I have hard time finding relationships and constraints defined on the MySQL database. Thanks, Link to comment Share on other sites More sharing options...
Recommended Posts