Column count doesn't match value count at row 1

PHP
1

I’m getting this error: “Column count doesn’t match value count at row 1”
Can you help me figure out why?

<?php
error_reporting (0);
include_once ('classes/config.php');
include_once ('includes/reserved_names.php');
$user_ip    = mysql_escape_string($_SERVER['REMOTE_ADDR']);

if ( $enable_forum == 1 ) header("Location: " . $smf_bridge_register);

$send_confirm_email = $config['enabled_features_confirmation_email'] ;

// define access for loading non display php files
define('access',true);

$ahah			= 1;
$show_register	= '';
$load_ajax		= '';
$form_submitted	= '';
$action		= '';
$new_user_name	= '';
$account_type 	= '';
$first_name		= '';
$last_name 		= '';
$email_address 	= '';
$email_address2 	= '';
$user_name 		= '';
$password 		= '';
$confirm_password = '';
$country_list	= '';
$dob_month		= '';
$dob_day		= '';
$dob_year		= '';
$zip_code		= '';
$birthday		= '';
$error_message 	= '';
$checked 		= '';

$show_register	= 1;
$load_ajax		= 1;
$procede 		= true;
$action		= mysql_real_escape_string( $_GET['action'] );
$new_user_name	= mysql_real_escape_string( $_GET['new_user_name'] );
$site_name 		= mysql_real_escape_string($config['site_name']);
$form_submitted	= mysql_real_escape_string( $_POST['form_submitted'] );
$max_len_username	= (int)$config['max_username_length'];

if ( $action == 'check_user' ) {
	$new_user_name	= trim(strtolower($new_user_name));
	if ( $new_user_name == '' ) {
		echo "<font color=\"#EE0000\" size=\"2\"><b>".$config['fill_all_fields']."</b></font>";
		die();
	}
	if ( strlen($new_user_name) < 4 ) {
		echo "<font color=\"#EE0000\" size=\"2\"><b>".$config['username_4_chars']."</b></font>";
		die();
	}
	if ( strlen($new_user_name) > $max_len_username ) {
		echo "<font color=\"#EE0000\" size=\"2\"><b>".$config['username_to_long']."</b></font>";
		die();
	}

	// check against reserved names e.g. -> admin etc... or adult words ban
	if ( in_array( $new_user_name, $reserved_names ) ) {
		echo "<font color=\"#EE0000\" size=\"2\"><b>".$config['username_not_allowed'] ."";
		die();
	}

	$sql		= "SELECT user_name FROM member_profile WHERE user_name = '$new_user_name'";
	$query	= @mysql_query($sql);
	$count	= @mysql_num_rows($query);

	if ( $count > 0 ) {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#EE0000\" size=\"2\">&nbsp;<b>=> $lang_not_available</b></font>";
	} else {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#00DD00\" size=\"2\">&nbsp;<b>=> $lang_available </b></font>";
	}

	@mysql_free_result($result);
	@mysql_close();
	die();
}

if ( $action == 'show_country' ) {
	$country_fields_all	= '';
	$show_fields		= '';
	$country_list 		= file('includes/country.list');

	foreach ( $country_list as $country_select )
	{
		$country_fields_all = $country_fields_all . $country_select;
	}

	$show_fields  = '<div style="margin-left:30px; float:left;"><b>'.$lang_country.':</b></div>';
	$show_fields .= '<div style="margin-left:95px; float:left;" id="country_list">';
	$show_fields .= '<select class="FormSpecialInput_1" id="country_list" name="country_list" style="font-size: 9pt; width:128px; height:20px; font-weight:bold; color:#444444; letter-spacing: 1; border: 1px solid #DFDCDC; background-color: #FDFDFD">';
	$show_fields .= $country_fields_all;
	$show_fields .= '</select></div>';

	echo $show_fields;
	die();
}

