Checklist displays either too much info, or not enough.

[mugwumpr]

Hello.  I'm running an informational site about an egg-collection game, and would like to offer my members a checklist where they can keep track of which eggs they've already found, and add new ones as they find them.

 

I want the checklist to list each of the available eggs (only ONCE) and put a checkmark beside the ones they have, and a "Found it!" form button beside all the rest so they can add them to their collection when they find new ones.

 

I have the required info in two tables - one for the egg information (eggs) and one to link the member's ID with the ID of each of the eggs that they've found (collection).  These are MySQL InnoDB tables linked via foreign key on the egg ID.

 

The form button is working properly, and INSERTing the correct information into the "collection" table.  The cookie with the member's ID that's set at login is being read correctly, so that part's all good.

 

The problem I'm having is in pulling the information out of the tables and displaying it correctly.  All I can manage is one of the following two things:

 

1)  Displaying ALL the eggs that are in the "collection" table, with a checkmark beside the first, and buttons (or nothing) beside the rest. (E.g. Seven people, including Joe, have collected the green egg.  When Joe looks at the checklist, he sees his green egg with a checkmark beside it, but he also sees the other six green eggs collected by Bob, Mary, Ethel, etc.)

 

2)  Displays ONLY the eggs that have been already collected, with no form buttons to add new ones, which defeats the entire purpose of the thing.

 

Would someone please be able to have a look at my code and tell me where I'm going wrong?  I've been fighting with this for 3 weeks now, and I've run out of things to try.  :-\  ???

 

 

if (isset ($_COOKIE['ID'])) {
$uid = escape_data($_COOKIE['ID']);
} 

if (isset($_POST['submitted'])) {
$eggp = escape_data($_POST['egg_ID']);
$namp = escape_data($_POST['egg_name']);
//update the database
$query = "INSERT IGNORE INTO fd_collection (member_ID, egg_ID, egg_name) VALUES ('" . $uid . "', '" . $eggp . "', '" . $namp . "')";
$result = @mysql_query($query) or die(mysql_error()); 
}


// make the query
$resultcount = mysql_query("SELECT * FROM fd_eggs");

// count number of eggs
$egg = mysql_num_rows($resultcount);
echo "<p>There are currently $egg eggs in the database.   <a href=\"checklistpaged.php\">Display paged</a>.</p>";


// make the query
$query = "SELECT fd_eggs.ID AS feid, fd_eggs.name AS fename, fd_eggs.releasedate AS rdate, fd_eggs.warehouse_ID AS wid, fd_eggs.warehousename AS wname, fd_eggs.price_ID AS price, fd_eggs.hatchable_ID AS hatch, fd_eggs.notes AS notes, fd_collection.member_ID as memb, fd_collection.egg_ID as eggid
FROM fd_eggs
LEFT JOIN fd_collection
ON fd_eggs.ID = fd_collection.egg_ID
ORDER BY releasedate DESC"; 
$result = @mysql_query($query) or die(mysql_error());

$egg = $row['egg_ID'];


echo '<p> </p>';
echo '<table width="90%" border="0" cellspacing="0" cellpadding="0">';


$bg = '#eeeeee'; //set row background variable

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); //set background colours to alternate rows 

echo '<tr bgcolor="' . $bg . '"><td width="5%" align="left">';

if (($row['memb']) == $uid) { 
if (isset($row['eggid'])) { 
echo '<img src="pics/yes.gif">'; 
} elseif (empty($row['eggid'])) { 
echo '<form action="' . $_SERVER['PHP_SELF'] . '?start=' . $start . '" method="post"> 
<input type="hidden" name="member_ID" value="' . $uid . '" /> 
<input type="hidden" name="egg_ID" value="' . $row['feid'] . '" /> 
<input type="hidden" name="egg_name" value="' . $row['fename'] . '" /> 
<input type="submit" name="submit" value="Found it!" /> 
<input type="hidden" name="submitted" value="TRUE" /> 
</form>'; 
} 
} else {}


echo '</td><td>';
echo '<img src="../pics/found/egg-found' . $row['feid'] . '.png" height="105px" width="80px"></td><td>';
echo '<table width="90%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td width="35%">';
echo '<b>Name:</b> </td><td>';
echo $row['fename'];
echo '</td></tr><tr><td>';
echo '<b>Release Date:</b> </td><td>';
echo date('j M Y', strtotime($row['rdate']));
echo '</td></tr><tr><td>';
echo '<b>Warehouse Name:</b> </td><td>';
if ($row['wid'] == 1) {
echo $row['wname'];
} else {
echo ' ';
}
echo '</td></tr><tr><td>';
echo '<b>Price:</b> </td><td>';
switch ($row['price']) {
case 1:
echo '100 shells';
break;
case 2:
echo '200 shells';
break;
case 3:
echo '250 shells';
break;
case 4:
echo '500 shells';
break;
case 5:
echo '1000 shells';
break;
case 6:
echo '2000 shells';
break;
case 7:
echo '5000 shells';
break;
case 8:
echo '10 shells';
break;
default:
echo ' ';
} 

echo '</td></tr><tr><td>';
echo '<b>Hatchability:</b> </td><td>';
if ($row['hatch'] == 1) {
echo '<a href="view_egg.php?id=' . $row['feid'] . '">hatchable</a>';
} elseif ($row['hatch'] == 2) {
echo '<a href="view_egg.php?id=' . $row['feid'] . '">not-hatchable</a>';
} elseif ($row['hatch'] == 3) {
echo '<a href="multihatch.php?id=' . $row['feid'] . '">multiple</a>';
} 
echo '</td></tr><tr><td>';
echo '<b>Notes:</b> </td><td>';
echo $row['notes'];
echo '</td></tr></table>';
}

echo '</td></tr></table>';

 

[Ken2k7]

Too long to read. Try implementing this SQL example.

 

SELECT cols
FROM eggs_table e
LEFT OUTER JOIN member_table m
     ON e.egg_id = m.egg_id
WHERE m.name = 'member_name';

[mugwumpr]

Adding the WHERE clause will produce the 2nd instance, where it only shows what the member already has, with none of the others displayed and no way to add new ones.

 

Is there some way to filter out the duplicates from the collections table with PHP when it generates the page?  That's really the biggest issue.  The checkmarks and buttons are working properly according to the logged-in member; I just want it to stop displaying all the other members' eggs.