doczaius

I have a query issue that I can't figure out without doing it manually with for statements and multiple queries in PHP. I have a "log" table that includes index (PK), userid (FK), pointValue 1, user1, 10 2, user2, 4 3, user1, 10 I have a "hierarchy" table that includes userid (PK), role, location, first, last user9, leader, home, john, doe user1, follower, home, john, hancock user2, follower, away, steve, balmer user4, leader, away, mark, cuban I need to create a descending list of "leaders" with their first and last name and a sum of pointValues that match their location. With the example above I would hope to end up with something like John Doe 20 Mark Cuban 4 With PHP I could run a query for all of the leaders and their locations, then cycle through the array using the location value to create multiple queries to retrieve the sum of point values. Is there any way to do this with just 1 query?

Thanks for everyones help before.  Heres something new thats racking my mind. I'm working on the script that runs and updates the 2nd table every few hours. I have already written code to check for new teams, but the following code that updates any new and existing records with the scores just is not cooperating: [code]$query = "UPDATE 2007_db SET $timestamp = '{$teamarray[$i][3]}' WHERE team_id = '{$teamarray[$i][0]}'";[/code] This is returning: [quote] You have an error in your SQL syntax near '1168922152 = '11629377' WHERE team_id = '0'' at line 1[/quote] $timestamp is referring to a previously added field to the table using ALTER. I've tried every conceivable form of single, double quoting and concatenation hoping that SQL is trying to interpret the data again like before but nothing works.  I'm sure its super simple but I will leave it to you geniuses to tell me that.

BINGO! 21618 loops and no errors.  The table is full of correct data. I did have to enclose the array variables in brackets though. First run I ended up with a bunch of duplicate entries of "Array". [code]$query = "INSERT INTO 2007_db VALUES ('{$teamarray[$i][0]}', '{$teamarray[$i][1]}', '{$teamarray[$i][2]}', '$i')";[/code] I definitely will have to look into using PEAR, especially since this is going to get sickly more complex once I start doing calculations.

Any would be an understatement lol. Getting stuff like: [quote]Unknown column 'teamxio' in 'field list'1500 You have an error in your SQL syntax near 'Source Clan,233703,1500)' at line 11501 You have an error in your SQL syntax near 'Reno,21503,6429)' at line 16430 You have an error in your SQL syntax near 'Gurus,21496,6430)' at line 16431 You have an error in your SQL syntax near 'Martini,21489,6431)' at line 16432[/quote] I think mySQL is trying to interpret the data in the teamarray[$i][1] variables.  I have the table setup as team_id      mediumint primary key team_name  varchar team_score  int index          mediumint

Ok, I'm working on populating the data for the main table right now.  All of the data is stored in a multidimensional array in php.  I am using a for loop to insert the data into the mySQL table but every time I execute the script only 215 records are populated.  Whats odd is that they aren't even the first 215 values. [code] // Begin MySQL transaction mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "I wasn't able to select the database $database. Double check that it exists!"); for($i = 0; $i < sizeof($teamarray); $i++) { $query = "INSERT INTO 2007_db VALUES (".$teamarray[$i][0].",".$teamarray[$i][1].",".$teamarray[$i][2].",".$i.")"; // Last column is to track which pass of the array is being entered into sql table echo("$i<BR>"); // Tracks the amount of cycles through loop mysql_query($query); } mysql_close(); // End MySQL transaction [/code] Here are the first ten records of the table after the script is run. [quote][table][tr][td]team_id[/td] [td]team_name[/td] [td]team_score[/td] [td]index[/td][/tr] [tr][td]10002[/td] [td]10002 [/td][td]4514710 [/td][td]168[/td][/tr] [tr][td]22000 [/td][td]junkteam14042 [/td][td]2034886 [/td][td]292[/td][/tr] [tr][td]32049 [/td][td]993911 [/td][td]550173 [/td][td]783[/td][/tr] [tr][td]44365 [/td][td]950560 [/td][td]347926 [/td][td]1095[/td][/tr] [tr][td]22204 [/td][td]junkteam14104 [/td][td]234345 [/td][td]1497[/td][/tr] [tr][td]45663 [/td][td][table][tr][td]6767777 [/td][/tr][/table][/td][td]127010 [/td][td]2369[/td][/tr] [tr][td]22010 [/td][td]junkteam14046 [/td][td]81118 [/td][td]3174[/td][/tr] [tr][td]41096 [/td][td]777777 [/td][td]76050 [/td][td]3304[/td][/tr][/table][/quote] Here is the output in firefox: [quote] 0 1 2 3 4 5 6 7 8 9 10...17573[/quote] Any ideas?

Maybe this will help.  This is a table that basically contains all of the data that is provided every three hours: [table] [tr][td]ID[/td][td]NAME[/td][td]SCORE[/td][td]NUM_OF_GAMES[/td][/tr] [tr][td]34213[/td][td]A Team[/td][td]99423[/td][td]99[/td][/tr] [tr][td]45343[/td][td]Team America[/td][td]87323[/td][td]94[/td][/tr] [tr][td]67434[/td][td]The New Guys[/td][td]5432[/td][td]11[/td][/tr] [/table] In the above, the ID and the NAME will never change, but there may be new additions.  NUM_OF_GAMES might update every three hours, whatever value is present can be overwritten, ie it does not need to be tracked.  I do need the SCORE however to be recorded for every update.  So every three hours I need the time/date recorded and the current SCORE for each ID.  Basically this information will be referenced for charts/graphs and predictions, ie total progress over past week, month, quarter etc. So I might run a query for all the scores for ID 34212, and 67434 over the past week so that I can calculate the average say 24 hour pace for each team, and make a prediction as to when team 67434 might, if ever, overtake team 34212.

I'm working on a project right now that will require a rather large database, but having trouble figuring out the best way to set it up.  I've used mySQL before but just to store simple web data. If this were an excel workbook I would set it up like this: Sheet1: #ID  NAME NUMERIC_DATA1 NUMERIC DATA2. 1    whatever  454264  33 2    somthing else  232455  67 etc. Sheet2: #ID  DATA_VALUE@TIMESTAMP  DATA_VALUE@TIMESTAMP 1  454264 476547 2  232455 300120  Sheet 1 would update periodically when there are new users.  Sheet 2 updates every three hours, every day of the year.  So in the example above I would append new columns to the end for every three hours.  Note that there are 40,000+ existing users so as I said before this is going to be fairly large. I suppose the question is how many tables should I use, and how exactly would I setup the fields, reference them in PHP etc. I know this is pretty vague but hopefully someone can make sense of it, if not just let me know what other info you need.