[RESOLVED] Fetching data from table only fetching certain rows

TheCase

Hi,
I have a cell to display the comments stored in each record from the table. The problem is it only displays every third record in the database three times, how do I get it to get the correct comment. Hope this makes sence.


  echo " 
  <table width='70%' cellspacing='0' cellpadding='0' border='1' align='center'>

<tr>
<td align='center' width='25%'><b><p>Date</p></b></td>
<td align='center' width='25%'><b><p>Presenter 1</p></b></td>
<td align='center' width='25%'><b><p>Presenter 2</p></b></td>
<td align='center' width='25%'><b><p>Engineer</p></b></td>

</tr>    

   ";


  $colors = array("#df8700", "#006700", "#cf0000");

  $count = 0;

  while ($row = mysql_fetch_assoc($query))
      {
      if ($count == 0)
          {
          echo '<tr>';
          $bg = ($bg == '#FFD5A8' ? '#BBBBBB' : '#FFD5A8');
          echo "<td bgcolor=\"$bg\" align=\"center\" width='25%' ><p class='table'>{$row['show']}</p> </td>";
          }



      if ($row['userid'] == '31')
          {
          echo "<td bgcolor=\"$bg\" align=\"center\" width='25%' ><font color=\"{$colors['1']}\"><p class='table'>None</p> </font></td>";
          }
      else
          {
          echo " <td bgcolor=\"$bg\" align=\"center\" width='25%' ><font color=\"{$colors[$row['status']]}\"><p class='table'>{$row['user']} </font></p> </td>";
          }

      if ($count + 1 == 3)
          {


          echo "</tr> 
          <tr>
          <td></td>
          <td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td>
          <td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td>
          <td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td> 
          </tr>
          ";

           $count = '0';

          }
      else
          {
          ++$count;
          }
      }


  echo "</table><BR>";

Thanks

anoopmail

multiple problems are there in the code. First of all, show how you wrote the mysql_query() line.
I guess you are trying to show 3 comments from each user on the page. Am I correct? First try to get the correct data, then do the code to color it as you want. Mixing both together would break your brain veins 😉

$count = '0';

should be

$count = 0;

{$colors['1']}

should be

{$colors[1]}

TheCase

Hi,

Thanks for your response. I am trying to show 3 comments from each user for each cell. Here is the MySQL query

$query = mysql_query("SELECT showrota.id, userid, `comment`, `status`, `date`, `show`,
  CONCAT( u1.username, ' ', u1.lastname ) AS user
FROM showrota
INNER JOIN users AS u1 ON u1.id = showrota.userid

I would be greatful if you could explain your corrections to my code as I am unsure of the difference between putting the number in quotes or without

Derokorian

Numbers in quotes are strings, numbers without quotes are numeric.

anoopmail

even not an optimum solution, try this

 while ($row = mysql_fetch_assoc($query))
          {
              echo '<tr>';
              $bg = ($bg == '#FFD5A8' ? '#BBBBBB' : '#FFD5A8');
              echo "<td bgcolor=\"$bg\" align=\"center\" width='25%' ><p class='table'>{$row['show']}</p> </td>";
          if ($row['userid'] == '31')
              {
              echo "<td bgcolor=\"$bg\" align=\"center\" width='25%' ><font color=\"{$colors['1']}\"><p class='table'>None</p> </font></td>";
              }
          else
              {
              echo " <td bgcolor=\"$bg\" align=\"center\" width='25%' ><font color=\"{$colors[$row['status']]}\"><p class='table'>{$row['user']} </font></p> </td>";
              }
              echo "</tr>
              <tr>
              <td></td>
              <td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td>";
$row = mysql_fetch_assoc($query) ;
echo "<td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td>" ;
$row = mysql_fetch_assoc($query) ;
echo "<td bgcolor=\"$bg\" align=\"center\" width='25%'><p class='table'>{$row['comment']}</p></td>
              </tr>
              ";

      }

TheCase

Thanks for your help, that was helpful while not being optimum I think this could be down to the table design do you think rather than this:

CREATE TABLE IF NOT EXISTS `showrota` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `show` enum('1','2','3','4') NOT NULL,
  `userid` int(5) NOT NULL,
  `comment` varchar(100) NOT NULL DEFAULT 'No Comment',
  `status` enum('0','1','2') NOT NULL DEFAULT '0',
  `date` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

This would be better?

CREATE TABLE IF NOT EXISTS `shows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` varchar(100) NOT NULL,
  `presenter1id` smallint(5) NOT NULL,
  `presenter2id` smallint(5) NOT NULL,
  `engineerid` smallint(5) NOT NULL,
  `presenter1status` enum('0','1','2') NOT NULL DEFAULT '0',
  `presenter2status` enum('0','1','2') NOT NULL DEFAULT '0',
  `engineerstatus` enum('0','1','2') NOT NULL DEFAULT '0',
  `presenter1comment` varchar(60) NOT NULL DEFAULT 'No Comment',
  `presenter2comment` varchar(60) NOT NULL DEFAULT 'No Comment',
  `egineercomment` varchar(60) NOT NULL DEFAULT 'No Comment',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Although with the last option if the user wanted to update their comment I will have to do an if function to determine if they are presenter1comment, presenter2comment or engineercomment, I shouldn't think this matters though and would end up have an optimum code

anoopmail

If u r max of 2 presenter, 1 engineer per show, it seems fine.

TheCase

Thanks for your help