Mysql Record Check

[stb74]

When entering a socccer match I would like to check to see if the match exists already in the database.

I have the following to check for the current date.

[code]

SELECT LM.matchID
FROM bbob_matches LM
WHERE
(LM.matchHomeID = '$home[$i]' OR LM.matchAwayID = '$home[$i]' OR
LM.matchHomeID = '$away[$i]' OR LM.matchAwayID = '$away[$i]')
AND LM.matchDate = '$dateandtime'
[/code]

Can anyone help with the code that I need to check all other dates.

[fenway]

I'm not sure I understand this query... are you sure you don't mean AND inside the parentheses?

[stb74]

No

When I enter a match for a date.
2005-11-12 Man Utd - Arsenal

If I go back later and add another match to that date, the following code checks that the teams are not already added to a match.

[code]
//
//Query to check if home or away team already exists in the current day
//
$query = mysql_query("
SELECT LM.matchID FROM
nafl_matches LM
WHERE
(LM.matchHomeID = '$home[$i]' OR LM.matchAwayID = '$home[$i]' OR
LM.matchHomeID = '$away[$i]' OR LM.matchAwayID = '$away[$i]')
AND LM.matchDate = '$dateandtime'
", $dbconnect)
or die(mysql_error());


if(mysql_num_rows($query) == 0)
{
//
//Writes the data
//
}
else
{
echo "Match Exists";
}
[/code]


With the previous code I cannot add the same match on the same date, but I can add it multiple times on different dates.

I think the code should be something like

[code]
//
//Query to check for home game
//
$homematch = mysql_query("
SELECT LM.matchID FROM
nafl_matches LM
WHERE
(LM.matchHomeID = '$home[$i]' AND LM.matchAwayID = '$away[$i]')
", $dbconnect)
or die(mysql_error());

//
//Query to check for away game
//
$awaymatch = mysql_query("
SELECT LM.matchID FROM
nafl_matches LM
WHERE
(LM.matchHomeID = '$away[$i]' AND LM.matchAwayID = '$home[$i]')
", $dbconnect)
or die(mysql_error());

I would need to combine the queries that if any are true that it displays an error.

[/code]

[printf]

Just use OR to combine them...


[code]$query = "SELECT COUNT(*) AS found FROM nafl_matches AS LM WHERE LM.matchHomeID = '" . $away[$i] . "' AND LM.matchAwayID = '" . $home[$i] . "' OR LM.matchHomeID = '" . $home[$i] . "' AND LM.matchAwayID = '" . $away[$i] . "'";

$result = mysql_query ( $query );

$total  = mysql_fetch_assoc ( $result );

if ( $total['found'] > 0 )
{
echo 'sorry match already exists';
}
else
{
// insert new match data...
}[/code]

[stb74]

Cheers

I had tried that earlier but gave up when it didn't work.  Must have had bad code somewhere.  Pasted in yours and works grand.

Now that I have that sorted, would it be possible to add another query to it.

I have another field called matchInfo, that if the match has been postponed or abondoned I would put a P or A in this field.  So that when I display the match for the users they see it as

Man Utd P - P Arsenal.

With the above code it stops me adding matches that I have already entered, but can I allow for the postponed games.

Thanks

[stb74]

I thought the code works but not the way I want it.

[code]

$query = "
SELECT COUNT(*) AS found
FROM downarea_matches AS LM
WHERE (LM.matchHomeID = '" . $home[$i] . "'
AND LM.matchAwayID = '" . $away[$i] . "'
OR LM.matchHomeID = '" . $away[$i] . "'
AND LM.matchAwayID = '" . $home[$i] . "')
AND matchDivisionID = '$divisionid'
AND matchSeasonID = '$seasonid'";

$result = mysql_query ( $query );

$total  = mysql_fetch_assoc ( $result );

if ( $total['found'] > 0 )
{
echo 'sorry match already exists';
}
else
{
//
//Writes the data
[/code]

This code will match

if hometeam AND awayteam OR awayteam and hometeam is present in the database.

Its a bit hard to explain, but I will try.

In a season two teams will play each other twice

1. Man U v Arsenal

2. Arsenal v Man U

If I want to add match 1. but it already exists then flag error and if I want to add match 2 but exists then flag error.

[fenway]

Then I don't understand the problem anymore....

[stb74]

As you can tell I don't know what I am doing but I am making more of an effort in trying to explain than you are trying to help.

[fenway]

[quote author=stb74 link=topic=114579.msg466963#msg466963 date=1163364059]
As you can tell I don't know what I am doing but I am making more of an effort in trying to explain than you are trying to help.
[/quote]
That's highly unlikely on both counts.  What does it do now, and what do you need it to do?

[stb74]

Two teams can only play each other twice.

A v B

B v A

I wanted to add a check that stopped me adding any of the matchs a second time.

I believe I have sorted it out now anyway.