php - Database help needed i'm stumped

792

I recently downloaded some software made with the codeigniter framework after following the install instructions, I ran the website on localhost, which loads up but as soon as I goto register a new member it gives me the following error. I'm brand new to all of this so what may seem trivial to you probably won't to me just a heads up.

Error Number: 1054 Unknown column '{-code-4}' in 'field list' INSERT INTO `bw_users` (`password`, `location`, `register_time`, `salt`, `{-code-4}`, `{-code-5}`, `{-code-6}`, `{-code-7}`, `user_hash`, `user_name`, `user_role`, `public_key`, `private_key`, `private_key_salt`, `wallet_salt`, `local_currency`) VALUES $2a$10$51GaLf4o644Rb/FxR8Os9enxWuop1V.haISOHzinT1Tq674.5SaXm', '224', 1454533259, '$2a$10$51GaLf4o644Rb/FxR8Os9hA2gGDxVQ==', 'f0a57fb24bcda7bd87e26bc9bafb71ee', 'ae7522de65812b', '', '1', '2eb1a2c0b086bf044a80',

Filename: models/Users_model.php

Line Number: 37

I pulled up the Users_model line 37 and the code surrounding it

I then checked the rest of the code for {-code-4} and the only place I could see it was towards the bottom of the users_model.php, it is as follows.

{-code-2}

This is the bw_users part of my sql database, I can post the whole database dump if needed.

{-code-3}

Can someone please tell me what I need to do, to remove this error and be able to register users, i've been trying to figure this out all day and it's driving me crazy. Thank you for any help.

EDIT

Answer

Thanks for the replys so far, I have modified the tables as follows with the information provided by @Vũ Tuấn Anh

ALTER TABLE bw_users ADD COLUMNactivation_hash VARCHAR(128), ADD COLUMNactivation_id VARCHAR(128), ADD COLUMNemail_address VARCHAR(64),
ADD COLUMNemail_activated SMALLINT(4);

CREATE TABLE IF NOT EXISTS `bw_users` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `banned` enum('0','1') DEFAULT '0',
  `block_non_pgp` enum('0','1') DEFAULT '0',
  `entry_paid` enum('0','1') DEFAULT '0',
  `force_pgp_messages` enum('0','1') DEFAULT '0',
  `location` int(3) NOT NULL,
  `login_time` int(20) NOT NULL,
  `display_login_time` enum('0','1') DEFAULT '0',
  `password` varchar(128) NOT NULL,
  `public_key` blob NOT NULL,
  `private_key` blob NOT NULL,
  `private_key_salt` varchar(64) NOT NULL,
  `register_time` int(20) NOT NULL,
  `salt` varchar(128) NOT NULL,
  `wallet_salt` varchar(128) NOT NULL,
  `user_hash` varchar(25) NOT NULL,
  `user_name` varchar(40) NOT NULL,
  `user_role` enum('Buyer','Vendor','Admin') NOT NULL,
  `local_currency` int(11) NOT NULL,
  `completed_order_count` int(9)  DEFAULT '0',
  `totp_secret` varchar(25),
  `totp_two_factor` enum('0','1') DEFAULT '0',
  `pgp_two_factor` enum('0','1') DEFAULT '0',
  'activation_hash' varchar(128),
  'activation_id' varchar(128),
  'email address' varchar(64),
  'email_activated' smallint(4),
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_hash` (`user_hash`,`user_name`),
  KEY `user_name` (`user_name`,`user_hash`,`banned`,`entry_paid`,`register_time`,`user_role`, 'activation_has', 'activation_id', 'email_address', 'email_activated')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `bw_users` (`banned`, `block_non_pgp`, `entry_paid`, `force_pgp_messages`, `location`, `login_time`, `display_login_time`, `password`, `public_key`, `private_key`, `private_key_salt`, `register_time`, `salt`, `pgp_two_factor`, `user_hash`, `user_name`, `user_role`, `local_currency`, `completed_order_count`, `totp_two_factor`, `totp_secret`, 'activation_hash', 'activation_id', 'email_address', 'email_activated') VALUES
('0', '0', '1', '0', 1, 0, '0', '%PASSWORD%', '%PUBLIC_KEY%', '%PRIVATE_KEY%', '%PRIVATE_KEY_SALT%', '%REGISTER_TIME%', '%SALT%', '0', '%USER_HASH%', 'admin', 'Admin', 0, 0, '0', '');

The problem now is when I goto add the database again, it is saying there is a syntax error and it won't add the new lines, what have I done wrong here?

At the bottom were I also put them into the insert into code, do I need to move them or any of them to the Primary Key, Unique Key or Key line instead?

418

Answer

Solution:

Modify your table with:

ALTER TABLE bw_users
ADD COLUMN `activation_hash` VARCHAR(128), 
ADD COLUMN `activation_id` VARCHAR(128), 
ADD COLUMN `email_address` VARCHAR(64), 
ADD COLUMN `email_activated` SMALLINT(4);

EDITED ANSWER AFTER QUESTION UPDATED:

Your sql insert statement has basic error when using' character. Please use ` as bellow:

INSERT INTObw_users (banned,block_non_pgp,entry_paid,force_pgp_messages,location,login_time,display_login_time,password,public_key,private_key,private_key_salt,register_time,salt,pgp_two_factor,user_hash,user_name,user_role,local_currency,completed_order_count,totp_two_factor,totp_secret,activation_hash,activation_id,email_address,email_activated) VALUES ('0', '0', '1', '0', 1, 0, '0', '%PASSWORD%', '%PUBLIC_KEY%', '%PRIVATE_KEY%', '%PRIVATE_KEY_SALT%', '%REGISTER_TIME%', '%SALT%', '0', '%USER_HASH%', 'admin', 'Admin', 0, 0, '0', '');

More detail: Your old:

'activation_hash' => `activation_hash`
'activation_id'   => `activation_id`
'email_address'   => `email_address`
'email_activated' => `email_activated`

If you get syntax error, please carefully check syntax. It is simple.

People are also looking for solutions to the problem: PHP Extract Codes From TextArea and Pass to Mysql SELECT IN Query

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question