[RESOLVED] SELECT won't get first record

[robkir]

This bit of code uses two joined tables to group multiple soccer games held at the same location, on the same date and on the same field into report.

The problem is that if the very first location's playing field in the table has only one game on one field (such as just one game at 8 am, as opposed to multiple games (8 am then 10 am them noon, etc) on the same playing field) the SELECT statement doesn't pick it up. Weirdly enough, after the first record is processed, any other record can have just one playing field in it and it gets included. If I add a second game to the very first record, then the SELECT statement includes it.

I know this isn't much to go on...and I have a clunky work around, but I'd sure like to use this method.

Any ideas would be gratefully accepted. Please let me know if you need additional info.

$result=mysql_query("SELECT master_game_sched.gameDate, master_game_sched.siteID, master_game_sched.field, sites.siteName
FROM master_game_sched
JOIN sites ON master_game_sched.siteID = sites.siteID

GROUP BY gameDate, siteID, field
ORDER BY siteName, field
LIMIT 1 , 40");

while($row = mysql_fetch_array($result))
{   echo "<tr><td align='right'>" .$row['siteName'] . "</td>";
    echo "<td>" . convertDate($row['gameDate']) . "</td>";}
    echo "<td>" . $row['field'] . "</td></tr>";  

}

[dagon]

try a right join instead of left

[robkir]

Darn, that would have been an easy fix, but no, it didn't cure the problem. The second table contains the name of the location site. Any other thoughts...?

[Bonesnap]

Is it just me or should there be a WHERE clause in there? You stated that it joins the tables based on three criteria, but haven't provided any values.

Personally, I use NATURAL JOIN whenever I need to select data from multiple tables. I find it not only simpler semantically, but from a coding perspective as well.

[robkir]

Hi Bonesnap

I took out the JOIN altogether and darned if it didn't do the same thing. I also tried resort DESC vs ASC and it still doesn't pick up the very first record. I am totally stumped.

Any other ideas?

Bob

[laserlight]

dagon wrote:

try a right join instead of left

Actually, that is an inner join, not a left join, to begin with.

Bonesnap wrote:

Is it just me or should there be a WHERE clause in there? You stated that it joins the tables based on three criteria, but haven't provided any values.

The ON clause is a limited WHERE clause in disguise.

Bonesnap wrote:

Personally, I use NATURAL JOIN whenever I need to select data from multiple tables. I find it not only simpler semantically, but from a coding perspective as well.

It is not always feasible to use a natural join. I would avoid natural joins in view that being explicit about the join condition is clearer and safer.

robkir wrote:

Any other ideas?

Post the schema for the relevant tables along with sample data, along with info on what exactly you intend to select from the sample data.

[Bonesnap]

laserlight;10966947 wrote:

The ON clause is a limited WHERE clause in disguise.

You learn something new everyday.

Something else I noticed, though: should there be a GROUP BY clause included, seeing as there are no aggregate functions used at all in the column selection?

laserlight;10966947 wrote:

It is not always feasible to use a natural join. I would avoid natural joins in view that being explicit about the join condition is clearer and safer.

Indeed, natural joins may not always be feasible unless you structure your database to maximize their use, as I do. I've had databases with dozens of tables and I've been able to use natural joins 98% of the time. In any case, it shouldn't be difficult selecting data from two tables with a natural join.

Also, I don't see how...

SELECT user_fname, job_title FROM users NATURAL JOIN jobs WHERE user_id = 42;

...is unclear or unsafe. Clearly a WHERE clause is needed, and it's implied there's a user_id column in each table. shrug Personal preference I suppose.

[laserlight]

Bonesnap wrote:

Something else I noticed, though: should there be a GROUP BY clause included, seeing as there are no aggregate functions used at all in the column selection?

Yeah, I also suspect that the GROUP BY is causing the problem, but I would like to see the facts more clearly before committing.

Bonesnap wrote:

Also, I don't see how... (...) ...is unclear or unsafe.

It is potentially unclear because the columns to join on are implied, and assumptions do not always hold true. It is potentially unsafe because a change to the schema could change the result of the query, unintentionally.

[Bonesnap]

laserlight;10966957 wrote:

It is potentially unclear because the columns to join on are implied, and assumptions do not always hold true.

I don't see this as much of an issue. If you've designed the database yourself, chances are you're familiar with its structure and how tables are joined. If you happen to forget, you can always reference the schema. If you're new to a project, I think we can both agree it's within your and the project's best interests to familiarize yourself with the database structure before diving in. And again, if you forget or are unsure of something, you can always reference the schema.

laserlight;10966957 wrote:

It is potentially unsafe because a change to the schema could change the result of the query, unintentionally.

This is true for any type of join, though; a change in schema can break any join or cause unexpected results. I don't see this as a valid argument against natural joins.

[laserlight]

Bonesnap wrote:

I don't see this as much of an issue. If you've designed the database yourself, chances are you're familiar with its structure and how tables are joined. If you happen to forget, you can always reference the schema. If you're new to a project, I think we can both agree it's within your and the project's best interests to familiarize yourself with the database structure before diving in. And again, if you forget or are unsure of something, you can always reference the schema.

Well, if you prefer to discard defensive programming for the sake of convenience and the appearance of simplicity, then that really is your choice.

Bonesnap wrote:

This is true for any type of join, though; a change in schema can break any join or cause unexpected results. I don't see this as a valid argument against natural joins.

The problems associated with a change to the schema can be more insidious with a natural join since the conditions are implicit.

[robkir]

Hi Laserlight,

Good explanations of JOIN--thanks.

