Insert with select (I think...)

[Dieneces]

Hi guys,
Let me try to explain my little doubt.
I have 2 tables (player and class). The class table have the information of various classes (classID, name, INTC, DEFC and DEXC). The classID is used for the relation with the player table and the 3 last are the attributes specifics for each class.
Now... When I insert a new player, the player can choose the class (from a combobox), and the he can choose the strengh, hitpoints and magicpoints, but the other 3 attributes (INT,DEF and DEX) would be for the corresponding class he choosed.
I already have the registration working fine, but... without this 3 values (INTC, DEFC and DEXC). I now that I have to use some kind of insert with a select statement so I can retrieve the values from the class table...

This are the tables:

CREATE TABLE IF NOT EXISTS `classes` (
`ClasseID` int(1) NOT NULL auto_increment,
`Class` text NOT NULL,
`INTEC` int(3) NOT NULL default '0',
`DEFC` int(3) NOT NULL default '0',
`DEXC` int(3) NOT NULL default '0',
PRIMARY KEY (`ClasseID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `player` (
`Nome` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`user_password` varchar(40) NOT NULL,
`Classe` text NOT NULL,
`HP` int(3) default NULL,
`MP` int(3) default NULL,
`STRH` int(3) default NULL,
`INTE` int(3) default NULL,
`DEF` int(3) default NULL,
`DEX` int(3) default NULL,
`EXP` int(5) default '0',
`registration_date` datetime NOT NULL,
PRIMARY KEY (`Nome`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The insert that I have now is something like this:
$q = "INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', NOW())";

I tried something like:
INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH,INTE, DEF, DEX, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', (SELECT INTEC, DEFC, DEXC FROM classes WHERE player.Classe = '$cls'), IDNOW())";

Where the $cls is the class that was chosen by the user. Now, I know that is incorrect because it keeps me telling that:
“Column count doesn't match value count at row 1”
I’m searching for some more insight about insert statements with selects. But if someone could point me in the right direction...
Many many thanks in advance,

[Dieneces]

ok, the problem is in the multiple values of the select...

If I run this query it will work:

$q = "INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH,INTE, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', (SELECT INTEC FROM classes WHERE classeID='$cls'), NOW())";


But this one not...:

$q = "INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH,INTE, DEF, DEX, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', (SELECT INTEC, DEFC, DEXC FROM classes WHERE classeID='$cls'), NOW())";


How can I pass more then one value from the select? Anyone know? Is this possible?

[chazzy]

move everything into the select statement...

ie..


INSERT INTO whatever_table
SELECT '$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', INTEC, DEFC, DEXC , NOW()
FROM classes WHERE classeID='$cls'

[Dieneces]

well I found a solution... (even tough its not the more correct one....)

Just use 3 selects! One for each row I want. Simple :P

ocuh, after I update the topic I saw your post chazzy.

Many thanks, that one is a better solution I think! I will try tomorrow.

[Dieneces]

Well it didn't work... same error...

but this works:

$q = "INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH,INTE, DEF, DEX, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', (SELECT INTEC FROM classes WHERE ClasseID='$cls'), (SELECT DEFC FROM classes WHERE ClasseID='$cls'), (SELECT DEXC FROM classes WHERE ClasseID='$cls'), NOW())";

[chazzy]

Well it didn't work... same error...

but this works:

$q = "INSERT INTO player (Nome, user_password, email, Classe, HP, MP, STRH,INTE, DEF, DEX, registration_date) values ('$un', SHA1('$p'), '$e', '$cls', '$hp', '$mp', '$f', (SELECT INTEC FROM classes WHERE ClasseID='$cls'), (SELECT DEFC FROM classes WHERE ClasseID='$cls'), (SELECT DEXC FROM classes WHERE ClasseID='$cls'), NOW())";

That looks nothing like the example I gave you.

[Dieneces]

Yes I know... and I know that is not the correct way of doing it (3 call to the database)... but Im still trying to make your example work #)

[chazzy]

well mine was only an example. you should probably still add the columns your insert. if it gives you an error, you should probably post the error.