Either too many results or not enough

zebraplayer

Hi folks,

Thanks in advance for your time.

I am trying to display a catalog of films for a film festival I started. I can't afford to hire someone to do this, so I am struggling through it myself. I have limited knowledge of coding, so I'd like to pick your brains on this matter.

Some films in the festival have two showtimes. Meaning they play twice. For some reason, though, I can only get the ones that play twice to show up on my results page

Sample 1: http://www.charlottefilmfestival.org/eventguide/eventcatalog2.php

Or get them all to show up on my results page, but multiple repeated times

Sample 2: http://www.charlottefilmfestival.org/eventguide/eventcatalog3.php

Here's the relevant code for Sample 1:

<?php
mysql_select_db($database_CFF1, $CFF1);
$query_rs_films = "SELECT * FROM films, categories, country, dates, prices, venues, genre, dates2, venues2 WHERE films.country=country.countryID AND films.category=categories.categoryID AND films.date=dates.dateID AND films.venue=venues.venueID AND films.venue2=venues2.venueID2 AND films.date2=dates2.dateID2 AND films.price=prices.priceID AND films.genre=genre.ID ORDER BY title ASC";
$rs_films = mysql_query($query_rs_films, $CFF1) or die(mysql_error());
$row_rs_films = mysql_fetch_assoc($rs_films);
$totalRows_rs_films = mysql_num_rows($rs_films);
?>


<?php do { ?>
<table width="640"  border="0" cellpadding="0" cellspacing="4">
  <tr class="text1">
    <td width="13%"><p><img src="<?php echo $row_rs_films['film_image']; ?>" alt="<?php echo $row_rs_films['title']; ?>" name="filmimages" border="0"></p></td>
    <td width="48%" valign="top"><p><strong><?php echo $row_rs_films['title']; ?><br>
                <em><?php echo $row_rs_films['categoryname']; ?></em><br>
        (<?php echo $row_rs_films['coutryname']; ?>, <?php echo $row_rs_films['year']; ?>, <?php echo $row_rs_films['trt']; ?> min.)</strong></p></td>
    <td width="39%" valign="top" class="homecontent"><div align="right">
        <p><?php echo $row_rs_films['datename']; ?>, <?php echo $row_rs_films['time']; ?><br>
            <a href="javascript:;" onClick="MM_openBrWindow('VenuesFrame.php','VenueDetail','width=368,height=418')"><?php echo $row_rs_films['venuename']; ?></a><br>
            <br><?php if ($row_rs_films['date2'] <=0) { } else echo $row_rs_films['datename2']; ?>&nbsp;&nbsp;<?php echo $row_rs_films['time2']; ?><br>
			<a href="javascript:;" onClick="MM_openBrWindow('VenuesFrame.php','VenueDetail','width=368,height=418')"><?php if ($row_rs_films['venue2'] <=0) { } else echo $row_rs_films['venuename2']; ?></a><br><br>
            <?php echo $row_rs_films['price']; ?><br>
        <a href="http://www.carolinatix.org" target="_blank" class="TextBold" alt="Purchase Tickets">BUY TIX </a></p>
    </div></td>
  </tr>
  <tr class="text1">
    <td valign="top"><p>&nbsp;</p></td>
    <td colspan="2"><p class="homecontent"><?php echo $row_rs_films['description1']; ?>&nbsp;&nbsp;<span class="TextBold">Genre</span>:&nbsp;<?php echo $row_rs_films['genre']; ?></p></td>
  </tr>
  <tr class="text1">
    <td height="23" colspan="3"><hr></td>
  </tr>
</table>
<?php } while ($row_rs_films = mysql_fetch_assoc($rs_films)); ?>

Here's the relevant code for Sample 2:

<?php
mysql_select_db($database_CFF1, $CFF1);
$query_rs_films = "SELECT * FROM films, categories, country, dates, prices, venues, genre, dates2, venues2 WHERE films.country=country.countryID AND films.category=categories.categoryID AND films.date=dates.dateID AND films.venue=venues.venueID AND films.venue2!=venues2.venueID2 AND films.date2!=dates2.dateID2 AND films.price=prices.priceID AND films.genre=genre.ID ORDER BY title ASC";
$rs_films = mysql_query($query_rs_films, $CFF1) or die(mysql_error());
$row_rs_films = mysql_fetch_assoc($rs_films);
$totalRows_rs_films = mysql_num_rows($rs_films);
?>


<?php do { ?>
<table width="640"  border="0" cellpadding="0" cellspacing="4">
  <tr class="text1">
    <td width="13%"><p><img src="<?php echo $row_rs_films['film_image']; ?>" alt="<?php echo $row_rs_films['title']; ?>" name="filmimages" border="0"></p></td>
    <td width="48%" valign="top"><p><strong><?php echo $row_rs_films['title']; ?><br>
                <em><?php echo $row_rs_films['categoryname']; ?></em><br>
        (<?php echo $row_rs_films['coutryname']; ?>, <?php echo $row_rs_films['year']; ?>, <?php echo $row_rs_films['trt']; ?> min.)</strong></p></td>
    <td width="39%" valign="top" class="homecontent"><div align="right">
        <p><?php echo $row_rs_films['datename']; ?>, <?php echo $row_rs_films['time']; ?><br>
            <a href="javascript:;" onClick="MM_openBrWindow('VenuesFrame.php','VenueDetail','width=368,height=418')"><?php echo $row_rs_films['venuename']; ?></a><br>
            <br><?php if ($row_rs_films['date2'] <=0) { } else echo $row_rs_films['datename2']; ?>&nbsp;&nbsp;<?php echo $row_rs_films['time2']; ?><br>
			<a href="javascript:;" onClick="MM_openBrWindow('VenuesFrame.php','VenueDetail','width=368,height=418')"><?php if ($row_rs_films['venue2'] <=0) { } else echo $row_rs_films['venuename2']; ?></a><br><br>
            <?php echo $row_rs_films['price']; ?><br>
        <a href="http://www.carolinatix.org" target="_blank" class="TextBold" alt="Purchase Tickets">BUY TIX </a></p>
    </div></td>
  </tr>
  <tr class="text1">
    <td valign="top"><p>&nbsp;</p></td>
    <td colspan="2"><p class="homecontent"><?php echo $row_rs_films['description1']; ?>&nbsp;&nbsp;<span class="TextBold">Genre</span>:&nbsp;<?php echo $row_rs_films['genre']; ?></p></td>
  </tr>
  <tr class="text1">
    <td height="23" colspan="3"><hr></td>
  </tr>
</table>
<?php } while ($row_rs_films = mysql_fetch_assoc($rs_films)); ?>

Can someone please tell me what I am doing wrong?

I need your help!

Thanks!

Piranha

What is the difference between the two codes? It may be easier to help you out if you give that information.

zebraplayer

sorry:

Code 2 has a ! after venue2 and date2

films.venue2!=venues2.venueID2 AND films.date2!=dates2.dateID2

Whereas code 1 doesn't:

films.venue2=venues2.venueID2 AND films.date2=dates2.dateID2

I was told to add the ! to include the films without a second show.

-Louis

Piranha

I'm sorry, but the query and the code are to complicated to understand without knowing how the database looks like, including keys, relationships and what information is stored in the 8!!! tables in that query.

It is possible that you could use "GROUP BY films.id", it is simply impossible to know without knowing what information you really need.