php mysql, multiple rows insert

[dannybrazil]

Hello,

 

I have an attendance form that looks like that (It automatically adds the students names and info)

 

student 1  DATE      Att [yes/no] (radio)

student 2    DATE      Att [yes/no] (radio)

.

.

 

student N

 

SUBMIT

 

Now I want that each student will get a DIFFERENT ROW in the DB table.

id            name                  date                            att

1              st1                  2011-01-01                    yes

2              st2                  2011-01-01                    no

.

.

n

 

Is there a smart way to do so ?

 

Thanks

 

[mikosiko]

Imho you should have 2 tables...

Users

user_id

firstname

lastname

<+General info for the student>

 

AND

UserAttendance

user_id

attdate

att

 

this last one with FK user_id to Users,  and UK (user_id + attdate)

 

In you form DATE should be captured only one time.

[dannybrazil]

I have found this code....work fine..but how can I add multipule form entries.

 

In this example we have 'student_id' ONLY..how do I add for example: 'date' and 'name' etc...

 

<form method="POST" action="insert.php">
<input type="checkbox" name="id[]" value="32">Article #32<br>
<input type="checkbox" name="id[]" value="38">Article #38<br>
<input type="checkbox" name="id[]" value="45">Article #45<br>
<input type="checkbox" name="id[]" value="59">Article #59<br>
<input type="Submit">
</form>


$query = 'INSERT INTO related_articles (id) VALUES (' .
implode('), (', $_POST['id']) . ')';
$result = mysql_query($query)
or die('Could not execute INSERT query');

 

 

Now I want to add as well $_post['date']  /  $_post['name']....where do I add it ?

 

[Psycho]

First off, let's start with your form. The radio buttons should look something like this:

Student 1
<input type="radio" name="attend[1] value="1" /> Yes
<input type="radio" name="attend[1] value="0" /> No

Student 2
<input type="radio" name="attend[2] value="1" /> Yes
<input type="radio" name="attend[2] value="0" /> No

 

The number inside the name parameter will be the student ID. Then in your processing code you can iterate through all the POST data and generate a single INSERT statement to create all the records.

 

$insertVals = array();
foreach($_POST['attend'] as $studentID => $attended)
{
    $insertVals[] = "('$studentID', '$attended')";
}

//Create and run INSERT query
$query = "INSERT INTO attendance
              (`studentID`, `attended`)
          VALUES " . implode(', ', $insertVals);
$result = mysql_query($query) or die (mysql_error());

 

This assumes the table has a date field that default to the current date when new records are created. Otherwise you need to generate the date in PHP and add it to the values.

[dannybrazil]

I do have MORE <input> to add

 

<input type="hidden" name="student_id" value="'.$id_st.'" />
<input type="hidden" name="student_name" value="'.$st_name.'" />
<input type="hidden" name="student_email" value="'.$st_email.'" />
<input type="hidden" name="teacher_name" value="'.$teacher.'" />
<input type="hidden" name="grp" value="'.$grp.'" />
<input type="hidden" name="date" value="'.date('Y-m-d').'" />

<input type="radio" name="att['.$id_st.']" value="Yes" />Yes
 | 
<input type="radio"  name="att['.$id_st.']" value="No" />No


