As the title says when I am creating a drop down list with my mysqli query in my second form (attendanceOut.php) I'm not getting any items. I am already pulling some of the info from a previous drop down box in my attendance.php which works correctly as far as I can tell. I'm sure it has to do with the fact that this one is being pulled from an INNER JOIN but I can't find where I've messed up on the syntax. Any help would be greatly appreciated. So without further ado some code.
<?php
$course = $_GET["CourseName"];
$startTime = $_GET["BeginTime"];
$course = trim($course);
$course = strip_tags($course);
$startTime = trim($startTime);
$startTime = strip_tags($startTime);
?>
<html>
<meta http-equiv="Content-Type" content="text/html"; charset=utf-8">
<body>
<?php
$mydbconn = new mysqli("www.evilscriptmonkeys.com", "capstone", "capstone","attendance");
if (mysqli_connect_errno())
{
printf("connection failed: %s
", mysqli_connect_error());
}
if ($_GET['action'] == 'Create Form')
{
//$result = mysqli_query($mydbconn, "SELECT * FROM student WHERE CourseName = ' " .$course."'");
$c =$course;
$s = $startTime;
$sql = ("SELECT * FROM student WHERE CourseName = '$c' AND Time > '$s'");
$result = $mydbconn->query($sql);
printf ($course);
printf ($startTime);
echo '<table border="1">';
$classFields = $result->fetch_fields();
foreach ($classFields as $classData)
{
echo "<th> $classData->name </th>";
}
while ($row = $result->fetch_assoc())
{
echo "<tr>";
foreach($row as $col=>$val)
{
echo "<td> $val </td>";
}
echo "</tr>";
}
}
?>
<form method = "get" action="changeID.php">
Student Name: <select Name='UserName'>
<option value = "">---Select---</option>
<?php
if ($_GET['action'] == 'Edit Student ID')
{
$c =$course;
$result = mysqli_query($mydbconn, "SELECT s2.UserName
FROM student s1
INNER JOIN students s2
ON s1.UserName = s2.UserName
WHERE s1.CourseName = '$c'");
while(($row = mysqli_fetch_assoc($result)))
{
echo "<option value=\" " . $row['s2.UserName'] . "\">". $row['s2.UserName'] . "</option>";
}
}
?>
</select>
<input type="submit" name="action" value="Change" />
</body>
</html>
You can use an alias and then access with it in the array:
<?php
if ($_GET['action'] == 'Edit Student ID')
{
$c =$course;
$result = mysqli_query($mydbconn, "SELECT s2.UserName AS UserName
FROM student s1
INNER JOIN students s2
ON s1.UserName = s2.UserName
WHERE s1.CourseName = '$c'");
while(($row = mysqli_fetch_assoc($result)))
{
echo "<option value=\" " . $row['UserName'] . "\">". $row['UserName'] . "</option>";
}
}
?>
Hope this help.