display table items

[davidolson]

How to display items like bottom table?

 

And here is the code

<?

print"
<table style=\"width:100%\" class=\"tableList\">
	<tr>
		<th style=\"width:35%\">Prize Name</th> 
		<th style=\"width:12%\">Amount</th>
		<th style=\"width:12%\">Points</th>
		<th style=\"width:12%\">Available</th>
		<th style=\"width:12%\">Redeemed</th>
		<th style=\"width:17%\">Action</th>
	</tr>";

	$giftCardQuery = 'SELECT currency, amount, pointsPrice, instant_gift_cards.id, instant_gift_cards.giftCardName, instant_gift_cards.giftCardImage FROM instant_gift_card_codes INNER JOIN instant_gift_cards ON (instant_gift_card_codes.giftCardId = instant_gift_cards.id) WHERE instant_gift_cards.status = :cardStatus ORDER BY instant_gift_cards.dateCreated DESC';
	$giftCard = $db->prepare($giftCardQuery);
	$giftCard->bindValue(':cardStatus', 'Enabled', PDO::PARAM_STR);
	$giftCard->execute();
	
	if($giftCard->rowCount() > '0'){
	
		while($giftCardRow = $giftCard->fetch(PDO::FETCH_ASSOC)){
			
			$giftCardsAvailableQuery = 'SELECT count(*) FROM instant_gift_card_codes WHERE currency = :currency AND amount = :amount AND pointsPrice = :pointsPrice AND giftCardId = :id AND status = :status';
			$giftCardsAvailable = $db->prepare($giftCardsAvailableQuery);
			$giftCardsAvailable->bindParam(':currency', $giftCardRow['currency'], PDO::PARAM_STR);
			$giftCardsAvailable->bindParam(':amount', $giftCardRow['amount'], PDO::PARAM_STR);
			$giftCardsAvailable->bindParam(':pointsPrice', $giftCardRow['pointsPrice'], PDO::PARAM_STR);
			$giftCardsAvailable->bindParam(':id', $giftCardRow['id'], PDO::PARAM_INT);
			$giftCardsAvailable->bindValue(':status', 'Available', PDO::PARAM_STR);
			$giftCardsAvailable->execute();
			$gCardsAvailable = $giftCardsAvailable->fetch(PDO::FETCH_COLUMN);
			
			$giftCardsRedeemedQuery = 'SELECT count(*) FROM instant_gift_card_codes WHERE currency = :currency AND amount = :amount AND pointsPrice = :pointsPrice AND giftCardId = :id AND status = :status';
			$giftCardsRedeemed = $db->prepare($giftCardsRedeemedQuery);
			$giftCardsRedeemed->bindParam(':currency', $giftCardRow['currency'], PDO::PARAM_STR);
			$giftCardsRedeemed->bindParam(':amount', $giftCardRow['amount'], PDO::PARAM_STR);
			$giftCardsRedeemed->bindParam(':pointsPrice', $giftCardRow['pointsPrice'], PDO::PARAM_STR);
			$giftCardsRedeemed->bindParam(':id', $giftCardRow['id'], PDO::PARAM_INT);
			$giftCardsRedeemed->bindValue(':status', 'Redeemed', PDO::PARAM_STR);
			$giftCardsRedeemed->execute();
			$gCardsRedeemed = $giftCardsRedeemed->fetch(PDO::FETCH_COLUMN);
			
			if($giftCardRow['giftCardImage']){
				$nameOrImage = '<img src="./images/giftcardrewards/'.$giftCardRow['giftCardImage'].'" alt="'.$giftCardRow['giftCardName'].'" title="'.$giftCardRow['giftCardName'].'">';
			}else{
				$nameOrImage = $giftCardRow['giftCardName'];
			}
			
			if($gCardsAvailable == '0'){
				$redeemAction = 'Out of Stock';
			}
			elseif($userInfo['currentPoints'] < $giftCardRow['pointsPrice']){
				$needed = $giftCardRow['pointsPrice'] - $userInfo['currentPoints'];
				$redeemAction = 'You need '.$needed.' point(s)';
			}
			elseif($userInfo['currentPoints'] >= $giftCardRow['pointsPrice']){
				$redeemAction = '<input type="button" value="Redeem" onclick="if(confirm(\'Are you sure to redeem this prize?\')){location.href=\'index.php?do=instantGiftCards&action=redeem&cardId='.$giftCardRow['id'].'&amount='.$giftCardRow['amount'].'\';}">';
			}
			
			print"
	<tr>
		<td>".$nameOrImage."</td>
		<td style=\"text-align:center\">".$giftCardRow['currency'].$giftCardRow['amount']."</td>
		<td style=\"text-align:center\">".$giftCardRow['pointsPrice']."</td>
		<td style=\"text-align:center\">".$gCardsAvailable."</td>
		<td style=\"text-align:center\">".$gCardsRedeemed."</td>
		<td style=\"text-align:center\">".$redeemAction."</td>
	</tr>";
		
		}
		
	}else{
		
		print"
	<tr>
		<td colspan=\"4\" style=\"text-align:center;color:#2B1B17;padding:15px 0\">No prizes added.</td>
	</tr>";
		
	}
	
