How do I get the result of two select statement in a single row?

Question

I am using two select statments in a php page as follows:

$num1 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen1' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subind1' ");

and

$num2 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen2' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subind2' ");

at present the result is displayed using separate while loops as follows

while($nval=  mysql_fetch_array($num1))
{
    $vv=$nval['SId'];

    $result = mysql_query("SELECT * FROM mtable WHERE SId='$vv'");

    while($row = mysql_fetch_array($result))
    {
        print "<tr height='18'>";
        print "<td width=200 align=left>" . $row['Country'] . "</td>";
        print "<td width=70 align=center>" . $row['MidEstimate'] . "</td>";
        print "<td width=70 align=center>" . $row['LowEstimate'] . "</td>";
        print "<td width=70 align=center>" . $row['HighEstimate'] . "</td>";
        print "<td width=89 align=center>" . $row['StudyLocation'] . "</td>";
        print "<td width=139 align=center>" . $row['ReferenceID'] . "</td>";
        print "<td width=89 align=center>" . $row['Quality'] . "</td>";
        print "<td width=89 align=center>" . $row['Relevance'] . "</td>";
        print "<td width=61><a href='/sites/default/files/popupboxCD.php?SId=$vv' onClick='openpopup(this.href);return false;'>".$row['Info']."</a></td>";
        print "</tr>";
        print "<tr height='1'><td colspan='9' style='padding:0'><hr size='0.5' color='#CCCCCC' noshade='noshade' /></td></tr>";
    }
}

and

$num2 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen2' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subind2' ");
print $num2;

print "<table border='0' width='875' align='center'>

<tr><td colspan=5 height=10></td></tr>
<tr>
    <td width='200' bgcolor='#99CCF5' align='center'><b>Country</b></td>
    <td width='70' bgcolor='#99CCF5' align='center'><b>Mid</b></td>
    <td width='70' bgcolor='#99CCF5' align='center'><b>Low</b></td>
    <td width='70' bgcolor='#99CCF5' align='center'><b>High</b></td>
    <td width='89' bgcolor='#99CCF5' align='center'><b>Study Location</b></td>
    <td width='139' bgcolor='#99CCF5' align='center'><b>Reference</b></td>
    <td width='89' bgcolor='#99CCF5' align='center'><b>Quality</b></td>
    <td width='89' bgcolor='#99CCF5' align='center'><b>Relevance</b></td>
    <td width='61' ></td>
</tr>";

while($nval=  mysql_fetch_array($num2))
{
    $vv=$nval['SId'];

    $result = mysql_query("SELECT * FROM mtable WHERE SId='$vv'");

    while($row = mysql_fetch_array($result))
    {
        print "<tr height='18'>";
        print "<td width=200 align=left>" . $row['Country'] . "</td>";
        print "<td width=70 align=center>" . $row['MidEstimate'] . "</td>";
        print "<td width=70 align=center>" . $row['LowEstimate'] . "</td>";
        print "<td width=70 align=center>" . $row['HighEstimate'] . "</td>";
        print "<td width=89 align=center>" . $row['StudyLocation'] . "</td>";
        print "<td width=139 align=center>" . $row['ReferenceID'] . "</td>";
        print "<td width=89 align=center>" . $row['Quality'] . "</td>";
        print "<td width=89 align=center>" . $row['Relevance'] . "</td>";
        print "<td width=61><a href='/sites/default/files/popupboxCD.php?SId=$vv' onClick='openpopup(this.href);return false;'>".$row['Info']."</a></td>";

        print "</tr>";
        print "<tr height='1'><td colspan='9' style='padding:0'><hr size='0.5' color='#CCCCCC' noshade='noshade' /></td></tr>";
    }
}

I would like to know if there's any way to merge these two table into single table?

The result should look like this:

Value1(common to both query., eg.country), Value2(from 1st query), Value2(from 2nd query)

I got only a single table in mysql which both query show results.

I am sorry my question confuses all. I am Lotus Notes Developer and not a PHP. So I am only a novice user in sql.

Here I repeat my query again...

At present I got two tables display result from two queries from a single table. I want to mix these result in a single table ?

That is my new table must have three columns. First with common value from both tabels and second column from first table second column and third column from second table second column. Second and third column in new table is dispalying data from same column from the main mysql data.

Hope this makes much better, thanx for all the suggestion. I will try this myself and if get succeed I will post it here.

http://stackoverflow.com/questions/6394240/multiple-query-data-into-single-html-table-php-mysql-array-not-printing-in-co — Gopipuli, Jun 14 '12 at 08:45

score 4 · Answer 1 · answered Jun 14 '11 at 09:33

4

with UNION

( SELECT * FROM mtable WHERE SId= ? ) UNION (SELECT * FROM mtable WHERE SId= ?)

If i understood correctly what you meant Also note in this case for this simple condition you can simply do:

SELECT * FROM mtable WHERE SId= ? OR SId = ?

answered Jun 14 '11 at 09:33

dynamic

46,985
55
154
231

