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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post

    best way to select data?

    hi guys..

    I have the following data which I need to export (for example)

    Code:
    process_name	2002	2003	2004	2005	2006
    ====================================================
    process1	1.23	4.32	234.21	221.43	665.32
    process2	0.98	0.34	5.32	2.56	7.56
    process3	1.11	2.43	5.41	1.34	6.32
    
    etc etc
    what would be the best way of getting this data from the database? I currently use a massive loop in sql... eg:

    SQL > get the process name
    SQL > get the answer for year 2002
    SQL > get the answer for year 2003
    SQL > get the answer for year 2004
    etc etc
    then move on to the next row (the next process)

    as you can see there is an SQL query for EACH row and EACH element of data. I am wondering if there is a better way to do it? The SQL export is taking FOR EVER at the moment as there can be upwards of 500 rows (processes) which need exporting...

  • #2
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    can you show the code you have so far?

    from what you said, your query is only selecting one column from one row.
    is that correct? If so, why dont you just select more than one column from each row?
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    the code is all over the place... but this is the method its using

    PHP Code:
    $process_Result mysql_query("SELECT * FROM processes");
    while (
    $processes mysql_fetch_array($process_result)) {
       
       
    $years mysql_query("SELECT * FROM YEARS") {

           
    $figures mysql_query("SELECT * FROM figures where processID=$processes['processID'] AND year=$years['year'];

       }
       

    the codes is too big really to post so I've made up the above... it basically gets all the processes, then for each of those, gets the "figures" and each of those is then got for each year...

  • #4
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    are your tables related in any way?
    if so, think about using joins.

    also, you shouldnt do the $years query inside the while loop.
    it gets the same data every time. you should do it once on the outside
    of the loop.
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    can you show the actual layout of your tables?
    no you should not do a query inside a php loop to get info from a second table you should be getting that from a join and then the php loops through all the records. see my link to categories/subcategories in my signature.

    also, if your data looks anything like the above you should most certainly normalize your table. you should NOT have separate columns for individual years. that would cause you to add a new column each year and would also make other queries more difficult to do than would be necessary with normalized data.


  •  

    Posting Permissions

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