Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    Output Tables from database with tr,td


    Hi! I have a database with Companys and their Workers.. i would like to have this outputted in tables.. How would i do that? thanks!

    ----------------
    customer 1
    ----------------
    - worker 1
    - worker 2
    - worker 3
    ----------------
    customer 2
    ----------------
    - worker 1
    - worker 2
    -----------
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,500
    Rep Power
    594
    Are you asking for HTML help creating tables or PHP? If the former then you are in the wrong forum. If the latter then post what you have done so far and explain where you are having trouble. If you are asking someone to provide the code for you then you need to post this in the 'hire-a-programmer' forum.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    No i'm just wondering what the logical way of building a structure like this is . because you need the names of the first table (customers) on the top and under that the corresponding workers. im not a lazy programmer spamming your forum no worries
  6. #4
  7. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by notflip
    No i'm just wondering what the logical way of building a structure like this is . because you need the names of the first table (customers) on the top and under that the corresponding workers. im not a lazy programmer spamming your forum no worries
    Well you'd query the DB and you'd get an array of results; you then go through each item in the array and display it to screen.

    During this loop, you'd need to check to see if the 'customer' name/ID has changed since the last iteration, and if so, close the old table and start a new one (unless you're on the last customer name/ID, in which case you would just close the table and move on with the rest of the script.
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by badger_fruit
    Well you'd query the DB and you'd get an array of results; you then go through each item in the array and display it to screen.

    During this loop, you'd need to check to see if the 'customer' name/ID has changed since the last iteration, and if so, close the old table and start a new one (unless you're on the last customer name/ID, in which case you would just close the table and move on with the rest of the script.
    Makes sense! Let me try that!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    badger_fruit's solution would work, and there are many more solutions. Sometimes it helps to first store data in a intermediary variable or array before writing to the page.

    Also, make sure you get your database and query right before you worry about presenting data. Probably should be something very similar to the following:
    Code:
    SELECT c.name AS customer,w.name AS worker
    FROM customers AS c
    INNER JOIN customers_has_workers AS chw ON chw.customers_id=w.id
    INNER JOIN workers AS w ON w.id=chw.workers_id
    ORDER BY c.name,w.name
  12. #7
  13. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by notflip
    Makes sense! Let me try that!
    OK, would love to hear how you get on
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by NotionCommotion
    badger_fruit's solution would work, and there are many more solutions. Sometimes it helps to first store data in a intermediary variable or array before writing to the page.

    Also, make sure you get your database and query right before you worry about presenting data. Probably should be something very similar to the following:
    Code:
    SELECT c.name AS customer,w.name AS worker
    FROM customers AS c
    INNER JOIN customers_has_workers AS chw ON chw.customers_id=w.id
    INNER JOIN workers AS w ON w.id=chw.workers_id
    ORDER BY c.name,w.name
    Im fairly new to Mysql, Can't understand much of your query is this like a custom column? can i make a custom column with td seperators? thanks for the help!
  16. #9
  17. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by notflip
    Im fairly new to Mysql, Can't understand much of your query is this like a custom column? can i make a custom column with td seperators? thanks for the help!
    That's just a guess at the query you'd need to use, we don't know anything about your Customers table and your workers table (Norton is presuming that they're two different tables).

    Let's say for example that all your data is in one horrible table like so:-

    MyTable
    ----------------------------------------
    Customer Name | Worker Name
    ----------------------------------------
    Customer1 | Worker1
    Customer1 | Worker2
    Customer2 | Worker1
    Customer2 | Worker2
    Customer3 | Worker1
    Customer3 | Worker2


    To get all that data, you'd use:-

    SELECT * FROM `MyTable`

    That would give you a result set with all the data in, pretty much as displayed.

    Now, imagine that you have two tables:-

    CustomerTable and WorkerTable

    CustomerTable would be something like this:-

    ID | CustomerName
    ----------------------------------------
    1 | Customer1
    2 | Customer2
    3 | Customer3

    WorkerTable would be something like this:-

    ID | WorkerName | CustomerID
    ----------------------------------------
    1 | Worker1 | 1
    2 | Worker2 | 1
    3 | Worker1 | 2
    4 | Worker2 | 2
    5 | Worker1 | 3
    6 | Worker2 | 4


    If you can imagine the column "Customer ID" from the WorkerTable is the "link" between the two.
    So, in this case, your query gets a little more complicated but returns the same as it did on my simple example earlier.

    SELECT * FROM CustomerTable INNER JOIN WorkerTable ON ID = CustomerID

    I hope that that helps

    ps. While I was bored at work I wrote some code which you might find useful?
    Firstly, see the live example --> HERE. If you want the code then, well, I'll post it but want you to try doing that yourself first
    Good luck!!!!!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by badger_fruit
    That's just a guess at the query you'd need to use, we don't know anything about your Customers table and your workers table (Norton is presuming that they're two different tables).

    Let's say for example that all your data is in one horrible table like so:-

    MyTable
    ----------------------------------------
    Customer Name | Worker Name
    ----------------------------------------
    Customer1 | Worker1
    Customer1 | Worker2
    Customer2 | Worker1
    Customer2 | Worker2
    Customer3 | Worker1
    Customer3 | Worker2


    To get all that data, you'd use:-

    SELECT * FROM `MyTable`

    That would give you a result set with all the data in, pretty much as displayed.

    Now, imagine that you have two tables:-

    CustomerTable and WorkerTable

    CustomerTable would be something like this:-

    ID | CustomerName
    ----------------------------------------
    1 | Customer1
    2 | Customer2
    3 | Customer3

    WorkerTable would be something like this:-

    ID | WorkerName | CustomerID
    ----------------------------------------
    1 | Worker1 | 1
    2 | Worker2 | 1
    3 | Worker1 | 2
    4 | Worker2 | 2
    5 | Worker1 | 3
    6 | Worker2 | 4


    If you can imagine the column "Customer ID" from the WorkerTable is the "link" between the two.
    So, in this case, your query gets a little more complicated but returns the same as it did on my simple example earlier.

    SELECT * FROM CustomerTable INNER JOIN WorkerTable ON ID = CustomerID

    I hope that that helps

    ps. While I was bored at work I wrote some code which you might find useful?
    Firstly, see the live example --> HERE. If you want the code then, well, I'll post it but want you to try doing that yourself first
    Good luck!!!!!
    wowie! you're a coding hero! Okay! i'l try myself first! I want to learn this! but don't put the code to far away haha! thank you very much! and damn you're fast at that. it's indeed 2 tables, already with the customer_id in the second table as you said. I can query out the results but it's difficult for me to display it like you did in the last example of the demo! I'll try

    Comments on this post

    • badger_fruit agrees : Coding hero, I like it :D Also, on standby with code but like how you're willing to try yourself first ... gold star for that man!
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    I got it working like this!

    Customer : Customer 1 / Worker : Worker 1
    Customer : Customer 1 / Worker : Worker 2
    Customer : Customer 1 / Worker : Worker 3

    Like in your first example.. Now im wondering what the step is to get the results ordered by customer.. Is it a query thing i need to change? Probably not right?
  22. #12
  23. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by notflip
    I got it working like this!

    Customer : Customer 1 / Worker : Worker 1
    Customer : Customer 1 / Worker : Worker 2
    Customer : Customer 1 / Worker : Worker 3

    Like in your first example.. Now im wondering what the step is to get the results ordered by customer.. Is it a query thing i need to change? Probably not right?
    No, you don't need to edit the query at all, if you have that, then your query is correct. Consider using an "IF" statement somewhere in your loop

    EDIT > My apologies, I mis-read your post, to order your results, yes, add an "ORDER BY" at the end ... eg "ORDER BY CustomerName ASC" (or if you want in reverse order, "ORDER BY CustomerName DESC").

    EDIT 2 > Note that you can order by more than one column
    For example:-

    ORDER BY CustomerName ASC, WorkerName ASC
    ORDER BY CustomerName ASC, WorkerName DESC

    or

    ORDER BY CustomerName DESC, WorkerName ASC
    Last edited by badger_fruit; October 25th, 2012 at 08:28 AM.
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    I managed to do a Break rule every new customer. Still not there! Thanks for the "best-help-ever". Seriously

    PHP Code:
    $count 1;
    while(
    $row mysql_fetch_array($klantenlijst)){
        
    $customerID$row['customer_id'];
        if(
    $customerID!= $count){
            echo 
    "<br />";
            
    $count++;
        }
        echo 
    $row['customer_name']; 
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    You want 3 tables, not 2 tables. It will save you much headache in the long run. Google "normalize database", and ask any specific database questions in the MySQL forum. Or maybe stick with two, learn the hard way like I did, and never forget the lesson!
  28. #15
  29. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by NotionCommotion
    You want 3 tables, not 2 tables. It will save you much headache in the long run. Google "normalize database", and ask any specific database questions in the MySQL forum. Or maybe stick with two, learn the hard way like I did, and never forget the lesson!
    Well im planning on learning it the good way! That's for sure. but for now i need to get this working hehe the normalization is in my bookmarks ready to be read.
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo