SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ripcurlksm View Post
    1) How would you store this in MySQL?
    1 way is store the data in a table with columns called the headings in your image, plus add a column for the data's country code.

    Quote Originally Posted by ripcurlksm View Post
    2) How would you loop through the database with PHP to build columns?
    this tutorial might help

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Table: surgeries
    Columns: type, year, numberofsurgeries

    Get the data from the table ordered by type and year, and create your table. Growth rate and total row will be calculated in PHP.

  4. #4
    SitePoint Addict kivison's Avatar
    Join Date
    Dec 2004
    Location
    Whitley Bay, Tyne & Wear, UK
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I concur with Kalon also you may want to get a great book by Kevin Yank of Sitepoint Database driven website. It was a great start for me Christmas 4 years ago now.

    HEre Get the book

  5. #5
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, data that can be calculated from stored data should not be stored in the database itself.

    another option to calculating it in the application (php or whatever) is to calculate the "column" as part of the sql query retrieving the records from the database

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Yet another way to do the summaries might be to do it on the client, I've got column (vertical) summaries working like that in the past, but not row (horizontal) totals at the same time I must admit (though I cannot easily see what "growth rate" is deduced from)

    Seeing the totals would then be dependent upon JS being turned on of course.


Bookmarks

Posting Permissions

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