Page 1 of 1

Help with insert into database code

Posted: Tue Jan 12, 2010 10:47 pm
by grozanc
Hello All,

I'm trying to create a simple "Take Attendance" page. I've listed the HTMl code and the PHP code that is supposed to dump everything into the database. My problem, is that the code is only dumping the last name on the check list into the datbase, and the function I'm trying to use isn't generating the date. Of course, all this is code I found on forums and I'm just piecing it together since I'm a newbie when it comes to PHP, so don't assume that I know what I'm doing, and the code that I'm listing is almost as is, except I have the form calling the PHP code instead of putting it all in one form. Please let me know if you have any suggestions, school starts next week, and thanks in advance!

Code: Select all

 
<form action="script.php" method="POST">
Student #1 <input type="checkbox" name="student[]" value="999999"><br />
Student #2 <input type="checkbox" name="student[]" value="666666">
<br/>
<INPUT TYPE="SUBMIT" VALUE="Submit" >
</FORM>
 
<?php
$host = 'xxxxxxxxxxxx';
$user = 'xxxxxxxxxxxx';
$pass = 'xxxxxxxxxxxx';
$db   = 'xxxxxxxxxxxx';
mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
 
$classdate = strtotime($_POST['date']);
foreach($_POST['student'] as $oasis_id);
$query = "INSERT INTO xxxxxxxxxxxx (oasis_id, classdate)".
"values ('$oasis_id','$classdate')";
mysql_query($query) or die('Error, query failed : ' . mysql_error()); 
?>
 

Re: Help with insert into database code

Posted: Wed Jan 13, 2010 1:43 am
by manohoo
I noticed incorrect foreach structure, it should be something like this:

Code: Select all

foreach($_POST['student'] as $oasis_id) {
   $query = "INSERT INTO xxxxxxxxxxxx (oasis_id, classdate) values ('$oasis_id','$classdate')";
   mysql_query($query) or die('Error, query failed : ' . mysql_error()); 
}
... the above assumes $_POST['student'] to be an array

Re: Help with insert into database code

Posted: Wed Jan 13, 2010 3:44 am
by ramblin54321
Hi Grozanc,
I don't like the html form because it doesn't have names and because so many tiny checkmarks can easily be error prone and <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> off a student if you check the wrong one. And you don't say anything about the output that it is going to give you, are you going to have to print out a report every day? I recommend that you start by just having a roster in the database which I will give you the code for now. Then tomorrow or the next day, I will give you the code to mark each student as present or absent as you call their name. It will then have functionality to later change to excused absence or tardy and will also allow you to enter points for homework, quizes, midterms, and the final exam.

First, use phpadmin to make a database called 'students', table called 'names', and two varchar 30 fields called 'Last' and 'First'. Notice the capitalization. I made a user 'teacher' with password 'pencil' and gave that user full permissions.

Your database settings include file is as follows and goes in a file called 'studentsSettings.php' in a folder called 'Teacher' under your htdocs or document root folder:

Code: Select all

<?
$host = 'localhost';
$username = 'teacher';
$password = 'pencil';
$database = 'students';
?>
Your form to enter the roster names is called 'Roster.htm' and goes in the Teacher folder:

Code: Select all

<html>
<body onload="document.forms[0].reset(), document.forms[0].Last.focus()">
<form action="../Teacher/CreateRoster.php" method="post">
<table>
<tr><td>last name:</td><td>first name:</td></tr>
 <tr><td><input type="text" size="30" name="Last"></td>
 <td><input type="text" size="30" name="First"></td>
<tr></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td align='right'><input type="Submit" value='enter'></td></tr> 
</table>
</form> 
</body>
</html>
Your script to place your entries into the database is called 'CreateRoster.php' and goes in the Teacher folder:

Code: Select all

<?
//open the database
include('../Teacher/studentsSettings.php');
$db = mysql_connect(localhost,$username,$password)
    or
