inserting to sql from textfield and textarea doesn't work

[cainam29]

please help, inserting datas from my textfields combined with my textareas to db not working,

 

please help on how to combine my VALUES properly. I cant seem to figure out how to combine the implode for my textareas and the other values from textfields.

 

below is my PHP code:

$Category2 = $_POST['Category2'];
$Category3 = $_POST['Category3'];
$Status = $_POST['Status'];
$Date = $_POST['Date'];
$Severity = $_POST['Severity'];
$BanType = $_POST['BanType'];
$XiD = $_POST['XiD'];
$Ticket = $_POST['Ticket'];

//Process the input textareas into arrays
$PhoneNumber = array_map('mysql_real_escape_string', explode("\n", $_POST['PhoneNumber']));
$Createdate = array_map('mysql_real_escape_string', explode("\n", $_POST['Createdate']));
$RemedyTicketNo = array_map('mysql_real_escape_string', explode("\n", $_POST['PhoneNumber']));

//Determine the values with the least amoutn of elements
$min_count = min($PhoneNumber, $Createdate, $RemedyTicketNo);

//Create array to hold INSERT values
$values = array();

//Create the INSERT values
for($index=0; $index<$min_count; $index++)
{
$values[] = "('{$PhoneNumber[$index]}', '{$Createdate[$index]}', '{$RemedyTicketNo[$index]}')"; 
}

if (isset($RemedyTicketNo)) 
{
$sql="INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name)
VALUES ('" . implode (', ', $values) ."', '".$Category2."', '".$Category3."', '".$Status."', '".$Date."', '".$Severity."', '".$BanType."', '".$XiD."')";
$result=mysql_query($sql);

header("Location: smp_backend_test.php");
}

Edited April 28, 2013 by cainam29

[mac_gyver]

you would need to start by defining how you want multiple sets of data to be inserted (you cannot write any code to do what you want if you don't know what you want the result to look like). do you want one row for each set of data (the way you should be storing data) or are you trying to store the values from multiple sets of data in one row (which is not the way you should storing data.)

Edited April 28, 2013 by mac_gyver

[cainam29]

i am trying to INSERT each line from textarea as a row to sql...wherein each line in textarea would be in the same row as my values in textfields...im not sure if how i combine my textarea and textfield values prior to INSERTing are correct...

[mac_gyver]

a multi-value/multi-row insert query's syntax is -

 

insert into table_name (column_a, column_b, column_c, column_d,...) values ('value1_a', 'value1_b', 'value1_c', 'value1_d',...),('value2_a', 'value2_b', 'value2_c', 'value2_d',...), ...

 

the order of the column names and the values must match. if you list the phone_number column as the second column, the phone number data must be the second value in each set.

 

you need to build the complete ('value1_a', 'value1_b', 'value1_c', 'value2_d',...) string, with a value for every column listed in the query, when you assign the data to $values[] = " ... ";

[cainam29]

i got ur point but here's how i would want it to be inserted:

 

INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name)

 

 

the values of ars_no, phone_number and create_date would come from my textareas...each line from these textareas would then correspond to the fixed values that i have for my textfields which are category_1, category_2, status, resolved_date, trouble_type_priority, ban_type, employee_id_name

 

so each line from my textareas would be a new row when inserted to my sql and all rows that would be created would all have the added fixed values from my textfields...

 

hope this makes sense...

[mac_gyver]

the $values[] = " ... " statement would be -

    $values[] = "('$RemedyTicketNo[$index]','$PhoneNumber[$index]','$Createdate[$index]',
    '$Category2','$Category3','$Status','$Date','$Severity','$BanType','$XiD')";

the corresponding $sql = " ... "; statement would be -

$sql="INSERT into tbl_main
 (ars_no,phone_number,create_date,category_1,category_2,status,resolved_date,trouble_type_priority,ban_type,employee_id_name)
 VALUES " . implode (',',$values);

[Barand]

It looks like you are building a query with too many (..)s

 

Should be

INSERT INTO table (a,b,c) VALUES
($a1, $b1, $c1),
($a2, $b2, $c2);

whereas you are creating

INSERT INTO table (a,b,c) VALUES
(
($a1, $b1, $c1),
($a2, $b2, $c2)
);

[cainam29]

thanks for all the suggestions...i think i found the error in my INSERT statement, i've tried to echo the query and its only doing this:

INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name) VALUES ('('1', '1', '1'), ('2', '2', '2')', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA')

so for this reason, datas are not uploaded. what i want it to do is to perform the below query:

is there a way that we can make my INSERT statement perform like below?

 

INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name) 
VALUES ('('1', '1', '1', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA'), ('2', '2', '2', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA')')

[cainam29]

 

thanks for all the suggestions...i think i found the error in my INSERT statement, i've tried to echo the query and its only doing this:

INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name) VALUES ('('1', '1', '1'), ('2', '2', '2')', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA')

so for this reason, datas are not uploaded. what i want it to do is to perform the below query:

is there a way that we can make my INSERT statement perform like below?

 

INSERT into tbl_main (ars_no, phone_number, category_1, category_2, status, create_date, resolved_date, trouble_type_priority, ban_type, employee_id_name) 
VALUES ('('1', '1', '1', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA'), ('2', '2', '2', 'SMP_Backend', 'Pending Request', 'Resolved', '2013-04-28', '5', 'I', 'AAA')')

 

mac_gyver code actually has this result...thanks mann...