php/mysql inserting multiple records error

[breakzzzz20]

what's wrong in my codes?

this is my insert.php:

`<?php

$host="localhost"; // Host name
$username="root"; // Mysql username
$password="rommelbarrio"; // Mysql password
$db_name="system"; // Database name
$tbl_name="grades"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form
$course = $_POST["course"];
$semester = $_POST["semester"];
$year = $_POST["year"];
$id_no = $_POST["id_no"];
$result = $_POST["grades"];

// Insert data into mysql
if (count($result > 0))
{
$new = array();

    foreach ($result as $key => $value)
    {
        $new[] = "('" . $course . "', '" . $semester . "', '" . $year . "', '" . $id_no . "', '" . $value["subject"] . "', '" . $value["descriptive_title"] . "', '" . $value["final_grades"] . "', '" . $value["remarks"] . "')";
    }

    if (count($new) > 0)
    {
        $query = mysql_query("INSERT INTO grades (course, semester, year, id_no, subject, descriptive_title, final_grades, remarks) VALUES". implode( ',', $new));

        if ($query)
        {
            echo "SUCCESS";
        }
        else
        {
            echo "FAILED";
        }
    }
}

// close connection
mysql_close();
?>

then this is my form:

the database/table is mess with my php codes..:}hope you all understand..:}

my output plan is this:

id_no year semester course subject descriptive_title final_grades remarks

1200 first second com.sci vb visual basic 1.2 pass
1200 first second com.sci php php 1.2 pass
1200 first second com.sci .net net 1.2 pass
1200 first second com.sci java java prog. 1.2 pass
1200 first second com.sci c++ c. prog. 1.2 pass

[broj1]

Do the classic trick: insert die(query) after the $query = mysql_query("INSERT INTO grades ... statement and inspect whether the query is OK. Test it in phpmyadmin (or post it here).

[broj1]

I prefer to construct statements this way since it is easier to debug:

> $new[] = "('$course', '$semester', '$year', '$id_no', '$value[\"subject\"]', '$value[\"descriptive_title\"]', '$value[\"final_grades\"]', ' $value[\"remarks\"]')";

And also you better check for the values in the $value array and assign them to variables (and possiblly clean them):

if(isset($value["descriptive_title"])) {

    $descriptive_title = $value["descriptive_title"];
}

[breakzzzz20]

$new[] = "('$course', '$semester', '$year', '$id_no', '$value[\"subject\"]', '$value[\"descriptive_title\"]', '$value[\"final_grades\"]', ' $value[\"remarks\"]')";

^^ thx broj1 for response..:} your codes give me an error..

[broj1]

What is the error?

[breakzzzz20]

$new[] = "('$course', '$semester', '$year', '$id_no', '$value[\"subject\"]', '$value[\"descriptive_title\"]', '$value[\"final_grades\"]', ' $value[\"remarks\"]')";

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\AppServ\www\sample admin\my-admin\insert_grades.php on line 27

[broj1]

As I said I would do it like this (it is cleaner and safer and less chances for mistakes):

// assign $value array to variables, you can also do some cleaning here
if(isset($value['subject']) && 
   isset($value['subject']) && 
   isset($value['subject']) && 
   isset($value['subject'])) {

    $subject = $value['subject'];
    $descriptive_title = $value['descriptive_title'];
    $final_grades = $value['final_grades'];
    $remarks = $value['remarks'];
    $is_error = false;
} else {
    $is_error = true;
}
if($is_error) {
    die('ERROR: values missing!');
}
$new[] = "('$course', '$semester', '$year', '$id_no', '$subject', '$descriptive_title', '$final_grades', ' $remarks')";

[breakzzzz20]

<?php

$host="localhost"; // Host name
$username="root"; // Mysql username
$password="rommelbarrio"; // Mysql password
$db_name="system"; // Database name
$tbl_name="grades"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form
$course = $_POST["course"];
$semester = $_POST["semester"];
$year = $_POST["year"];
$id_no = $_POST["id_no"];
$result = $_POST["grades"];

// Insert data into mysql
if(isset($value['course']) &&
isset($value['semester']) &&
isset($value['year']) &&
isset($value['id_no'])) {
$subject = $value['subject'];
$descriptive_title = $value['descriptive_title'];
$final_grades = $value['final_grades'];
$remarks = $value['remarks'];
$is_error = false;
} else {
$is_error = true;
}
if($is_error) {
die('ERROR: values missing!');
}
$new[] = "('$course', '$semester', '$year', '$id_no', '$subject', '$descriptive_title', '$final_grades', ' $remarks')";

    if (count($new) > 0)
    {
        $query = mysql_query("INSERT INTO grades (course, semester, year, id_no, subject, descriptive_title, final_grades, remarks) VALUES". implode( ',', $new));

        if ($query)
        {
            echo "SUCCESS";
        }
        else
        {
            echo "FAILED";
        }
    }

// close connection
mysql_close();
?>

don't have now an error,,sorry bro1,i'm really newbie in php/mysql seriously...i just replace your codes to my old codes..

[breakzzzz20]

good day all:}.i just put an index but now another problem..anyhelp would be appreciated on my problem..
i have a form but only the last row save in database..

this is my form codes:

`//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
$str = @trim($str);
if(get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
return mysql_real_escape_string($str);
}

