Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11
  1. #1
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005

    Help on descisson

    Hey guys i am kind of confused on something. So basically i have a web application in which i wan to create a section to fill receipt infomation, i need some infomation such as customer id customer name, customer address, customer tel, product id, sales agent id, order number and order date. The customer id, ordern number,order date ,agent id and product date will be store in one table.

    What i am confused about is that will it be better to generate the customer id using php mt_rand() function based on some criterias so that this info gets directly to the order table without any further queries or generate this id with mysql and query the database again to get this info and store in the order table (and i will need to store the customer id in who bought a specific product in the orders table).

    Which is better or what can i do that's better??

    Sent from my 404SC using Tapatalk

  2.  

  3. #2
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,238
    Member #
    5580
    Liked
    647 times
    The topic here is "normalization". You can Google "MySQL normalization"

    I'm not really an expert at MySQL, but here is my take.

    You'll have a table for the customers:
    customer id, customer name, customer address, customer tel

    You'll have a table for the agents:
    agent id, agent name, agent tel

    You'll have a table for the products:
    product id, product desc, product price

    Finally, you'll have a table for the "transactions":
    order no, order date, product id, customer id, agent id, order qty

    Each time an item is ordered, a row gets added to "transactions". This table is the most used one and has the most rows. The other tables only get updated when customers are added/modified, agents are added/modified, or products

    You can see that in "transactions", you have common column names as in the other tables. That is what allows you to JOIN them in a variety of ways.

    This whole system becomes efficient because you are not reading and writing redundant information to multiple tables. The customer id will always be the same even if the customer's phone number or address is changed. Changing them won't affect any other table.

    You'll want to really study JOIN functions with MySQL,
    ...and you should be using PDO: https://phpdelusions.net/pdo

    In the least use MySQLi ... instead of MySQL


  4. #3
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    Thanks. I was thing of using transactions in mysql. Such that when i get the customer info into the data and mysql automatically creates an id (wc i finally decided to adopt) , i will read this data inorder to store into the orders (transacttion) table. Cause my problem was if u should generate the IDs my self using php and store in the database or automatically generate withing the database

    Sent from my 404SC using Tapatalk

  5. #4
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    And am actually using mysqli through phpmyadmin

    Sent from my 404SC using Tapatalk

  6. #5
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    I created a contraint between my orders (transaction) table and the customer table, agent and product. So that if u possibly (ever) change the customer id it will change every where but if u delete a customer, it wont work as we cannot have an order which doesn't belong to any customer

    Sent from my 404SC using Tapatalk

  7. #6
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,238
    Member #
    5580
    Liked
    647 times
    I think if you had a customer id of 123, and you wanted to remove that customer, by using JOIN it would remove all rows containing the customer id 123 from all tables you specified in the JOIN. That would be done with 1 query.


  8. #7
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    What if i go directly to the database and delete?? With contrains u have to delete the data in a specfic order.

    Sent from my 404SC using Tapatalk

  9. #8
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    First from the orders table then either the customer table or product

    Sent from my 404SC using Tapatalk

  10. #9
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,238
    Member #
    5580
    Liked
    647 times
    If you go directly to the database and manually browse tables and delete things, you are right ... you have to do it all by hand.

    If you know the queries to do things, like the queries you do with PHP, you can run queries from the phpmyadmin ... where I think you are doing things manually?


  11. #10
    Junior Member
    Join Date
    Jan 2017
    Posts
    7
    Member #
    56005
    Am running the queries manually through phpmyadmin. But still got a problem

    Sent from my 404SC using Tapatalk


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 01:44 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com