How to run this SQL query

[Zack2008]

Hi, I am trying to run the following query, but it's only displaying the records where `home` is $team, but it's not displaying the records where `away` is $team

Here is my code

[PHP]$data = mysql_query("SE LECT DATE_FORMAT(Dat e,'%d/%m/%Y') AS UKDate , TIME_FORMAT(Tim e,'%H:%i') AS TimeNew , `Home` , `Away` , `Final` , `Status` , `FT` , `Att` , `ID` FROM matches WHERE `Away` OR `Home` LIKE CONVERT( _utf8 '$team' USING latin1 ) COLLATE latin1_swedish_ ci AND `Competition` LIKE CONVERT( _utf8 '$comp%' USING latin1 ) COLLATE latin1_swedish_ ci AND date BETWEEN '$datefrom' AND '$dateto' ORDER BY Date DESC") [/PHP]

I have also tried the code below, this displays the teams properly (although it's not sorting them by date order, it's but a load of away ones first, then a few home, then away, then a load of home ones :S) but it then ignores the other parts (e.g. certain competition or date range)

[PHP]$data = mysql_query("SE LECT DATE_FORMAT(Dat e,'%d/%m/%Y') AS UKDate , TIME_FORMAT(Tim e,'%H:%i') AS TimeNew , `Home` , `Away` , `Final` , `Status` , `FT` , `Att` , `ID` FROM matches WHERE `Away` LIKE CONVERT( _utf8 '$team' USING latin1 ) COLLATE latin1_swedish_ ci OR `Home` LIKE CONVERT( _utf8 '$team' USING latin1 ) COLLATE latin1_swedish_ ci AND `Competition` LIKE CONVERT( _utf8 '$comp%' USING latin1 ) COLLATE latin1_swedish_ ci AND date BETWEEN '$datefrom' AND '$dateto' ORDER BY Date DESC")[/PHP]

I have also tried changing $team to $hometeam and $awayteam and using AND, then putting the like part in for each of them, but then it looks for the home and away teams being the same in each record which obviously can't happen and if I put OR it ignores the other part of it like competition :S It might be really simple but I have tried different combinations and can’t see why it won’t work!

Thanks
Zack :)

[code green]

This will never work

FROM matches WHERE `Away` OR `Home` LIKE CONVERT

In what way does home or away relate to date order?

although it's not sorting them by date order, it's but a load of away ones first, then a few home,
then away, then a load of home ones

What are you trying to do?

but I have tried different combinations and can’t see why it won’t work!

[Zack2008]

Display the matches from the database, filtered by something different on each page. Sometimes it's just home team, sometimes just away, sometimes just competition. This page is where the home or away team are involved in the competition between certain dates. If I only pick home then it works but away matches are not included. The date orderby is so the latest match is at the top of the table at the end

Thanks
Zack:)

[code green]

Not quite following, buts lets have a look at the query that seems to give the closest result

$data = mysql_query("SELECT 
DATE_FORMAT(Date,'%d/%m/%Y') AS UKDate , 
TIME_FORMAT(Time,'%H:%i') AS TimeNew , 
`Home` , `Away` , `Final` , `Status` , `FT` , `Att` , `ID` 
FROM matches 
WHERE `Away` LIKE CONVERT( _utf8 '$team' 
USING latin1 ) COLLATE latin1_swedish_ci 
OR `Home` LIKE CONVERT( _utf8 '$team' USING latin1 ) COLLATE latin1_swedish_ci 
AND `Competition` LIKE CONVERT( _utf8 '$comp%' USING latin1 ) COLLATE latin1_swedish_ci AND date BETWEEN '$datefrom' AND '$dateto' 
ORDER BY Date DESC")

Please remove the clutter of CONVERT, USING and COLLATE.
I don't think you know what they mean or do, I'm sure I don't.
Are you Swedish?

Looking between this clutter I can see you have mixed AND and OR conditions
You cannot do this. They must be bracketed.

WHERE (`Away` LIKE '$team'  
OR `Home` LIKE '$team')
AND `Competition` LIKE '$comp%' 
AND date BETWEEN '$datefrom' AND '$dateto' 
ORDER BY Date DESC")

[Zack2008]

Not quite following, buts lets have a look at the query that seems to give the closest result

$data = mysql_query("SELECT 
DATE_FORMAT(Date,'%d/%m/%Y') AS UKDate , 
TIME_FORMAT(Time,'%H:%i') AS TimeNew , 
`Home` , `Away` , `Final` , `Status` , `FT` , `Att` , `ID` 
FROM matches 
WHERE `Away` LIKE CONVERT( _utf8 '$team' 
USING latin1 ) COLLATE latin1_swedish_ci 
OR `Home` LIKE CONVERT( _utf8 '$team' USING latin1 ) COLLATE latin1_swedish_ci 
AND `Competition` LIKE CONVERT( _utf8 '$comp%' USING latin1 ) COLLATE latin1_swedish_ci AND date BETWEEN '$datefrom' AND '$dateto' 
ORDER BY Date DESC")

Please remove the clutter of CONVERT, USING and COLLATE.
I don't think you know what they mean or do, I'm sure I don't.
Are you Swedish?

Looking between this clutter I can see you have mixed AND and OR conditions
You cannot do this. They must be bracketed.

WHERE (`Away` LIKE '$team'  
OR `Home` LIKE '$team')
AND `Competition` LIKE '$comp%' 
AND date BETWEEN '$datefrom' AND '$dateto' 
ORDER BY Date DESC")

Thanks, that final example works. I understand why that is now, I thought it would work if I put the OR part first, will have to remember the brackets in future then!

Nope, I don’t know what these CONVERT etc are for. I had problems getting code to work before so I went into PHPMyAdmin and ran the same and it worked, so I used the "create PHP code" option and pasted this into the PHP file, this included all the USING etc and as it finally worked I left it. The new version without all that looks so much better though when trying to edit.

Thanks :)

[code green]

OK, well done. But the query is not quite right.
I misplaced a bracket. Should close before the ORDER BY

WHERE (`Away` LIKE '$team'   
OR `Home` LIKE '$team') 
AND `Competition` LIKE '$comp%'  
AND date BETWEEN '$datefrom' AND '$dateto') 
ORDER BY Date DESC"

I have no idea what difference this will make.
In fact I am suprised an error wasn't thrown

[Zack2008]

It shows an error if I move the bracket to after $to

Parse error: syntax error, unexpected ';' in /home/updates/public_html/matches/consecutivedeta il.php on line 39

39 is [PHP]ORDER BY Date DESC"[/PHP] so don't know where unexpected ';' is!?

I'll leave it as ORDER BY Date DESC") while it's working though!

Thanks for your help :)

Edit: Maybe it's something to do with using PHP4? The new security features of PHP5 didn't like the league tables on the site so I changed it back to PHP4

[code green]

Whoops. The closing bracket is for the mysql_query() function.
I thought it was one of my suggestion brackets.