Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Dec 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How best to store this table in MySQL and print it with PHP?

    I've searched the web trying to find examples of how to store tabular data in MySQL and loop through the rows to assemble a table using PHP, but no luck.

    Here is a sample of my Excel file that I want to insert into MySQL. I then want to display it with PHP/HTML in its tabular form.

    Keep in mind I have a lot of tables exactly like this for different countries so I am looking for a good MySQL schema to efficiently store this data, then have PHP make use of it by looping through the rows and id's to pull the correct data.

    1) How would you store this in MySQL?
    2) How would you loop through the database with PHP to build columns?


  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,500
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    The first step is to create the MySQL database.

    You'll have to think about the structure ... because you could do it several ways.

    Here is one example:

    UNIQUE PRIMARY ID | COUNTRY | LOCATION | SURGERIES BY TYPE | SURGERY CODE | YEAR | QTY

    In my example, you use one table that contains information from all of the spreadsheets
    that you "save". By doing it this way, you can do searches/sorting by any of the columns.

    You don't need to save the growth rate, because that can be calculated by any
    years you select, and by any country ... the query possibilities are endless.

    Likewise, you don't need "totals", because you may query the totals between one or more locations.

    ==============

    The Excel part is like this ...
    You save each Excel file as a "comma delimited" file (CSV).
    Then, you have a PHP script where the user selects and uploads that CSV file.
    The PHP script breaks it apart and adds it to the MySQL table.

    Remember, all data goes into the same MySQL table, so you can compare countries,
    locations (perhaps you have hospital names), and you can give each surgery type
    a name and some sort of code. That gives you more "grouping" possibilities.

    With the database, you can use Google Charts to make great pie charts, etc.

    Decide what you want to do in regards to the MySQL table structure.



    .
    Last edited by mlseim; 12-07-2010 at 10:30 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •