Web Analytics Made Easy -
StatCounter PHP Invoicing System - CodingForum

Announcement

Collapse
No announcement yet.

PHP Invoicing System

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • PHP Invoicing System

    Hi there,

    I am researching the best way to implement an invoicing system in PHP and MySQL. Preset items will be stored in a db table, and each invoice will be a row in the table as well. Now, I cannot decide the best way to store the items associated with each invoice. I cannot simply link it to the invoice table (ie have a separate table invoice_items that has invoice_id and item_id columns) because the items can come and go as well as change price. My first thought is to create an xml file with the data in it and save that in a BLOB field, as well as on file somewhere else for a backup. What do you think? Is this efficient?

    Do you guys have any other ideas.. right now I have nothing planned as to the layout/setup of my invoicing system so its very flexible.

    Thanks in advance,

    FuZion

  • #2
    Originally posted by FuZion View Post
    I cannot simply link it to the invoice table (ie have a separate table invoice_items that has invoice_id and item_id columns) because the items can come and go as well as change price.
    This is were PHP/MySQL comes in very handy. You would have a status column in the database with a default value of "1". 1 means the item is current and can be displayed, 0 means the item is no longer available and will not be displayed but still remains in the database for future use. Price change is no problem.

    I don't know much about XML but from a little playing around with it I have come to the conclusion it's not very good for database, especially if you have a column that requires a long description.


    ----
    Leonard Whistler

    Comment


    • #3
      Ok, I'm glad you brought this up because this was one of my original ideas. The problem with this method rests with the fact that the prices of my products change approx. every 6 months, and these price changes will consist of several thousand products. So, after a few years my database will have 30000 records of the same data, with different prices. Is this really practical?

      On the XML note, I just want to clarify.. I will only store one invoice in each XML file, which will then be saved in a BLOB field and on disk. Is this practical?

      Thank you!

      Comment


      • #4
        Originally posted by FuZion View Post
        So, after a few years my database will have 30000 records of the same data, with different prices. Is this really practical?
        MySQL can, quite comfortably, handle far more records than 30k - millions. The important concept is properly indexing your tables and writing efficient queries.
        John

        Comment


        • #5
          Originally posted by FuZion View Post
          Ok, I'm glad you brought this up because this was one of my original ideas. The problem with this method rests with the fact that the prices of my products change approx. every 6 months, and these price changes will consist of several thousand products.
          As far as the prices is concerned I would update the prices when needed, over write the old price with the new price. This would of course be manual data entry and the better the admin panel the easier it would be.

          Originally posted by FuZion View Post
          On the XML note, I just want to clarify.. I will only store one invoice in each XML file, which will then be saved in a BLOB field and on disk. Is this practical?
          I recommend the PHP/MySQL to PDF solution for invoices. It's a big step up but very professional looking invoices can be generated with your MySQL database and PHP. More info: http://www.fpdf.org


          -----------
          Leonard Whistler

          Comment


          • #6
            Ok, so then you too agree that keeping everything within MySQL is the way to go? May I ask what your comments are against the XML method?

            Also, do you have any reading material on indexing/efficient queries that would help me out with this?

            Thank you for your help!

            Comment


            • #7
              Another thought on the table design:

              You might want to consider splitting your items table into 2 tables. The primary items table consisting of fields such as 'description', etc. The secondary items table consisting of a foreign key reference to the item in the primary table and the price. This way, the only table that will grow every six months is the items price table - a much smaller table. This would require the use of a more complex query to join the two tables, but can be done in one query.

              Your best bet for MySQL questions is the MySQL forum.
              John

              Comment


              • #8
                Originally posted by FuZion View Post
                May I ask what your comments are against the XML method
                No need to store a XML file with data that's already in your database. You can customize a SQL query to output an invoice in HTML, PDF, CSV , XML , etc.









                -----------
                Leonard Whistler

                Comment

                Working...
                X