//original INSERT
mysql_query("INSERT INTO student_attendance
              (`student_id`,`student_name`,`student_email`,`teacher_name`,  `grp`,  `date`,  `date_dd`,  `date_mm`,  `date_yy`,  `att` )
			  	  VALUES	
				('$_POST[student_id]','$_POST[student_name]','$_POST[student_email]','$_POST[teacher_name]','$_POST[grp]','$_POST[date]','$_POST[date_dd]','$_POST[date_mm]','$_POST[date_yy]','$_POST[att]' )") or die(mysql_error());

 

How to I add MORE var' to the code you gave me here:

$insertVals = array();
foreach($_POST['attend'] as $studentID => $attended)
{
    $insertVals[] = "('$studentID', '$attended')";
}

//Create and run INSERT query
$query = "INSERT INTO attendance
              (`studentID`, `attended`)
          VALUES " . implode(', ', $insertVals);
$result = mysql_query($query) or die (mysql_error());

 

 

[Psycho]

Give each input an array name such that it uses the student ID as the index. Then you can process all the fields associated with the student. And, by doing that you don't need the student ID field

<input type="hidden" name="student_name[1]" value="'.$st_name.'" />
<input type="hidden" name="student_email[1]" value="'.$st_email.'" />

 

Then in the processing code

$insertVals = array();
foreach($_POST['attend'] as $studentID => $attended)
{
    $student_name = $_POST['student_name']['$studentID];
    $student_email = $_POST['student_email']['$studentID];
    $insertVals[] = "('$studentID', '$student_name', '$student_email', '$attended')";
}

 

I only showed a couple of fields, but that should give you the idea. But, I don't see what you need those values in the processing code since I would assume they already exist in the DB. You should only need the student ID and the data that is new.

[dannybrazil]

Hi I have this code:

 

$insertVals = array();

foreach($_POST['att'] as $studentID => $attended)
{
    $grp = $_POST['grp'][$studentID];
    $date = $_POST['date'][$studentID];
    $insertVals[] = "('$studentID','$grp','$date','$attended')";
}

//Create and run INSERT query
$query = "INSERT INTO student_attendance
              ('student_id','grp','date','att')
          VALUES " . implode(',', $insertVals);
mysql_query($query) or die (mysql_error());

 

But get this problem:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''student_id','grp','date','att') VALUES ('4','WE001','2011-08-19','Yes' at line 2

 

Any reason ?

[dannybrazil]

I have founf another code that works....shaped it and it is working smoothly

foreach($_POST['student_id'] as $row=>$Act)
{
    $student_id = mysql_real_escape_string($Act); 
$student_name = mysql_real_escape_string($_POST['student_name'][$row]);
$student_email = mysql_real_escape_string($_POST['student_email'][$row]);
$teacher_name = mysql_real_escape_string($_POST['teacher_name'][$row]);
$grp = mysql_real_escape_string($_POST['grp'][$row]);
     $date = mysql_real_escape_string($_POST['date'][$row]);
 $date_dd = mysql_real_escape_string($_POST['date_dd'][$row]);
 $date_mm = mysql_real_escape_string($_POST['date_mm'][$row]);
 $date_yy = mysql_real_escape_string($_POST['date_yy'][$row]);
     $att = mysql_real_escape_string($_POST['att'][$row]);

    $involv = "INSERT INTO table (`student_id`,`student_name`,`student_email`,`teacher_name`,  `grp`,  `date`,  `date_dd`,  `date_mm`,  `date_yy`,  `att` )
               VALUES('$student_id', '$student_name', '$student_email', '$teacher_name','$grp','$date','$date_dd','$date_mm','$date_yy','$att')";

    mysql_query($involv);
}

 

Thnaks a lot for the people who help !!!!!!

[Psycho]

You should not run queries in loops! It is very inefficient. Looking for code to do what you want isn't a bad idea, but don't use it as a crutch. You should analyze the code to understand what it is doing. Then you can learn to write your own code when you need it. I have had several instances where I didn't know how to approach a problem and searched for some existing code to do what I needed. Then when I found some code I would analyze it step by step to understand the logic. In doing so I would usually find some error in the code. But, it was a good learning experience.

 

The code you last posted is no different than what I proposed except you are running one query for each record instead of one query to insert all the records. Also, you should not use mysql_real_escape_string() on EVERY input. You should only use it on input for string values. If you are expecting a numeric value then validate the value accordingly. Likewise, you need to validate date fields properly if they are going to be stored as date and/or time values in the database (which they should).

 

I didn't change the validations. I changed some of the validations. You shoudl create a function for validating the date input and converting to a mysql date value. And, you should include a switch to skip a record if it does not contain valid data.

 

$insertVals = array();
foreach($_POST['student_id'] as $id => $Act)
{
    $student_id    = intval($Act); 
    $student_name  = mysql_real_escape_string($_POST['student_name'][$id]);
    $student_email = mysql_real_escape_string($_POST['student_email'][$id]);
    $teacher_name  = mysql_real_escape_string($_POST['teacher_name'][$id]);
    $grp           = mysql_real_escape_string($_POST['grp'][$id]);
    $date          = mysql_real_escape_string($_POST['date'][$id]);
    $date_dd       = mysql_real_escape_string($_POST['date_dd'][$id]);
    $date_mm       = mysql_real_escape_string($_POST['date_mm'][$id]);
    $date_yy       = mysql_real_escape_string($_POST['date_yy'][$id]);
    $att           = intval($_POST['att'][$id]);

    //Need to add check to skip record if any required data is missing or is invalid

    $insertVals[] = "('$student_id', '$student_name', '$student_email', '$teacher_name','$grp','$date','$date_dd','$date_mm','$date_yy','$att')";
}

//Create query to insert all the records
$query = "INSERT INTO table
               (`student_id`,`student_name`,`student_email`,`teacher_name`,  `grp`,  `date`,  `date_dd`,  `date_mm`,  `date_yy`,  `att` )
           VALUES " . implode(', ', $insertVals);
mysql_query($query);