How do I set up this database and php correctly to minimize maintenance

I have two tables, here they are:

--
-- Table structure for table `Roster`
--

CREATE TABLE IF NOT EXISTS `Roster` (
`Car_Number` varchar(6) NOT NULL default '',
`Driver_Name` varchar(30) NOT NULL default '',
`Home_Town` varchar(30) default NULL,
`Cartoon_Character` varchar(30) default NULL,
`Highlights` varchar(50) default NULL,
`Email` varchar(50) default NULL,
`Phone` varchar(15) default NULL,
PRIMARY KEY (`Car_Number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Roster`
--

INSERT INTO `Roster` (`Car_Number`, `Driver_Name`, `Home_Town`, `Cartoon_Character`, `Highlights`, `Email`, `Phone`) VALUES
('00 R', 'John Schneider', 'Hudson', 'Oscar The Grouch', '', '', ''),
('4 R', 'Fritz Schockey', 'Loveland', 'Spiderman', '', '', ''),
('6 R', 'Barb Stechman', 'Loveland', 'TBD', '', '', ''),
('7 R', 'Larry Stone', 'Johnstown', 'Yosemite Sam', '2009 Rookie of the Year', '', ''),
('8 R', 'Joe Davis', 'Berthoud', 'Snoopy', '2009 2nd Place Points', '', ''),
('10 R', 'Frank Beck', 'Arvada', 'Chicken Little', '', '', ''),
('11 R', 'Charlie Drager', 'Loveland', 'The Grinch', '', '', ''),
('15 R', 'Bill McClintock', 'Colorado Springs', 'The Evil Monkey', '', '', ''),
('16 R', 'Jerry Hunter', 'Greeley', 'Pepe Le Pew', '', '', ''),
('18 R', 'Bryan McCauley', 'Sterling', 'Captain Underpants', '', '', ''),
('21 R', 'Ralph McCauley', 'Sterling', 'Sponge Bob Square Pants', '', '', ''),
('28 R', 'Matt Burkey', 'Sterling', 'Doc Hudson', '', '', ''),
('36 R', 'Roger Hornby', 'Westminster', 'Miss Behaving', '', '', ''),
('41 R', 'Stan Grippin', 'Brush', 'Fred Flintstone', '2009 Most Improved Driver', '', ''),
('44 R', 'Stu Carlson', 'Loveland', 'Tweety Bird', '2009 Point Champion', '', ''),
('46 R', 'Alex Amen', 'Fort Morgan', 'TBD', '', '', ''),
('55 R', 'Andy Rogers', 'Akron', 'Willey Coyote', '', '', ''),
('60 R', 'Danny Rogers', 'Akron', 'Marvin Martian', '', '', ''),
('68 R', 'Walker Hornby', 'Greeley', 'TBA', '', '', ''),
('77 R', 'Don Morrison', 'Firestone', 'Mr. Horse Power', '2009 3rd Place Points', '', '';

and


-- Table structure for table `Schedule`
--

CREATE TABLE IF NOT EXISTS `Schedule` (
`PointsAwarded` char(3) NOT NULL default '',
`Date` date NOT NULL default '0000-00-00',
`Place` varchar(100) NOT NULL default '',
`StartTime` varchar(10) default NULL,
`Notes` varchar(30) default NULL,
`Event` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Schedule`
--

INSERT INTO `Schedule` (`PointsAwarded`, `Date`, `Place`, `StartTime`, `Notes`, `Event`) VALUES
('*', '2010-10-09', 'I-76 Speedway / Ft. Morgan, CO', '5:00 PM', '10-09 through 10-10', 'FALL CLASSIC'),
('*', '2010-10-02', 'US 30 Speedway / Columbus, NE', 'TBD', '10-02 through 10-03', 'GOTRA CLASSIC'),
('', '2010-09-18', 'Big Country Speedway / Cheyenne, WY', '6:00 PM', '', ''),
('', '2010-09-11', 'Big Country Speedway / Cheyenne, WY', '6:00 PM', '', ''),
('', '2010-09-04', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-08-21', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('*', '2010-08-21', 'I-25 Speedway / Pueblo, CO', '6:00 PM', '', ''),
('', '2010-08-15', 'Colorado Motorsports Park / Byers, CO', '5:00 PM', '', ''),
('', '2010-08-14', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-07-31', 'I-25 Speedway / Pueblo, CO', '6:00 PM', '', ''),
('', '2010-08-07', 'Big Country Speedway / Cheyenne, WY', '6:00 PM', '', ''),
('', '2010-07-24', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-07-17', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-07-18', 'Colorado Motorsports Park / Byers, CO', '5:00 PM', '', ''),
('', '2010-07-10', 'I-25 Speedway / Pueblo, CO', '6:00 PM', '', ''),
('', '2010-06-26', 'I-25 Speedway / Pueblo, CO', '6:00 PM', '', ''),
('', '2010-07-03', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-06-20', 'Colorado Motorsports Park / CO', '2:00 PM', '', ''),
('', '2010-06-05', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('*', '2010-06-10', 'SLV / Alamosa, CO', 'TBD', '06-10 through 06-12', ''),
('', '2010-05-22', 'Big Country Speedway / Cheyenne, WY', '6:00 PM', '', ''),
('', '2010-05-08', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-05-15', 'I-76 Speedway / Ft Morgan, CO', '6:00 PM', '', ''),
('', '2010-05-01', 'Big Country Speedway / Cheyenne, WY', '6:00 PM', '', ''),
('*', '2010-04-24', 'El Paso County / Calhan, CO', '2:00 PM', '', ''),
('*', '2010-04-18', 'Hot Laps / I-76 Speedway / Ft Morgan, CO', '12:00 PM', '', ''),
('*', '2010-04-24', 'Hot Laps / Big Country Speedway / Cheyenne, WY', '12:00 PM', '', ''),
('*', '2010-04-11', 'Hot Laps / I-76 Speedway / Ft Morgan, CO', '12:00 PM', '', ''),
('', '2010-04-11', 'Colorado Motorsports Park / Byers, CO', '2:00 PM', '', '');


My PHP code for the page looks like this:

<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td><p>&nbsp;</p></td>
</tr>
<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";


$sqlstr2 = mysql_query("SELECT * FROM Roster");
while($row=mysql_fetch_array($sqlstr2)){

echo "</td><td width=50>";
echo $row['Car_Number'];
echo "</td></tr>";
}

echo "</table>";

?>

I'm trying to display a table that has the dates of the races across the top and car numbers on the left side so that I can somehow display the points earned for the car on a specific day. like this: http://www.rmdcra.org/Results.php 

I am pretty sure I need one more table with points. Points are assigne d to car number so I figured I would have a car number field and fields  for each day of the race. Can I somehow create the fields based on a query? I would like for that table to be updated automatically

I have a lot of questions and this is pretty much my first shot and building this kind of site, I would greatly appreciate any suggestions, I have been working on this thing for a few days and haven't gaining any headway.
patrickm12981Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1209ylCommented:
You could put an auto-increment feature on the table with SQL or phpMyAdmin. That way, every entry would add a number to it. YOu could implement it by having a <option type="hidden" name="id"> .
0
patrickm12981Author Commented:
On which table? both? all?
0
Steve BinkCommented:
Are all cars supposed to participate in every race?  

The way I see it, you have cars, you have races, then you have cars performing in races.  That means three tables: roster, races, results.  Roster can be exactly what you have, though I would recommend using a numeric, auto-numbering primary key.  The races table would correspond to everything in your Schedule table, minus the points awarded.  Again, I recommend using an auto-numbering primary key.  The final table, results, would have (minimum) three fields):

Car_Number (FK to roster.PKField)
Race_Number (FK to races.PKField)
Points

Place a unique key on car#-race#.

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

patrickm12981Author Commented:
Thank you very much routinet. All cars are assigned points for every points race, if they dont show then it is 0 points. Is there a way I can build the races table in an automatically? So that when I enter a new event into schedule it will automatically delete/add from races if it is a points race? Same situation with adding or deleting car numbers.
0
Steve BinkCommented:
What do you mean "build the races table"?  What is it *exactly* you want to have done automatically?  Providing an example would probably illustrate it best.
0
Ray PaseurCommented:
As usual, routinet is giving you good advice about the use of three tables.  I'd like to suggest that you get a little background in PHP and MySQL so you can understand the concepts of how tables relate to one another.  This book should help - very readable and a permanent part of my professional library since version one.

http://www.sitepoint.com/books/phpmysql4/

Best of luck with the project, ~Ray
0
patrickm12981Author Commented:
Thanks for the suggection Ray.

Routinet,

I have made Car_Number my primary key for table "Roster".
I have made an auto incremented field in "Schedule"
I have made a table called "Races" using Car_Number, Race_Number, and Points.
I placed a Unique key on Car_Number and Race_Number  in "Races" table.

Now I need to populate the table "Races", correct? What is the best way to do that? That is what is what I was referring to by automatic.


0
Steve BinkCommented:
Sounds good so far, though I still recommend a *numeric* primary key on the roster table.  While it is possible to use a varchar field as a primary key, indexing character fields is a much slower process than numeric indexing, and can lead to comparison issues (e.g., case sensitivity, character set, etc.).

To populate the races table, you'll need to make a small application that lets you enter data which is then inserted into the table.  Really, this is not hard to do, though you could say it is hard to do *right*.  At its core, you have a web form that takes the data and sends it to a form  handler that inserts it into the table.  If you have a known list of races or it will not need to change once created, you could just create the INSERT statement yourself with that data, or export it from, for example, an Excel spreadsheet into a format readable by MySQL.  A comma-delimited format should work just fine for those purposes.

Per Ray's suggestion, you would do yourself a favor to become familiar with the inner workings of MySQL.  It is hard to work with tools when you don't understand what they do or how they work.  The first link below is the home page for MySQL's manual.  This is for v5.1; be sure to look at the manual for the version you are using.  The other links are to specific language items you'll need for populating the tables.  Take some time to read through the sections.  It will make your job much easier.

http://dev.mysql.com/doc/refman/5.1/en/index.html
http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-manipulation.html
http://dev.mysql.com/doc/refman/5.1/en/insert.html
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
0
patrickm12981Author Commented:
Ok, I figured I would be making an admin page in PHP on the site to add/delete/update Roster and schedule. When I do the submit action I will just have to insert into both tables or maybe a CASCADE action. The way it is looking though I may have to create a new table for each race.

0
Steve BinkCommented:
Why would need a different table for each race?  That defeats the purpose of it being a relational database.  You put the races into the races table.  To populate the results table, you insert one record into results for each car in the roster table.  The race number will be the same for each record.  You can either insert the records with the points, if you're doing this after the race, or insert just the car records and use another admin page to assign the points later.
0
Ray PaseurCommented:
Here's my two cents on this (I have a very similar thing in the fishing contest results at LAPR Bass).

One principle you want to adopt is that you never use a meaningful value for a key.  In other words, your keys are generated internally, perhaps as auto-increment numbers.  They are used to control the relationships but are not needed as a part of the visible information payload.

You have Roster and Schedule.  As Results come in, you use the numeric keys from Roster and Schedule to associate the car's Results with the outcome of the race.  So the Results table might look something like this:

key
roster_key => points to the information about the car
schedule_key => points to the date and racecourse
points => tells how many points this car got in this race.

Then you can easily find out how many points any member of the Roster has at any given time.  You can use MySQL SUM() and ORDER BY functions to show which members of the Roster are on top of the league.  You can use COUNT() to find how many cars ran in a given race.  Etc.

HTH, (and buy that book), ~Ray
0
patrickm12981Author Commented:
I believe i am following. The races table you are looking populating as time goes on. in order for me to show a zero value for races that have not happened yet I will need to prepopulate the races table with the race number and the car number then set the default value for points to zero.

Also you suggected that I put a unique key on race number and car number, that doesnt work for me.
This is why:

Race_Number                Car_Number               Points
1                                          23R                                12
1                                          45R                                 15
2                                          23R                                 35
2                                          45R                                 22
There will be duplicate values in Race_Number and Car_Number. But then I should be able to do a select points from Races where race number equals 1 to give me all the points for race 1 or I can do select points from races where Car_Number = 23R to give me all of the points assigned to that car, then sum them to get my total.
I am thinking on a form do drop downs so then I can query for the race dates, then car numbers which should make it easy to input the points and just do an update statement to change the points from 0 to whatever they were awarded.
Does that sound right? how can I keep Race_Number and Car_Number in sync with their corresponding tables?


0
Steve BinkCommented:
You are not following the concept.  For example:

races (id, date)
1   Monday
2   Tuesday

cars (id, number)
1   23R
2   45R


results (raceid, carid, points)
1    1   12
1    2   15
2    1   35
2    2   22

The above data sets exactly duplicate what you explained.  To get a list of all results for race 1, you select...where raceid=1.  To get all points for car 23R, you select...where carid=1.  Need the name in the results?

SELECT * FROM results a INNER JOIN cars b ON a.carid=b.id  (this will return id, number, raceid, carid, points.  Note that id=carid)

When you are creating the forms, for example, to select the car, your code should query the cars table for id and number:
<?
echo "<select name=\"car_number\">";
$query="SELECT * FROM cars ORDER BY number";
if (!($result=mysql_query($query))) {
  // query failed.  report and ...
  die();
}
while ($row=mysql_fetch_assoc($result)) {
  echo "<option value=\"{$row['id']}\">{$row['number']}</option>";
}
echo "</select>";
?>

Open in new window

0
patrickm12981Author Commented:
I attached screen shots of the 3 tables I have and the data that is currently in them.Is everything correct? If so, How do I i dsplay the data in the table that I linked to?
I entered the data into the results table from the first race

Results.JPG
Roster.JPG
Schedule.JPG
Results-Data.JPG
Roster-Data.JPG
Schedule-Data.JPG
0
Steve BinkCommented:
So far that looks good.  Here are two sample queries:

1) To show the stats/info for all races for a single driver.  Since you only have one race entered, this should return only a single row:

SELECT * FROM Results a INNER JOIN Schedule b ON a.Race_Number=b.Race_Number WHERE a.Roster_Number=<driver's roster number>

2) To show the stats/info for all drivers for a single race.  Since you only have the one race, I used that actual value here.

SELECT * FROM Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number WHERE a.Race_Number=29

My last comment demonstrates how you would create a drop-down, so that a user can choose, for example, which driver or race to focus on.
0
patrickm12981Author Commented:
Sweet almost there. I got the drop downs done for the admin page so that i can input the race results, thank you for all your help and the query above works.

I am just finishing up the table now. How do I integrate the query to fill the values? The code shows my current page. If the car number does not have any value for racing that day then it should display as zero, so all cars should be showing a value for that day, whether it be 0 or the actual points they earned.

http://rmdcra.org/Results.php
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td><p>&nbsp;</p></td>
</tr>
<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";


$sqlstr2 = mysql_query("SELECT * FROM Roster");
while($row=mysql_fetch_array($sqlstr2)){

echo "</td><td width=50>";
echo $row['Car_Number'];
echo "</td></tr>";
}




echo "</table>";

?>

Open in new window

ResultsPage.JPG
0
patrickm12981Author Commented:
This is the query I have so far:
SELECT Date, Car_Number, Points FROM Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number INNER JOIN Schedule d ON a.Race_Number=d.Race_Number WHERE exists (Select Race_Number from Schedule where PointsAwarded not like "*")

Open in new window

Query1.JPG
0
Steve BinkCommented:
Looks good so far.  To draw the table, you'll need that dataset, plus another dataset of column headers.  When you read in your example set, iterate through it and organize it into an array.  Use the date dataset to print each row in a uniform manner.  See the code below for an example.

<?
$myarray=array();
$datearray=array();
while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];
echo "<table>";
foreach ($myarray as $key=>$val) {
  echo "<tr><td>$key</td>";
  foreach ($datearray as $key2=>$val2) {
    echo "<td>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

Open in new window

0
patrickm12981Author Commented:
Awsome! Thanks for the example, that helped. It's working for the most part. Now I just need the total column and through in a twist I need to order by the total descending. Here is the code for the page that I came up with:
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";
?>
<?php
$points_result = mysql_query("SELECT Date, Car_Number, Points FROM Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number INNER JOIN Schedule d ON a.Race_Number=d.Race_Number WHERE exists (Select Race_Number from Schedule where PointsAwarded not like '*')");
$date_result = mysql_query ("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
$myarray=array();
$datearray=array();
while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];}
foreach ($myarray as $key=>$val) {
  echo "<tr><td width=50>$key</td>";
  foreach ($datearray as $key2=>$val2) {
    echo "<td width=50>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

Open in new window

0
Steve BinkCommented:
For the sorting, you can control the order of your data by changing your query.  First, design a query that gives you a total of points per car:

SELECT Car_Number, SUM(Points) as PointTotal FROM Results GROUP BY Car_Number

Now join that query to your points query by using the Car_Number field, and add an ORDER BY clause to the end of it.  When you're iterating through the points array, make sure you save the total points in a variable somewhere (either in the same array, or another array for this job).  Finally, print out the total after you have printed the rest of the row, between lines 34 and 35.
0
patrickm12981Author Commented:
Here is the query to get me the total points but I am not sure how to join it to the points query.

SELECT Car_Number, SUM(Points) as PointTotal FROM Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number GROUP BY Car_Number order by PointTotal desc

0
Steve BinkCommented:
You don't need the join in that query.  You need to join that query to your first query.

Something like this:

SELECT Date, Car_Number, Points, e.PointTotal
FROM
    Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number
                    INNER JOIN Schedule c ON a.Race_Number=c.Race_Number
                    INNER JOIN (SELECT Car_Number, SUM(Points) as PointTotal FROM Results d GROUP BY Car_Number) e
                            ON e.Car_Number=b.Car_Number
ORDER BY PointTotal DESC
0
patrickm12981Author Commented:
I tried something similar but when I run the query I get an error:

#1054 - Unknown column 'Car_Number' in 'field list'
0
Steve BinkCommented:
Ah, my bad...you do need that join.  Replace the subquery with this:

SELECT b.Car_Number, SUM(a.Points) as PointTotal FROM Results a INNER JOIN Roster b ON a.Roster_Number=b.Roster_Number GROUP BY Car_Number
0
patrickm12981Author Commented:
SELECT Date, Car_Number, Points, e.PointTotal
FROM Results a
INNER JOIN Roster b ON a.Roster_Number = b.Roster_Number
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (


SELECT b.Car_Number, SUM( a.Points ) AS PointTotal
FROM Results a
INNER JOIN Roster b ON a.Roster_Number = b.Roster_Number
GROUP BY b.Car_Number
)
LIMIT 0 , 30

MySQL said:  

#1248 - Every derived table must have its own alias
0
Steve BinkCommented:
Yes, and that derived alias should be 'e'.  I also changed the aliases for the tables inside the subquery, just for the sake of clarity.

SELECT Date, Car_Number, Points, e.PointTotal
FROM Results a
INNER JOIN Roster b ON a.Roster_Number = b.Roster_Number
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Car_Number
) e
LIMIT 0 , 30
0
patrickm12981Author Commented:
I get:

MySQL said:  

#1052 - Column 'Car_Number' in field list is ambiguous



0
Steve BinkCommented:
Add the respective aliases to your selected fields as well, e.g., c.Date, etc.
0
patrickm12981Author Commented:
The PointTotal was repetative and not correct.

Query:

SELECT c.Date, b.Car_Number, a.Points, e.PointTotal
FROM Results a
INNER JOIN Roster b ON a.Roster_Number = b.Roster_Number
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Car_Number
) e


Results:
 
Query2.JPG
0
Steve BinkCommented:
The point total will be repetitive because you're joining the total based on car number.  How was it incorrect?  Run the subquery by itself and you should see the same results.

You can make it not repetitive by putting it in a query by itself, and generating another array to organize it as you did the other two.
0
patrickm12981Author Commented:
From running:

SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Car_Number

The totals are not correct the totals should be:

Query3.JPG
0
Steve BinkCommented:
Can you show the table's raw data?
0
patrickm12981Author Commented:
Sure, it looks like it is picking off the first value and repeating it, rather than moving through all of the results. here is the results table:
Results.JPG
0
Steve BinkCommented:
I'm not sure I follow you.  Post the results of the same query without the group.  This will let us manually match your grouped results with the raw data.  From your previous example, it looks like it is working as expected.

SELECT f.Roster_Number, g.Car_Number, f.Race_Number, f.Points
FROM Results f INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
0
patrickm12981Author Commented:
This is:

SELECT f.Roster_Number, g.Car_Number, f.Race_Number, f.Points
FROM Results f INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number

Query4.JPG
0
Steve BinkCommented:
The results look correct.  For example, car "10 R" has 38 and 98 points.  Added together, that equals 136, which is what the SUM() query showed.  Car "11 R" has 125, or 26+95.  How do you conclude the information is wrong?
0
patrickm12981Author Commented:
Car number 10 has 136  Total but as you can see from query3.jpg, above no other car has that total. Query3 returns the correct values but Query2 does not, it shows 11 as having 136 as well. query2 also shows car 10 as having 121 total points.
0
Steve BinkCommented:
LOL!  Sometimes I confuse myself.  Try the query below.  Note that I have replaced the "Roster b" table with the subquery.  You will still see PointTotal repeated on each record for an individual car, though.

SELECT c.Date, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
    SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
    FROM Results f
    INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
    GROUP BY g.Car_Number
    ) b ON a.Car_Number=b.Car_Number
0
patrickm12981Author Commented:
SQL query:  

SELECT c.Date, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (


SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Car_Number)b ON a.Car_Number = b.Car_Number
LIMIT 0 , 30

MySQL said:  

#1054 - Unknown column 'a.Car_Number' in 'on clause'
0
Steve BinkCommented:
Change the join to use Roster_Number, and add g.Roster_Number to the subquery's select fields.  You can remove Car_Number altogether if you don't need it.

SELECT c.Date, b.Roster_Number, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
    SELECT g.Roster_Number, g.Car_Number, SUM( f.Points ) AS PointTotal
    FROM Results f
    INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
    GROUP BY g.Roster_Number,g.Car_Number)b ON a.Roster_Number = b.Roster_Number
LIMIT 0 , 30
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patrickm12981Author Commented:
It Works! Awesome! Thanks again, the last step would be to integrate it into my php. I have tried a couple of things and they havn't worked, here is the code:
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";
?>
<?php
$points_result = mysql_query("SELECT c.Date, b.Roster_Number, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
    SELECT g.Roster_Number, g.Car_Number, SUM( f.Points ) AS PointTotal
    FROM Results f
    INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
    GROUP BY g.Roster_Number,g.Car_Number)b ON a.Roster_Number = b.Roster_Number order by b.PointTotal desc");
	
$date_result = mysql_query ("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
$myarray=array();
$datearray=array();
while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];}
foreach ($myarray as $key=>$val) {
  echo "<tr><td width=50>$key</td>";
  foreach ($datearray as $key2=>$val2) {
    echo "<td width=50>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

Open in new window

0
patrickm12981Author Commented:
I was looking at the code and thought below may work but it doesn't. What am I missing?
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";
?>
<?php
$points_result = mysql_query("SELECT c.Date, b.Roster_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
SELECT g.Roster_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Roster_Number
)b ON a.Roster_Number = b.Roster_Number order by b.PointTotal desc");
	
$date_result = mysql_query ("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
$myarray=array();
$datearray=array();
while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'][$row['PointTotal']];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];}
foreach ($myarray as $key=>$val) {
  echo "<tr><td width=50>$key</td>";
  foreach ($datearray as $key2=>$val2) {
    echo "<td width=50>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

Open in new window

0
patrickm12981Author Commented:
I found one mistake that i made when I was pmessing around, but it still doesnt work:
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";
?>
<?php
$points_result = mysql_query("SELECT c.Date, b.Roster_Number, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
    SELECT g.Roster_Number, g.Car_Number, SUM( f.Points ) AS PointTotal
    FROM Results f
    INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
    GROUP BY g.Roster_Number,g.Car_Number)b ON a.Roster_Number = b.Roster_Number order by b.PointTotal desc");
	
$date_result = mysql_query ("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
$myarray=array();
$datearray=array();
while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'][$row['PointTotal']];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];}
foreach ($myarray as $key=>$val) {
  echo "<tr><td width=50>$key</td>";
  foreach ($datearray as $key2=>$val2) {
    echo "<td width=50>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

Open in new window

0
patrickm12981Author Commented:
Got it.............this is the final result:
<?php include("includes/Header.html"); ?>
<?php include("includes/conn.php"); ?>


<table border=1 align="center" width=1200>

<tr>
<td width=50><p>Car #</p></td>
<?php
$sqlstr = mysql_query("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");
while($row=mysql_fetch_array($sqlstr)){

echo "</td><td width=50 align=center>";
echo date('m/d', strtotime($row['Date']));
echo "</td>";
}
echo "<td width=50 align=center>Total</td>";
echo "</tr>";
?>
<?php
$points_result = mysql_query("SELECT c.Date, b.Roster_Number, b.Car_Number, a.Points, b.PointTotal
FROM Results a
INNER JOIN Schedule c ON a.Race_Number = c.Race_Number
INNER JOIN (
    SELECT g.Roster_Number, g.Car_Number, SUM( f.Points ) AS PointTotal
    FROM Results f
    INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
    GROUP BY g.Roster_Number,g.Car_Number)b ON a.Roster_Number = b.Roster_Number order by b.PointTotal desc");
	
$date_result = mysql_query ("SELECT Date FROM Schedule WHERE `Schedule`.`PointsAwarded` = ' ' ORDER BY `Schedule`.`Date` ASC");

$point_total = mysql_query ("SELECT g.Car_Number, SUM( f.Points ) AS PointTotal
FROM Results f
INNER JOIN Roster g ON f.Roster_Number = g.Roster_Number
GROUP BY g.Car_Number order by PointTotal desc");

$myarray=array();
$datearray=array();
$totalarray=array();

while ($row=mysql_fetch_assoc($points_result)) {
  $myarray[$row['Car_Number']][$row['Date']]=$row['Points'];
}
while ($row=mysql_fetch_assoc($date_result)) {
  $datearray[]=$row['Date'];}

while ($row=mysql_fetch_assoc($point_total)) {
  $totalarray[]=$row['PointTotal'];}
  
foreach ($myarray as $key=>$val) {
	echo "<tr><td width=50>$key</td>";
 
	foreach ($datearray as $key2=>$val2) {
		echo "<td width=50>",((array_key_exists($val2,$val))?$val[$val2]:0),"</td>";
  }
  
	$val3 = current($totalarray); 
	echo "<td width=50>$val3</td>";
	$val3 = next($totalarray); 
}

echo "</tr>";
  
echo "</table>";
?>

Open in new window

0
Steve BinkCommented:
My apologies for the delay in returning.  I've been dealing with some of my own code issues.  Glad to hear you got it sorted out, though.  A couple notes on your code:

1) You are duplicating the </td> closing tag in the first while() loop.  Remove it from either line 13 (recommended) or 15.
2) lines 57 and 59 are duplicating work.  There is likely a better way to synchronize these arrays.

But, more importantly, it is working.  Good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.