Solved

Combining query results

Posted on 2004-11-01
221 Views
Last Modified: 2013-12-12
Hello,

I recently got assistance in the mysql forum on how to build a report that shows forum usage statistics over a period of 12 months.  So now I have 2 queries that give me the data, but to output the report properly I need this data to be in 1 result set.  So my question is if I have 2 queries, each returning 2 columns- how do I combine the results into a result set with 3 columns when 1 column is common between the 2 queries?

-------------------------
Month | Topics
==============
Aug    |  14
Sept   |   0
Oct     |  34

AND

-------------------------
Month | Posts
==============
Aug    |   5
Sept   |   17
Oct     |  20

INTO

-----------------------------
Month | Posts  |  Topics
================
Aug    |   5    |   14
Sept   |   17   |   0
Oct     |  20   |   34

Any ideas?
0
Question by:Mr_Shadow
    6 Comments
     
    LVL 2

    Expert Comment

    by:Silversoft
    Hi

    Is this what you looking for?

    SELECT Topics, Posts FROM tbl_topic, tbl_post GROUP BY Month
    0
     

    Author Comment

    by:Mr_Shadow
    well not really, im not looking to alter my queries, lets say i have query 1 which produces columns A and B.  I have query 2 which produces columns A and C.  I need 1 query object that has A, B, and C.  The question is is there any way to manipulate queries in this way in PHP.  I know the obvious answer is 'make the database do it in one query', well assuming i absolutely cannot, is there any way php can help.
    0
     
    LVL 2

    Expert Comment

    by:Silversoft
    Hmm... I think the only way to combine the query result of A,B and C is to alter your 2 queries and make it into 1 query like how i did it in the first post. If you would like to combine the two using PHP, I must say it's a mission, as you need to synchronize the rows so they match up. Another thing is that, you won't be able to do an ORDER BY if you have 2 queries forced into 1 query result, I suppose you can, but with extra extra coding and difficult to track errors.
    0
     
    LVL 1

    Accepted Solution

    by:

    $res=mysql_query(query1)
    for($k=0; $k < mysql_num_rows($res); $k++){
            @extract(mysql_fetch_array($res));
            $res2=mysql_query(SELECT TOPICS FROM query2 WHERE Month='$Month')
    @extract(mysql_fetch_array($res));



    0
     
    LVL 1

    Expert Comment

    by:InvolveIT
    I wasn't ready there
    But I think the idea is clear, just query for your topics within the loop where you get your results from query1.
    Then you have all the info together to list in a single line.
    If that's what you are looking for.
    0
     

    Author Comment

    by:Mr_Shadow
    sorry for my unresponsiveness, i have since hired a programmer to get this information from 1 query- i have sent him InvolveIT's response as a possible way to do it, since he also says it is impossible with 1 query.  thanks
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    SQL injection vulnerabilities have been described as one of the most serious threats for Web applications. Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases. Because…
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now