Display specific image depending on specific text in MySQL table using PHP

Question

In my MySQL database I have table named game with columns: sport_cat, timelive, match_title, selection,

I am exactly this MySQL query SELECT statement for displaying them in grid view on site:

<?
$qry = "
SELECT sport_cat,match_title,selection,
CASE 
WHEN game.result LIKE '' THEN 'PENDING'
WHEN game.result LIKE game.selection THEN 'WON'
WHEN game.result NOT LIKE game.selection THEN 'LOST'
END AS result
FROM game
";
$searchText = "";
if($_REQUEST['search_text']!=""){
$searchText = mysql_real_escape_string($_REQUEST['search_text']);
$qry .=" WHERE sport_cat.timelive LIKE '%$searchText%' " . 
       " OR game.timelive LIKE '%$searchText%' " .
       " OR game.match_title LIKE '%$searchText%' ";      
}

$qry .= " ORDER BY timelive DESC";

$obj = new pagination_class($qry,$starting,$recpage);       
$result = $obj->result;

?>

In first column sport_cat there are few possible text inputs: football, tennis, basketball.

HTML of my file where this part is looks like this:

<table>
<?if(mysql_num_rows($result)!=0){
$counter = $starting + 1;
while($data = mysql_fetch_array($result)) {?>
<tr>
<td align="center"><? echo $data['sport_cat']; ?></td>
<td align="center"><? echo $data['timelive']; ?></td>
<td align="center"><? echo $data['match_title']; ?></td>
<td align="center"><? echo $data['selection']; ?></td>
</tr>
<?
$counter ++;
} ?>
</table>

What I am trying to do is to display specific picture in those cell according to that input in cell. So if there is football want to display football.png picture or if there is tennis want to display tennis.png or if there is basketball want to display basketball.png. Also, if there is maybe some unknown input like cricket and don't have picture specified for that word would like to display unknown.png picture. Pictures are stored under images/icons on my server.

Now i'm not sure how to implement this code regarding picturing issue into this already existent i have. Thanks.

ADDED full example code: 03.11.2011.:

<?php
error_reporting(E_ALL^E_NOTICE);
include('pagination_class.php');
mysql_connect('xxx.xxx.xxx.xx', 'xxxxxx', 'xxxxxxxx') or die(mysql_error());
mysql_select_db('xxxxxxx');

?>
<script language="JavaScript" src="pagination.js"></script>
<link rel="stylesheet" type="text/css" href="style.css" />

<?
$qry = "
SELECT sport_cat,bidprice,timelive,match_title,selection,winnings_with_startamount,odds,odds*10 AS gainedodds,username,
CASE 
WHEN game.result LIKE '' THEN 'pending'
WHEN game.result LIKE game.selection THEN 'WON'
WHEN game.result NOT LIKE game.selection THEN 'LOST'
END AS result
FROM game
";
$searchText = "";
if($_REQUEST['search_text']!=""){
$searchText = mysql_real_escape_string($_REQUEST['search_text']);
$qry .=" WHERE game.sport_cat LIKE '%$searchText%' " . 
       " OR game.timelive LIKE '%$searchText%' " .
       " OR game.match_title LIKE '%$searchText%' " .
       " OR game.selection LIKE '%$searchText%' " .
       " OR game.username LIKE '%$searchText%'";
}

$qry .= " ORDER BY timelive DESC";

//for pagination
$starting=0;
$recpage = 10;//number of records per page

$obj = new pagination_class($qry,$starting,$recpage);       
$result = $obj->result;


function getStyleColorForStatus($status) {
  if ($status == 'WON') {
    return '#99ff99';
  }
  else if ($status == 'LOST') {
    return '#ff9999';
  }
  else if ($status == 'pending') {
    return '#e5e5e5';
  }
  return '';
}

?>

        <form name="form1" action="testpage.php" method="POST" style="background-color:#f9f9f9; -webkit-box-shadow: 0 1px 10px rgba(0,0,0,.1); -moz-box-shadow: 0 1px 10px rgba(0,0,0,.1); box-shadow: 0 1px 10px rgba(0,0,0,.1);">

        <table border="0" align="center" width="1000px" padding-left="0px" style="background:#F9F9F9;">

          <TD colspan="0" style="padding-left:0px; padding-bottom:5px; padding-top:10px; text-align:center;">
            Search: <input type="text" name="search_text" value="<?php echo $searchText; ?>">
                <input type="submit" value="Search">
          </TD> 

        <tr><TD colspan="0">

        <div id="page_contents">

            <table width="968px" cellspacing="0" cellpadding="5" align="center" frame="box" rules="none" style="padding-bottom:20px; margin-bottom:8px;margin-top:3px; -webkit-box-shadow: 0 1px 10px rgba(0,0,0,.1); -moz-box-shadow: 0 1px 10px rgba(0,0,0,.1); box-shadow: 0 1px 10px rgba(0,0,0,.1); border: 1px solid #cccccc;">
            <tr style="height: 50px;">
<td width="10%" align="center" td class="winheader"><div class="glowtext">Event Category</div></td></td>
<td width="5%" align="center" td class="winheader"><div class="glowtext">Bid Cost</div></td></td>
<td width="10%" align="center" td class="winheader"><div class="glowtext">Event Start Time</div></td></td>
<td width="35%" align="center" td class="winheader"><div class="glowtext">Market/Event</div></td></td>
<td width="10%" align="center" td class="winheader"><div class="glowtext">Selection</div></td></td>
<td width="10%" align="center" td class="winheader"><div class="glowtext">Winnings</div></td></td>
<td width="5%" align="center" td class="winheader"><div class="glowtext">Odds</div></td></td>
<td width="5%" align="center" td class="winheader"><div class="glowtext">Gained Odds</div></td></td>
<td width="10%" align="center" td class="winheader"><div class="glowtext">Winning Bidder</div></td></td>
<td width="10%" align="center" td class="winheader"><div class="glowtext">Final Result</div></td></td>
            </tr>
            <?
            if(mysql_num_rows($result)!=0){
                $counter = $starting + 1;
                $pathImg = "/images/icons/";
                while($data = mysql_fetch_array($result)) {
                    //calculate url image
                    $pathFile = $pathImg . $data['sport_cat'] . ".png";
                    if (!file_exists($pathFile)) {
                        $pathFile = $pathImg . "unknown.png";
                    }
                    ?>

    <tr class="initial" onMouseOver="this.className='highlight'" onMouseOut="this.className='normal'">

                    <td align="center"><img src="<?=$pathFile?>" alt="<?=$data['sport_cat']?>"></TD>
                    <td align="center"><? echo $data['bidprice']; ?></TD>
                    <td align="center"><? echo $data['timelive']; ?></TD>
                    <td align="left"><? echo $data['match_title']; ?></TD>              
                    <td align="left"><? echo $data['selection']; ?></TD>
                    <td align="center"><font color="green">&euro; <? echo $data['winnings_with_startamount']; ?></TD>
                    <td align="center"><? echo $data['odds']; ?></TD>
                    <td align="center"><? echo $data['gainedodds']; ?></TD>
                    <td align="center"><? echo $data['username']; ?></TD>
                    <td align="center" style="background-color:<?php echo getStyleColorForStatus($data['result']); ?>"><? echo $data['result']; ?></td>
                    </tr>

                <?
                    $counter ++;
                } 
            }
        ?>

                <tr><TD align="center" colspan="10" style="padding-bottom:1px; padding-top:10px; color:#333;"><? echo $obj->anchors; ?></TD></tr>
                <tr><TD align="center" colspan="10" style="padding-bottom:10px; padding-top:5px; color:#333;"><? echo $obj->total; ?></TD></tr>
            <?}else{?>
                <tr><TD align="center" colspan="10" style="padding-bottom:10px padding-top:10px; color:red;">No Data Found</TD></tr>
            <?}?>
            </TD></tr>
            </table>
        </div>
        </tr>
        </TD>
    </form>
    </table>