print"
</table>";
	
?>

[mogosselin]

The magic keyword here is rowspan. You need to set your row span on your 'title' cells for the number of rows they should take.

 

Here's an example:

<!DOCTYPE html>
<html>
<head>
	<title>Table merge</title>
	<style>
		table tr td {
			border:1px solid black;
			padding: 4px;	
		}
	</style>
</head> 
<body>
<table>
	<tr>
		<td rowspan='2'>Row 1</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
	</tr>
	<tr>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
	</tr>
	<tr>
		<td rowspan='2'>Row 2</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
	</tr>
	<tr>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
		<td>lorem ipsum</td>
	</tr>
	
</table>
</body>
</html>

So, you'll need to loop through your rows and count the number occurrences of the same title. For example, if your 'test prix 2' is displayed 5 times and you want to merge 5 cells vertically, you'll need to set your rowspan to 5 and NOT display all other 4 <td> tags with that title.

[Digitizer]

I am not very good with PDO, but see if i can be of some help or idea :/

try to get count of returned rows and put dynamic rowspan on TR

	$giftCard = $db->prepare($giftCardQuery);
	$giftCard->bindValue(':cardStatus', 'Enabled', PDO::PARAM_STR);
	$giftCard->execute();
	$count = $giftCard->rowCount(); // get count in a variable

and change the html like this

<tr>
  <td rowspan='".$count."'>".$nameOrImage."</td> // here
  <td style=\"text-align:center\">".$giftCardRow['currency'].$giftCardRow['amount']."</td>
  <td style=\"text-align:center\">".$giftCardRow['pointsPrice']."</td>
  <td style=\"text-align:center\">".$gCardsAvailable."</td>
  <td style=\"text-align:center\">".$gCardsRedeemed."</td>
  <td style=\"text-align:center\">".$redeemAction."</td>
</tr>";

Edited August 25, 2014 by Digitizer

[mac_gyver]

to do what mogosselin has suggested, you will need to pre-process the data that the query returns, making an array of arrays, where the main array index is the instant_gift_cards id. then as you are looping through the different instant_gift_cards to display them, you can use count() on the sub-array for each instant_gift_cards to find out the rowspan value.

 

in order to do this, you must first separate the business logic that's getting the data, from the presentation logic that's displaying the data. you should be able to put a comment in your code, above which is only php and database query logic, without any html/css/javascript.. and below which there is no database specific code, just php variables, loops, echo/print statements... that produces the html/css/javascript based on the data that was retrieved by the business logic.

 

you also need to NOT run queries inside of loops. you can write one query that gets all this information at once (time permitting, someone will probably post an example.)

 

edit: also, for static/program produced values, like your status = 'Enabled', there's no point is using a prepared query for that. just build the query with the value in it. place-holders in queries are for data that must be escaped/cast to prevent errors or to prevent sql injection and for queries that will be executed more than once with different values.

 

you queries will also be easier to write and read if you use alias names for the tables being referenced.

[Digitizer]

tried to edit my answer in favor of @mogo, but now cant.. lolz..

and mine is wrong... just tested here

[Psycho]

A few things:

 

1. Don't use the short opening PHP tags (unless you are using the short form to output a variable)

 

2. Don't "mix" the PHP and HTML output. Separate the logic from the output. It will make your code much easier to read and much more flexible.

 

3. Don't put numbers in quotes when you are comparing numerical data

 

if($giftCard->rowCount() > '0')

 

4. Don't run queries in loops! It is a performance killer.

 

To solve this problem, I would suggest putting the query results into a multi-dimensional array to make it easy to know how many rows per parent item when you create the output.

[mac_gyver]

here's a single (one) query that gets all the data your code currently is getting - 

