/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Joining 2 Tables

Copy linkTweet thisAlerts:
Feb 04.2011

I have 2 tables:

[B]Table 1 – cle[/B]
id | cle | newmember | eppm

[B]Table 2 – cle_signup[/B]
id | fname | lname | cle | year

From the “cle_signup” table, I need distinct first & last names and from the “cle” table, I need the sum of “newmember” OR the sum of “eppm”.

The output is all names from the “cle_signup” table (not distinct names) and the sum is of the eppm only. What am I doing wrong?

[code=php]$query = “SELECT DISTINCT cle_signup.fname AS fname, cle_signup.lname AS lname, cle.newmember AS newmember, cle.eppm AS eppm, cle_signup.year AS year FROM cle JOIN cle_signup ON cle.cle=cle_signup.cle WHERE cle_signup.attended=’x’ ORDER BY cle_signup.lname”;

$result = mysql_query($query) or die(“Couldn’t execute query: “.mysql_error());

while ($rows=mysql_fetch_array($result)) {
$fname = $rows[‘fname’];
$lname = $rows[‘lname’];
$name = “$lname, $fname”;
$year = $rows[‘year’];
$newmember = array(newmember);
$eppm = array(eppm);

echo “<tr><td class=”search”>$name</td>”;

if ($year == “1st or 2nd year” AND $accredited == “Yes”) {
echo “<td>” . array_sum($newmember) . “</td></tr>”;
} else {
echo “<td>” . array_sum($eppm) . “</td></tr>”;
}
}[/code]

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@beylahauthorFeb 04.2011 — Ok I have gotten a bit further in the code - I am able to show the names and their values but I am not able to show the name only once.

I have tried to use an array_unique but cant seem to get it to work.

[code=php]$query = "SELECT cle_signup.fname AS fname, cle_signup.lname AS lname, cle.newmember AS newmember, cle.eppm AS eppm, cle_signup.year AS year, cle.accredited AS accredited FROM cle JOIN cle_signup ON cle.cle=cle_signup.cle WHERE cle_signup.attended='x' ORDER BY cle_signup.lname";

$result = mysql_query($query) or die("Couldn't execute query: ".mysql_error());

while ($rows=mysql_fetch_array($result)) {
$fname = $rows['fname'];
$lname = $rows['lname'];
$name = "$lname, $fname";
$namearray = array($name);
$nameunique = array_unique($namearray);
$year = $rows['year'];
$accredited = $rows['accredited'];
$newmember = $rows['newmember'];
$eppm = $rows['eppm'];
$newmembersum = array($newmember);
$eppmsum = array($eppm);

echo "<tr><td class="search">";
foreach ($nameunique as $value){
echo "$value";
}
echo "</td>";
if ($year == "1st or 2nd year" AND $accredited == "Yes") {
echo "<td>" . array_sum($newmembersum) . "</td></tr>";
} else {
echo "<td>" . array_sum($eppmsum) . "</td></tr>";
}
}[/code]
Copy linkTweet thisAlerts:
@Jarrod1937Feb 04.2011 — Try doing a group by lname. That should limit it to only unique last names, though this can be an issue if you have the chance of having duplicate last names (and being different records you want).

p.s. you can do alias names for your tables so you don't have to type them out all the time. Ex:

select t1.field1, t2.field1 from table1 t1 left join table2 t2 on t1.id=t2.id

p.s.2. I'd have to look at your table structures, but if the condition for which sum to use is based on a table field value, you can then do that within your sql query as well (using SUM, group by, and an IF statement).
Copy linkTweet thisAlerts:
@beylahauthorFeb 04.2011 — thank you for all you help, love the alias thing - i will definitely use that

for your second p.s. I tried to do the sum in the sql but it said i could not mix the two - I would appreciate all the help you can direct my way

for the cle table - the table is a discription of courses offered (title, time date etc) and there are 2 fields that have a number value worth the course value and depending on the year that the person is in - i need to add up all the value of the courses they have been to

does that make sense or do i need to send more info and if so what info
Copy linkTweet thisAlerts:
@Jarrod1937Feb 04.2011 — To mix aggregate functions (sum, count...etc) you have to then use a group by clause. Here is a query to get you started:

SELECT cle_signup.fname AS fname, cle_signup.lname AS lname, [B]IF(cle_signup.year='1st or 2nd year' AND cle.accredited='Yes', SUM(cle.newmember) , SUM(cle.eppm)) as course_value_total[/B], cle.newmember AS newmember, cle.eppm AS eppm, cle_signup.year AS year, cle.accredited AS accredited FROM cle JOIN cle_signup ON cle.cle=cle_signup.cle WHERE cle_signup.attended='x' [B]group by cle.signup.lname[/B] ORDER BY cle_signup.lname

A Mysql IF statement basically works as IF(condition is true, do this, else do this). Though, do the students have a unique id associated with them or are you just relying on their names to be absolutely unique? It would be far better to have an id associated with the student, otherwise you may run into trouble when there are duplicate names for separate students.

Edit: Nevermind, just saw where you got accredited from, post has been updated.
×

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.7,
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: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

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