die("could not connect");
mysql_select_db($database) or die( "Unable to select database");
//take the form variables out of the post array and put into non-array variables
$last = $_POST['Last'];
$first = $_POST['First'];
//take the regular variables and put them into the corresponding table field names prior to inserting into SQL
$sql = "INSERT INTO names SET        
Last= '$last',
First='$first'";
//insert posted entries into database table
$inserted = mysql_query($sql, $db) or die(mysql_error());
//print a heading for the output page
echo ("<b><center><font color='#330099' size='4'>Student Attendance<br>");
//output the table of entries
print("<table border='5' width = '100%' cellspacing='2' cellpadding='2'>");
print("<tr><th> Last Name </th> <th> First Name </th></tr>");
//order by last name ascending
$query = "SELECT * FROM names ORDER BY Last";
//take everything from the database which is in the form of a mysql array and it will go into a php array variable called result
$result = mysql_query($query) or die("Couldn't execute query");
//while there are rows in the array do the following:
while ( $row = mysql_fetch_array($result) ) {
//put the array variables back into regular variables
$last= $row["Last"]; 
$first= $row["First"];
//print what you got before looping to the while statement for the next row
print("<tr><td width='50%'>$last </td>
<td width='50%'>$first </td></tr>");
}
//close the connection
mysql_close();
//close the table and the rest of the html but include a link to get back to data entry
echo ('<body onload="document.forms[0].submit.focus()">');
echo ('<form action="Roster.htm" method="post">');
echo ("<INPUT TYPE=submit NAME=submit VALUE= 'Back to input form'></form>");
print("</table>");
print("</html>");
?>
Try it out if you already have your class roster, otherwise I am using the roster of the Baltimore Ravens to test it.

Re: Help with insert into database code

Posted: Wed Jan 13, 2010 2:46 pm
by grozanc
Thanks guys!

ramblin54321's solution took care of the immediate problem. However, I'll try out the other solution soon.

Thanks again!

Re: Help with insert into database code

Posted: Sat Jan 16, 2010 3:42 am
by ramblin54321
Well I was having so much trouble with my shopping cart problem that I decided to work on your easier problem. Well, I had a problem with yours too, I wanted to validate for if you would ever forget to check present or absent and if so, an alert would pop up. It worked outside of the program but for some unknown reason, the javascript wouldn't work inside the program. So I used php to take care of it which just means that instead of being alerted while you are still on the form, you will have to click a link to go back to it. So before anyone posts any javascript, please test it inside of this program first. I'll get you your processing tomorrow.

For your attendance form you will need to go into phpadmin (your same students database), go to sql and enter this command: ALTER TABLE names ADD id INT AUTO_INCREMENT PRIMARY KEY;
Unfortunately, the ids don't match alphabetically but not a big deal. At least we know a unique id for each student and how many students there are.

Here is the code for your attendance form. Tested in internet explorer 7 and latest version of Firefox. Not sure how it will look with a name longer than 14 characters. Save it in Teacher and name it Attendance.php

Code: Select all

<?
//open the database
include('../Teacher/studentsSettings.php');
$db = mysql_connect(localhost,$username,$password)
    or
die("could not connect");
if ( $_GET["id"] == NULL ) {
$num = 1;
 }else{ 
$num = $_GET["id"];
$num = $num + 1;}
mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM names WHERE id='$num'";
//take the first and last name for id 1 and put them into a mysql array result variable
$result = mysql_query($query) or die("Couldn't execute query");
//put the mysql array variable into a php array variable
$row = mysql_fetch_array($result);
//put the array variables back into regular variables
$last= $row["Last"]; 
$first= $row["First"];
//close the connection
mysql_close();
//start html
echo ('<html><style type="text/css">  body {FONT-SIZE: 20pt; FONT-FAMILY: Lucida Sans}');
echo ('<script type="text/javascript">
function Validate()
{
    if (document.form1.pora[0].checked || document.form1.pora[1].checked)
        return true;
    alert("You must enter present or absent");  
    return false;
}
</script>');
echo ('</style><body>');
//print a heading for the Attendance page
echo ('<b><center><font face="century gothic">Student Attendance <br>');
$date = (date("l, F j, Y"));
echo ($date);
//show a form to mark students present one by one
//the form tag goes outside of the table to prevent an alignment problem of white space under the form in the table 
print('<FORM name="form1" method="post" action="../Teacher/record.php" onsubmit="Validate()">');
print("<table border='5' width = '90%' cellspacing='2' cellpadding='2'>");
print("<tr><th> Last Name </th> <th> First Name </th> <th>p or a?</th></tr>");
print('<tr><td width="35%"><font size="8">');
print($last);
print('</td><td width="35%"><font size="8">');
print($first);
print('</td><td><div align="left"><font size="6">');
echo ('<INPUT TYPE=RADIO NAME="pora" VALUE="p">present</div><div align="right"><INPUT TYPE=RADIO NAME="pora" VALUE="a">absent</div></td></tr>');
echo ("<tr><td></td><td></td><td align='center'><INPUT TYPE=submit NAME=submit VALUE= 'record'></td></tr>");
print ('</table>');
//well the div to put horizontal space between the two buttons just messed up the vertical alignment but I can live with it
//add the needed variables to the form date and id
echo ('<input type="hidden" name="date" value="');
echo ($date);
echo ('">');
echo ('<input type="hidden" name="id" value="');
echo ($num);
echo ('">');
echo ('</FORM>');
print ('</body></html>');
?>
 
Here is the code which will eventually process it but for now is for validating it and is required to make the form work. Save it in Teacher and name it record.php

Code: Select all

<?
$date = $_POST['date'];
$id = $_POST['id'];
$pora = $_POST['pora'];
echo ($date.'<br>');
echo ($id.'<br>');
echo ($pora.'<br>');
echo date('m/d/y', strtotime($date)); 
echo ('<br>');
if ($pora != "p" && $pora != "a") {
echo ("<font size='8'>You must enter present or absent <br> <br>");
$id = $id - 1;
echo"<a href='Attendance.php?id=$id'>Go back to same student</font></a>";
} else {
echo"<a href='Attendance.php?id=$id'><font size='8'>Next Student</font></a>";
}
?>
 
Aint this fun! This is one of the few times when I would have used Excel instead of php but I'm learning a lot of php by doing this. The only thing I don't like about this program is that it takes three mouse clicks per student because of the way the variables are passed.

Re: Help with insert into database code

Posted: Sun Jan 17, 2010 4:37 am
by ramblin54321
So today was mysql learning day. Impossible to dynamically create a table name such as $date. But you can do everything below the table layer dynamically such as insert and query but it requires preparing or some complicated process. However fortunately for me it allows date=CURDATE(). So for grozanc and anyone following this (who knows maybe someone 3 years from now will be searching google and come across this and find it useful for something else):
Delete your record.php file in the folder Teacher and replace it with this one -

Code: Select all

<?
$date = date("Y-m-d");
$id = $_POST['id'];
$pora = $_POST['pora'];
echo ($date.'<br>');
echo ($id.'<br>');
echo ($pora.'<br>');
//if nothing was entered click to go back otherwise click for next student
if ($pora != "p" && $pora != "a") {
echo ("<font size='8'>You must enter present or absent <br> <br>");
$id = $id - 1;
echo"<a href='Attendance.php?id=$id'>Go back to same student</font></a>";
} else {
echo"<a href='Attendance.php?id=$id'><font size='8'>Next Student</font></a><br><br><br><br><br>";
//display the link to print the daily attendance report when finished
echo"<a href='dailyReport.php><font size='8'>When finished, click here to print daily attendance report.</font></a>";
//open the database
include('../Teacher/studentsSettings.php');
$db = mysql_connect(localhost,$username,$password)
    or
die("could not connect");
mysql_select_db($database) or die( "Unable to select database");
 
//take the regular variables and put them into the corresponding table field names prior to inserting into SQL
$sql = "INSERT INTO students.absences (id, date) VALUES ('$id', '$date')";
//if there is an absence, insert it into the database table for this date
if ($pora == 'a') {
$inserted = mysql_query($sql, $db) or die(mysql_error());
} else {
echo ("<br><br><br>no absence to recorded on this student");
}
}
?>
All it does is insert the id of anyone absent and today's date into a table called absence. So you will need to go into phpadmin and create the table in the student database with two fields id tinyint 2 NULL and date date NULL. To process it to print out a daily report here is the code for a file called dailyReport.php saved in the Teacher folder. Notice the mysql Select statement; seems simple if you know how to do it but if not, what a pain!

Code: Select all

<?
//print out a heading
print("<center><font size = '8'>The following students were absent today <br>");
$date = (date("l, F j, Y"));
echo ($date);
print("</center><br><table border='5' width = '100%' cellspacing='2' cellpadding='2'>");
print("<tr><th align = 'left'><font size = '8'> Last name </th> <th align = 'left'><font size = '8'> First name </th>");
//open the database
include('../Teacher/studentsSettings.php');
$db = mysql_connect(localhost,$username,$password)
    or
die("could not connect");
mysql_select_db($database) or die( "Unable to select database");
//get the data
$query = "SELECT * FROM absences left join names on names.id = absences.id WHERE absences.date = CURDATE()  ";
$result=mysql_query($query);
while($row = mysql_fetch_array($result)) {
$last = $row["Last"]; 
$first = $row["First"];
print ('<tr><td><font size = "7">' . $last . '</td>' . '<td><font size = "7">' . $first . '</td></tr>');
}
print ('</table>');
?>