i getting data from a db sql

Question

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?

Joe Stefanelli · Accepted Answer · 2011-02-07 20:37:44Z

2

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

answered Feb 7, 2011 at 20:37

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cute Over a year ago

will you explain it a bit more?

KM. Over a year ago

+1, this one query is way more efficient than the OP's design! Why get all the rows you need to SUM then individually get the SUMs one at a time, when you can just get ALL the SUMs at once.

Joe Stefanelli Over a year ago

@cute: The inner join links the ur table with the my table ON the common column use. The group by produces a sum for each distinct ur.use. Anything else you need clarification on?

cute Over a year ago

can you make it along with php?

RichardTheKiwi Over a year ago

Incorrect optimization. INNER JOIN means that any USE without a MY record goes missing

|

RichardTheKiwi · Accepted Answer · 2011-02-07 20:54:55Z

1

$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'];
}

answered Feb 7, 2011 at 20:54

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

1 Comment

KM. Over a year ago

+1, good idea on LEFT JOIN, but it depends on the OP intended usage

Drew McGhie · Accepted Answer · 2011-02-07 20:48:09Z

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.

Collectives™ on Stack Overflow

i getting data from a db sql

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related