UPDATING New Column in Database with Info From another

[hyphymike]

Ok So Im having a problem.

Im trying to get a script to do this:

Take info from Database: Players

• playerName
• onlinetime

Match playerName with playerName
And put that info into Database: times
With the Colums:

• playerName
• 1-24
• time1-24
• 1-23
• time1-23
• etc etc for all dates.

I just want it to add the snapshot from DB: Players and put it as the row titled by the date.

This is the code I Was using, but It created seperate rows for the player each day instead of Inserting it into the existing row matching the playerName in players

$mytime = date('m-d');
$newcolumn = $mytime;
$query1 = "ALTER TABLE times ADD `$newcolumn` DOUBLE NULL DEFAULT NULL , ADD `time$newcolumn` varchar(45) NULL DEFAULT NULL";
$query2 = "SELECT `playerName`,`onlinetime` FROM `players` WHERE `playerName` != ''";
$query3 = "INSERT INTO times (`playerName`,`$newcolumn`) $query2";
$query4 = "UPDATE times SET `time$newcolumn`='$mytime', $newcolumn=''$query5''";

I need them to UPDATE instead of INSERT but I seem to have gone off course. They wont match up to the playerNames in the new database.

Any help would be greatly appriciated.

[hyphymike]

Whoops this is actually what it is for $query4

$query4 = "UPDATE times SET `time$newcolumn`='$mytime' WHERE `playerName` != ''";

Sorry for double post didnt see the edit until just now.

This is what I have now but it doesnt work and its not correct but its on the right track. You can see where Im tryin to go with it
I want to merge $query4 and $query3 from the first post.

$query1 = "ALTER TABLE times ADD `$newcolumn` DOUBLE NULL DEFAULT NULL , ADD `time$newcolumn` varchar(45) NULL DEFAULT NULL";
$query5 = "SELECT `onlinetime` FROM `players` WHERE `playerName` != ''";
$query4 = "UPDATE times SET `time$newcolumn`='$mytime', $newcolumn=''$query5''";

[diafol]

These queries don't seem to make much sense as you've not run any queries. All you have is strings - OK strings that translate to SQL queries, but they're still just strings. Run them. The use of query vars inside some sql statements don't make much sense.

[hyphymike]

They are querys. I run them a little below this code with

mysql_query($query1) or Die
etc

[diafol]

I think the structure for your second table is all wrong. You don't need to create a column for each date. I'd do this:

player_id (int - foreign key on the player_id in the user table)
dated (date format, e.g. yyyy-mm-dd)
timed (time format mm:ss or even mmss - whichever allows you to sort the easiest)

[hyphymike]

im trying to plot points on a graph with this, I dont understand how that would give me a snapshot of each date. I need their hours as it is every day to save in its dated table. Im fairly new to PHP so pardon my noobness. Thanks for helping.

For example this is how I want my database set up so that I can pull the number of minutes from the column "01-25" where the playername is the same. And then I can put that point on a graph

Heres a picture of what I'm trying to do.
http://prntscr.com/5ln16

[diafol]

I see that, but it's just a data table with two variables. You filter the SQL for aperson, e.g.

SELECT dated,timed FROM table WHERE user_id = 2 ORDER BY dated

(e.g. user_id = 2 references Mike)

You are then presented with a resultset resource which contains multiple rows, each with two values.

[hyphymike]

hm ok thanks.

[hyphymike]

The selecting it is not really a problem Im more trying to populate this table. Idk if im supposed to create something for it to read off or what. I dont know how to get the user_id and playerName to align with each other and then find itself to put the next days point in.

http://prntscr.com/5m698

How should I create this table for the second one.

All I have right now in it is a user_id column that is AUTO_INCREMENT and PRIMARY and a varchar(40) for the username.

And neither of these are working.
Im trying.

$mytime = date('Y-m-d');
$newcolumn = $mytime;
$query2 = "SELECT `onlinetime` FROM `players` ORDER BY `user_id`";
$query3 = "SELECT `playerName` FROM `players`  WHERE `playerName` != ''ORDER BY `playerName`";
$query4 = "UPDATE times SET $newcolumn=''$query2''";


mysql_query($query2) or die('Query2 failed: '.mysql_error());
mysql_query($query3) or die('Query3 failed: '.mysql_error());
mysql_query($query4) or die('Query4 failed: '.mysql_error());
}

Then I tried working with this a little further down and I got this error

$result1a = mysql_query("SELECT `onlinetime` FROM `players` ORDER BY `user_id`");
$result1b = mysql_query("SELECT `playerName` FROM `players` ORDER BY `user_id`");
if (mysql_num_rows($result1a)>= 1) {
  while($row = mysql_fetch_array( $result1a )) {
mysql_query("UPDATE times2 SET $newcolumn=''$result1a'', `playerName`=''$result1b''") or die('Update failed: '.mysql_error());
}
}

Error: Update failed: 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 '2012-01-26=''Resource id #3'', `playerName`=''Resource id #4''' at line 1

[diafol]

I'm a bit confused from the screenshot - why do you need to transfer identical data from table1 to table2? THis is my take on things:

users
=====
user_id
username
password
...

times
=====
time_id
user_id
mins

You can get everybody output like this:

SELECT u.username, t.mins FROM users AS u INNER JOIN times AS t ON u.user_id = t.user_id

Just modify it with a WHERE clause to filter the data to one user, e.g.

SELECT u.username, t.mins FROM users AS u INNER JOIN times AS t ON u.user_id = t.user_id WHERE u.user_id = 2

OR

SELECT u.username, t.mins FROM users AS u INNER JOIN times AS t ON u.user_id = t.user_id WHERE u.username = 'Mike'