Show us what you've tried so far. We can help fix that. But we won't write it from scratch for you. This is a "please help" site, not "please do it for me" — Marc B
– Marc B, Commented Nov 2, 2011 at 14:34

JellyBelly · Accepted Answer · 2011-11-03 08:33:45Z

4

try this:

$sql = "SELECT * FROM game";
$result = mysql_query($sql) or die(mysql_error());
$pathImg = "/images/icons/";
while ($row = mysql_fetch_array($result)) {
    $pathFile = $pathImg . $row['sport_cat'] . ".png";
    if (!file_exists($pathFile)) {
        $pathFile = $pathImg . "unknown.png";
    }
    echo '<img src="' . $pathFile . '">';
}

You edit Ask and I edit Anwer :P

<table>
    <?
    if (mysql_num_rows($result) != 0) {
        $counter = $starting + 1;
        $pathImg = "/images/icons/";
        while ($data = mysql_fetch_array($result)) {
            //calculate url image
            $pathFile = $pathImg . $data['sport_cat'] . ".png";
            if (!file_exists($pathFile)) {
                $pathFile = $pathImg . "unknown.png";
            }
            ?>
            <tr>
                <td align="center"><img src="<?=$pathFile?>" alt="<?=$data['sport_cat']?>"></td>
                <td align="center"><? echo $data['sport_cat']; ?></td>
                <td align="center"><? echo $data['timelive']; ?></td>
                <td align="center"><? echo $data['match_title']; ?></td>
                <td align="center"><? echo $data['selection']; ?></td>
            </tr>
            <?
            $counter++;
        }
    }
    ?>
