need little help on database sql

[Nicholas]

hi im trying to create a new mod but i require little help on the sql, what im trying to do is create new database table but with different effect...

what it does is say i create table saying:

catcher (eg. announcements)

and in that table there is sections saying:

userid

throws

catches

fails

NOTE: these are just an example, its nothing to do with the mod im gonna try make.

how can i set it up so there is the same amount of catagories as the users tables, so every user in the game has a slot

if you understand what i mean... i cant think of how to explain it :(

if you understand and can help me on the script or whatever is required to insert the information into the database section it be very greatful

NOTE: can you use the example table and catagories and ill change the information to create the table i need

i havnt though of the table names and etc yet why im using an example.

thanks Nicholas.

[rulerofzu]

EH?

PHPmyAdmin

create table specify the amount of fields you want and then fill out the details on the next screen.

If you change your mind on any fields simply alter them or delete them or add more fields as required.

Is that what you mean?

[Nicholas]

there was a script out there that selects how many users you got in the game and then inserts information into the new table automaticly

i dont want to do

INSERT INTO `blah` (`userid` ,`blah` ,`blah` ,`blah`) VALUES ('1', '0', '0', '0');

INSERT INTO `blah` (`userid` ,`blah` ,`blah` ,`blah`) VALUES ('2', '0', '0', '0');

INSERT INTO `blah` (`userid` ,`blah` ,`blah` ,`blah`) VALUES ('3', '0', '0', '0');

INSERT INTO `blah` (`userid` ,`blah` ,`blah` ,`blah`) VALUES ('4', '0', '0', '0');

over 1000 times. thats just long lol, there was a script that does it all for you in secounds

but i cant think of how to create that script.

[BoGFx]

are you talking about auto increment?

[Nicholas]

think so. can anyone help me create this modification script?

this probably wouldnt work (wrote it on here) but maybe you find out what i mean...

<?php

$collect = mysql_query("SELECT `userid` FROM `users`");

$userid = mysql_fetch_array($collect);

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('$userid', '0', '0', '0', '0');

?>

and say i have 40 users on my game, would it insert into table "ballplayer" like this?

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('1', '0', '0', '0', '0');

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('2', '0', '0', '0', '0');

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('3', '0', '0', '0', '0');

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('4', '0', '0', '0', '0');

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('5', '0', '0', '0', '0');

so on upto

INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('40', '0', '0', '0', '0');

then stop? and everyone in the game have their own personal section on that table.

as the mod im gonna try and create uses a lot of sections in the table and i dont want to put it all in users table.

NOTE: thats just an example table lol. could come up with a mod for this table information i just put here. lol.

[BoGFx]

V2

<?php
$q=$db->query("SELECT userid FROM users");
while($r=db->fetch_row($q))
{
INSERT INTO `ballplayer` (`userid` ,`balls` ,`throws` ,`hits`, `misses`) VALUES ('$r['userid']', '0', '0', '0', '0');
}
?>

 

Untested

[Nicholas]

got it, works fine now. had to edit that little script little tho lol.

apart from that thanks for helping me sort one out :)

im about 40% done on my 1 of a kind mod :P

[Nicholas]

words: 1,387

characters (without spaces): 13,450

characters (with spaces): 14,501

paragraphs: 463

lines: 538

completion of mod: about 35%

this is gonna be one big ass mod lol. never made massive mod before ^^

[a_bertrand]

You don't need that. What you need to do is when you do a select you do it with a left (or right) join where the user table is the main one.

Check that:

http://engine.nowhere-else.org/article_5.html

[Nicholas]

i dont know how to do left or right join seleting yet, never tried learning it lol.

but its all sorted now.

but thanks for the artical, i might try learning left or right joining query lol.

just ive never used it in any mods ive edited or created.

[rulerofzu]

Left and Right joins are simple. Quite often abused as normal queries are though.

SELECT * FROM

SELECT u.*,i.* FROM users u LEFT JOIN i inventory ON u.userid=i.inv_userid

There is a lot of the left join example above in mcc

Much better to be selective

SELECT u.userid,i.inv_userid