Inserting multidiamenstional array into mysql with pdo

Question

I've a multidimensional array to insert into mysql table. Trying to add datetime time along with other elements in that array. but everytime i get this error. :

Notice: Undefined offset: 10 in C:\xampp\htdocs\Auto-attendance\main\proccess_attendance.php on line 47

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'att_status' cannot be null' in C:\xampp\htdocs\Auto-attendance\main\proccess_attendance.php:60 Stack trace: #0 C:\xampp\htdocs\Auto-attendance\main\proccess_attendance.php(60): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\Auto-attendance\main\proccess_attendance.php on line 60

I don't get where is the prob.

<?php

 require_once("db_const.php");
if (!$_SERVER['REQUEST_METHOD']=='POST' 
    || !$_POST['submit']=='save'
    || empty($_POST['subject_name'])
    || empty($_POST['subject_code'])        
    || empty($_POST['department_name'])
    || empty($_POST['department_short_name'])
    || empty($_POST['teacher_name'])        
    || empty($_POST['date'])        
    || empty($_POST['time'])        
    || empty($_POST['student']) 
    || empty($_POST['stroll'])  
    || empty($_POST['status'])      

    ) {
header('Location: ../student_attendance.php?page=take-attendance');
} else {
## connect mysql server
/** $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
# check connection
if ($mysqli->connect_errno) {
    echo "<p>MySQL error no {$mysqli->connect_errno} :     
 {$mysqli->connect_error}</p>";
    exit();
   }**/ 
## query database
# prepare data for insertion       538 072 662  2716
$subject_name               = $_POST['subject_name'];
$subject_code               = $_POST['subject_code'];
$department_name            = $_POST['department_name'];
$department_short_name      = $_POST['department_short_name'];
$teacher_name               = $_POST['teacher_name'];
$date                       = $_POST['date'];
$time                       = $_POST['time'];
$student                    = $_POST['student'];
$student_roll               = $_POST['stroll'];
$status                     = $_POST['status'];

$date=date_create_from_format("j-M-Y", $date);
$date= date_format($date,"Y-m-d");

$timestamp = date('Y-m-d H:i:s');


 foreach($student as $k=>$v){ 
    $st[] = array($subject_name,  $subject_code, $department_name,    
  $department_short_name, $teacher_name, $date,  $time, $timestamp,  $v, 
  $student_roll[$k], $status[$k]); }


  $db = new PDO('mysql:host=localhost;dbname=auto_attendance', 'root', 
  '');
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   $insert = $db->prepare(
    'INSERT INTO student_attendance (subject_name , subject_code,   
     department_name, department_short_name,
 teacher_name, date, time, timestamp, student_name, student_roll, 
 att_status) VALUES (?, ?, ?, ?, ?, ?, ?,  ?, ?, ?, ?)');


  foreach($st as $values) {
  $result = $insert->execute($values);

   }

   if($result == true) {
    echo "<script>alert('Record successfuly 
    Saved.');window.location.href='../student_attendance.php?page=take-
    attendance';</script>";

      }

    else  {
    echo "<script>alert('Record couldnt be 
   Saved.');window.location.href='../student_attendance.php?page=take-
   attendance';</script>";

    } 


   } 
   ?>

