cute
cute

Reputation: 21

i getting data from a db sql

i getting data from a db and want each row to get data from other table

$query = "SELECT use FROM ur WHERE user='Gue'"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo $row['use']; 
        echo '<br>';
}

now i want that each $row['use'] should get data from another table 'my'

$query = "SELECT SUM(mon) FROM my WHERE use='$use'";  //$use = row['use']

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    echo $row['SUM(mon)'];
}

Is there any solution?

Upvotes: 2

Views: 165

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

$query = "SELECT ur.use, SUM(my.mon) sumMon
          FROM ur
          LEFT JOIN my on my.use=ur.use
          WHERE ur.user='Gue'"; 
$result = mysql_query($query) or die(mysql_error());
$use = -1;
while($row = mysql_fetch_array($result)){
    // process the `use` rows. SUM only returns one row, if you were going for
    // raw records, uncomment the two parts commented out below
//    if ($row['use'] != $use) {
        echo $row['use'];
        echo '<br>';
//        $use = $row['use'];
    }

    // process the `sum` rows, only if there were any records
    echo $row['sumMon'];
}

Upvotes: 1

Drew McGhie
Drew McGhie

Reputation: 1086

What you're looking to do is get a list of 'Use's from the ur table. For each 'use', you're looking to get the sum of the 'mon' field in the my table that has the same use.

Putting your second query inside the while loop will work, but the better idea would be to leverage the power of the sql database you're using to do the filtering for you.

What you're looking for is a join or an in statement (both work, and are, for the most part, functionally equivalent).

The answer given by Joe Stefanelli gives one possible query solution (joining and grouping), another is this:

select my.use, sum(my.mon) as mon_sum
from my
where my.use in (select use from ur where user = 'Gue')
group by my.use

This would also work. The idea is that you group by use and get the sum, and then filter out only the ones you need using the sub-query in the in statement.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

select ur.use, sum(my.mon)
    from ur
        inner join my
            on ur.use = my.use
    where ur.user = 'Gue'
    group by ur.use

Upvotes: 2

Related Questions