SELECT igc.id, igc.giftCardName, igc.giftCardImage,
    igcc.currency, igcc.amount, igcc.pointsPrice,
    sum(if(igcc.status ='available',1,0)) as available,
    sum(if(igcc.status ='redeemed',1,0)) as redeemed
FROM instant_gift_cards igc
INNER JOIN instant_gift_card_codes igcc ON igc.id = igcc.giftCardId
WHERE igc.status = 'Enabled'
    group by igc.id, igcc.amount
    ORDER BY igc.dateCreated DESC, igcc.amount

another comment about what you are currently doing - you are repeating the currency, amount, and pointsPrice in each same amount row in your instant_gift_card_codes table, for each code under any giftCardId. you should have another table that holds only one copy of the currency, amount, and pointsPrice information. your instant_gift_card_codes table would then only have an id, the id of the corresponding row in this new table, the code, and the status - available/redeemed.

[davidolson]

current DB schema

instant_gift_cards => id -> giftCardName -> giftCardImage -> dateCreated -> status
instant_gift_card_codes => id -> giftCardId -> currency -> amount -> pointsPrice -> giftCardCode -> cardWinner -> dateCreated -> dateRequested -> status

[mac_gyver]

for your current information, the business logic to retrieve the data would be -  

    $query = "
        SELECT igc.id, igc.giftCardName, igc.giftCardImage, igcc.currency, igcc.amount, igcc.pointsPrice,
            sum(if(igcc.status ='available',1,0)) as available,
            sum(if(igcc.status ='redeemed',1,0)) as redeemed
        FROM instant_gift_cards igc
        INNER JOIN instant_gift_card_codes igcc ON igc.id = igcc.giftCardId
        WHERE igc.status = 'Enabled'
        GROUP BY igc.id, igcc.amount
        ORDER BY igc.dateCreated DESC, igcc.amount";
    $stmt = $db->query($query);
    $giftCard = array();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $giftCard[$row['id']][] = $row;
    }

and the presentation logic would be - 

print"
<table style=\"width:100%\" class=\"tableList\">
    <tr>
        <th style=\"width:35%\">Prize Name</th> 
        <th style=\"width:12%\">Amount</th>
        <th style=\"width:12%\">Points</th>
        <th style=\"width:12%\">Available</th>
        <th style=\"width:12%\">Redeemed</th>
        <th style=\"width:17%\">Action</th>
    </tr>";


if(!empty($giftCard)){
    // there's at least one gift card to display
    foreach($giftCard as $arr){
        // $arr is an array of arrays, one sub-element for each amount under each gift card
        $rowspan = count($arr);


        $first = 1; // a flag to detect the first pass through the following loop
        foreach($arr as $row){
            // row will be the database row for each amount under one gift card
            echo "<tr>"; // start a row
            if($first++ == 1){
                // output the one-time information here...
                if($row['giftCardImage']){
                    $nameOrImage = '<img src="./images/giftcardrewards/'.$row['giftCardImage'].'" alt="'.$row['giftCardName'].'" title="'.$row['giftCardName'].'">';
                }else{
                    $nameOrImage = $row['giftCardName'];
                }
                echo "<td rowspan='$rowspan'>$nameOrImage</td>";
            }
            // output the common data for each html table row here...
            
            if($row['available'] == '0'){
                $redeemAction = 'Out of Stock';
            }
            elseif($userInfo['currentPoints'] < $row['pointsPrice']){
                $needed = $row['pointsPrice'] - $userInfo['currentPoints'];
                $redeemAction = 'You need '.$needed.' point(s)';
            }
            elseif($userInfo['currentPoints'] >= $row['pointsPrice']){
                $redeemAction = '<input type="button" value="Redeem" onclick="if(confirm(\'Are you sure to redeem this prize?\')){location.href=\'index.php?do=instantGiftCards&action=redeem&cardId='.$row['id'].'&amount='.$row['amount'].'\';}">';
            }
        
            print"<td style=\"text-align:center\">".$row['currency'].$row['amount']."</td>
                <td style=\"text-align:center\">".$row['pointsPrice']."</td>
                <td style=\"text-align:center\">".$row['available']."</td>
                <td style=\"text-align:center\">".$row['redeemed']."</td>
                <td style=\"text-align:center\">".$redeemAction."</td>
            </tr>";
        }
    }
}else{
    
    print"
    <tr>
        <td colspan=\"4\" style=\"text-align:center;color:#2B1B17;padding:15px 0\">No prizes added.</td>
    </tr>";
}
    
print"
</table>";