if ( $procede == true ) {

	$account_type 	= 'Standard';
	$first_name		= trim(mysql_real_escape_string( $_POST['first_name'] ));
	$last_name 		= trim(mysql_real_escape_string( $_POST['last_name'] ));
	$email_address 	= trim(mysql_real_escape_string( $_POST['email_address'] ));
	$email_address2 	= trim(mysql_real_escape_string( $_POST['email_address2'] ));
	$user_name 		= trim(mysql_real_escape_string( $_POST['user_name'] ));
	$password 		= trim(mysql_real_escape_string( $_POST['password'] ));
	$confirm_password = trim(mysql_real_escape_string( $_POST['confirm_password'] ));
	$country_list	= trim(mysql_real_escape_string( $_POST['country_list'] ));
	$dob_month		= (int) mysql_real_escape_string( $_POST['dob_month'] );
	$dob_day		= (int) mysql_real_escape_string( $_POST['dob_day'] );
	$dob_year		= (int) mysql_real_escape_string( $_POST['dob_year'] );
	$zip_code		= (int) mysql_real_escape_string( $_POST['zip_code'] );

	if ( $_POST['terms'] == 'yes' ) {
		$checked = 'checked=\"checked\"';
    		$procede = true;

	} else {
		$procede = false;
    		$error_message = $config['agree_to_terms'];
	}

	if ( strlen($user_name) < 4 ) {
		$error_message = $config['username_4_chars'];
		$procede = false;
	}
	if ( strlen($user_name) > $max_len_username ) {
		$error_message = $config['username_to_long'];
		$procede = false;
	}

	// check against reserved names e.g. -> admin etc... or adult words ban
	if ( in_array( $user_name, $reserved_names ) ) {
		$error_message = $config['username_not_allowed'];
		$procede = false;
	}

	// dDB birthday must be this format =>2008-09-17
	$birthday		= $dob_year .'-'. $dob_month .'-'. $dob_day;

	foreach ($_POST as $key => $value) {

      	if (!isset($value) || ($value == '')) {
            	$display_key = @str_replace('_', ' ', $key);
            	if ( $display_key == 'zip code' && $value == '' ) {
            		$value = 'none';
            	} else {
            		$error_message = $error_message . ' - ' . $display_key . '  '.$lang_required.' ';
            		$procede = false;
            	}

        	} else {

        	      if ( $key == 'email_address2' ) $key = 'email_address';

          	      if ( $key !== 'email_address'  && (!eregi("^[ _a-zA-Z0-9-]*$", $value)) ) {
            		$display_key = @str_replace('_', ' ', $key);
                		$error_message = $error_message . ' - ' . $display_key . ' '.$config['invalid_email_text'].' ';
                		$procede = false;
            	}

            	if ( $key == 'email_address' && !eregi("^[_a-zA-Z0-9-]+(\.[_a-zA-Z0-9-]+)*@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*(\.[a-z]{2,3})$", $value) ) {
            		$display_key = @str_replace('_', ' ', $key);
                		$error_message = $error_message . ' - ' . $display_key . ' '.$config['invalid_email_format'].' ';
                		$procede = false;
            	}

            	if ( $key == 'user_name' ) {

            		// check against reserved names e.g. -> admin etc... or adult words ban
            		if ( in_array( $user_name, $reserved_names ) ) {
            			$display_key = @str_replace('_', ' ', $key);
            			$error_message = $error_message . ' ' .$config['username_not_allowed'] . ' !';
            			$procede = false;
            		}
            	}

        	}
    	}
}

// checking if emails match
if ( $procede == true ) {
	if ( $email_address !== $email_address2 ) {
      	$error_message = ' '.$config['emails_do_not_match'];
        	$procede = false;
    	}
}

// checking if passwords match
if ( $procede == true ) {
	if ($confirm_password !== $password) {
      	$error_message = ' '.$config['password_do_not_match'];
        	$procede = false;
    	}
}

