/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Joining 2 name columns

Copy linkTweet thisAlerts:
May 25.2011

I have a table with: name1, name2, hours1, hours2. A person can be in either name1 or name2 column and I am trying to total their hours and it does not matter if they are list in name1 or name2. I get their names but “0” for the totals. What am I doing wrong in the sumhours?

[code=php]$query=”
SELECT DISTINCT name1 AS name FROM lss
UNION
SELECT DISTINCT name2 AS name FROM lss WHERE name2!=”
UNION
SELECT SUM(hours1) AS sumhours1 FROM lss
UNION
SELECT SUM(hours2) AS sumhours2 FROM lss”;
$result = mysql_query($query) or die(“Couldn’t execute query: “.mysql_error());
while ($rows=mysql_fetch_array($result)) {
$name=$rows[‘name’];
$sumhours1=$rows[‘sumhours1’];
$sumhours2=$rows[‘sumhours2’];
$hours=($sumhours1+$sumhours2);

echo “<tr><td>$name</td>”;
echo “<td>$hours</td>”;
}
[/code]

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@qrizlyMay 25.2011 — Try this:

[code=php]$query="
SELECT SUM(hours1) + SUM(hours2) as total_hours, name
FROM lss
GROUP BY name";[/code]
Copy linkTweet thisAlerts:
@beylahauthorMay 26.2011 — I have tried this code and get the following error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in totals.php on line 113

Couldn't execute query:


[code=php]<?php
$query1="
SELECT DISTINCT name1 AS name FROM lss
UNION
SELECT DISTINCT name2 AS name FROM lss WHERE name2!=''
";
$result = mysql_query($query1) or die("Couldn't execute query: ".mysql_error());
while ($rows=mysql_fetch_array($result)) {
$name=$rows['name'];
$query2="SELECT SUM(hours1) + SUM(hours2) AS totalhours, name FROM lss GROUP BY name";
(line113)$rows=mysql_fetch_assoc($query2) or die("Couldn't execute query: ".mysql_error());
$totalhours=$rows['totalhours'];

echo "<tr><td>$name</td>";
echo "<td>$totalhours</td>";
echo "<td>$percent<span style="color:#999999">&#37;</span></td></tr>";
}
?>[/code]
Copy linkTweet thisAlerts:
@beylahauthorMay 26.2011 — I have found the answer:

[code=php]<?php
$query1="
SELECT DISTINCT name1 AS name FROM lss
UNION
SELECT DISTINCT name2 AS name FROM lss WHERE name2!=''
";
$result = mysql_query($query1) or die("Couldn't execute query: ".mysql_error());
while ($rows=mysql_fetch_array($result)) {
$name=$rows['name'];
$query2=mysql_query("SELECT SUM(hours1) AS sumhours1, SUM(hours2) AS sumhours2 FROM lss WHERE name1='$name' OR name2='$name'");
$rows=mysql_fetch_assoc($query2) or die("Couldn't execute query: ".mysql_error());
$sumhours1=$rows['sumhours1'];
$sumhours2=$rows['sumhours2'];
$totalhours=($sumhours1+$sumhours2);

echo "<tr><td>$name</td>";
echo "<td>$totalhours</td>";
echo "<td>$percent<span style="color:#999999">&#37;</span></td></tr>";
}
?>
[/code]
Copy linkTweet thisAlerts:
@beylahauthorMay 26.2011 — Sorry this is not resolved - i have totals but not correct totals

it seems that all but one of the totals is off by 4.2 (the other is by 6.1)
Copy linkTweet thisAlerts:
@beylahauthorMay 26.2011 — I have been able to get correct totals but only for the first entry - it will not do the while loop

[code=php]<?php
$query1="
SELECT DISTINCT name1 AS name FROM lss
UNION
SELECT DISTINCT name2 AS name FROM lss WHERE name2!=''
";
$result = mysql_query($query1) or die("Couldn't execute query1: ".mysql_error());
while ($rows=mysql_fetch_array($result)) {
$name=$rows['name'];

$query2="SELECT SUM(hours1) AS sumhours1, name1 FROM lss WHERE name1='$name' GROUP BY name1";
$result = mysql_query($query2) or die("Couldn't execute query1: ".mysql_error());
while ($rows=mysql_fetch_array($result)) {
$sumhours1=$rows['sumhours1'];
}
$query3="SELECT SUM(hours2) AS sumhours2, name2 FROM lss WHERE name2='$name' GROUP BY name2";
$result = mysql_query($query3) or die("Couldn't execute query1: ".mysql_error());
while ($rows=mysql_fetch_array($result)) {
$sumhours2=$rows['sumhours2'];
}

$totalhours=($sumhours1+$sumhours2);

echo "<tr><td>$name</td>";
echo "<td>$totalhours</td>";
echo "<td>$percent<span style="color:#999999">&#37;</span></td></tr>";
}
?> [/code]
Copy linkTweet thisAlerts:
@beylahauthorMay 26.2011 — OK I have fixed it:

[code=php]<?php
$query1="
SELECT DISTINCT name1 AS name FROM lss
UNION
SELECT DISTINCT name2 AS name FROM lss WHERE name2!=''
";
$result1 = mysql_query($query1) or die("Couldn't execute query1: ".mysql_error());
while ($rows=mysql_fetch_array($result1)) {
$name=$rows['name'];

$query2=mysql_query("SELECT SUM(hours1) AS sumhours1, name1 FROM lss WHERE name1='$name' GROUP BY name1");
$rows=mysql_fetch_assoc($query2);
$sumhours1=$rows['sumhours1'];

$query3=mysql_query("SELECT SUM(hours2) AS sumhours2, name2 FROM lss WHERE name2='$name' GROUP BY name2");
$rows=mysql_fetch_assoc($query3);
$sumhours2=$rows['sumhours2'];

$totalhours=($sumhours1+$sumhours2);
echo "<tr><td>$name</td>";
echo "<td>$totalhours</td>";
echo "<td>$percent<span style="color:#999999">&#37;</span></td></tr>";
}
?>
[/code]
×

Success!

Help @beylah spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.2,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...