Combining Multiple Database Customer Tables

I need the customer's email address, order date/time, name, product ordered, and address to be in a single table. Is there a way to combine multiple items into a new table that can be added to the database?

zen_address_book lists name and address
zen_customers_info lists customers_info_date_account_created
zen_customers lists email address
zen_customers_basket lists product id
zen_products_description lists product id and name of product

What business objective are you attempting to solve??

Where would this information need to be accessible from?

If you are trying to EXTRACT that data, then there is no need to create new tables / fields... Just write a script that gathers it all up and produces it in the output you desire.

Thank you for the quick replies! I am connecting the ZenCart database to Moodle external database authentication and enrollment. Moodle will only allow me to connect to 1 table. My customers do not create an account in ZenCart. I want to use their email address as a login user name and their zip code as a password. These are in 2 different tables.
Next I want that same table to list the correct course ordered. This is in a 3rd table. I also want the statutory required minimum time to start when they order.

.Moodle will only allow me to connect to 1 table
Maybe you should be asking there about connecting to more than one table

I did. Without customization, everything needs to be in 1 table.

It seems like everything is always interconnected. If I create a new table in phpMyAdmin and add 6 or so columns...pulling info from other tables, will I bring down the whole system? Or will it have no effect?

If I create a new table in phpMyAdmin and add 6 or so columns...pulling info from other tables, will I bring down the whole system?
You can do this
Or will it have no effect
Without code to populate these records in the new table I suspect there will be no effect

You could write a script to copy the required info from the original tables to your new moodle table; but every time there is a new order (or a customer updates some information) your table will be out of date. You need some sort of auto-updating written into every function that affects the relevant fields, which is a significant amount of custom coding. Depending on how your moodle table gets used, it might be feasible to run the updating script every time that table is accessed. No matter what, you will need custom coding written by somebody. Will it be more cost-effective and efficient in the long run to code for this continual updating, or to get code to use the existing tables?

Thank you! I didn't realize that zen_orders was a more comprehensive table in the database, that included most of what I wanted. As black belt suggested....that would be the table to use and maybe add to.

I'm confused again. :-(
I am looking for product ID numbers in the database table. The only place that I see the correct ID numbers is in the zen_products_description table. I can't find a table that matches up the customer's name (or customer number) with the product ID. I see an orders_id, but I can't find that referred to anywhere else.
I can't understand how the order connects the 'customer' with the 'product ordered' and makes a connection between the two, in the database.
All of my products in the catalog have unique ID's. I am trying to pull the product ID from the database (or the product name). Is it possible that I have not turned on the connection between the product and the product ID? With no mention of product id, product name or even product description; these are the columns in the most comprehensive table, zen_orders:
orders_id customers_id customers_name customers_company customers_street_address customers_suburb customers_city customers_postcode customers_state customers_country customers_telephone customers_email_address customers_address_format_id delivery_name delivery_company delivery_street_address delivery_suburb delivery_city delivery_postcode delivery_state delivery_country delivery_address_format_id billing_name billing_company billing_street_address billing_suburb billing_city billing_postcode billing_state billing_country billing_address_format_id payment_method payment_module_code shipping_method shipping_module_code coupon_code cc_type cc_owner cc_number cc_expires cc_cvv last_modified date_purchased orders_status orders_date_finished currency currency_value order_total order_tax paypal_ipn_id ip_address dropdown gift_message checkbox COWOA_order

I have tried using the wonderful 'developers tool kit', but that doesn't seem to point me to database tables.

I think you will find these cross indexed between the orders table and the orders_products table

Of all the order-related tables:

orders
orders_products
orders_products_attributes
orders_products_download
orders_status
orders_status_history
orders_total

orders_products holds the specific product information, cross-referenced to the orders table by the orders_id field.

Thank you Kobra for the response!
My orders_products_id and orders_id numbers look 'unique', but my products_id column isn't pulling the ID number for the product correctly. 5 different courses under products_name, and all have a products_id of 4. Where is that information pulled from? It is pulling the correct price and products_name, but not the correct products_id for the product.

Look at your products table & check the ID

How were these entered - manually or via say easy populate?

I see the orders_id referenced in both tables zen_orders_products and zen_orders. Shouldn't the products_id match the products_name in the orders_products table as referenced in the catalog? Shouldn't the products_id and the products_name match the information in the catalog? (snippet previous post)

Are these variations added as attributes??

Hard copy versus online

No, they are individual products.

I see what you are saying. It is the products_id in the products_attributes table that is all 4's because they all have the same attributes. It isn't making reference to the id associated with the product itself.

So there isn't a table that lists both the product id ordered and the name of the person who ordered it. There is only a name associated with an order id, not a product id ordered.