SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is it possible to query a query?

    I have a query which brings back a set of test scores for a student from a table that holds the scores for a whole class. Can I treat the $Result set that the query produces as a table and write things like:

    $Result2="Select sum(testscores) AS ScoreTotal FROM $Result";

    this could be rubbish I don't know?

    I would like to do this as I want to first of all print all the data brought back from the first query and then process that data again to produce a total score for the student. I could just write a whole new query but would my suggestion be more efficient?

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't do it exactly like that, because you're trying to perform a SQL query on a set of results, not on a database. You could certainly write some PHP code to calculate all of the values you need - you've already selected the data from the database, so it's just a case of manipulating it.

    If you're planning on writing the information out, that implies you're looping through the results to do this. You could simply use a variable to do the sum that you need, adding to it as you loop through the results. After you've written all the results out, this variable would contain the sum of all the records, so there'd be no need to do another query or anything.

    In general, calls to the database are inefficient, so it's usually best to avoid it when there's a simple alternative.

    Hope that made some sense .
    Nick Wilson [ - email - ]

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    That makes a lot of sense thankyou. I think I know what to do now.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL does its job well - but it is not a proper relational database management system and this is one area where it fails. If you were using a proper RDMS that supported ANSI SQL92 then you could perform nested queries in the one sql statement, as you want to do (query the results of a query). That's the bad news.

    The good news is that there is most often a way around these limitations. Even though MySQL doesn't implement a very full version of the SQL language - it is still pretty good at most things. So if you tell us what your table/s schemas are and the results you want, someone will no doubt be able to show you how to write a query for what you want.


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
  •