mysql_num_rows problem inside an array

[bschultz]

I have a mysql database table that lists baseball and softball umpires for the season.  The table has columns for: date, sport, umpire1, umpire2, umpire3, umpire 4, umpire5, visitor team, and home team.

 

I'm trying to write a bit of code that will tell me how many times I have schedule (manually) an umpire to see a particular school. 

 

Here's what I've come up with.

 

<?php
require_once "/config.php";
$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);   

$start_date = '2011-04-01';
$end_date = '2011-06-01';

$umpires = Array('umpire 1 name', 'umpire 1 name', 'umpire 2 name', 'umpire 3 name', 'umpire 4 name', 'umpire 5 name', 'umpire 6 name');

$schools = Array('Bagley', 'Bemidji', 'Blackduck', 'Fosston', 'International Falls', 'Kelliher', 'Laporte', 'Lake of the Woods', 'Remer', 'Walker');   
?>

<?php 
foreach($umpires as $umps) {    //selects the first umpire in the array
        echo "<table width='20%' border ='1'>";
            echo "<tr><td>$umps</td><td>Baseball</td><td>Softball</td></tr>";

foreach($schools as $town)  {    //selects the first town in the array

$sql_baseball = "SELECT * FROM $table WHERE (ump1 = $umps OR ump2 = $umps OR ump3 = $umps OR ump4 = $umps OR ump5 = $umps) AND (visitor = $town OR home = $town) AND sport = 'Baseball' AND date >= $start_date AND DATE <= $end_date";           
$result_baseball = mysql_query($sql_baseball);
$count_baseball=mysql_num_rows($result_baseball);

$sql_softball = "SELECT * FROM $table WHERE (ump1 = $umps OR ump2 = $umps OR ump3 = $umps OR ump4 = $umps OR ump5 = $umps) AND (visitor = $town OR home = $town) AND sport = 'Softball' AND date >= $start_date AND DATE <= $end_date";           
$result_softball = mysql_query($sql_softball);
$count_softball=mysql_num_rows($result_softball);

echo "<td>$town</td><td>$count_baseball</td><td>$count_softball</td></tr>";
} //loops the towns
echo "</table>";
}   //ends each umpire
?>

 

 

As you can see, I've put all the umpire names in an array...and each school in a separate array.

 

The html tables are being displayed (with the correct names and schools listed...but I'm getting this error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /by_school.php on line 33

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /by_school.php on line 37

 

Line 33 is this - - - $count_baseball=mysql_num_rows($result_baseball);

Line 37 is this - - - $count_softball=mysql_num_rows($result_softball);

 

I'm guessing that the problem is that I have multiple selects (one for each town) while using the same variable names for each select...but I can't figure out how to concacate the variable names properly...or even if that's the problem.

 

Any ideas?

 

Thanks!

[Skewled]

Where is $db set?

 

mysql_select_db($db,$dbc);

 

Also $start_date and $end_date I don't see where those values are coming from. So the query will fail on that alone if the connection isn't established properly. That's why I'm asking about $db.

[bschultz]

those are both in config.php.  It appears that it's a problem with the select.  I can't even strip it down to one umpire (hardcoded) and one town (hardcoded) without getting the same result.

[Skewled]

$umpires = Array('umpire 1 name', 'umpire 1 name', 'umpire 2 name', 'umpire 3 name', 'umpire 4 name', 'umpire 5 name', 'umpire 6 name');

 

Also you repeate umpire 1 name twice in your array.

 

The table has columns for: date, sport, umpire1, umpire2, umpire3, umpire 4, umpire5, visitor team, and home team.

So in your query you're using ump1, ump2 etc.. they should match the table structure of umpire1, umpire2, etc...

 

Give this a try:

 

<?php $sql_baseball = "SELECT * FROM $table WHERE sport = 'Baseball' AND date >= $start_date AND DATE <= $end_date (umpire1 = $umps OR umpire2 = $umps OR umpire3 = $umps OR umpire4 = $umps OR umpire5 = $umps) AND (visitor = $town OR home = $town)";?>

[bschultz]

Thanks for the help.  Unfortunately, it has the same results.

 

In the "real" code I'm using, I have actual umpire names in the array...which I'd rather not post in a public forum....not Umpire name 1, umpire name 2 etc.

[Skewled]

Sorry I wasn't able to help you find the issue.

 

I would lastly look at how your working with the date. I would change DATE to dateend to keep it clear, you also need to make sure the date format your comparing to is the same format in your database. (date, timestamp, datetime, etc..) If they don't match you'll likely end up with invalid results like you are experiencing or no results displayed.

 

I really hope someone else can shed some light on this and spot something we're both missing.

 

noticed a typo $dbc = mysql_pconnect($host, $username, $password);
should be : $dbc = mysql_connect($host, $username, $password);

[sasa]

SELECT * FROM $table WHERE (ump1 = '$umps' OR ump2 = '$umps' OR ump3 = '$umps' OR ump4 = '$umps' OR ump5 = '$umps') AND (visitor = '$town' OR home = '$town') AND sport = 'Baseball' AND `date` >= $start_date AND `date` <= $end_date

you must put strimg values in qutes and field name date in ``

[bschultz]

That did it...thank you!!!