0

I have two tables 'quiz' and 'topics'.
The relevant columns in 'quiz' are 'passState' (which returns 1 or 0 representing true/false) and 'managerId' which is a record number referring to a particular administrator.
The relevant columns in 'topics' are 'managerId' and six other columns named 'equip1' .... 'equip6' respectively, which refer to the allocation of particular quizzes to particular student groups. Each quiz can be allocated to none or any or all of the groups 'egroup1' to 'egroup6' by the allocation of true/false value to each.
The code below displays all of the required data on the page, but not in the layout I want. Despite the use of SELECT DISTINCT the data is returned in duplicate. As well as that, the image file 'tick2.png' (row 28) is not returned in any meaningful way. Firstly, it should only be returned when 'passState' has a value of 1. Secondly, it is being returned to all of the rows in the array, but only to one of the duplicate rows but randomly it seems.
I don't understand what's wrong. I appreciate that this is a long winded explanation, but I'd really appreciate some help.

<?php
//$query1 = mysql_query("SELECT DISTINCT comment, url_big, url_small, title, pdf, comment FROM topics WHERE managerId = $managerId AND $egroup = 1 ORDER BY title"); 
$query1 = mysql_query("SELECT DISTINCT comment, url_big, url_small, title, pdf, passState
					  FROM topics
					  INNER JOIN quiz
					  ON topics.managerId = quiz.managerId
					  WHERE ( topics.$egroup = 1
					  AND quiz.userId = '$userId' )
					  ORDER BY topics.title
					  ");
?>
                  <table width="850" style="font-size:16px">
                    <tr>
                      <?php
while ($row1 = mysql_fetch_array($query1))
{
?>
                      <td width="45"></td>
                      <td width="132" style="vertical-align:middle;"><?php echo "<a href='../../wood/wood_tool_images/{$row1['url_big']}' target='_blank'><img src = '../../wood/wood_tool_images/{$row1['url_small']}' /><br />\n"; ?></td>
                      <td width="25"></td>
                      <td width="172" style="vertical-align:middle;"><?php echo "{$row1['title']} <br />\n"; ?></td>
                      <td width="25"></td>
                      <td width="132" style="vertical-align:middle;"><?php echo "<a href='../equipment/{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n"; ?></td>
                      <td width="25"></td>
                      <td width="112" style="vertical-align:middle; text-align:right;"><?php echo "<a href='../../wood/wood_tool_images/pdf/{$row1['pdf']}' target='_blank'><img src='../../wood/wood_tool_images/pdf/icon.jpg' /><br />\n"; ?></td>
                      <td width="15"></td>
                      <td width="132" style="vertical-align:middle; text-align:left;" ><?php echo "{$row1['comment']} <br />\n"; ?></td>
                      <td width="45"><?php if ("{$row1['passState']}" == 1) {echo "<img src=' ../../wood/wood_tool_images/tick2.png' /><br />\n";}?></td>
                    </tr>
                    <?php
}
?>
                  </table>
2
Contributors
6
Replies
7
Views
6 Years
Discussion Span
Last Post by smantscheff
0

First try to solve your MySQL problem, then the PHP problem.
Submit a test case which contains the relevant CREATE TABLE statements, some INSERT statements to fill the tables with test data and your query which goes amiss. Then anyone will gladly try to look into your problem.
As of now, your PHP code is quite unreadable. Separate styles from HTML with CSS and PHP logic from HTML with variables. Do all your PHP logic before the output is generated.

0

First try to solve your MySQL problem, then the PHP problem.
Submit a test case which contains the relevant CREATE TABLE statements, some INSERT statements to fill the tables with test data and your query which goes amiss. Then anyone will gladly try to look into your problem.
As of now, your PHP code is quite unreadable. Separate styles from HTML with CSS and PHP logic from HTML with variables. Do all your PHP logic before the output is generated.

I hope this is what you are asking for.

CREATE TABLE IF NOT EXISTS `topics` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `managerId` int(11) NOT NULL,
  `equipname` varchar(100) NOT NULL,
  `pdf` varchar(50) NOT NULL,
  `comment` varchar(100) NOT NULL,
  `title` varchar(100) NOT NULL,
  `url_big` varchar(100) NOT NULL,
  `url_small` varchar(100) NOT NULL,
  `egroup1` int(4) NOT NULL DEFAULT '0',
  `egroup2` int(4) NOT NULL DEFAULT '0',
  `egroup3` int(4) NOT NULL DEFAULT '0',
  `egroup4` int(4) NOT NULL DEFAULT '0',
  `egroup5` int(4) NOT NULL DEFAULT '0',
  `egroup6` int(4) NOT NULL DEFAULT '0',
  `egroup7` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `managerId` (`managerId`),
  KEY `equipname` (`equipname`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=242 ;

INSERT INTO `topics` (`id`, `managerId`, `equipname`, `pdf`, `comment`, `title`, `url_big`, `url_small`, `egroup1`, `egroup2`, `egroup3`, `egroup4`, `egroup5`, `egroup6`, `egroup7`) VALUES
(220, 38, 'airnailerforbrads', 'airnailerforbradssmall.pdf', 'Read this Air Nailer for Brads instruction sheet.', 'Air Nailer for Brads', 'airnailerforbradsbig.jpg', 'airnailerforbradssmall.jpg', 0, 0, 0, 1, 1, 1, 0),
(219, 38, 'hollowchiselmortiser', 'hollowchiselmortisersmall.pdf', 'Read the Hollow Chisel Mortiser instruction sheet.', 'Hollow Chisel Mortiser', 'hollowchiselmortiserbig.jpg', 'hollowchiselmortisersmall.jpg', 0, 0, 1, 1, 1, 1, 0),
(217, 38, 'woodlathe', 'woodlathesmall.pdf', 'Read the Wood Lathe instruction sheet.', 'Wood Lathe', 'woodlathebig.jpg', 'woodlathesmall.jpg', 0, 1, 1, 1, 1, 1, 0);
(216, 38, 'shaper', 'shapersmall.pdf', 'Read the Shaper instruction sheet.', 'Shaper', 'shaperbig.jpg', 'shapersmall.jpg', 0, 0, 0, 0, 0, 1, 0),
(215, 38, 'jointersurfacercombo', 'jointersurfacercombosmall.pdf', 'Read the Jointer Surfacer Combo instruction sheet.', 'Jointer Surfacer Combo', 'jointersurfacercombobig.jpg', 'jointersurfacercombosmall.jpg', 0, 0, 0, 0, 0, 1, 0),
(214, 38, 'jointer', 'jointersmall.pdf', 'Read the Jointer instruction sheet.', 'Jointer', 'jointerbig.jpg', 'jointersmall.jpg', 0, 0, 0, 0, 0, 1, 0),


CREATE TABLE IF NOT EXISTS `quiz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quizId` varchar(255) NOT NULL,
  `quizTitle` varchar(255) NOT NULL,
  `managerId` int(11) NOT NULL,
  `userId` varchar(255) NOT NULL,
  `userGroup` varchar(60) NOT NULL,
  `userScore` float NOT NULL,
  `totalScore` float NOT NULL,
  `passScore` float NOT NULL,
  `passState` tinyint(4) NOT NULL,
  `Result` mediumtext NOT NULL,
  `userDate` date NOT NULL,
  `addDate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;

INSERT INTO `quiz` (`id`, `quizId`, `quizTitle`, `managerId`, `userId`, `userGroup`, `userScore`, `totalScore`, `passScore`, `passState`, `Result`, `userDate`, `addDate`) VALUES
(21, '100709143305', 'Saw Scroll Safety Quiz', 38, 'Jack Jones', 'year7', 15, 15, 15, 1, '', '2010-10-18', '2010-10-18 00:00:00'),
(37, '1234567890', 'Random Orbit Sander Safety Quiz', 38, 'Jack Jones', 'Year10', 15, 15, 15, 1, '', '2010-10-24', '0000-00-00 00:00:00'),
(39, '1233234545', 'Bandsaw Safety Quiz', 38, 'Jack Jones', 'Year9', 12, 13, 15, 0, '', '2010-10-20', '2010-10-03 18:47:32');

and

<?php
$query1 = mysql_query("SELECT DISTINCT comment, url_small, title, pdf, passState
					  FROM topics
					  INNER JOIN quiz
					  ON topics.managerId = quiz.managerId 
					  WHERE ( topics.$egroup = 1
					  AND quiz.userId = '$userId' )
					  ORDER BY topics.title
					  ");

	while ($row1 = mysql_fetch_array($query1))
	{
echo "<img src = '../{$row1['url_small']}' /><br />\n"; 
echo "{$row1['title']} <br />\n"; 
echo "<a href='../{$row1['title']} .php'>blah, blah</a><br />\n"; 
echo "<a href='../{$row1['pdf']}' ><img src='filename1.jpg' /><br />\n"; 
echo "{$row1['comment']} <br />\n";
	if ("{$row1['passState']}" == 1) {echo "<img src='filename2.png' /><br />\n";} else {echo " ";} 
	}
?>
-2

Table structure and data are a good start.
It would be a better start if it would work. Your insert statements contain a syntax error which proves that you did not test them. Plese do not leave all your homework to others.
When you have corrected the error (probably only a typo), prepare the SQL query which you are unhappy with and present it in an repeatable form. Your query contains PHP variables which are not known to us. Of course it would not be hard to reconstruct them, but, as I said before, don't let us do your homework.
Wnen you have the query, please show us the query and the results and tell us why you are unhappy with them.

0

I just thought you should know that I am not doing homework, but am a 62 year old who is trying to learn this stuff. Your response is less than helpful. Please don't make assumptions about people in you forum comments. Better that you stick to what you know.

Table structure and data are a good start.
It would be a better start if it would work. Your insert statements contain a syntax error which proves that you did not test them. Plese do not leave all your homework to others.
When you have corrected the error (probably only a typo), prepare the SQL query which you are unhappy with and present it in an repeatable form. Your query contains PHP variables which are not known to us. Of course it would not be hard to reconstruct them, but, as I said before, don't let us do your homework.
Wnen you have the query, please show us the query and the results and tell us why you are unhappy with them.

0

If you chose to argue instead of repairing your test case until at least the problem becomes clear, fine with me.
If you instead opt for getting helped, please submit a reproducable test case.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.