// checking if username and email is unique
if ( $procede == true ) {

	$sql = "SELECT user_name, email_address FROM member_profile";
    	$query = @mysql_query($sql);

	while ($result = (@mysql_fetch_array($query))) {

      	if ( !empty($result['user_name']) || !empty($result['email_address']) ) {

      		// case insensitive login and registration
        		$reg_user_name	= strtolower($user_name);
        		$db_user_name 	= strtolower($result['user_name']);

            	if ($db_user_name == $reg_user_name) {
                		$error_message = ' '.$config['username_is taken'];
                		$procede = false;
            	}
            	if ( $result['email_address'] == $email_address ) {
                		$error_message = $error_message . ' -'.$config['email_already_exists'];
                		$procede = false;
                	}
           }
    }
}

// if any errors display error message => [var.message_type] - [var.error_message]
if ($procede == false && $form_submitted == 'yes') {
	$message_type	= $lang_error;
    	$blk_notification = 1;
    	$show_signup = 1;

    	$template 		= "themes/$user_theme/templates/main_1.htm";
    	$inner_template1 	= "themes/$user_theme/templates/inner_signup_form.htm";
    	$TBS 			= new clsTinyButStrong;
    	$TBS->NoErr 	= true;

    	$TBS->LoadTemplate("$template");

    	$TBS->Render 	= TBS_OUTPUT;
    	$TBS->Show();

    	@mysql_close();
    	die();
}

// START => load page with form
if ( !isset($form_submitted) || ($form_submitted == '') ) {

	$show_signup 	= 1;
	$template 		= "themes/$user_theme/templates/main_1.htm";
      $inner_template1 	= "themes/$user_theme/templates/inner_signup_form.htm";
      $TBS 			= new clsTinyButStrong;
      $TBS->NoErr 	= true;

      $TBS->LoadTemplate("$template");
      $TBS->Render 	= TBS_OUTPUT;
      $TBS->Show();
      die();
}


