REPLACE .. INTO issue

[woolyg]

MySQL client version: 5.0.33

 

Statement:

REPLACE INTO teamdata_table1 (Number, TeamPlayerID, player_statsFirstname, player_statsLastname, player_statsAge, player_statsFitness, player_statsSpeed, player_statsStrength, player_statsHandling, player_statsAttacking, player_statsTackling, player_statsTactics, player_statsPassing, player_statsImage, player_statsTeamID) 
SELECT counttotwenty.number, player_statsID, player_statsFirstname, player_statsLastname, player_statsAge, player_statsFitness, player_statsSpeed, player_statsStrength, player_statsHandling, player_statsAttacking, player_statsTackling, player_statsTactics, player_statsPassing, player_statsImage, player_statsTeamID 
FROM player_stats, counttotwenty 
WHERE player_stats.player_statsTeamID='1';

 

Hi all,

What I wanna do with the above code is refresh a table (teamdata_table1) with information from tables player_stats and counttotwenty. Table counttotwenty is one column - the numbers 1 to 20. Table player_stats is the statistics of 20 players - 14 columns. Table teamdata_table1 is a mixture of both, with 15 columns - the column from counttotwenty marking the player number in column 1, and columns 2-14 are the player data.

 

When I run the query, it runs OK, and displays the info from table counttotwenty (ie, a column showing numbers 1 to 20) but the rest of the table only shows info from player with playerstatsID of 20.

 

All I would like to do is pack everything from the 2 tables into teamdata_table1, and have it refresh each time I run the script. Can anyone see what I'm doing incorrectly?

 

All help appreciated!

 

Cheers - woolyg

 

 

[bubblegum.anarchy]

Forget the REPLACE untill you have the SELECT returning the information required.

 

At a glance, I do not see any joining of the tables player_stats and counttotwenty.

[woolyg]

Thanks,

I've pared it down to the following code:

 

SELECT * FROM counttotwenty INNER JOIN player_stats 
WHERE player_stats.player_statsTeamID='1';

 

..and what I'm getting is this:

 

 

 

But what I'd really like is this (excuse the patched together image):

 

 

..showing player details in columns 2-15 for 20 rows, with the numbers 1-20 in column 1. So basically, I'd like not to have duplicated player entries.

All info appreciated again - thanks.

 

Woolyg

 

 

 

 

[bubblegum.anarchy]

sorry, I am lost now - do you want all the information from player_stats grouped by the player_statsID prepended with a oolumn counter that resets every 20 records?

[woolyg]

Sorry for the confusion.

 

I want to achieve a table of 20 players from a certain team to be populated, displayed by number.

 

If I select all players from a certain team, it displays their stats fine. When I try to incorporate numbering their entries from 1 to 20, thats where I'm hitting the wall..

[bubblegum.anarchy]

This is the best I could come up with:

 

SELECT ifnull(@n:=@n + 1, @n:=1), tablename.* FROM tablename LIMIT 20;