Query SQL error

spirit receiver · 07-15-2006, 11:09 AM

Sorry, I really don't understand what you're trying to do. There's no field named "activation" in that table, so you can't assign a value to a field of that name.
"id" is still not set to be incremented automatically, otherwise it would say so in the "extras" column of the table description. You can use an "ALTER TABLE" statement similar to the one above to change that.

gabsik · 07-16-2006, 09:05 AM

I seriously promise i will study a SQL guide soon , now i only need to auto_increment the id number for new user registred .... How would you do it ???

spirit receiver · 07-16-2006, 09:51 AM

Quote:

Originally Posted by gabsik

How would you do it ???

I thought this was what I explained all the time. Try the following command:

Code:

ALTER TABLE jos_users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;

Invoke "DESCRIBE jos_users;", it will hopefully show up with an auto_increment flag for the column 'id'. Then you can use the following command to insert a new row which will get an 'id' automatically:

Code:

INSERT INTO jos_users (name) VALUES ('John');

gabsik · 07-16-2006, 12:48 PM

First command

Code:

mysql> ALTER TABLE jos_users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

DESCRIBE jos_users;

Code:

+-----------------------+-----------------------+------+-----+---------------------+----------------+
| Field                 | Type                  | Null | Key | Default             | Extra          |
+-----------------------+-----------------------+------+-----+---------------------+----------------+
| id                    | mediumint(9)          |      | PRI | NULL                | auto_increment

last command

Code:

mysql> INSERT INTO jos_users (name) VALUES ('ciccio');
Query OK, 1 row affected (0.00 sec)

results:

Code:

mosuser::store failed<br/>Unknown column 'activation' in 'field list' SQL=INSERT INTO jost_users(
'id','name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('0','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')

spirit receiver · 07-16-2006, 04:23 PM

Code:

INSERT INTO jost_users('id','name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('0','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')

Where does that command come from? As the error messages indicates, there's no column 'activation', so you won't be able to insert any values into it. There wasn't such a column in your first post, so I guess you know how to change the program that issues this SQL statement. And when you're at it, remove the 'id' part from the statement (and the corresponding '0').

gabsik · 07-16-2006, 11:40 PM

What command do you mean?
anyway the sequence of commands is :
1

Code:

mysql> ALTER TABLE jos_users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;

2

Code:

INSERT INTO jos_users('id','name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('0','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')

Quote:

And when you're at it, remove the 'id' part from the statement (and the corresponding '0').

This means i have to remove id and value 0 from the 2 command , right ?
So it looks like

Code:

INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')

gabsik · 07-16-2006, 11:50 PM

That's what i did:

Code:

mysql> ALTER TABLE jos_users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')
    ->

It doesn't look it worked .... i must be stupid !

spirit receiver · 07-17-2006, 04:55 PM

Quote:

Originally Posted by gabsik

What command do you mean?

The command that I quoted in my previous post.

Quote:

So it looks like

Code:

INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate','activation')VALUES('ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-16 19:41:51','937099cfec294262792a285909b41725')

There's still a field named "activation" here. You can't insert a field that doesn't exist in your table. Moreover, the values you're trying to insert don't seem to fit the field names you specify:
name - ciccio
username - ciccio@yahoo.it (?)
email - 27b4b5b01b0d1fcab2046369720ff75e
password -
usertype - 1
block - 18
gid - 2006-07-16 19:41:51
registerDate - 937099cfec294262792a285909b41725
activation (!) -

gabsik · 07-17-2006, 08:46 PM

I'm messing things up , look this code .....

Code:

mysql> INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate')VALUES('ciccio','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-1804:55:00','51805edae6e5131c6f6d7941e9add048')
    -> describe jos_users; ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''name','username','email','password','usertype','block','gid','
mysql> INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate')VALUES('ciccio','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','','1','18','2006-07-1804:55:00','51805edae6e5131c6f6d7941e9add048')

.... if names and VALUES fit togheter !!!
Do they have to be related to an already registred user ? ... a fake one ?
It says it's wrong here:

Quote:

Check the manual that corresponds to your MySQL server version for the right syntax to use near ''name','username','email','password','usertype','block','gid','

I registred to my site using the above username pass ecc i have copied them from the error window, give it a try ... https://www.gabrix.ath.cx

spirit receiver · 07-18-2006, 03:59 AM

No, they don't fit. There are 8 field names and 9 values. And the error message probably stems from a missing semicolon before "DESCRIBE jos_users;". In principle, there's no restriction on the data you may enter, it'll always create a new row in your table. But if you want to use this for registration data for your website, you should probably make sure that user names are unique.

gabsik · 07-18-2006, 01:24 PM

Quote:

Originally Posted by spirit receiver

No, they don't fit. There are 8 field names and 9 values. And the error message probably stems from a missing semicolon before "DESCRIBE jos_users;". In principle, there's no restriction on the data you may enter, it'll always create a new row in your table. But if you want to use this for registration data for your website, you should probably make sure that user names are unique.

You are such a SQL expert!
My site is nearly ready,the only problem is users cannot register.
After the

Code:

mysql> ALTER TABLE jos_users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;

command,how would you arrange names and their values for new users ?How can i guess values like encripted password,like gid,usertype,block (???)What are they?A good link?,please help ,let's go over this post otherways i don't know when it will ends if ......

Code:

mysql> INSERT INTO jos_users('name','username','email','password','usertype','block','gid','registerDate')VALUES('ciccio','ciccio','ciccio@yahoo.it','27b4b5b01b0d1fcab2046369720ff75e','1','18','51805edae6e5131c6f6d7941e9add048','2006-07-1804:55:00'
    -> describe jos_users; ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''name','username','email','password','usertype','block','gid','

spirit receiver · 07-18-2006, 03:01 PM

Quote:

Originally Posted by gabsik

You are such a SQL expert!

I'm certainly not. But your last SQL statement is obviously incomplete again, it's missing a closing bracket and a semicolon at least, and the 'registerDate' doesn't look like a valid 'datetime' to me. I advise you to consult the mysql reference manual.

gabsik · 07-18-2006, 06:34 PM

Well it does look to me you know lots of SQL! Now

Quote:

I'm certainly not. But your last SQL statement is obviously incomplete again, it's missing a closing bracket and a semicolon at least, and the 'registerDate' doesn't look like a valid 'datetime' to me. I advise you to consult the mysql reference manual.

apart bracket and semicolon and datetimes i need to see the way to do this query could someone just quote this query ... Grrrr !!!!
For sure you can't say i'm not making any effort to resolve this problem !!!

chrism01 · 07-19-2006, 01:49 AM

As spirit_receiver was trying to point out :
1. each separate SQL cmd must end with a semi-colon ie ';'
2. as per prev page, your table does NOT have a column called 'activation', so you CANNOT try to put a value in it.
Maybe you meant 'user_active' ?
HTH

gabsik · 07-19-2006, 02:58 AM

Well ....

Is possible to see how you do it ????