I've made a checklist for my collector's site which SEEMED to be working great.... until more than one person started building their collection..... then it fell apart.
There are 2 MySQL tables involved, connected with a LEFT JOIN:
fd_eggs - all the information about the eggs
fd_collection - intermediate table that links member with egg found (member ID and egg ID per row, so 5 eggs found, 5 rows)
What I want it to do is to display a list of all the eggs (each egg only ONCE), then I want it to display either a checkmark or a form button beside each one which is determined by reading the member's ID number from a cookie received at login. Pressing the form button will enter a row into the fd_collection table that links the member_ID to the egg_ID. So far, it's doing the INSERTing flawlessly; I have just what I wanted to have in the fd_collection table. The problem arises when I try to get the data OUT of the table to display it.
It's not only displaying those eggs associated with the member_ID in the cookie, it's displaying EVERY occurrence of that egg_ID in the fd_collection table. It is, however, correctly putting a checkmark beside the first one if the person has it, so it is recognizing the association with the ID in the cookie on some level.
Example: 7 people have the green egg, including Joe. Joe looks at the list and sees 7 identical rows of the green egg, with a checkmark beside the first one, and form buttons beside the others. What Joe should see is one row with a checkmark and a green egg; he shouldn't be seeing the other 6 identical green eggs that have been collected by Mary, Ethel, Bob, etc.
You can see the problem on this page - there are multiples of each egg instead of just one of each with a varying checkmark or button.
The only part referring to the member_ID is the part where it decides whether to display checkmark or button, but here's all the code, because I have no idea whatsoever where I've screwed it up 🙁 :
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 (($row['eggid']) > 0) {
echo '<img src="pics/yes.gif">';
}
} else {
echo '<form action="' . $_SERVER['PHP_SELF'] . '" 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>';
}
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>';
