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++; } ?> Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/ 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"); Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562694 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 Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562699 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! Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562711 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? Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562714 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++; } ?> Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562717 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) Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562726 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. Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562731 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. Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562735 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 Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562736 Share on other sites More sharing options...
DarkWater Posted June 11, 2008 Share Posted June 11, 2008 You left ; off of $selectThis = and $row= Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562738 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. Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562740 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> Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562742 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. Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562745 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. Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562749 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++; } ?> Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562752 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"); Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562756 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! Link to comment https://forums.phpfreaks.com/topic/109669-something-is-wrong-with-my-php-code/#findComment-562759 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.