error while running install.sql in community auth

Code:

-- Trigger updates passwd_modified_at field if passwd modified
--

delimiter $$  <<this line
DROP TRIGGER IF EXISTS ca_passwd_trigger ;
$$           <<this line
CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
   IF ((NEW.passwd <=> OLD.passwd) = 0) THEN
       SET NEW.passwd_modified_at = NOW();
   END IF;
END;$$   <<this line
delimiter ;

-- --------------------------------------------------------

The $$ is causing an error when I try to run install.sql in phpmyadmin. A table called New is created but it is empty. What am I doing wrong?

proof that an old dog can learn new tricks

Just remove this trigger. It's not required.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

That worked. Thanks! I have a more general question about coummunity Auth. Is the path for a new user of community auth to create a stand alone application with it during installation? The reason I am asking is that on steps 12 of the install intructs, it asks the user to use Key_creator controller to create a key which then should be setup in config.php. Step 13 describes creating a test user, programmatically, but what comes first?

I have been following the instructs (I am up to step 13) and installing directly to my application. But I am starting to think that this is wrong and I should have created a standalone Community Auth application first. I also see the create_user method in the examples directory and that makes me think that I should be creating a standalone, first, just to prove that it works? I realize that the demo was removed and this is probably the reason that I don't understand the install steps.

proof that an old dog can learn new tricks

You just need to create the encryption key correctly, and before you create any users. Maybe the steps could be consolidated, but if you're not sure how to create a proper encryption key, the Key_creator is there for you.

There's no reason why Community Auth can't be installed into an existing application. It's made to be easy to do.

Take the time to read over the documentation. It's worth it, and Community Auth is very easy to use if you do so.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

I am trying to create a user. But I am getting an error

A Database Error Occurred
Error Number: 1054
Unknown column 'user_id' in 'where clause'
SELECT * FROM `users` WHERE `user_id` = 2147484848
Filename: C:/xampp/htdocs/sub_crud2/system/database/DB_driver.php
Line Number: 691

I think that this is due to the fact that I already had a table called users, so when I ran sql.ini the script didn't create the users table that Community Auth needs, because there is an already existing users table (with different field names). How do you recommend to resolve this? I can modify sql.ini to create table users2, but then I will need to modify the community auth code, all over. And I don't want to make my system so I have a "custom version" of Community Auth.

proof that an old dog can learn new tricks

It sounds like you know what you need to do, and Community Auth makes this easy, as long as you use the db_tables config file. You could just change the value of user_table to something else, like ca_users. Then just rename the table in the install script to ca_users. When you want to use that table, you'd still use $this->db_table('user_table'). There's nothing "custom" about doing that. Community Auth was created with this flexibility for cases like this.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

I did what you suggested. I am still getting:
A Database Error Occurred
Error Number: 1054
Unknown column 'user_id' in 'where clause'
SELECT * FROM `users` WHERE `user_id` = 779771974
Filename: C:/xampp/htdocs/sub_crud2/system/database/DB_driver.php
Line Number: 691

when I run localhost/Examples/create_user from the address bar on the browser.
I have traced through the error to the call to
$user_data['user_id'] = $this->examples_model->get_unused_id();

at the point of calling this line above, $this is full of data. Yet when I set a breakpoint in the function get_unused_id()and I look at $this, it is empty. So the line in get_unused_id():

$query = $this->db->where( 'user_id', $random_unique_int )
->get_where( $this->db_table('user_table') );

fails because $this only has one value which is "acl". I have no idea why $this is empty or what acl is.

Any idea what is going on?

proof that an old dog can learn new tricks

I did what you suggested. I am still getting:
A Database Error Occurred
Error Number: 1054
Unknown column 'user_id' in 'where clause'
SELECT * FROM `users` WHERE `user_id` = 779771974
Filename: C:/xampp/htdocs/sub_crud2/system/database/DB_driver.php
Line Number: 691

when I run localhost/Examples/create_user from the address bar on the browser.
I have traced through the error to the call to
$user_data['user_id']    = $this->examples_model->get_unused_id();

at the point of calling this line above, $this is full of data. Yet when I set a breakpoint in the function get_unused_id()and I look at $this, it is empty. So the line in get_unused_id():

       $query = $this->db->where( 'user_id', $random_unique_int )
           ->get_where( $this->db_table('user_table') );

fails because $this only has one value which is "acl". I have no idea why $this is empty or what acl is.

Any idea what is going on?

Another hint.  I just tried running login from the "http://localhost/index.php/login?redirect=Examples%2Findex"

I get this error:
Error Number: 1054
Unknown column 'auth_level' in 'field list'
SELECT `username`, `email`, `auth_level`, `passwd`, `user_id`, `banned` FROM `users` WHERE LOWER( username ) = '[email protected]' OR LOWER( email ) = '[email protected]' LIMIT 1
Filename: C:/xampp/htdocs/sub_crud2/system/database/DB_driver.php
Line Number: 691

It seems that the Community Auth is still trying to use the 'users' table instead of the ca_users table.  Here is my db_tables


// USER RELATED TABLES
$config['user_table']                   = 'ca_users';  /*modified 6/1/18 due to naming conflict */

// LOGIN ERROR RELATED TABLES
$config['errors_table']                 = 'login_errors';
$config['IP_hold_table']                = 'ips_on_hold';
$config['username_or_email_hold_table'] = 'username_or_email_on_hold';

proof that an old dog can learn new tricks

Honestly, this doesn't seem to have anything to do with Community Auth. You mentioned that you have an existing users table; Where did that come from? The scope of the support I can provide is limited to Community Auth, and it's hard to help in a case like this because you've got other code that is part of the problem.

If you want to make a download available for me, and it has a full site backup of files and database, I'll install it on my local machine and let you know what's wrong. Keep in mind, due to my schedule this may take more than 1 day.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

>>You mentioned that you have an existing users table; Where did that come from?
I have a table called users that my application uses and that I created.
>>because you've got other code that is part of the problem.
and that is why I brought up building a demo app. I think I will create a brand new clean CI install and try using that. I will create a new database with new tables with sql.ini.  What I don't get is why the db_tables didn't work?

proof that an old dog can learn new tricks