$semester =clean($_POST['semester']);
$course = clean($_POST['course']);
$year = clean($_POST['year']);

$sql="SELECT * FROM semester_year WHERE semester = '$semester' AND course = '$course' AND year = '$year' ";
$result=mysql_query($sql);

if(mysql_num_rows($result) >= 1)
{
// Define $color=1
$color="1";

echo '<table width="700" cellpadding="0" cellspacing="0" border="1">';

 echo "<tr> <th>Subject</th> <th>Descriptive Title</th> <th>Final Grades</th> <th>Remarks</th> </tr>";

while($rows=mysql_fetch_array($result)){

// If $color==1 table row color = #66b2ff
if($color==1){
echo "<tr bgcolor=''>
<td><center><input type='text' name='grades[0][subject]' id='grades[][subject]' value=".$rows['subject']."></center></span></td>
<td><center><input type='text' name='grades[0][descriptive_title]' id='grades[][descriptive_title]' value=".$rows['descriptive_title']."></center></td>
<td><center><input type='text' name='grades[0][final_grades]' id='grades[][final_grades]'></input></center></td>
<td><center><input type='text' name='grades[0][remarks]' id='grades[][remarks]'></input></center></td>

</tr>";
// Set $color==2, for switching to other color
$color="2";
}

// When $color not equal 1, use this table row color
else {
echo "<tr bgcolor=''>
<td><center><input type='text' name='grades[0][subject]' id='grades[][subject]' value=".$rows['subject']."></center></span></td>
<td><center><input type='text' name='grades[0][descriptive_title]' id='grades[][descriptive_title]' value=".$rows['descriptive_title']."></center></td>
<td><center><input type='text' name='grades[0][final_grades]' id='grades[][final_grades]'></input></center></td>
<td><center><input type='text' name='grades[0][remarks]' id='grades[][remarks]'></input></center></td>

</tr>";
// Set $color back to 1
$color="1";
}

}
echo '</table>';
}

else
{
echo "No Record Found";
}

i just put an index:

but now is my problem,only the last row only save in the database.

this is my picture:

`

[breakzzzz20]

need help..thank you..:}

[broj1]

Your insert query has to be checked if it is OK. Put this code before theif ($query) statement:

die($query);

This will display your final query and stop the script. Please post the displayed query here (and then remove the above die statement).

[breakzzzz20]

hello broj1,,this is the query

INSERT INTO system.grades (
id_no ,
course ,
semester ,
year ,
subject ,
descriptive_title ,
final_grades ,
remarks
)
VALUES (
'003', 'cs', 'second', 'second', 'cs', 'prog', '2', 'pass'
), (
'003', 'cs', 'second', 'second', 'java', 'prog', '2.1', 'pass'
) (
'003', 'cs', 'second', 'second', 'vb', 'prog', '2.3', 'pass'
), (
'003', 'cs', 'second', 'second', 'C++', 'prog', '2.4', 'pass'
);

[broj1]

In your insert query there is a comma (",") missing after the second set of values which probably breaks your query (see below).

INSERT INTO system.grades
(id_no ,course ,semester ,year ,subject ,descriptive_title ,final_grades ,remarks)
VALUES 
('003', 'cs', 'second', 'second', 'cs', 'prog', '2', 'pass'), 
('003', 'cs', 'second', 'second', 'java', 'prog', '2.1', 'pass')
('003', 'cs', 'second', 'second', 'vb', 'prog', '2.3', 'pass'), 
('003', 'cs', 'second', 'second', 'C++', 'prog', '2.4', 'pass');

Can you please post the array $new so I can have a look at it. Place this code before if (count($new) > 0) line and post the output:

die(print_r($new, 1))

[broj1]

And another question: is it OK that all the id_no values are the same?

[breakzzzz20]

it's ok now i already debug it..:}but really thanks for your help,really appreciate..:}