// if no errors register user and load welcome page
if ($procede == true) {

	if ( strtolower($_POST['captext']) != strtolower($_SESSION['security_code']) ) {

		// POSSIBLE BOT ATTACK => TODO: ADD SECURITY LOG ACTIONS ETC....
		// LOADING TEMPLATE IS MAYBE USELESS SINCE THIS WOULD COME FROM A SCRIPT POST
		// MAYBE SHOULD JUST DO A DIE HERE!

		$message_type	= $lang_error;
		$blk_notification = 1;
    		$show_signup 	= 0;

    		$template 		= "themes/$user_theme/templates/main_1.htm";
    		$inner_template1 	= "themes/$user_theme/templates/inner_signup_form.htm";
    		$TBS 			= new clsTinyButStrong;
    		$TBS->NoErr 	= true;

    		$TBS->LoadTemplate("$template");
	    	$TBS->Render 	= TBS_OUTPUT;
    		$TBS->Show();

	    	@mysql_close();
    		die();

	} else {

		$random_code	= randomcode();
    		$password_email	= $password;
    		$password		= md5($password);
    		$passwordSalt 	= substr(md5(rand()), 0, 4);

    		if ( $zip_code > '' ) $country_list = 'USA';

		if ( $send_confirm_email == 'yes' ) {

			// insert new user record
			$sql = "INSERT into member_profile (email_address, user_name, password, passwordSalt, first_name, last_name, zip_code, country, user_ip, birthday, account_status, account_type, date_created, random_code)
    				  VALUES ('$email_address', '$user_name', '$password', '$passwordSalt', '$first_name', '$last_name', '$zip_code', '$country_list', '$user_ip', '$birthday', 'new', 'standard', NOW(), '$random_code')";

    			@mysql_query($sql) or die($config['error_26']);//error

    			// get new user_id
    			$sql 		= "SELECT user_id, email_address, random_code FROM member_profile WHERE random_code = '$random_code' AND email_address = '$email_address'";
    			$query 	= @mysql_query($sql);

    			while ($result = @mysql_fetch_array($query)) $user_id = $result['user_id'];

			// set new user default privacy settings
    			$sql		= "INSERT INTO privacy (videocomments, profilecomments, privatemessage, friendsinvite, newsletter, user_id, publicfavorites, publicplaylists) VALUES ('yes', 'yes', 'yes', 'yes', 'yes', '$user_id', 'yes', 'yes')";
	    		$query 	= @mysql_query($sql);

			@mysql_close();

			// send email
      		$email_template	= 'email_templates/newmember.htm';
      		$subject 		= $config['email_welcome'];
      		$to 			= $email_address;
      		$from 		= $config['notifications_from_email'];

      		//send email template to TBS for rendering of variable inside
      		$template 		= $email_template;
      		$TBS 			= new clsTinyButStrong;
      		$TBS->NoErr 	= true;

      		$TBS->LoadTemplate("$template");
      		$TBS->tbs_show(TBS_NOTHING);
      		$message 		= $TBS->Source;

      		//load postage.php
			define('access',true);
			include ('includes/postage.php');

    			// show success
    			$notification_type	= $config['notification_success'];
    			$message 			= $config['registration_success'];
    			$blk_notification 	= 0;

    			$template 			= "themes/$user_theme/templates/main_1.htm";
    			$inner_template1 		= "themes/$user_theme/templates/inner_notification.htm";
    			$TBS 				= new clsTinyButStrong;
    			$TBS->NoErr 		= true;

    			$TBS->LoadTemplate("$template");
    			$TBS->Render 		= TBS_OUTPUT;
    			$TBS->Show();

    			@mysql_close();
    			die();

		// else send confirmation email is off so we complete the regisration process and show sign in message
		} else {

			// insert new user record
			$sql = "INSERT into member_profile (email_address, user_name, password, passwordSalt, first_name, last_name, zip_code, country, birthday, account_status, account_type, date_created, random_code)
    				  VALUES ('$email_address', '$user_name', '$password', '$passwordSalt', '$first_name', '$last_name', '$zip_code', '$country_list', '$birthday', 'active', 'standard', NOW(), '$random_code')";

    			@mysql_query($sql) or die($config['error_26']);//error

    			// get new user_id
    			$sql 		= "SELECT user_id, email_address, random_code FROM member_profile WHERE random_code = '$random_code' AND email_address = '$email_address'";
    			$query 	= @mysql_query($sql);

    			while ($result = @mysql_fetch_array($query)) $user_id = $result['user_id'];

			// set new user default privacy settings
    			$sql		= "INSERT INTO privacy (videocomments, profilecomments, privatemessage, friendsinvite, newsletter, user_id, publicfavorites, publicplaylists) VALUES ('yes', 'yes', 'yes', 'yes', 'yes', '$user_id', 'yes', 'yes')";
	    		$query 	= @mysql_query($sql);

			@mysql_close();

			$show_signup	= 0;
			$show_login		= 1;
			$show_action_msg	= 1;

			$message_type	= $config['notification_success'];
   			$error_message 	= $config['reg_confirm_complete'];
    			$action_msg		= $message_type . " - " . $error_message;

    			$template 		= "themes/$user_theme/templates/main_1.htm";
    			$inner_template1 	= "themes/$user_theme/templates/inner_signup_form.htm";
    			$TBS 			= new clsTinyButStrong;
    			$TBS->NoErr 	= true;
			$TBS->LoadTemplate("$template");
    			$TBS->Render 	= TBS_OUTPUT;
    			$TBS->Show();

   			@mysql_close();
    			die();
		}
	}// end captcha check if

}

?>
2

I can’t see the issue jumping out at me, I would suggest debugging by running each insert query seperately until you find the exact one that is causing an issue. I have a feeling that one of the variables that you are passing directly into the query might be blank.
I suggest that you look into PDO as it is much safer to use then what you have been, also maybe sanitize your input first

1 Like
3

Yes, which query is throwing the error message, and what values are you using in that query?

4

