poleposters Posted April 2, 2008 Share Posted April 2, 2008 I'm creating a business directory. I have two tables I need to use for a query. One is called coupon and contains all the info for the listing. ie, business name, phone, blurb etc. The other is the user info which includes the registrants details and a column called paid_listing. This is set as zero by default however changes to "1" when the listing is paid. At the moment I have a query which displays the listing aswell as a button to view the business profile.I want to make the business profile as part of the paid listing. Basically I just need to run a condition where if paid_listing>0 the button is displayed.My problem is that the paid_listing column is in the user table not the coupon table. I know I need to perform a join. The code below is the script I have now without the conditional for the view profile. $selectlinks="SELECT * from coupon WHERE postcode like '%$searchterm' and cat_id=$catid or suburb like '%$searchterm' and cat_id=$catid order by business_name DESC limit $start,$numentries"; $selectlinks2=mysql_query($selectlinks) or die(mysql_error()); $numlinks=mysql_num_rows($selectlinks2); if($numlinks>0) { while($selectlinks3=mysql_fetch_array($selectlinks2)) { $selectlinks3['line_one']=htmlspecialchars($selectlinks3['line_one']); $selectlinks3['line_two']=htmlspecialchars($selectlinks3['line_two']); $selectlinks3['business_name']=htmlspecialchars($selectlinks3['business_name']); $selectlinks3['phone']=htmlspecialchars($selectlinks3['phone']); print "<div id='row'>"; print "<div id='coupon'>"; print "<div id='img'><img src='$selectlinks3[coupon_image]'></div>"; print "<div id='main'>"; print "<h2>$selectlinks3[line_one]</h2>"; print "<h2>$selectlinks3[line_two]</h2>"; print "<h3>$selectlinks3[business_name]</h3>"; print "<h4>$selectlinks3[phone]</h4>"; print "</div>"; print "</div>"; print "<div id='profile'>"; print "<a href='profile.php?businessid=$selectlinks3[business_id]'><img src='view.gif'></a><br/>"; print "<a href='#'><img src='print.gif'></a><br/>"; print "<a href='#'><img src='send.gif'></a><br/>"; print "</div>"; print "</div>"; } This script works great.But to retrieve the paid_listing data I need to join the users table with the coupon table. This is what I've tried $selectlinks="SELECT * from coupon,users WHERE coupon.business_id=users.business_id AND postcode like '%$searchterm' and cat_id=$catid or suburb like '%$searchterm' and cat_id=$catid order by business_name DESC limit $start,$numentries"; $selectlinks2=mysql_query($selectlinks) or die(mysql_error()); $numlinks=mysql_num_rows($selectlinks2); if($numlinks>0) { while($selectlinks3=mysql_fetch_array($selectlinks2)) { $selectlinks3['line_one']=htmlspecialchars($selectlinks3['line_one']); $selectlinks3['line_two']=htmlspecialchars($selectlinks3['line_two']); $selectlinks3['business_name']=htmlspecialchars($selectlinks3['business_name']); $selectlinks3['phone']=htmlspecialchars($selectlinks3['phone']); print "<div id='row'>"; print "<div id='coupon'>"; print "<div id='img'><img src='$selectlinks3[coupon_image]'></div>"; print "<div id='main'>"; print "<h2>$selectlinks3[line_one]</h2>"; print "<h2>$selectlinks3[line_two]</h2>"; print "<h3>$selectlinks3[business_name]</h3>"; print "<h4>$selectlinks3[phone]</h4>"; print "</div>"; print "</div>"; print "<div id='profile'>"; if ($selectlinks3['phone']>0) { print "<a href='profile.php?businessid=$selectlinks3[business_id]'><img src='view.gif'></a><br/>"; } print "<a href='#'><img src='print.gif'></a><br/>"; print "<a href='#'><img src='send.gif'></a><br/>"; print "</div>"; print "</div>"; } The problem is when I run the script, I get the same listing appearing mulitple times if I search by "suburb". However if I type in the postcode(zip), the listings appear fine. Which leads me to believe that the problem is with the conditionals attached to my query. $selectlinks="SELECT * from coupon,users WHERE coupon.business_id=users.business_id AND postcode like '%$searchterm' and cat_id=$catid or suburb like '%$searchterm' and cat_id=$catid order by business_name DESC limit $start,$numentries"; Can anyone help with the syntax? Cheers, Link to comment https://forums.phpfreaks.com/topic/99140-problem-with-joins-with-conditionals-arrays-and-loops-in-a-single-query/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.