@hakre: Yes, ideally you use `UNION` when you want to retrieve rows from two tables that have no relationship. – Salman A Jun 14 '11 at 10:10
And in this case wouldn't it double the data? – hakre Jun 14 '11 at 10:12
yes I tried this but the reslut display is not the way I wanted. It will display like as follows country, midestimate (from 1st query) country, midestimate (from 2nd query) country, midestimate (from 1st query) country, midestimate (from 2nd query) and so on..... my requirement is like as follow country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) ..... thanx for the reply. – Gopipuli Jun 14 '11 at 10:32

score 2 · Answer 2 · answered Jun 14 '11 at 11:27

I'm sorry but I can't follow what kind of output / result you are looking for. I'm unable to interpret what you mean by "result should come in a single table(php page) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) and so on"

Anyways, I think you don't need to run the first SELECT statement to retrieve mtable.SId and then other multiple SELECTs (within loop) to read other columns from mtable.

I think that the below single query should work as it merges your two queries and returns a single resultset:

SELECT * FROM mtable 
WHERE (Pathogen='$pathogen1' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subind1')
OR (Pathogen='$pathogen2' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subind2');

+1; this is the only answer so far that answers the question properly. You just need to OR the where clauses into a single SELECT statement. — tenfour, Jun 14 '11 at 11:33

hakre · Answer 3 · 2011-06-14T11:44:40.453

Your two queries:

$num1 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen1' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subind1' ");
$num2 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen2' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subind2' ");

They only differ in the WHERE part. You can combine those by grouping them together with a OR:

mysql_query("
SELECT SId FROM mtable 
  WHERE
    (
      Pathogen='$pathogen1' && Topic='$topic1' 
      && Indicator='$ind1' && IndicatorSubGroup='$subind1'
    )
    OR
    (
      Pathogen='$pathogen2' && Topic='$topic2' 
      && Indicator='$ind2' && IndicatorSubGroup='$subind2'
    )
");

The WHERE part is an expression which is documented in the Mysql manual as well: Expression Syntax. There are multiple ways to express what you're looking for, the example above is only one way.

If you need more general information about the SELECT statement, it is documented here: SELECT Syntax.

I first thought this sounds like a job for a JOIN but then I saw you only have one table called mtable only, so a join or union is not necessary at all, you can write that with a simple select query over the one table:

SELECT * FROM mtable WHERE Pathogen='$pathogen2' AND Topic='$topic2' AND Indicator='$ind2' AND IndicatorSubGroup='$subind2'

instead. It should return all the records you're interested in.

Your query

mtable contain columns like country, midestimate, etc.. two select statement search the same table with different parameters. and result should come in a single table(php page) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) and so on. thanx for the reply – Gopipuli 42 mins ago

You asked about to combine those two queries into one. That's possible. You write you have two select statements. I try to gather them from your question:

$num1 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen1' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subind1' ");
`$num2 = mysql_query("SELECT SId FROM mtable WHERE Pathogen='$pathogen2' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subind2' ");

Those two queries have some part `

In case you need to take the data from two or more tables:

As written, first is sounded like a JOIN to me. With joins you can combine data from multiple tables in the result based on references between the two. The references need to be formulated in the query.

A more general explanation is on Wikipedia, the Mysql manual has the syntax.

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a

This selects all fields from both table t1 and table t2 for every product in which t2.a equals t1.a.

You can add a where clause at the end if it and naturally you can replace * with only those column names you want to return. As you now have two tables, you need to prefix the columns with it's table prefix if you have duplicate column names with the tables, otherwise Mysql would not know which one to choose.

mtable contain columns like country, midestimate, etc.. two select statement search the same table with different parameters. and result should come in a single table(php page) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) country, midestimate (from 1st query), midestimate (from 2nd query) and so on. thanx for the reply — Gopipuli, Jun 14 '11 at 10:41
Gopipuli, I actually had problems to understand your question. I have no clue about your level of knowledge about SQL, so probably the updated answer will now be about what you try to find out. Probably this is what you're looking for. — hakre, Jun 14 '11 at 11:41
I think this is something close to my requirement [linl]http://stackoverflow.com/questions/3613498/multiple-query-data-into-single-html-table-php-mysql[/link] — Gopipuli, Jun 15 '11 at 05:45
Hi hakre, thanx for the support. "Multiple query data into single HTML Table (PHP, MySQL)" is what I tried to express here. Anyway solution is not yet found... — Gopipuli, Jun 15 '11 at 10:28
Gopipuli: Yeah. I love your awesome and very concrete description and it must be something that hard to get what you ask for that nobody here can help you. Keep up the good work. And while waiting, have you scanned through the MySQL manual lately to learn a bit about SQL? — hakre, Jun 15 '11 at 10:31
Hi hakre, sure I will do. earlier I done a crash course in sql and It does not frequently come to my works. Am just a IBM LDN 7 certified developer,that's all. I got my thing working a little and the code help I got from this awesome site. thank you once again and thanks for the patience in reply. — Gopipuli, Jun 15 '11 at 11:41

How do I get the result of two select statement in a single row?

3 Answers3

Your query

In case you need to take the data from two or more tables:

Linked