A logical problem with two tables

Question

Hey guys, I created a list for fixtures.

$result = mysql_query("SELECT date FROM ".TBL_FIXTURES." WHERE compname = '$comp_name' GROUP BY date");
    $i = 1;
    $d = "Start";
    while ($row = mysql_fetch_assoc($result)) 
    {
        $odate = $row['date'];
        $date=date("F j Y", $row['date']);
        echo "<p>Fixture $i - $d to $date</p>";
    }

As you can see from the query, the date is displayed from the fixtures table. The way my system works is that when a fixture is "played", it is removed from this table. Therefore when the entire round of fixtures are complete, there wont be any dates for that round in this table. They will be in another table.

Is there anyway I can run an other query for dates at the same time, and display only dates from the fixtures table if there isnt a date in the results table?

"SELECT * FROM ".TBL_CONF_RESULTS." 
                               WHERE compid = '$_GET[id]' && type2 = '2' ORDER BY date" 

That would be the second query!

EDIT FROM HERE ONWARDS...

Is there anyway I can select the date from two tables and then only use one if there are matches. Then use the rows of dates (GROUPED BY) to populate my query? Is that possible?

Answer 1

From the notes on this page:

//performs the query
$result = mysql_query(...);

$num_rows = mysql_num_rows($result);

//if query result is empty, returns NULL, otherwise,
//returns an array containing the selected fields and their values
if($num_rows == NULL)
{
    // Do the other query
}
else
{
    // Do your stuff as now
}

Answer 2

1. WHERE compid = '$_GET[id]' presents an oportunity for SQL Injection.
2. Are TBL_FIXTURES and TBL_CONF_RESULTS supposed to read $TBL_FIXTURES and $TBL_CONF_RESULTS?

Answer 3

ChrisF has the solution!

One other thing you might think about is whether it is necessary to do a delete and move to another table. A common way to solve this type of challenge is to include a status field for each record, then rather than just querying for "all" you query for all where status = "x". For example, 1 might be "staging", 2 might be "in use", 3 might be "used" or "archived" In your example, rather than deleting the field and "moving" the record to another table (which would also have to happen in the foreach loop, one would assume) you could simply update the status field to the next status.

So, you'd eliminate the need for an additional table, remove one additional database hit per record, and theoretically improve the performance of your application.

Answer 4

Seems like what you want is a UNION query.

$q1 = "SELECT DISTINCT date FROM ".TBL_FIXTURES." WHERE compname = '$comp_name'";
$q2 = "SELECT DISTINCT date FROM ".TBL_CONF_RESULTS. 
      "WHERE compid = '$_GET[id]' && type2 = '2'";

$q = "($q1) UNION DISTINCT ($q2) ORDER BY date";

Answer 5

It sounds like you want to UNION the two result sets, akin to the following:

SELECT f.date FROM tbl_fixtures f
WHERE f.compname = '$comp_name'
UNION SELECT r.date FROM tbl_conf_results r
WHERE r.compid = '$_GET[id]' AND r.type2 = '2'
GROUP BY date

This should select f.date and add rows from r.date that aren't already in the result set (at least this is the behaviour with T-SQL). Apparently it may not scale well, but there are many blogs on that (search: UNION T-SQL).