[RESOLVED] Simplify My Query Plz

[creativeink]

I am doing a query connecting two tables and it is displaying my information twice and I only need it displayed once.

HTML Page:
http://www.creativeink.ca/sean/my_listings.php

Code

<?php require_once('Connections/Sean.php'); ?>

<?php
mysql_select_db($database_Sean, $Sean);
$query_Archives = "SELECT * FROM pictures p INNER JOIN galleries g ON p.gallery_id = g.id WHERE active='1' ORDER BY g.id DESC";
$Archives = mysql_query($query_Archives, $Sean) or die(mysql_error());
$row_Archives = mysql_fetch_assoc($Archives);
$totalRows_Archives = mysql_num_rows($Archives);
?>


<?php if ($totalRows_Archives > 0) { // Show if recordset not empty ?>
<?php do { ?>
<table width="100%" cellpadding="0" cellspacing="0" border="0" align="center" class="text">
<tr>
  <td>
	<table width="99%" cellpadding="0" cellspacing="0" border="0" class="table" align="center">
	<tr background="images/backbar.jpg" valign="bottom"><td align="center" class="title_mylisting" style="line-height:1.9em;"><?php echo substr(($row_Archives['title']), 0, 80); ?></td></tr>
	<tr><td align="center"><br /><ul class="gallery clearfix">
<?php
$gallery_id = $row_Archives['id'];
$photoquery = mysql_query("SELECT * FROM pictures p INNER JOIN galleries g ON p.gallery_id = g.id WHERE gallery_id = '".$gallery_id."'");
$photos = mysql_num_rows($photoquery);

for($i = 0; $i<$photos; $i++) {
$row = mysql_fetch_array($photoquery);
if(is_int($i/1)) {
print "<li><a href=\"".$row['picture']."\" rel=\"prettyPhoto\" title=\"".$row['caption']."\"><img src=\"".$row['picture']."\" border=\"0\" height=\"70\" /></a></li>&nbsp;";
}else{
print "<li><a href=\"".$row['picture']."\" rel=\"prettyPhoto\" title=\"".$row['caption']."\"><img src=\"".$row['picture']."\" border=\"0\" height=\"70\" /></a></li>&nbsp;";
}
}
?></ul>
</td></tr>
<tr><td>&nbsp;</td></tr>
<tr><td>
	<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center" bgcolor="#272727" class="text">
    <tr><td colspan="3"><img src="images/redbar.jpg" border="0" width="100%"></td></tr>
    <tr><td colspan="3">&nbsp;</td></tr>
	<tr valign="top"><td align="right" width="3%"><img src="images/bullet.gif" width="8" height="8" border="0" />&nbsp;</td><td width="97%" align="left" class="featured_small"><?php echo $row_Archives['overview']; ?></td><td width="3%">&nbsp;</td></tr>
    <tr><td colspan="3">&nbsp;</td></tr>
	</table>
</td></tr>
<tr>
  <td>
	<table width="100%" height="53" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr valign="middle" height="45" bgcolor="#b80000">
    <td width="35%" height="51" align="left" class="price" style="background:url(images/bttm-cost.jpg); background-repeat:no-repeat; padding-left:50px; padding-top:1px;"><?php echo substr(($row_Archives['price']), 0, 40); ?></td>
	<td width="30%" align="center"><ul class="gallery clearfix"><li><a href="<?php echo $row_Archives['gallery']; ?>?iframe=true&amp;width=80%&amp;height=80%" rel="prettyPhoto"><img src="images/bttm-view.jpg" width="140" height="27" border="0" /></a></li></ul></td>
	<td width="35%" align="center"><ul class="gallery clearfix">
	  <li><a href="https://home.hlcmortgages.com/royallepage/affordability.php?RLPe=www.royallepage.ca?iframe=true&amp;width=600&amp;height=450" rel="prettyPhoto"><img src="images/bttm-calculator.jpg" width="97" height="33" border="0" /></a></li><li><a href="http://Maps.Google.com/maps?q=<?php echo $row_Archives['location']; ?>&hl=en?iframe=true&amp;width=80%&amp;height=80%" rel="prettyPhoto" title="<?php echo $row_Archives['location']; ?>"><img src="images/bttm-map.jpg" width="78" height="35" border="0" /></a>&nbsp;&nbsp;</li></ul></td>
    </tr>
	</table>
</td></tr>
</table><br />
<?php } while ($row_Archives = mysql_fetch_assoc($Archives)); ?>
<?php } // Show if recordset not empty ?>
    <?php if ($totalRows_Archives == 0) { // Show if recordset empty ?>
<p align="center"><img src="images/royal_lepage.jpg" border="0" /></p>
      <?php } // Show if recordset empty ?>

<?php
mysql_free_result($Archives);
?>

It is displaying perfectly other then twice thank you sooo much for the help really appreciate it !

[toxic_brain]

Do u really need a inner join inside the loop? Can't we replace

$photoquery = mysql_query("SELECT * FROM pictures p INNER JOIN galleries g ON p.gallery_id = g.id WHERE gallery_id = '".$gallery_id."'");

to

$photoquery = mysql_query("SELECT * FROM pictures WHERE gallery_id = '$gallery_id' ");

[creativeink]

thanks for the reply, it definetely simplified my query like the subject said lol but it still displays twice 😕

[rulian]

SELECT b.*, FROM galleries a LEFT JOIN pictures b ON a.gallery_id = b.id WHERE a.gallery_id = "THE ID"

your * is going to pull info from both tables
if rows are repeated t hen it could be a parenting issue

[halojoy]

what is displaying twice?
show some output html code

[toxic_brain]

on the 4th line, replace

$query_Archives = "SELECT * FROM pictures p INNER JOIN galleries g ON p.gallery_id = g.id WHERE active='1' ORDER BY g.id DESC"; 

with

$query_Archives = "SELECT * FROM galleries WHERE active='1' ORDER BY id DESC"; 

[creativeink]

toxic_brain you are the best!

I was over thinking my query I guess :mad:

thank you so much

[toxic_brain]

don't forget to mark the thread as resolved