It looks like whatever you pass in for att_status can't be null, you may need to check whats passed in to `$_POST['status']` — Joey Ciechanowicz, Apr 08 '15 at 14:29
Show us the CREATE TABLE statement for student_attendance table — user4035, Apr 08 '15 at 14:43
This message seems pretty clear - `Column 'att_status' cannot be null'`. WHich line is line 47? Which is line 60? — Mike Brant, Apr 08 '15 at 14:49
[Had you been checking for errors on the PHP side of things, it would have **avoided** this question entirely.](http://php.net/manual/en/function.error-reporting.php) — Funk Forty Niner, Apr 08 '15 at 15:01

Rizier123 · Accepted Answer · 2015-04-08T15:25:38.023

Well your code is a bit of a mess, so I cleaned as much as I could:

1. I changed your if statement so that it is a bit more readable also I remove the else part, since you redirect if something isn't set.

2. Also put an exit(); after each header() call. To make sure the script execution is stopped.

3. Also I wrapped your database stuff into a try and catch block to catch PDOExceptions

4. I also changed the assignment of the execute() call, since you want to check if every execution was successful and not only the last one

<?php 

    require_once("db_const.php");

    function checkPostIndex(array $indexes = [""]) {
        if(empty($indexes)) return FALSE;
        return array_reduce($indexes, function($result, $v){
            return $result && !empty($_POST[$v]);
        }, TRUE);
    }


    $indexes = ["subject_name", "subject_code", "department_name", "department_short_name", "teacher_name", "date", "time", "student", "stroll", "status"];

    if($_SERVER["REQUEST_METHOD"] !== "POST" || $_POST['submit'] !== "save" || checkPostIndex($indexes)) {
        header("Location: ../student_attendance.php?page=take-attendance");
        exit();
    }

    $subject_name               = $_POST['subject_name'];
    $subject_code               = $_POST['subject_code'];
    $department_name            = $_POST['department_name'];
    $department_short_name      = $_POST['department_short_name'];
    $teacher_name               = $_POST['teacher_name'];
    $date                       = $_POST['date'];
    $time                       = $_POST['time'];
    $student                    = $_POST['student'];
    $student_roll               = $_POST['stroll'];
    $status                     = $_POST['status'];

    $date = DateTime::createFromFormat("j-M-Y", $date)->format("Y-m-d");
    $timestamp = date("Y-m-d H:i:s");


    foreach($student as $k => $v) { 
        $tmpOne = (!empty($student_roll[$k])?$student_roll[$k]:"");
        $tmpTwo = (!empty($status[$k])?$status[$k]:"");
        $st[] = [$subject_name,  $subject_code, $department_name, $department_short_name, $teacher_name, $date,  $time, $timestamp,  $v, $tmpOne, $tmpTwo];
    }


    try {

        $db = new PDO("mysql:host=localhost;dbname=auto_attendance", "root", "");
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $insert = $db->prepare('INSERT INTO student_attendance
                                (subject_name , subject_code, department_name, department_short_name, teacher_name, date, time, timestamp, student_name, student_roll, att_status)
                                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

        foreach($st as $values) {
            $result[] = $insert->execute($values);
        }

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

?>

    <script>alert('Record <?php echo (!in_array("FALSE", $result, true)?"successfuly":"couldnt be") ?> Saved.');window.location.href='../student_attendance.php?page=take-attendance';</script>

Side Notes:

Add error reporting at the top of your file(s) to get useful error messages:

<?php
    ini_set("display_errors", 1);
    error_reporting(E_ALL);
?>

(Turn it on only while testing, never in production!)

I don't think cleaning up their messy code has yielded and answer here! The question is "I don't get where is the prob." the error is "Column 'att_status' cannot be null'". — webternals, Apr 08 '15 at 14:50
@webternals You're absolutely right; We need to see the table schema and also we need to know which line is line 47 with the undefined offset, but I think if OP uses the cleaned up code we can spot the errors better and don't have to search in messy code — Rizier123, Apr 08 '15 at 14:52

score 0 · Answer 2 · answered Apr 08 '15 at 14:53

0

The problem is with the value being passed in to $_POST['status']; If you track down what is being passed in to this variable you will find it is the problem!

answered Apr 08 '15 at 14:53

webternals

161
7

that's the point, thanks. – Ami Nirob Apr 08 '15 at 14:58
2

@BorhanNirob Are you even reading the comments?! That was already pointed out 30min ago! Like 5 times!! – Rizier123 Apr 08 '15 at 15:00

Inserting multidiamenstional array into mysql with pdo

2 Answers2