While Statement - PHP & MySQL

AdWarm · March 17, 2011, 12:02pm

Hi Guys,

I have a little problem which i know exactly why its happening but cant think of a way of getting round the problem, i have been learning PHP for just 3 days and i think im getting good at it and have nearly finished building my first PHP project (an Affiliate Network involving click tracking, lead tracking, cookies, subid tracking and more). I’m currently coding the Statistics page and below is some the code I’m using:

<?
$query = "SELECT

    ProgramName  AS 'name'

  , COUNT(*)     AS 'count'

  , SUM(Payout)  AS 'payout'

FROM

  affleads

WHERE

  AffID = '%s'

AND

  Date = '$datenow'

AND

  Status = 'Pending'

GROUP BY

  ProgramName;

";


$query2 = "SELECT

    ProgramName  AS 'name2'

  , COUNT(*)     AS 'count2'

  , SUM(Payout)  AS 'payout2'

FROM

  affleads

WHERE

  AffID = '%s'

AND

  Date = '$datenow'

AND

  Status = 'Approved'

GROUP BY

  ProgramName;

";

$result2 = mysql_query(sprintf(
	$query2,
	mysql_real_escape_string($affid)
));


while($row = mysql_fetch_assoc($result)){

?>

<tr class="campaignheader">
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row['name']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row['count']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row2['count2']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader">£</td>
<td bgcolor="#FFFFFF" class="campaignheader">£<? printf($row['payout']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader">£<? printf($row2['payout2']); ?></td>
</tr>

<? 
}
mysql_close();
?>

Now here is the output from using the PHP While and the SQL from Query:

Now I’m trying to also add values into the table from Query2 and this is the code i have used:


$result = mysql_query(sprintf(
	$query,
	mysql_real_escape_string($affid)
));

$result2 = mysql_query(sprintf(
	$query2,
	mysql_real_escape_string($affid)
));

while($row2 = mysql_fetch_assoc($result2)){

while($row = mysql_fetch_assoc($result)){

<tr class="campaignheader">
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row['name']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row['count']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader"><? printf($row2['count2']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader">£</td>
<td bgcolor="#FFFFFF" class="campaignheader">£<? printf($row['payout']); ?></td>
<td bgcolor="#FFFFFF" class="campaignheader">£<? printf($row2['payout2']); ?></td>
</tr>

<? 
}
}
mysql_close();
?>

and here is the output:

Now i bet your thinking, well whats the problem? The problem is i have 0 approved leads for Tesco Mobile in my database so there shouldnt be a value for Approved Leads for Tesco Mobile and there also shouldnt be a value for Approved £. Now what i think is happening here is because when the SQL is being ran there is no results for Tesco Mobile for Approved Leads so its using the data from Nufield Health because its on a while loop. Table picture below:

How would i go about doing what i need it to do and if there is no value then it will display ‘0’. Also the way i have done it i know is gonna cause a few problems so can someone help me out and code it in a different way please.

Any help would be great and thank you in advance.

Thanks!

system · March 17, 2011, 12:46pm

Right… the problem is that you’re looping inside your loop, which means you’re printing the same records for both of the results of your first query… you should really put it all into a single query for performance and clarity, e.g.:


$query = "SELECT
    ProgramName  AS `name`,
    SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,
    SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `confirmed_leads`,
    SUM(Payout) AS `payout`,
    SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,
    SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `approved`,
    SUM(Payout)  AS `payout`
FROM affleads
WHERE AffID = '%s'
    AND Date = '$datenow'
GROUP BY ProgramName;";

Cheers.

AdWarm · March 17, 2011, 1:09pm

Hi Steve,

Thanks for the reply, I’m testing the SQL in PHPMYADMIN and its returning 0 results?

Any help would be great and thanks for your help.

Thanks

system · March 17, 2011, 1:50pm

What are you putting in place of your dynamic where clause?

WHERE AffID = ‘%s’
AND Date = ‘$datenow’

Try cutting out the where clause and see what you get. This should work in PhpMyAdmin:


SELECT
    ProgramName  AS `name`,
    SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,
    SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `confirmed_leads`,
    SUM(Payout) AS `payout`,
    SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,
    SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `approved`,
    SUM(Payout)  AS `payout`
FROM affleads
GROUP BY ProgramName;

AdWarm · March 17, 2011, 2:26pm

Hi Steve,

Ok iv got it working using your SQL but i kept the WHERE clause in it but its not displaying correctly. Take a look at the output below.


$query = "SELECT
    ProgramName  AS `name`,
    SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,
    SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `confirmed_leads`,
    SUM(Payout) AS `payout`,
    SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,
    SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `approved`,
    SUM(Payout)  AS `payout`
FROM affleads
WHERE AffID = '$affid'
AND
Date = '$datenow'
GROUP BY ProgramName;";

Let me explain what each field means.

Campaign = ‘ProgramName’
Pending Leads = Number of records with ‘Status’ as ‘Pending’ for ‘ProgramName’
Confirmed Leads = Number of records with ‘Status’ as ‘Approved’ for ‘ProgramName’
Payout = Value of ‘Payout’ of ‘ProgramName’
Pending £ = Total number of ‘Payout’ added together with Status as ‘Pending’ for ‘ProgramName’
Approved £ = Total number of ‘Payout’ added together with Status as ‘Approved’ for ‘ProgramName’

MySQL Table:

So lets say $affid=1 then the output should be the following:

I’ve tried to explain as much as i can to help you understand what I’m trying to do, if you have any questions feel free to ask. Any hlep would be great.

Thanks so far for your help

Mandes · March 17, 2011, 2:48pm

its this part of the sql thats in error I think


[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `confirmed_leads`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(Payout) AS `payout`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `approved`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(Payout)  AS `payout`[/COLOR][/FONT]

If you want to count money then you should be ‘CASE WHEN … THEN Payout ELSE 0’
If you want to count occurences the you use ‘CASE WHEN … THEN 1 ELSE 0’


[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `confirmed_leads`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(Payout) AS `payout`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000]  SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `approved`[/COLOR][/FONT]

not sure why you had Sum(Payout AS payout) twice

AdWarm · March 17, 2011, 2:57pm

Hi Mandes,

Thank you for resolving this issue. This has fixed the problem.

Once again thank you

system · March 17, 2011, 3:00pm

That’s my bad… I misplaced the confirmed_leads vs. approved titles (I had changed “approved” to “confirmed”, and did it on the wrong field lol)… was a sloppy 60 second hack of SQL lol.

AdWarm · March 17, 2011, 3:05pm

Hi Transio,

No problem, thank you for your help too, much appreciated

Thank you.

Topic		Replies	Views
While loop's issue PHP	4	477	October 8, 2014
PHP while statement.. Problem? PHP	18	4929	September 8, 2011
While loop PHP	6	500	October 8, 2014
Kevin Yank : Php and Mysql query PHP	47	4025	February 28, 2010
While Loop mysteriously Stops at 1st row! PHP	4	574	October 8, 2014

While Statement - PHP & MySQL

Related topics