how to combine two different tables data into a single json object

iam making a json object by the following query

    $arr = array();
        $qs=qq("select main_cat_id,main_cat_name,id,开发者_Go百科name,cat_id,cat_id_main from main,scheme where main_cat_name='xyz' and cat_id_main=main_cat_id");
        while($obj = mysql_fetch_object($qs))
        {
            $arr[] = $obj;
        }
        $total=sizeof($arr);
        $jsn_obj='{"scheme":'.json_encode($arr).',"totalrow":"'.$total.'"}';

 // this is a mock example

Now iam having another query

Query 2:

    $q=qq("select main_cat_id,main_cat_name,id,name,cat_id,cat_id_main from main,scheme where main_cat_name='xyz' and cat_id_main=main_cat_id order by scheme_1_year DESC");
    while($r=mysql_fetch_array($q))
    {
        $cats_id=$r['cat_id'];
        if($cats_id!=0)
        {
            $qw=qq("select cat_name from category where cat_id='$cats_id'");  // Query 3
            $rw=mysql_fetch_array($qw);
            $catm_name=$rw['cat_name'];
        }
    }
// this is a mock example

both are doing the same thing but the later is calculating some cats name as well.This category table cant be merged with the earlier two tables so i have to calculate this cat_name with another query.

Now i want a json object that should be having all the entries of Query 2 and respective Query 3 result. sumthing like this (if explained in simple words): query 2 result[0] plus query 3 result[0],query 2 result[1] plus query 3 result[1]

How can i club these two results together in a json object. Plz guide!!

---

It looks like you have cat_id in the table category and in either main or scheme. When using a column name that occurs in two different tables of a query, you have to identify which table you're talking about when you use the column name.

I don't have the schema to know which columns are from which table, but here is a guess at how to join them. (Note: I added spaces after the commas to make it easier to read)

$q=qq("select main_cat_id, main_cat_name, id, name, scheme.cat_id, cat_id_main, category.cat_name from main, scheme, category where main_cat_name='xyz' and cat_id_main=main_cat_id and scheme.cat_id=category.cat_id order by scheme_1_year DESC");

Alternatively, you can use table aliases.

$q=qq("select main_cat_id, main_cat_name, id, name, s.cat_id, cat_id_main, c.cat_name from main m, scheme s, category c where main_cat_name='xyz' and cat_id_main=main_cat_id and s.cat_id=c.cat_id order by scheme_1_year DESC");

If cat_id from the original qq is in main instead of scheme, then you will have to adjust the query accordingly. This can still be done with or without table aliases.

$q=qq("select main_cat_id, main_cat_name, id, name, main.cat_id, cat_id_main, category.cat_name from main, scheme, category where main_cat_name='xyz' and cat_id_main=main_cat_id and main.cat_id=category.cat_id order by scheme_1_year DESC");
$q=qq("select main_cat_id, main_cat_name, id, name, m.cat_id, cat_id_main, c.cat_name from main m, scheme s, category c where main_cat_name='xyz' and cat_id_main=main_cat_id and .cat_id=c.cat_id order by scheme_1_year DESC");

An additional note, if you want a single query to select two columns from two different tables and the columns have the same name, then you should use column alias. (e.g select ... category.cat_name as category_cat_name from main, ...) It looks like this ability might simplify your schemas (or at least column names) and make them easier to follow.