[SOLVED] Mysql query using php question

ballhogjoni · April 25, 2007

Can someone tell me whats wrong with this query. Its saving the data but it skips the fname column and starts with the lname column instead.

$query = "INSERT INTO contact_n_questions (fname, lname, position, email, state, areacode, prefix, linenumber, question1, question2, question2q1, question2q2, question2q3, question2q4, question2q5, question2q6, question3, question4q1, question4q2, question4q3, question4q4, question4q5, question5q1, question5q2, question5q3, question5q4, question5q5, question5q6, question6, question7, question8, question9, tandc, comments) VALUES ('','$fname','$lname','$position','$email','$state','$areacode','$prefix','$linenumber','$tandc','$question1','$question2','$question2q1','$question2q2','$question2q3','$question2q4','$question2q5','$question2q6','$question3','$question4q1','$question4q2','$question4q3','$question4q4','$question4q5','$question5q1','$question5q2','$question5q3','$question5q4','$question5q5','$question5q6','$question6','$question7','$question8','$question9')";

Wildbug · April 25, 2007

You have an empty string as the first argument in VALUES. Thus it puts an empty string into fname and $fname into lname and so on....

clown[NOR] · April 25, 2007

just what I wrote

ballhogjoni · April 25, 2007

I need that empty string in the VALUES portion to auto increment the ID column.

ballhogjoni · April 25, 2007

help me please

Wildbug · April 25, 2007

No, you don't. As long as the default value for an AUTO_INCREMENT column is NULL, then it will increment itself. Or, if you do, indeed, need that empty string to do so, then you also have to include the AUTO_INCREMENT column in the column list.

clown[NOR] · April 26, 2007

you only need the empty column for the id IF you didnt preset the columns to add info to like you've done...

if you do like this

$query = "INSERT INTO contact_n_questions VALUES ('', 'all', 'variables', 'here')";

tauchai83 · April 26, 2007

if you set the id to auto increment, it will automatically increment itself. It should be your primary key. You do not need to insert the value again.

ballhogjoni · April 26, 2007

Ok this is what my query looks like now. I am running into a new problem, none of the data is being written to my db. I have echoed the variables and the values are being passed but for some reason it wont write to the db.

$query = "INSERT INTO contact_n_questions VALUES ('','$fname','$lname','$position','$email','$state','$areacode','$prefix','$linenumber','$question1','$question2','$question2q1','$question2q2','$question2q3','$question2q4','$question2q5','$question2q6','$question3','$question4q1','$question4q2','$question4q3','$question4q4','$question4q5','$question5q1','$question5q2','$question5q3','$question5q4','$question5q5','$question5q6','$question6','$question7','$question8','$question9','$tandc')";

Wildbug · April 26, 2007

There wouldn't happen to be a mysql_error() that you'd like to share with us, would there?

ballhogjoni · April 26, 2007

No. The page pulls up fine when I'm passing the variables. Heres all my code.

<?php
$username="xxxxx";
$password="xxxxx";
$database="xxxxx";

$fname=$_POST['fname'];
$lname=$_POST['lname'];
$position=$_POST['position'];
$email=$_POST['email'];
$state=$_POST['state'];
$areacode=$_POST['areacode'];
$prefix=$_POST['prefix'];
$linenumber=$_POST['linenumber'];
$question1=$_POST['question1'];
$question2=$_POST['question2'];
$question2q1=$_POST['question2q1'];
$question2q2=$_POST['question2q2'];
$question2q3=$_POST['question2q3'];
$question2q4=$_POST['question2q4'];
$question2q5=$_POST['question2q5'];
$question2q6=$_POST['question2q6'];
$question3=$_POST['question3'];
$question4q1=$_POST['question4q1'];
$question4q2=$_POST['question4q2'];
$question4q3=$_POST['question4q3'];
$question4q4=$_POST['question4q4'];
$question4q5=$_POST['question4q5'];
$question5q1=$_POST['question5q1'];
$question5q2=$_POST['question5q2'];
$question5q3=$_POST['question5q3'];
$question5q4=$_POST['question5q4'];
$question5q5=$_POST['question5q5'];
$question5q6=$_POST['question5q6'];
$question6=$_POST['question6'];
$question7=$_POST['question7'];
$question8=$_POST['question8'];
$question9=$_POST['question9'];
$tandc=$_POST['tandc'];


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO contact_n_questions VALUES ('','$fname','$lname','$position','$email','$state','$areacode','$prefix','$linenumber','$question1','$question2','$question2q1','$question2q2','$question2q3','$question2q4','$question2q5','$question2q6','$question3','$question4q1','$question4q2','$question4q3','$question4q4','$question4q5','$question5q1','$question5q2','$question5q3','$question5q4','$question5q5','$question5q6','$question6','$question7','$question8','$question9','$tandc')";


mysql_query($query);
mysql_close();


echo $fname;
echo $lname;


?>
<html>
<head>
<meta http-equiv="refresh" content=".0;url=http://www.xxxxxx/thankyou.php"
</head>
<body>
</body>
</html>

saint959 · April 26, 2007

hi,

can you try this and see if it works:

