display the remaining records from a MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Red Blaze
Forum Commoner
Posts: 40
Joined: Mon Mar 27, 2006 3:45 pm

display the remaining records from a MySQL

Post by Red Blaze »

Hello again,

I have two tables in MySQL.

Items
Gallery_Photos

In the gallery page, it displays all the photos with checkboxes. Clicking submit will store the selected checkboxes in the items table.
Now I have a page displaying the photos stored in the items table. Under each photo, there's a link saying 'Change' to change the photo.

Then it takes you to a change page. I want to tell PHP ONLY select from gallery_photos the remaining records. Here's what I have in that page:

Code: Select all

<?php require_once('../Connections/prophot.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$userid = $_SESSION['MM_UserID'];

//grabs the selected photo
$colname_current_photo = "-1";
if (isset($_GET['filename'])) {
  $colname_current_photo = (get_magic_quotes_gpc()) ? $_GET['filename'] : addslashes($_GET['filename']);
}
mysql_select_db($database_prophot, $prophot);
$query_current_photo = sprintf("SELECT * FROM gallery_photos WHERE photo_filename = '%s'", $colname_current_photo);
$current_photo = mysql_query($query_current_photo, $prophot) or die(mysql_error());
$row_current_photo = mysql_fetch_assoc($current_photo);
$totalRows_current_photo = mysql_num_rows($current_photo);

//grabs the current album
$colname_callalbum = "-1";
if (isset($_GET['aid'])) {
  $colname_callalbum = (get_magic_quotes_gpc()) ? $_GET['aid'] : addslashes($_GET['aid']);
}
mysql_select_db($database_prophot, $prophot);
$query_callalbum = sprintf("SELECT * FROM gallery_albums WHERE album_id = %s", $colname_callalbum);
$callalbum = mysql_query($query_callalbum, $prophot) or die(mysql_error());
$row_callalbum = mysql_fetch_assoc($callalbum);
$totalRows_callalbum = mysql_num_rows($callalbum);

//grabs everything in the items table that belongs to this user and in the current ablum and with a unlocked cart.
mysql_select_db($database_prophot, $prophot);
$query_recordedphotos = "SELECT * FROM items WHERE userid = $userid AND albumid = $aid AND lockedcart = 0";
$recordedphotos = mysql_query($query_recordedphotos, $prophot) or die(mysql_error());
$row_recordedphotos = mysql_fetch_assoc($recordedphotos);
$totalRows_recordedphotos = mysql_num_rows($recordedphotos);

//grabs all the photos in the album
$colname_remainingphotos = "-1";
if (isset($_GET['aid'])) {
  $colname_remainingphotos = (get_magic_quotes_gpc()) ? $_GET['aid'] : addslashes($_GET['aid']);
}
mysql_select_db($database_prophot, $prophot);
$query_remainingphotos = sprintf("SELECT * FROM gallery_photos WHERE photo_album = %s", $colname_remainingphotos);
$remainingphotos = mysql_query($query_remainingphotos, $prophot) or die(mysql_error());
$row_remainingphotos = mysql_fetch_assoc($remainingphotos);
$totalRows_remainingphotos = mysql_num_rows($remainingphotos);
?><table width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td width="110" valign="top"><img src="<?php echo $images_dir ?>/tb_<?php echo $row_current_photo['photo_filename']; ?>" /></td>
    <td valign="top"><form name="form1" method="post" action="">
      <p>
        <select name="select">
          <option>Available Sizes</option>
        </select>
      </p>
      <p>
        <input name="size" type="submit" id="size" value="Change Size">
        <input name="cancle" type="submit" id="cancle" value="Cancle">
      </p>
    </form>    </td>
  </tr>
  <tr>
    <td colspan="2" valign="top"><table align="center" cellpadding="5" cellspacing="5">
      <tr>
<?php

// Variable to handle the number of columns
$newline = 0;

do { 

?>
        <td><img src="<?php echo $images_dir ?>/tb_<?php echo $row_remainingphotos['photo_filename']; ?>" alt="<?php echo $row_remainingphotos['photo_filename']; ?>" border="0" /></td>
      <?php

    // Increase the number of columns displayed
    $newline++;
    if ($newline == "4")
    {
        // Start a new row and reset the $newline variable
        echo ("</tr><tr>\n");
        $newline = 0;
    }    
} while ($row_remainingphotos = mysql_fetch_assoc($remainingphotos));

// Add any additional columns if needed to fill the last row
for ($newline; $newline < 4; $newline++)
{
    echo ("<td>&nbsp;</td>\n");
}

?>
      </tr>
    </table></td>
  </tr>
</table>
<?php
mysql_free_result($current_photo);
mysql_free_result($callalbum);
mysql_free_result($remainingphotos);
mysql_free_result($recordedphotos);
?>
I want to know the best approach for this. I don't really know where to start looking, so I asked for help. Help is greatlly appreciated.

~RB
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

I want to tell PHP ONLY select from gallery_photos the remaining records.
Is that you want to get all the Gallery_Photos that are not present in Items?
Red Blaze
Forum Commoner
Posts: 40
Joined: Mon Mar 27, 2006 3:45 pm

Post by Red Blaze »

That's right. My apologies if I didn't express myself correctly.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

OUTER JOIN in your mysql query?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

SELECT * FROM Gallery_Photos LEFT JOIN Items ON Gallery_Photos.Id = Items.Gallery_Photos_Id  WHERE Items.Id IS NULL
Post Reply