It might help if you do a few things.

  • turn error_reporting back on
  • remove the @ error suppressing

You will likely get a lot of DEPRECATED Notices but it should help in seeing where the problem is until the server updates to the current PHP version at which time the script will fail completely

5

Thanks for your replies.
I have done this:
//error_reporting (0);
but still just see this:
Column count doesn’t match value count at row 1

Can you please clarify how to “run each query seperately”?
What is PDO?
Regarding " what values are you using in that query", doesn’t that show in this code (which I didn’t write)
Also, I don’t see @ error.

Any additional help will be appreciated.

6

They are not part of an error message, it is an “error control operator”

http://php.net/manual/en/language.operators.errorcontrol.php

PHP supports one error control operator: the at sign (@). When prepended to an expression in PHP, any error messages that might be generated by that expression will be ignored.

The code you posted has 20 of them that I can see (as far as the DEPRECATED mysql_ lines). eg. there are 4 in this block

	$sql		= "SELECT user_name FROM member_profile WHERE user_name = '$new_user_name'";
	$query	= @mysql_query($sql);
	$count	= @mysql_num_rows($query);

	if ( $count > 0 ) {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#EE0000\" size=\"2\">&nbsp;<b>=> $lang_not_available</b></font>";
	} else {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#00DD00\" size=\"2\">&nbsp;<b>=> $lang_available </b></font>";
	}

	@mysql_free_result($result);
	@mysql_close();

Do not simply comment out those lines because the code needs them.
If you prefer much better to save a back-up copy or do like

	$sql		= "SELECT user_name FROM member_profile WHERE user_name = '$new_user_name'";
//	$query	= @mysql_query($sql);
	$query	= mysql_query($sql);
//	$count	= @mysql_num_rows($query);
	$count	= mysql_num_rows($query);

	if ( $count > 0 ) {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#EE0000\" size=\"2\">&nbsp;<b>=> $lang_not_available</b></font>";
	} else {
		echo "<font size=\"2\"><b>$new_user_name</b></font><font color=\"#00DD00\" size=\"2\">&nbsp;<b>=> $lang_available </b></font>";
	}

//	@mysql_free_result($result);
	mysql_free_result($result);
//	@mysql_close();
	mysql_close();
7

PDO is one of the two ways PHP provides for accessing databases - the other is MYSQLi

These were both introduced 11 years ago as much improved ways of accessing databases compared to the MYSQL way that was around before that (which was flagged for removal from PHP two and a half weeks ago and actually removed in the new PHP version released last week).

1 Like
8

No, we were hoping to see the contents of each variable, in case something within the variables is causing the problem. So each time you create a query, echo it to the console after you’ve done so - the display will be a bit weird, but you will be able to post the results. For each of these areas, where you create an sql query:

$sql = "INSERT into member_profile (email_address, user_name, password, passwordSalt, first_name, last_name, zip_code, country, user_ip, birthday, account_status, account_type, date_created, random_code)
VALUES '$email_address', '$user_name', '$password', '$passwordSalt', '$first_name', '$last_name', '$zip_code', '$country_list', '$user_ip', '$birthday', 'new', 'standard', NOW(), '$random_code')";

on the next line, add in

echo $sql;

This will display the entire $sql string on your page somewhere, which might give a clue to what’s going wrong. The only places that can be causing that error message are the lines where you execute a query that inserts into the database - you’ve got two sections where you do that twice, once to insert into member_profile, the second to insert into privacy. The first thing is to find out which of these queries is doing it, then figure out a strategy as to how to deal with it. Has it ever worked, that is, is this new code that you’re trying to start using, or old code that used to work but now does not? And does it fail only when you type certain entries into the form, and if so, what entries?

The comments about PDO are because the way you access the database in this code (those functions that start with mysql_) are changing. Newer ways to do it have been advised for many years in preference to the older functions, and with the latest release of PDO the old ones will stop working completely. So if it’s new code, and you’ve had someone write it for you (you mentioned that you didn’t write it), I’d suggest you get them to re-write it using the more modern functions.

