I need help with a query which I do not have the knowledge to implement. I am aware that mysql functions are deprecated but my website is only a prototype so it is not important for the purpose of this project.
I have a site which is used to display video tutorials for users for training purposes. Users are presented with list of 14 questions with checkboxes, they can tick the relevant checkboxes and view each associated tutorial on the next page.
The user_id of the person logged on also inserts into my database so I can identify who has selected the checkboxes, along with each checkbox value (1 or 0) into a seperate column in my answers table (enisatanswer). Here is my code for inserting which is working fine. The SELECT statement at the start is to maintain my SESSION details of the user, and is used across each page.
<?php
session_start();
include_once 'dbconnect.php';
if(!isset($_SESSION['user']))
{
header("Location: index.php");
}
$res=mysql_query("SELECT * FROM users WHERE user_id=".$_SESSION['user']);
$userRow=mysql_fetch_array($res);
if(isset($_POST['submit']))
{
header("Location: eNISATVids.php");
@$userID=$_SESSION['user'];
@$checkbox1=$_POST['Log'];
@$checkbox2=$_POST['Worktray'];
@$checkbox3=$_POST['Visual'];
@$checkbox4=$_POST['ChangePd'];
@$checkbox5=$_POST['Logout'];
@$checkbox6=$_POST['ClientSearch'];
@$checkbox7=$_POST['StartAssessment'];
@$checkbox8=$_POST['Finalise'];
@$checkbox9=$_POST['Print'];
@$checkbox10=$_POST['Hcn'];
@$checkbox11=$_POST['Lcid'];
@$checkbox12=$_POST['Soscare'];
@$checkbox13=$_POST['Reassign'];
@$checkbox14=$_POST['Close'];
$query="INSERT INTO enisatanswer (user_id,Log,Worktray,Visual,ChangePd,Logout,ClientSearch,StartAssessment,Finalise,Print,Hcn,Lcid,Soscare,Reassign,Close) VALUES
('$userID', '$checkbox1', '$checkbox2','$checkbox3', '$checkbox4', '$checkbox5', '$checkbox6','$checkbox7', '$checkbox8','$checkbox9', '$checkbox10','$checkbox11', '$checkbox12', '$checkbox13', '$checkbox14')";
mysql_query($query) or die (mysql_error() );
if($query==true)
{
echo'<script>alert("Your choices have inserted Successfully \n \n Please click on Display eNISAT Tutorials at the buttom of the page to view your videos ")</script>';
}
else
{
echo'<script>alert("Failed To Insert")</script>';
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Welcome - <?php echo $userRow['username']; ?></title>
/<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="header">
<div id="left">
<label>NHSCT eNISAT Tutorials</label>
</div>
<div id="right">
<div id="content">
Welcome <?php echo $userRow['forename']; ?> <a href="logout.php?logout">Sign Out</a>
</div>
</div>
</div>
<br>
<p align="center"><img src="title.jpeg" width="400"height="100" alt="title.jpeg">
<br>
<br>
<center>
<h2>Please select the tasks you require assistance with, before clicking DISPLAY ENISAT TUTORIALS:<h2>
<br>
<table align="center" height="0" width="70%" border="1" bgcolor = "white">
<form action="" method="post"
<tr>
<td colspan="2">Tick each relevant box:</td>
</tr>
<tr>
<td>How to login</td>
<td><input type="checkbox" name="Log" value="1"></td>
<tr>
<td>How to manage your worktray</td>
<td><input type="checkbox" name="Worktray" value="1"></td>
<tr>
<td>How to change your visual settings (Colours and text size)</td>
<td><input type="checkbox" name="Visual" value="1"></td>
</tr>
<tr>
<td>How to change your own password on the system</td>
<td><input type="checkbox" name="ChangePd" value="1"></td>
</tr>
<tr>
<td>How to logout of the system</td>
<td><input type="checkbox" name="Logout" value="1"></td>
</tr>
<tr>
<td>How to search for a client on the system</td>
<td><input type="checkbox" name="ClientSearch" value="1"></td>
</tr>
<tr>
<td>How to start an assessment</td>
<td><input type="checkbox" name="StartAssessment" value="1"></td>
</tr>
<tr>
<td>How to finalise an assessment</td>
<td><input type="checkbox" name="Finalise" value="1"></td>
<tr>
<td>How to print an assessment</td>
<td><input type="checkbox" name="Print" value="1"></td>
</tr>
<tr>
<td>How to create a client and referral manually through Find on H+C</td>
<td><input type="checkbox" name="Hcn" value="1"></td>
</tr>
<tr>
<td>How to submit a referral from LCID (LCID Users only)</td>
<td><input type="checkbox" name="Lcid" value="1"></td>
</tr>
<tr>
<td>How to submit a referral from Soscare (Soscare Users only)</td>
<td><input type="checkbox" name="Soscare" value="1"></td>
</tr>
<tr>
<td>How to reassign a referral on eNISAT</td>
<td><input type="checkbox" name="Reassign" value="1"></td>
</tr>
<tr>
<td>How to close a referral on eNISAT</td>
<td><input type="checkbox" name="Close" value="1"></td>
</tr>
<tr>
<td <td><button name="submit" type="submit" onclick="window.location.href='eNISATVids.php'">Display eNISAT Tutorials</button></td>
</tr>
</table>
</div>
</form>
</body>
</html>
My next page at present displays links in a table for all 14 of my videos from my questions table 'enisatquestion'.
My question is, can anyone help me with a query to only display the video links for the the checkboxes that were selected by the user, instead of them all. I am guessing that in order to do this a variable would need to be created for each column from my 'enisatanswer' table. My columns are (Log,Worktray,Visual etc)
So my query i presume would be something like:
SELECT * FROM enisatquestion WHERE enisatanswer (Log,Worktray,Visual....) VALUES =1 and user_id = $userID=$_SESSION['user'];
I am really not sure about this Statement
Any help would be greatly appreciated. Here is my code to display my videos
<?php
session_start();
include_once 'dbconnect.php';
if( !isset( $_SESSION['user'] ) ) header("Location: index.php");
$res=mysql_query("SELECT * FROM users WHERE user_id=".$_SESSION['user']);
$userRow=mysql_fetch_array( $res );
$query = "SELECT eNISATQuestion, eNISATVideo FROM enisatquestion";
$result = mysql_query( $query );
/* A default message if the query fails or there are no records */
$enisatquestion='<h2>Sorry, there are no records</h2>';
/* you cannot output content outside the html tags, not valid ~ it will work but NO */
if( $result ) {/* if there is a recordset, proceed and generate html table */
$enisatquestion = "<table >";
while ( $row = mysql_fetch_assoc($result) ) {
$enisatquestion .= "<tr><td><a href='{$row['eNISATVideo']}'>{$row['eNISATQuestion']}</a></td></tr>";
}
$enisatquestion .= "</table>";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Welcome - <?php echo $userRow['username']; ?></title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="header">
<div id="left">
<label>NHSCT eNISAT Tutorials</label>
</div>
<div id="right">
<div id="content">
Welcome <?php echo $userRow['forename']; ?> <a href="home.php?home">Return to Homepage</a> <a href="logout.php?logout">Sign Out</a>
</div>
</div>
<br>
<br>
<br>
<br>
<p align="center"><img src="title.jpeg" width="400"height="100" alt="title.jpeg">
<br>
<br>
<center>
<h2>Click on the each link to open your tutorial in Windows Media Player<h2>
<br>
<?php
/* output the html table here, below your header */
echo $enisatquestion;
/*
If the query failed then the default gets displayed
*/
?>
</div>
</body>
</html>