I took the JOIN statement out for the moment since the second table's only function was to provide the primary table's siteID with the actual name of soccer game's location. The primary table has three records that I want to present to the user. The date, location, and the field (field being the grassy expanse that the kids actually play on, not a field in a record) . Ultimately, but not included in this question since I know how to do it, the user will be able to click on the generated hyperlink and see all the home and away games taking place on that particular date, location, field.

If a table has several records with the same date, location, field (for example 11/27/2010, West Park, Field #1 8:00 am; 1/27/2010, West Park, Field #1 10:00 am) and it is first on the sorted locations, it will show up on the report. But, if there is only one game (8:00 am), a blank record is printed. After that first record, other records with just one entry, will show up. Weird.

By the way, my original code used a WHILE loop. I changed this to a DO loop. The difference was with the WHILE loop, nothing was printed in the first instance. With the DO loop, a blank record was printed.

Here's the complete code:

$sql="SELECT gameDate, siteID, field
FROM master_game_sched
GROUP BY gameDate, siteID, field
ORDER BY siteID desc, field 
LIMIT 1 , 100";
$result=mysql_query($sql, $link )or die(mysql_error($mysql));
;
echo "<table border=1>";
echo "<tr bgcolor='yellow' align='center'><td>Site</td><td>Date</td><td>Field #</td></tr>";
do {

   echo "<tr><td align='right'>" .$row['siteID'] . " ";
    echo "<td>" . convertDate($row['gameDate']) . "</td>";
    echo "<td>" . $row['field'] . "</td></tr>";  

}
while($row = mysql_fetch_array($result, MYSQL_ASSOC));
echo "</table>";

Sample output ( the -- below is the first line of data. There is nothing there, but the -- appears because I have a function that turns the date (2001127 into 11-27-2010)

Site Date Field #
--
11 11-27-2010 1
11 11-27-2010 2
11 11-26-2010 3
11 11-28-2010 4

And the schema (in DESC order)

gameID whatDiv gameDate gameTime flight siteID field home away
310 10B 20101126 09:00:00 12 1
307 19G 20101128 14:00:00 11 4 D B
308 19G 20101128 15:00:00 11 4 B D
309 19G 20101128 16:00:00 11 4 A C

[laserlight]

robkir wrote:

By the way, my original code used a WHILE loop. I changed this to a DO loop. The difference was with the WHILE loop, nothing was printed in the first instance. With the DO loop, a blank record was printed.

Your original while loop was correct. The problem here seems to be with the SQL, so trying to change the PHP code to work around it is rather futile.

[robkir]

Could be the GROUP BY clause...and it makes sense in a way, since there is only item, and nothing else to group it too. Although, that doesn't explain why a similiar situation (one date, site, field) in a record later on is included in the report. I didn't think I needed an aggregate column functions and I am not sure which one I would use. Any suggestions?

[laserlight]

What if you used SELECT DISTINCT instead of GROUP BY?

[robkir]

I went into phpMyAdmin and ran both scenarios, DISTINCT and GROUP BY, and either one gives the desired results in the database. But when I amend the code to either category--the problem is still there. So...I'd guess there's something in the PHP...?

Thanks for staying with this. I appreciate the input.

[laserlight]

robkir wrote:

I went into phpMyAdmin and ran both scenarios, DISTINCT and GROUP BY, and either one gives the desired results in the database. But when I amend the code to either category--the problem is still there. So...I'd guess there's something in the PHP...?

Fascinating observation, because if it works from phpMyAdmin, then it probably is not a problem with the SQL. Try:

$sql = "SELECT DISTINCT gameDate, siteID, field
    FROM master_game_sched
    ORDER BY siteID desc, field
    LIMIT 1 , 100";
$result = mysql_query($sql, $link) or die(mysql_error($mysql));
echo '<table border="1"><tr bgcolor="yellow" align="center"><td>Site</td><td>Date</td><td>Field #</td></tr>';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf('<tr><td align="right">%d</td> <td>%s</td> <td>%s</td></tr>',
        $row['siteID'],
        htmlspecialchars(convertDate($row['gameDate'])),
        htmlspecialchars($row['field']));
}
echo '</table>';

Basically, I corrected the HTML (but you should be using CSS 😉 ) and fixed potential output problems.

[robkir]

Yeah, my html is very sloppy and CSS is slowly seeping its way into my head.

Sorry to report that wasn't the solution, either. It seems like such a simple request to the database and that it oughta work (I think that about everything I do even if it never works).

Any other ideas?

[laserlight]

Double check. Is this query really working as expected?

SELECT DISTINCT gameDate, siteID, field
    FROM master_game_sched
    ORDER BY siteID desc, field
    LIMIT 1 , 100

[robkir]

I think so. Here is a copy of the phpMYADMIN SQL query window:

SELECT DISTINCT gameDate, siteID, field
FROM master_game_sched
ORDER BY siteID DESC , field
LIMIT 0 , 30

Here's the phpMYADMIN output

gameDate	siteID	field
		20101126	12	1
		20101127	11	1
		20101127	11	2
		20101126	11	3
		20101128	11	4

The number 12 shows up.

Here's my (well, actually your) php query

$sql = "SELECT DISTINCT gameDate, siteID, field 
    FROM master_game_sched 
    ORDER BY siteID desc, field 
    LIMIT 1 , 100"; 

And the output is

Site Date Field #
11 11-27-2010 1
11 11-27-2010 2
11 11-26-2010 3
11 11-28-2010 4

As you can see the site number 12 is missing.

[laserlight]

Oh, but that is really dumb. LIMIT 1, 100 causes the first row to be skipped and up to 100 of the remaining rows to be retrieved.

Change it to LIMIT 0, 100 or simply LIMIT 100.