$query = "INSERT INTO contact_n_questions VALUES ('$fname','$lname','$position','$email','$state','$areacode','$prefix','$linenumber','$question1','$question2','$question2q1','$question2q2','$question2q3','$question2q4','$question2q5','$question2q6','$question3','$question4q1','$question4q2','$question4q3','$question4q4','$question4q5','$question5q1','$question5q2','$question5q3','$question5q4','$question5q5','$question5q6','$question6','$question7','$question8','$question9','$tandc')";

Wildbug · April 26, 2007

// ...
mysql_query($query);
if (mysql_errno()) echo "Database error: ",mysql_error();
mysql_close();
// ...

And the variable reassignment seems a little ... cumbersome ... at best. You could use $_POST directly, or better yet, use a sprintf and mysql_real_escape_string.

But check your return value from MySQL first.

ballhogjoni · April 26, 2007

saint959

Ya I have tried that and it doesn't work.

ballhogjoni · April 26, 2007

wildburg this is an error that came up: Database error: Column count doesn't match value count at row 1ChrisChris

Wildbug · April 26, 2007

It could be cleaner, but try this for now:

$query = sprintf("INSERT INTO contact_n_questions (fname, lname, position, email, state, areacode, prefix, linenumber, question1, question2, question2q1, question2q2, question2q3, question2q4, question2q5, question2q6, question3, question4q1, question4q2, question4q3, question4q4, question4q5, question5q1, question5q2, question5q3, question5q4, question5q5, question5q6, question6, question7, question8, question9, tandc, comments) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
	mysql_real_escape_string($_POST['fname']),
	mysql_real_escape_string($_POST['lname']),
	mysql_real_escape_string($_POST['position']),
	mysql_real_escape_string($_POST['email']),
	mysql_real_escape_string($_POST['state']),
	mysql_real_escape_string($_POST['areacode']),
	mysql_real_escape_string($_POST['prefix']),
	mysql_real_escape_string($_POST['linenumber']),
	mysql_real_escape_string($_POST['question1']),
	mysql_real_escape_string($_POST['question2']),
	mysql_real_escape_string($_POST['question2q1']),
	mysql_real_escape_string($_POST['question2q2']),
	mysql_real_escape_string($_POST['question2q3']),
	mysql_real_escape_string($_POST['question2q4']),
	mysql_real_escape_string($_POST['question2q5']),
	mysql_real_escape_string($_POST['question2q6']),
	mysql_real_escape_string($_POST['question3']),
	mysql_real_escape_string($_POST['question4q1']),
	mysql_real_escape_string($_POST['question4q2']),
	mysql_real_escape_string($_POST['question4q3']),
	mysql_real_escape_string($_POST['question4q4']),
	mysql_real_escape_string($_POST['question4q5']),
	mysql_real_escape_string($_POST['question5q1']),
	mysql_real_escape_string($_POST['question5q2']),
	mysql_real_escape_string($_POST['question5q3']),
	mysql_real_escape_string($_POST['question5q4']),
	mysql_real_escape_string($_POST['question5q5']),
	mysql_real_escape_string($_POST['question5q6']),
	mysql_real_escape_string($_POST['question6']),
	mysql_real_escape_string($_POST['question7']),
	mysql_real_escape_string($_POST['question8']),
	mysql_real_escape_string($_POST['question9']),
	mysql_real_escape_string($_POST['tandc']),  // This was out of sequence
	mysql_real_escape_string($_POST['comments'])   // I added this assuming you meant to

);

ballhogjoni · April 26, 2007

crap, it still doesn't work and gives me the same error. :'(

Wildbug · April 26, 2007

Oops, that would be because I added the comment line but failed to add another '%s' in the string format. Do that, and it should work.

ballhogjoni · April 26, 2007

Yay, Thank You so Much.

Wildbug · April 26, 2007

Follow-up:

Keeping things straight in a query string that long can be confusing, as you have discovered. Since your $_POST keys are the same as the column names into which they will be inserted, you could automate your query string generation so that it will remain fluid in the face of change. You can change any of the values in the array below, and it will still generate a valid string (assuming you've used the right names). I indented the query_string bit so that it's easier to decipher.

<?php
$data = array(
'fname',
'lname',
'position',
'email',
'state',
'areacode',
'prefix',
'linenumber',
'question1',
'question2',
'question2q1',
'question2q2',
'question2q3',
'question2q4',
'question2q5',
'question2q6',
'question3',
'question4q1',
'question4q2',
'question4q3',
'question4q4',
'question4q5',
'question5q1',
'question5q2',
'question5q3',
'question5q4',
'question5q5',
'question5q6',
'question6',
'question7',
'question8',
'question9',
'tandc',
'comments'
);

$query_string = 'INSERT INTO contact_n_questions ('
. implode(',',$data)
. ") VALUES ('"
. implode(
	"','",
	array_map(
		create_function(
			'$x',
			'global $_POST;return mysql_real_escape_string($_POST[$x]);'
		),
		$data
	)
)
. "')";

mysql_query($query_string);
?>

ballhogjoni · April 26, 2007

cool thats freking awesome

Sign In

[SOLVED] Mysql query using php question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information