[RESOLVED] displaying things from sql

Desbrina

On one part of my website it displays items, but i want to make it so that if you dont have a certain one it displays a blank

I'm not sure how easy it is to do, but i haven't worked it out after playing with it

I have 2 tables (user_inventory and cards)

It displays the cards i currently have fine using this

$sql = mysql_query("SELECT cards.alttext,cards.url,user_inventory.item_id FROM user_inventory,cards WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' AND user_inventory.status = 'collecting' AND user_inventory.item_id = cards.ID ORDER BY cards.category, cards.deck ASC"); 	
while ($row = mysql_fetch_array($sql)) {
	echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" /> " ; 
}

The only way really to tell if you have the card or not is by the alttext, it has the card deck and number in it. The blank card's altext is like "deck 00"

Is ther a way of doing this?

ragedigital

You can always put in an "IF" statement... if I understand you correctly.

$sql = mysql_query("SELECT cards.alttext,cards.url,user_inventory.item_id FROM user_inventory,cards WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' AND user_inventory.status = 'collecting' AND user_inventory.item_id = cards.ID ORDER BY cards.category, cards.deck ASC"); 	

if(mysql_num_rows($sql)>0){
    while ($row = mysql_fetch_array($sql)) {
	echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" /> " ; 
    }
}
else
    echo "<img src=\"blank.gif\" alt=\"BLANK" /> " ;

Desbrina

i'm not sure thats going to work. i have to get the blank url from the database, as theirs over 100 of them, and the blank could be before or after the existing ones, since theres usually 20 to a set

ragedigital

Then just modify the "IF" statement where you need it:

$sql = mysql_query("SELECT cards.alttext,cards.url,user_inventory.item_id FROM user_inventory,cards WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' AND user_inventory.status = 'collecting' AND user_inventory.item_id = cards.ID ORDER BY cards.category, cards.deck ASC");      

while ($row = mysql_fetch_array($sql)) { 
   if($row['url'] != "")    

       echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" /> " ; 
    else 
        echo "<img src=\"blank.gif\" alt=\"BLANK" /> " ; 
}

bpat1434

You could also modify the SQL to use the CASE WHEN THEN syntax and give a default image:

SELECT cards.alttext, user_inventory.item_id,
    CASE cards.url
        WHEN "" THEN "images/blank.gif"
        ELSE cards.url
    END CASE url
FROM user_inventory, cards 
WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' 
  AND user_inventory.status = 'collecting' AND user_inventory.item_id = cards.ID
ORDER BY cards.category, cards.deck ASC

You could also use the IF syntax to do it as well.

SELECT cards.alttext, user_inventory.item_id,
    IF(cards.url = "", "images/blank.gif", cards.url) AS url
FROM user_inventory, cards 
WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' 
  AND user_inventory.status = 'collecting' AND user_inventory.item_id = cards.ID
ORDER BY cards.category, cards.deck ASC

Just some other options outside of PHP coding 😉

Oh, and that's not to mention you can set a default value of "blank.gif"..... or whatever default image you want....

Desbrina

i've actually almost got it working myself. Its a long way round

The only problem i'm having is its showing all the cards, and not just the one i have
I think its to do with my sql query, but am unsure as to how to fix it

	$counter = 1;
	$con = mysql_connect('localhost', '****', '*******') or die('Could not connect to database!');
    mysql_select_db('*****', $con);
	$sql = mysql_query("SELECT cards.alttext,cards.url,user_inventory.item_id,cards.deck,user_inventory.deck FROM user_inventory,cards WHERE user_inventory.name = '".mysql_real_escape_string($_GET['user'])."' AND user_inventory.status = 'collecting' AND user_inventory.deck = cards.deck ORDER BY cards.category, cards.deck ASC"); 	   

	if (mysql_num_rows($sql)) {	 
	print $row['deck'];
		$i = 0; 
		while ($row = mysql_fetch_array($sql)) {
			if (preg_match("/ja(0[1-9]|1[0-8])|(jase[1-2])|(future)|(menaceobi)|(sithadult)|(sithjunior)|(menaceamidala)|(menaceanakin)|(menacejunior)|(menacemaul)|(clonesadult)|(clonesjunior)/i", $row['deck']))
		define('CARDS_PER_ROW', 3);
	else
		define('CARDS_PER_ROW', 5);
			if($counter <= 9) {
				$deck = "{$row['deck']} 0{$counter}";
			} else {
				$deck = "{$row['deck']} {$counter}";
			}
			$deck1 = $row['deck'];
				if($deck == $row['alttext']) {
					echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" />" ; 
					++$counter;
					if (++$i % CARDS_PER_ROW == 0)
        			echo '<br />';
				} else {
					$sql2 = mysql_query("SELECT * FROM cards00 where deck = '".$deck1."'"); 	   

					if (mysql_num_rows($sql2)) {	   

					while ($row = mysql_fetch_array($sql2)) {
						echo "<img src=\"{$row['url']}\" alt=\"{$row['alttext']}\" />" ;
						++$counter;
						if (++$i % CARDS_PER_ROW == 0)
        				echo '<br />';
					}
				}
			}
		}
	}
}