[RESOLVED] not sure how to do query

[Desbrina]

I'm trying to make my database better. Currently i have these 2 tables

CREATE TABLE `cards` (
 `ID` int(255) NOT NULL auto_increment,\n  `amount` int(4) NOT NULL,
  `type` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `deck` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `alttext` varchar(255) NOT NULL,
  `features` varchar(255) NOT NULL,
  `worth` int(1) NOT NULL,
  `deny` int(3) NOT NULL,
 `donated` varchar(255) NOT NULL,
  `created` tinyint(1) NOT NULL,
 PRIMARY KEY  (`ID`),
 KEY `worth` (`worth`),
  KEY `deny` (`deny`),
  KEY `cards_idx_worth_deny` (`worth`,`deny`),
  KEY `alttext` (`alttext`)
) ENGINE=InnoDB AUTO_INCREMENT=9903 DEFAULT CHARSET=latin1

CREATE TABLE `user_inventory` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL COMMENT 'Name of the user',
  `url` varchar(255) NOT NULL,
  `alttext` varchar(255) NOT NULL,
  `deck` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL,
  `card_id` int(11) NOT NULL,
  `status` varchar(255) NOT NULL,
  `worth` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `alttext` (`alttext`)
) ENGINE=InnoDB AUTO_INCREMENT=21496 DEFAULT CHARSET=latin1

I want to get rid of the duplicate fields in user_inventory that are already in cards

Before i remove the fields i want to rewrite the sql statements. I have the main ones done but i'm having trouble with one and i dont have a clue what to do

SELECT url,alttext,amount,type,deck
FROM user_inventory
WHERE name = '".mysql_real_escape_string($_GET['user'])."'
AND status = 'collecting'
ORDER BY deck, alttext

What the code does is select the cards a user current has as collecting. The problem is, if i take out the url filed, theres no way of telling if a user has the card or not (if a user doesn't then the url is like %00.png)

I thought about deleting the 00.png entries, but i've then got the problem of adding them in from the cards00 table (same layout as cards table)

This is the PHP code i'm using

// Function for Collecting Cards
function collect() {
	$user = mysql_real_escape_string($_GET['user']);
    $con = mysql_connect('***', "****", "*************") or die('Could not connect to database!');
    mysql_select_db("*************", $con);
	$sql = "SELECT url,alttext,amount,type,deck
			FROM user_inventory
			WHERE name = '".$user."'
			AND status = 'collecting' ORDER BY deck, alttext";     

	$sql1 = mysql_query($sql);
	if (!$sql1) { echo( mysql_error()); }
	if (mysql_num_rows($sql1)) {
		$cards = mysql_query("SELECT * FROM user_inventory
		WHERE name = '".$user."'
		AND status = 'collecting'
		AND url NOT LIKE '%00.png'")or die(mysql_error());
		echo "Collecting: (Total Cards: ".mysql_num_rows($cards).")<br />"; 	  

		$i = 0; 
		while ($row = mysql_fetch_array($sql1)) {
			if($deck != $row['deck']) { echo "<br />"; $i=0; $perrow = 0; }
			$deck = $row['deck'];
			//echo "Deck: ".$deck." Row: ".$row['deck'];
			if (($row['type'] == "puzzle") and $row['amount'] == 15) { $perrow = 3;
			} elseif (($row['amount'] % 5) == 0) { $perrow = 5; 
			} elseif (($row['amount'] % 4) == 0) { $perrow = 4; 
			} elseif (($row['amount'] % 3) == 0) { $perrow = 3; 
			} else { $perrow = 5; }
			echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" />";
			if ($row['type'] != 'puzzle') {
				echo ' ';
			}
			if (++$i % $perrow == 0){
				$i=0;
				echo '<br />'; // Insert a new line
			} 
		}
	}
}

So i want the final code to appear like the Collecting Section here currently does, with blank cards (cards with no number or picture) where a suer doesn't have a card. Link: http://midnighttempest.com/users/user.php?user=Desbrina