flyersman Posted June 11, 2008 Share Posted June 11, 2008 Hi Everyone, Im having trouble with my code. Its supposed to show the top 6 "credited" offers. Which means the top 6 offers with the most 1's in the status field in MySQL. Right now its just showing any 6 with 1's in status. And its showing them in ID order. Do you know whats wrong with this code. Here it is. Any help would be appreciated. Thanks! <? $rows=$mysql->doSelect("offers, offer_clicks, creatives", "creatives.image_link, offers.name, offers.id", "offers.id=offer_clicks.offer_id && offers.id=creatives.offer_id && creatives.name='120x60' && offer_clicks.status>0 GROUP BY offers.id LIMIT 6"); $count_creative=0; while($row=$rows->nextRow()) { if($count_creative==12) echo "</tr> <tr><td> </td></tr> <tr>"; echo "<td><a href='creatives.php?campaign=" . $row['id'] . "'><img src='" . $row['image_link'] . "' border='0' ><br>" . $row['name'] . "</a></td>"; $count_creative++; } ?> Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 you can add an order by and that should fix it $rows=$mysql->doSelect("offers, offer_clicks, creatives", "creatives.image_link, offers.name, offers.id", "offers.id=offer_clicks.offer_id && offers.id=creatives.offer_id && creatives.name='120x60' && offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status DESC LIMIT 6"); Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 Thanks for the help. But now its showing the ones with a status of atleast 1, but the ones with the LOWEST amount of 1's in status. It should be the ones with the highest. If you can help me with that, thatd be great Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 Also, is it possible to only display the ones that have available = 1, in the table offers? Thanks again! Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 what does the query string look like that is being sent to the DB? Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 Im not sure. This is the whole code for it. <? $rows=$mysql->doSelect("offers, offer_clicks, creatives", "creatives.image_link, offers.name, offers.id", "offers.id=offer_clicks.offer_id && offers.id=creatives.offer_id && creatives.name='120x60' && offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status ASC LIMIT 6"); $count_creative=0; while($row=$rows->nextRow()) { if($count_creative==12) echo "</tr> <tr><td> </td></tr> <tr>"; echo "<td><a href='creatives.php?campaign=" . $row['id'] . "'><img src='" . $row['image_link'] . "' border='0' ><br><br>" . $row['name'] . "</a></td>"; $count_creative++; } ?> Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 ok lets break it down a little so we can find the error. standard syntax for doSelect() is doSelect($table, $condition = " ", $selectThis = "*") so lets set these 3 variables and see where it takes us //set all the pieces for the function call //Tables being searched $table = "offers, offer_clicks, creatives"; //This is your WHERE Statement $conditions = "WHERE offers.id=offer_clicks.offer_id AND offers.id=creatives.offer_id AND creatives.name='120x60' and offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status DESC LIMIT 6"; //Fields to view Default is "*" for all fields $selectThis = "creatives.image_link, offers.name, offers.id" $rows=$mysql->doSelect($table, $condition, $selectThis) Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 acutally i noticed in your last post you had your order by set to ASC. which is lowers to highest, try using DESC for decending order. Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 Yeah but DESC displays the ones with atleast a 1 in status, but the least amount. It supposed to be the most amount. Ill try your code above. Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 With the code lookink like this I now get an error <? //set all the pieces for the function call //Tables being searched $table = "offers, offer_clicks, creatives"; //This is your WHERE Statement $conditions = "WHERE offers.id=offer_clicks.offer_id AND offers.id=creatives.offer_id AND creatives.name='120x60' and offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status DESC LIMIT 6"; //Fields to view Default is "*" for all fields $selectThis = "creatives.image_link, offers.name, offers.id" $rows=$mysql->doSelect($table, $condition, $selectThis) echo "</tr> <tr><td> </td></tr> <tr>"; echo "<td><a href='creatives.php?campaign=" . $row['id'] . "'><img src='" . $row['image_link'] . "' border='0' ><br><br>" . $row['name'] . "</a></td>"; $count_creative++; } ?> Parse error: syntax error, unexpected T_VARIABLE in /home/yourgift/domains/expressrevenue.com/public_html/new/dashboard.php on line 38 Quote Link to comment Share on other sites More sharing options...
DarkWater Posted June 11, 2008 Share Posted June 11, 2008 You left ; off of $selectThis = and $row= Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 good catch. i hate when i do that. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted June 11, 2008 Share Posted June 11, 2008 good catch. i hate when i do that. Ha, it gets annoying after a while, lol. P.S: Grats on your 69th post. </immature> Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 hmmm, now when I added ; it doesn't display anything at all. The images, and the 6 are not there. Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 I generally don't use auto query building functions like that. if you have access to a query browser try and build your string in there first. something like SELECT creatives.image_link, offers.name, offers.id FROM `offers`, `offer_clicks`, `creatives` WHERE offers.id=offer_clicks.offer_id AND offers.id=creatives.offer_id AND creatives.name='120x60' and offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status DESC LIMIT 6; and fiddle with it. Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 Thanks for the help, I think though when i put ASC it worked. Weird, but it did. Now from that code (which is below) is there anyway to make it only display the ones where in the offers table available = 1? Thanks. <? $rows=$mysql->doSelect("offers, offer_clicks, creatives", "creatives.image_link, offers.name, offers.id", "offers.id=offer_clicks.offer_id && offers.id=creatives.offer_id && creatives.name='120x60' && offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status ASC LIMIT 6"); $count_creative=0; while($row=$rows->nextRow()) { if($count_creative==12) echo "</tr> <tr><td> </td></tr> <tr>"; echo "<td><a href='creatives.php?campaign=" . $row['id'] . "'><img src='" . $row['image_link'] . "' border='0' ><br><br>" . $row['name'] . "</a></td>"; $count_creative++; } ?> Quote Link to comment Share on other sites More sharing options...
grimmier Posted June 11, 2008 Share Posted June 11, 2008 glad that worked, only display the ones with available = 1 just add it to your string $rows=$mysql->doSelect("offers, offer_clicks, creatives", "creatives.image_link, offers.name, offers.id", "offers.id=offer_clicks.offer_id && offers.id=creatives.offer_id && offers.available = 1 && creatives.name='120x60' && offer_clicks.status>0 GROUP BY offers.id ORDER BY offer_clicks.status ASC LIMIT 6"); Quote Link to comment Share on other sites More sharing options...
flyersman Posted June 11, 2008 Author Share Posted June 11, 2008 And it works, thanks for all the help! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.