9

Thanks for all of your help. To answer your questions, thisisn’t newly added code and has worked before.
Your suggestions helped me narrow down the issue. Much thanks.
Upon testing your suggestion, I realized that part of this file’s code didn’t appear in this thread posting(sorry about that). And when I removed that part from the file and ran the registration, it was successful. Whivh helped me to determine that this part of the code is having the ‘Column Count’ issue:

			$sql01 = "SELECT start FROM credit_config LIMIT 1";
		$query1 = @mysql_query($sql01);

		while ($result = @mysql_fetch_array($query1)) {$startcredit = $result['start'];}

		$sql2 = "INSERT INTO credits (user_id, total_credits, pending_credits, last_purchase, total_purchases, name) VALUES ('$user_id', '$startcredit', '0', NOW(), '0', '$user_name')";
		$query2 = @mysql_query($sql2) or DIE(mysql_error());

		$sql02 = "INSERT INTO purchases (id, type, user_id, vid_id, date, name, uploader, uploaderID, title, amount, videoid, descr, promo, channel_id) VALUES ('', '$adjustment', $user_id, 0, NOW(), '$user_name', 'none', 'none', 'none', '$startcredit', 'none', 'none', 'none')";
		$query02 = mysql_query($sql02) or die(mysql_error());

So, I added:

echo $sq2;

below this:

			$sql2 = "INSERT INTO credits (user_id, total_credits, pending_credits, last_purchase, total_purchases, name) VALUES ('$user_id', '$startcredit', '0', NOW(), '0', '$user_name')";

and see this:
INSERT INTO credits (user_id, total_credits, pending_credits, last_purchase, total_purchases, name) VALUES (‘8288’, ‘6’, ‘0’, NOW(), ‘0’, ‘alex1’)Column count doesn’t match value count at row 1

Can you help me a little more from here? Does this error displayed give you an idea of what I might investigate?

10

The “column count doesn’t match value count” message suggests that
(list, of, fields)
doesn’t have the same count as
VALUES (list, of, values)

I’ve looked a few times but didn’t see any obvious mistake

Something has changed somewhere.

The main problem is because using the DEPRECATED mysql_* functions don’t do prepared queries and the code concatenates

My guess is that a boundary or delimiter character is making its way into the query.

Letting PHP show you the error or displaying the constructed query by using echo should help you pinpoint the problem

11

Having a bit of a search around on this, one other thing came up - are there any triggers on the table you are updating when you get the error message? You can configure triggers to update other tables automatically, which might contain the error.

12

Thanks again for your replies.
I don’t know much about triggers or concayenated code.
But, when I comment out these lines:

$sql02 = "INSERT INTO purchases (id, type, user_id, vid_id, date, name, uploader, uploaderID, title, amount, videoid, descr, promo, channel_id) 
		VALUES ('', 'adjustment', '$user_id', 0, NOW(), '$user_name', 'none', 'none', 'none', '$startcredit', 'none', 'none', 'none')";

The file works successfully. So, I have a tendancy to think that these lines are the problem.
Is the problem that INSERT has a 14 count and VALUS has 13?

13

Yes,that would do it.
The code is saying “into these 14 fields, put these 13 values” and one of them is screaming “hey, what about me?!”

Do you know which value is missing?

14

Thanks again for taking the time to reply. Much appreciated.
I looked back on previous code and channel_id was added to INSERTS, but nothing was added to VALUES it seems. So, I added an extra ‘0’ to VALUES and it works successfully.

I know channel_id needs to be a number’, so 0 may be appropriate. Any ideas on that will help me get more enlightened.
Thanks again

15

I don’t know enough to know if 0 is appropriate.

Can you post the table schemas? i.e. run SHOW COLUMNS queries

http://dev.mysql.com/doc/refman/5.7/en/show-columns.html

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.