| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-19-11, 22:42
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
|
php, multi-insert problem
|
|
Hi everyone,
I am working with wamp 2.1 , including php 5.3 and mysql 5.1.53. I have a php script with which I am trying to dynamically build a mysql query to insert into a table.I have tried to test the query with phpmyadmin. Unfortunately I am getting an error that I can't figure out.
#1136 - Column count doesn't match value count at row 1
INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echo var="date_local" -->` )
VALUES (
'(none)', '(none)'
), (
'(none)', '(none)'
), (
'(none)', '(none)'
), (
'Tuesday, 19-Jul-2011 17:15:46 CDT', 'Tuesday, 19-Jul-2011 17:15:52 CDT'
)
the sql statement is
Code:
INSERT INTO milp (`<!--#echo var="auth_type" -->`, `<!--#echo var="content_length" -->`, `<!--#echo var="content_type" -->`, `<!--#echo var="date_local" -->`) VALUES ('(none)','(none)') , ('(none)','(none)') , ('(none)','(none)') , ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
the show create table yields
CREATE TABLE `milp` (
`<!--#echo var="auth_type" -->` char(40) DEFAULT NULL,
`<!--#echo var="content_length" -->` char(40) DEFAULT NULL,
`<!--#echo var="content_type" -->` char(40) DEFAULT NULL,
`<!--#echo var="date_local" -->` char(40) DEFAULT NULL,
....................
Can anyone spot the problem?
Thanks in advance,
KC
|
|
07-20-11, 00:02
|
|
Registered User
|
|
Join Date: May 2011
Posts: 24
|
|
you specify 4 columns to insert to, but only provide 2 columns of data.
and whats with the field name?
|
|
07-20-11, 08:49
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
|
|
I may be misunderstanding the entire insert process. Here is what I want my table to look like:
"auth_type" "content_length" "content_type" "date_local"
(none) (none) (none) Tuesday, 19-Jul-2011 17:15:46
(none) (none) (none) Tuesday, 19-Jul-2011 17:15:52
Sorry, I'm a noob,
KC
|
|
07-20-11, 11:50
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 10
|
|
Im not sure what variable your field names are referencing but take a look at the below and try to mimic it.
-----------------------------
INSERT INTO `tablename`
(
field1,
field2,
field3,
field4
)
VALUES
(
data1,
data2,
data3,
data4
);
-----------------------------
The only other issue I see is your lacking a parathesis in your value statement.
Yours shows:
-----------------------------
VALUES (
'(none)','(none)') ,
('(none)','(none)') ,
('(none)','(none)') ,
('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
-----------------------------
It should be:
-----------------------------
VALUES (
('(none)','(none)') ,
('(none)','(none)') ,
('(none)','(none)') ,
('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
);
-----------------------------
good luck,
Ryan
|
Last edited by rpot; 07-20-11 at 11:53.
|
07-20-11, 12:43
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Thanks Ryan,
I should explain further that my goal is to do a single insert statement to insert multiple rows. (according to an article I found this should be doable) . Please see Multiple Insert in Single Query – PHP/MySQL » Blog Archive » Amoeba Solution Kiosk.
So , in line with this, I am trying to build the query to be like :
Code:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Thanks for looking at it,
KC
|
|
07-20-11, 12:50
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 10
|
|
That's fine, but if you open a paranthesis you must close it..
VALUES(
(VALUE1),(VALUE2),
(VALUE3),(VALUE4)
);
So the statement should look like this:
INSERT INTO table
(field1, field2, field3, field4)
VALUES
(data1, 'data11', 'data12', 'data13'),
(data2, 'data21', 'data22', 'data23'),
(data3, 'data31', 'data32', 'data33'),
(data4, 'data41', 'data42', 'data43');
|
|
07-20-11, 14:14
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Hi Ryan,
Sorry to be dense about this, but I've looked at it several times and I don't see the parentheses error. I am using
Code:
INSERT INTO milp (`<!--#echo var="auth_type" -->`, `<!--#echo var="content_length" -->`, `<!--#echo var="content_type" -->`, `<!--#echo var="date_local" -->`) VALUES('(none)','(none)') , ('(none)','(none)') , ('(none)','(none)') , ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
which generates:
#1136 - Column count doesn't match value count at row 1
INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echo var="date_local" -->` )
VALUES (
'(none)', '(none)'
), (
'(none)', '(none)'
), (
'(none)', '(none)'
), (
'Tuesday, 19-Jul-2011 17:15:46 CDT', 'Tuesday, 19-Jul-2011 17:15:52 CDT'
)
I've also tried the values with an extra set of parentheses and with and without a semicolon at the end, within phpmyadmin.
I'm clearly doing something wrong, 
Thanks again,
KC
|
|
07-20-11, 14:34
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 10
|
|
Ok lets try this from PHPMYADMIN:
INSERT INTO milp
(
auth_type,
content_length,
content_type,
date_local
)
VALUES
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');
If this works without an issue then what's occuring is your echo statements contain no values since its likely being processed through PHP. PHPMYADMIN needs a sql corrected statement and has no way of defining your variables. Hence the reason I put the field name directly into the statment.
let me know,
Ryan
|
|
07-20-11, 17:05
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Thanks Ryan,
I decided to simplify it even more, but I'm still having problems.
Code:
INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echovar="date_local" -->` ) VALUES ('none', 'none', 'none', 'Tuesday, 19-Jul-2011 17:15:46')
I am now getting the following error when I try the query in phpmyadmin:
#1054 - Unknown column '<!--#echo var="auth_type" -->' in 'field list'
INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echovar="date_local" -->` )
VALUES (
'none', 'none', 'none', 'Tuesday, 19-Jul-2011 17:15:46'
)
Any ideas?
Thanks,
KC
|
|
07-20-11, 20:21
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 10
|
|
Ok....  .... We will get this. First lets forget about PHP altogether and lets get the query operating properly. Once we get the query we can easily setup the PHP. So with that said lets drop all notions of trying to establish a variable in your query. What I want you to try is to simply cut and past the below query into your PHPMYADMIN MySQL Query Window and execute it. If we get an error we can work from there.
--------------------------------
INSERT INTO milp
(
auth_type,
content_length,
content_type,
date_local
)
VALUES
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');
--------------------------------
If and when this works, as it should because I setup a test table and executed this perfectly fine, we can work with your variables.
Your error is because you are attempting to create a field with this statement: `<!--#echo var="auth_type" -->`
So essentially what your attempting to tell MySQL if it could interpret the statement is: echo the variable "Auth_Type". which technically wont work because we havent established the variable Auth_Type, nor can we from a pure SQL statment.
Let me know how this works, within PHPMYADMIN, and then lets continue.
Ryan
|
|
07-21-11, 17:51
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Hi Ryan,
Thanks for persisting on this. I'm sorry to take so long to get back to you, but because my fields differ from yours, I've rewritten some php, so that when a new table is generated, the fields will appear as you have indicated.
It should be working now and I will try your query and get back to you shortly.
Regards,
KC
|
|
07-22-11, 08:57
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Ok Ryan,
So I have created a test table 'milp' as follows
CREATE TABLE `milp` (
`auth_type` char(40) DEFAULT NULL,
`content_length` char(40) DEFAULT NULL,
`content_type` char(40) DEFAULT NULL,
`date_local` char(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and using phpmyadmin I have executed your sql ststement;
Code:
INSERT INTO milp
(
auth_type,
content_length,
content_type,
date_local
)
VALUES
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');
It does work, inserting the rows correctly.
|
|
07-22-11, 09:35
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 10
|
|
Ok great. So now let me make sure we clear any misunderstanding. You are wanting a script via php to insert variables which you need to establish, correct?
Can you provide me with what exactly your trying to do and where these variables are coming from (IE a Form using GET, POST, SESSION) we can get the php working.
Or if your good to go, then good luck, glad its all worked out..
Ryan
|
|
07-22-11, 17:46
|
|
Registered User
|
|
Join Date: Jul 2011
Posts: 13
|
|
Hi Ryan,
I could probably use some help here. Here is the function I am using to build the prior SQL query string. I'd appreciate your thoughts on how to proceed:
Just a word on structure:
I am saving my data in an associative array with multiple values 'glued' to gether with the ~ charachter
The array looks like:
[A]=>B~C~D~E
[F]=>G~H~I~J
[K]=>L~M~N~O
PHP Code:
<?php
function build_sql_insert($table, $data)
{
function addQuotes($value) {
//sprintf('%s', $value );
//echo $value;
$output = sprintf("'%s'", $value );
echo $output;
return $output;
}
function addbrackets($value) {
//sprintf('%s', $value );
//echo $value;
$output = sprintf('(%s)', $value );
echo $output;
return $output;
}
foreach(array_values($data) as $val) {
$pieces=explode("~", $val);
$data1= array_map("addQuotes", $pieces);
$values[] = implode(',', $data1); // VALUES CONTAINS THE REBUILT QUOTED ARRAY VALUES
print ' VAL ' ;
var_dump($val);
}
print ' VALUES ' ;
$data2= array_map("addbrackets", $values);
var_dump($data2);
// $values = implode(',', $data1); // VALUES CONTAINS THE REBUILT QUOTED ARRAY VALUES
// exit;
echo " ARRAYvalS ". count((array_keys($data))).'<br>';
echo " ARRAYkeys ". count((array_values($data))).'<br>';
echo " values ". count($values).'<br>';
echo " data1`ARRAYvalS ".'<br>';
//var_dump($data1);
print ' HELLO '.sprintf("INSERT INTO %s (`%s`) VALUES (%s) ", $table, implode('`, `', array_keys($data)), implode(") , (", $values) );
// $sql=sprintf("INSERT INTO %s (`%s`) VALUES (%s) ", $table, implode('`, `', array_keys($data)), implode(") , (", $values) );
$sql=sprintf("INSERT INTO `%s` (`%s`) VALUES %s", $table, implode('`, `', array_keys($data)), implode(" , ", $data2) );
// print ' HELLO '.sprintf("INSERT INTO %s (`%s`) VALUES ('%s')", $table, implode('`, `', array_keys($data)), implode("') , ('", array_values($data)) );
echo $sql;
return($sql);
}
?>
}
?>
Thanks,
KC
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
php, multi-insert problem