</table>

edited Nov 3, 2011 at 8:33

answered Nov 2, 2011 at 14:39

JellyBelly

2,4312 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ivy Over a year ago

Thanks for reply, now I see I didn't explain well my question as cannot use this code in my case, so now modified it with more details...

Ivy Over a year ago

Argh! @JellyBelly cannot get it work with your code, just receiving Server Error when adding this code on file. So added more code at my question which I have there to clearly explain better problem. Added HTML part of code so please take a look! other html having there i think is not important. Thanks for patience, I am total beginner.

JellyBelly Over a year ago

@Ivy: I edit my answer! Before you received a fatal error because I thought he was already an array $result however was still undergoing treatment mysql_fetch_array! :D

Ivy Over a year ago

To @JellyBelly: again just receiving Server error after your last modification :( Now in my initial question at the end of post I put copy/paste of whole file code just to be sure. Please take a look now on my answer continuation above to see for sure what is wrong there and to make it work.

Ivy Over a year ago

Oh sorry, didn't notice your answer here before! Here is error received in Chrome:

HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request.

Scott Rowley · Accepted Answer · 2011-11-02 14:43:01Z

1

Something like the following should work.

<?php
$sql = "SELECT * FROM game";
$result = mysql_query($sql)or die(mysql_error());
while ($row = mysql_fetch_array($result)){
// Show images

if($row['sport_cat']=='football'){
 print "<img src='football.png'/>";
}
elseif($row['sport_cat']=='basketball'){
 print "<img src='basketball.png'/>";
}
elseif($row['sport_cat']=='tennis'){
 print "<img src='tennis.png'/>";
}else{
 print "<img src='unknown.png' />";
}


} // End while loop
?>

edited Nov 2, 2011 at 14:43

answered Nov 2, 2011 at 14:36

Scott Rowley

4841 gold badge8 silver badges30 bronze badges

1 Comment

Ivy Over a year ago

Hi Scott. Thanks for reply. Could you try to modify this according to my edited question above now? Sorry, I didn't explain right first time :(

Collectives™ on Stack Overflow

Display specific image depending on specific text in MySQL table using PHP

2 Answers 2

You edit Ask and I edit Anwer :P

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You edit Ask and I edit Anwer :P

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related