Jump to content

Problem with JOINS with conditionals arrays and loops in a single query


poleposters

Recommended Posts

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,

 

 

 

 

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.