What's wrong with my join statement? It returns BOOLEAN in PHP

Question

To make my question as clear as possible (I've been moaned at previously for being too vague), here's the structure and data of two tables I'm working with in a project. Table 1 contains a list of football games, table 2 contains UK football teams that will be recorded in table 1.

I'm just learning about SQL from a book I took out from the library. I've followed the instructions on joining table data. Here, I am simply trying to echo the data onto the page, to make sure my query is right before styling the page around this data.

    <?php
        //Set DB Variables
        $dbc = mysql_connect(host, username, password);
        $db = mysql_select_db(database);
        $results= mysql_query("SELECT 'tbl_games.game_ID', 'tbl_games.game_date', 'tbl_teams.team_name' FROM tbl_teams, tbl_games
            WHERE 'tbl_games.team1_ID' = 'tbl_teams.team_ID' AND 'tbl_games.team2_ID' = 'tbl_teams.team_ID' AND 'tbl_games.team1_score' IS NULL AND 'tbl_games.team2_score' IS NULL");
        while ($row = mysql_fetch_array($results)) {
            foreach ($row as $columnName => $results) {
                echo 'Column name: '.$columnName.' Column data: '.$columnData.'<br/>';
            }
        }
    ?>

There are no errors being printed on the page when I run the code, it just doesn't print anything. But there are (or at least should be) some results showing up. What did I get wrong here?

--
-- Table structure for table `tbl_games`
--

DROP TABLE IF EXISTS `tbl_games`;
CREATE TABLE IF NOT EXISTS `tbl_games` (
  `game_ID` int(6) NOT NULL AUTO_INCREMENT,
  `team1_ID` int(4) NOT NULL,
  `team2_ID` int(4) NOT NULL,
  `team1_score` int(2) DEFAULT NULL,
  `team2_score` int(2) DEFAULT NULL,
  `game_date` date NOT NULL,
  PRIMARY KEY (`game_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=121 ;

--
-- Dumping data for table `tbl_games`
--

INSERT INTO `tbl_games` (`game_ID`, `team1_ID`, `team2_ID`, `team1_score`, `team2_score`, `game_date`) VALUES
(1, 42, 34, NULL, NULL, '2013-08-23'),
(2, 159, 45, NULL, NULL, '2013-08-23'),
(3, 5, 122, NULL, NULL, '2013-08-23'),
(4, 67, 12, NULL, NULL, '2013-08-24'),
(5, 60, 155, NULL, NULL, '2013-08-24'),
(6, 78, 105, NULL, NULL, '2013-08-24'),
(7, 101, 156, NULL, NULL, '2013-08-24'),
(8, 134, 144, NULL, NULL, '2013-08-24'),
(9, 142, 47, NULL, NULL, '2013-08-24'),
(10, 13, 88, NULL, NULL, '2013-08-24'),
(11, 21, 120, NULL, NULL, '2013-08-24'),
(12, 19, 16, NULL, NULL, '2013-08-24'),
(13, 20, 123, NULL, NULL, '2013-08-24'),
(14, 26, 29, NULL, NULL, '2013-08-24'),
(15, 36, 51, NULL, NULL, '2013-08-24'),
(16, 77, 21, NULL, NULL, '2013-08-24'),
(17, 81, 84, NULL, NULL, '2013-08-24'),
(18, 85, 18, NULL, NULL, '2013-08-24'),
(19, 132, 96, NULL, NULL, '2013-08-24'),
(20, 162, 50, NULL, NULL, '2013-08-24'),
(21, 22, 131, NULL, NULL, '2013-08-24'),
(22, 25, 152, NULL, NULL, '2013-08-24'),
(23, 86, 46, NULL, NULL, '2013-08-24'),
(24, 97, 27, NULL, NULL, '2013-08-24'),
(25, 107, 140, NULL, NULL, '2013-08-24'),
(26, 109, 115, NULL, NULL, '2013-08-24'),
(27, 127, 133, NULL, NULL, '2013-08-24'),
(28, 146, 69, NULL, NULL, '2013-08-24'),
(29, 150, 112, NULL, NULL, '2013-08-24'),
(30, 2, 38, NULL, NULL, '2013-08-24');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_teams`
--

DROP TABLE IF EXISTS `tbl_teams`;
CREATE TABLE IF NOT EXISTS `tbl_teams` (
  `team_ID` int(4) NOT NULL AUTO_INCREMENT,
  `team_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`team_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=164 ;

--
-- Dumping data for table `tbl_teams`
--

INSERT INTO `tbl_teams` (`team_ID`, `team_name`) VALUES
(1, 'Aberdeen'),
(2, 'Accrington Stanley'),
(3, 'AFC Bournemouth'),
(4, 'AFC Wimbledon'),
(5, 'Airdrieonians'),
(6, 'Albion Rovers'),
(7, 'Aldershot Town'),
(8, 'Alfreton Town'),
(9, 'Alloa Athletic'),
(10, 'Annan Athletic'),
(11, 'Arbroath'),
(12, 'Arsenal'),
(13, 'Aston Villa'),
(14, 'Ayr United'),
(15, 'Barnet'),
(16, 'Barnsley'),
(17, 'Berwick Rangers'),
(18, 'Birmingham City'),
(19, 'Blackburn Rovers'),
(20, 'Blackpool'),
(21, 'Bolton Wanderers'),
(22, 'Bradford City'),
(23, 'Braintree Town'),
(24, 'Brechin City'),
(25, 'Brentford'),
(26, 'Brighton & Hove Albion'),
(27, 'Bristol City'),
(28, 'Bristol Rovers'),
(29, 'Burnley'),
(30, 'Burton Albion'),
(31, 'Bury'),
(32, 'Cambridge United'),
(33, 'Cardiff City'),
(34, 'Carlisle United'),
(35, 'Celtic'),
(36, 'Charlton Athletic'),
(37, 'Chelsea'),
(38, 'Cheltenham Town'),
(39, 'Chester'),
(40, 'Chesterfield'),
(41, 'Clyde'),
(42, 'Colchester United'),
(43, 'Coventry City'),
(44, 'Cowdenbeath'),
(45, 'Crawley Town'),
(46, 'Crewe Alexandra'),
(47, 'Crystal Palace'),
(48, 'Dagenham & Redbridge'),
(49, 'Dartford'),
(50, 'Derby County'),
(51, 'Doncaster Rovers'),
(52, 'Dumbarton'),
(53, 'Dundee'),
(54, 'Dundee United'),
(55, 'Dunfermline'),
(56, 'East Fife'),
(57, 'East Stirlingshire'),
(58, 'Elgin City'),
(59, 'England'),
(60, 'Everton'),
(61, 'Exeter City'),
(62, 'Falkirk'),
(63, 'FC Halifax Town'),
(64, 'Fleetwood Town'),
(65, 'Forest Green Rovers'),
(66, 'Forfar Athletic'),
(67, 'Fulham'),
(68, 'Gateshead'),
(69, 'Gillingham'),
(70, 'Greenock Morton'),
(71, 'Grimsby Town'),
(72, 'Hamilton Academical'),
(73, 'Hartlepool United'),
(74, 'Heart of Midlothian'),
(75, 'Hereford United'),
(76, 'Hibernian'),
(77, 'Huddersfield Town'),
(78, 'Hull City'),
(79, 'Hyde'),
(80, 'Inverness Caledonian Thistle'),
(81, 'Ipswich Town'),
(82, 'Kidderminster Harriers'),
(83, 'Kilmarnock'),
(84, 'Leeds United'),
(85, 'Leicester City'),
(86, 'Leyton Orient'),
(87, 'Lincoln City'),
(88, 'Liverpool'),
(89, 'Livingston'),
(90, 'Luton Town'),
(91, 'Macclesfield Town'),
(92, 'Manchester City'),
(93, 'Manchester United'),
(94, 'Mansfield Town'),
(95, 'Middlesbrough'),
(96, 'Millwall'),
(97, 'Milton Keynes Dons'),
(98, 'Montrose'),
(99, 'Morecambe'),
(100, 'Motherwell'),
(101, 'Newcastle United'),
(102, 'Newport County'),
(103, 'Northampton Town'),
(104, 'Northern Ireland'),
(105, 'Norwich City'),
(106, 'Nottingham Forest'),
(107, 'Notts County'),
(108, 'Nuneaton Town'),
(109, 'Oldham Athletic'),
(110, 'Oxford United'),
(111, 'Partick Thistle'),
(112, 'Peterborough United'),
(113, 'Peterhead'),
(114, 'Plymouth Argyle'),
(115, 'Port Vale'),
(116, 'Portsmouth'),
(117, 'Preston North End'),
(118, 'Queen of the South'),
(119, 'Queen''s Park'),
(120, 'Queens Park Rangers'),
(121, 'Raith Rovers'),
(122, 'Rangers'),
(123, 'Reading'),
(124, 'Republic of Ireland'),
(125, 'Rochdale'),
(126, 'Ross County'),
(127, 'Rotherham United'),
(128, 'Salisbury City'),
(129, 'Scotland'),
(130, 'Scunthorpe United'),
(131, 'Sheffield United'),
(132, 'Sheffield Wednesday'),
(133, 'Shrewsbury Town'),
(134, 'Southampton'),
(135, 'Southend United'),
(136, 'Southport'),
(137, 'St Johnstone'),
(138, 'St Mirren'),
(139, 'Stenhousemuir'),
(140, 'Stevenage'),
(141, 'Stirling Albion'),
(142, 'Stoke City'),
(143, 'Stranraer'),
(144, 'Sunderland'),
(145, 'Swansea City'),
(146, 'Swindon Town'),
(147, 'Tamworth'),
(148, 'Torquay United'),
(149, 'Tottenham Hotspur'),
(150, 'Tranmere Rovers'),
(151, 'Wales'),
(152, 'Walsall'),
(153, 'Watford'),
(154, 'Welling United'),
(155, 'West Bromwich Albion'),
(156, 'West Ham United'),
(157, 'Wigan Athletic'),
(158, 'Woking'),
(159, 'Wolverhampton Wanderers'),
(160, 'Wrexham'),
(161, 'Wycombe Wanderers'),
(162, 'Yeovil Town'),
(163, 'York City');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

See [this answer](http://stackoverflow.com/a/11674313/250259) for how to troubleshoot this. — John Conde, Aug 24 '13 at 21:15
You're using single quotes `'` instead of backquotes `\`` in your query. — Barmar, Aug 24 '13 at 21:17
Thanks for being verbose, however, there's also a saying to reduce it to the bare minimum example that demonstrates the issue. Yes, it's a burden, because it requires you as asking the question to take the problem out of the concrete code where it originally arises and only create a second testbed on which an excerpt of the problems code is written from scratch with as little code as necessary to reproduce the issue. Not only will that result in finding the answer already in 99.999% of all cases, it's also more work then even writing down such a long question as you did. — hakre, Aug 24 '13 at 21:27
Okay, I try to make it more put: Your question contains way too much code. You need to pinpoint your issue first before asking. After reducing, you first of all need to search, too. As you can see it was never meant to *add* to the thread, but the bare opposite: To remove from the thread. Hope you're getting it now. — hakre, Aug 24 '13 at 21:37
@Hakre There's only about 10 lines of code, the rest is just his table schema and sample data, which we almost always ask for in database questions. Admittedly, it's more data than we probably need. — Barmar, Aug 24 '13 at 23:05
@Barmar: And we also ask for the minimum example. If this is repdroduceable with two tables one column each, then we only need an exemplary schema of those two. And if it is reproduceable with two lines of data for those two tables as well. That's what we ask for and I'd say that this would also show the OP wrapping his/her mind around the problem and did some trouble-shooting and expresses wish to understand and learn. — hakre, Aug 25 '13 at 07:06

paxdiablo · Answer 1 · 2013-08-24T21:30:47.137

Those single quotes ' in your queries (in the PHP code) should be backticks `, same as they are in your statments creating the table.

Otherwise your comparing literal strings in your where clause, which don't match.

And, as an aside, I'm not really a big fan of using backticks unless it's absolutely necessary. In my opinion, it often makes the SQL statements less readable. It has its place, of course and some may prefer consistency over readability, but I'm not one of them :-)

However, even once you fix that, you're going to have a problem with:

WHERE tbl_games.team1_ID = tbl_teams.team_ID
  AND tbl_games.team2_ID = tbl_teams.team_ID

since you appear to be asking for all those games where a team played against itself, something I've not seen in a lot of sporting competitions.

You need to rethink what you're asking for. For example, if you simply want all games where a team played (as either team 1 or 2), you would use OR rather than AND (and also use parentheses to ensure proper mixing of AND/OR).

Thanks for the pointer, I have changed them all but the resulting output is still the same, the code prints nothing to the screen. — Mike Ashfield, Aug 24 '13 at 21:25
Better still, he should remove the backticks. The backticks add nothing. — Andy Lester, Aug 24 '13 at 22:20

Barmar · Answer 2 · 2013-08-24T21:34:57.847

        $results= mysql_query("SELECT 'tbl_games.game_ID', 'tbl_games.game_date', 'tbl_teams.team_name' FROM tbl_teams, tbl_games
        WHERE 'tbl_games.team1_ID' = 'tbl_teams.team_ID' AND 'tbl_games.team2_ID' = 'tbl_teams.team_ID' AND 'tbl_games.team1_score' IS NULL AND 'tbl_games.team2_score' IS NULL");

should be:

        $results= mysql_query("SELECT `tbl_games`.`game_ID`, `tbl_games`.`game_date`, `tbl_teams`.`team_name` FROM tbl_teams, tbl_games
        WHERE `tbl_games`.`team1_ID` = `tbl_teams`.`team_ID` AND `tbl_games`.`team2_ID` = `tbl_teams`.`team_ID` AND `tbl_games`.`team1_score` IS NULL AND `tbl_games`.`team2_score` IS NULL");

You were using the wrong type of quotes, and the . must not be inside the quotes.

Since none of your table or column names are reserved words, you could do it without the quotes entirely:

        $results= mysql_query("SELECT tbl_games.game_ID, tbl_games.game_date, tbl_teams.team_name FROM tbl_teams, tbl_games
        WHERE tbl_games.team1_ID = tbl_teams.team_ID AND tbl_games.team2_ID = tbl_teams.team_ID AND tbl_games.team1_score IS NULL AND tbl_games.team2_score IS NULL");

I think the actual query you may want is this:

SELECT tbl_games.game_ID, tbl_games.game_date, tbl_teams.team_name
FROM tbl_teams, tbl_games
WHERE (tbl_teams.team_ID = tbl_games.team1_ID AND tbl_games.team1_score IS NULL)
OR (tbl_teams.team_ID = tbl_games.team2_ID AND tbl_games.team2_score IS NULL)

This finds all the games that each team is scheduled to play in, and they have a null score.

SQLFIDDLE

This doesn't have any effect on the output. It still prints nothing to the screen. Based on the data and table structure, should it? I may have written it wrong. — Mike Ashfield, Aug 24 '13 at 21:24
`SELECT tbl_games.game_ID, tbl_games.game_date, tbl_teams.team_name FROM tbl_teams, tbl_games WHERE tbl_games.team1_ID = tbl_teams.team_ID AND tbl_games.team2_ID = tbl_teams.team_ID;` returns 0 rows, but `SELECT tbl_games.game_ID, tbl_games.game_date, tbl_teams.team_name FROM tbl_teams, tbl_games WHERE tbl_games.team1_ID = tbl_teams.team_ID;` returns the desired result, minus the sames of team 2 for each game. — Mike Ashfield, Aug 24 '13 at 21:30
Better still, he should remove the backticks. The backticks add nothing. — Andy Lester, Aug 24 '13 at 22:19

r.m · Accepted Answer · 2013-08-24T21:42:44.910

probable cause: it is selecting data from 2 tables (tbl_teams and tbl_games)
The condition is that tbl_teams.team_ID equals team2_ID and team1_ID. Unless there are games where the same team was on both sides, that's never going to work.

You didn't specify what you want the query to return, so I'll assume you just want the results for both teams.

What you probably want is a join that involves 3 sets of data: game, the first team, the second team. in SQL this can be done using an alias.

try this:

SELECT tbl_games.game_ID, 
       tbl_games.game_date,
       team1.team_name as "team1_name",
       team2.team_name as "team2_name"
FROM 
    tbl_teams as team1, 
    tbl_teams as team2, 
    tbl_games
WHERE 
    tbl_games.team1_ID = team1.team_ID AND 
    tbl_games.team2_ID = team2.team_ID AND
    tbl_games.team1_score IS NULL AND 
    tbl_games.team2_score IS NULL;

Adding the "is null" will reduce the results. If all games have a score, you won't get any results.

Another potential issue with your query is that the field names are between single quotes. I'm not sure those are removed by php before actually executing the query. If they're not, that's bad; anything between single quotes is treated as a string. That would mean the condition 'tbl_games.team1_score' IS NULL is actually comparing string 'tbl_games.team1_score' with null and not the content of the field named team1_score.

Edit: added the is NULL to the where clause as in the example data you do have nulls.

Why did you remove the `tbl_games.teamN_score IS NULL` condition from the WHERE clause? — Barmar, Aug 24 '13 at 21:36
@Barmar: ups, you're right. I didn't see the example set in the question. I figured it could have been a possible cause. I'll edit it back in. — r.m, Aug 24 '13 at 21:39

score 0 · Answer 4 · answered Aug 24 '13 at 22:06

I think the problem with your query is at this condition 'tbl_games.team1_ID' = 'tbl_teams.team_ID' AND 'tbl_games.team2_ID' = 'tbl_teams.team_ID'. Your are comparing same id to two different fields tbl_games.team1_ID & tbl_games.team2_ID. I think tbl_games.team1_ID is a team played vs tbl_games.team2_ID which means tbl_games.team1_ID != tbl_games.team2_ID. so you just need only one team either of the two. Your query may look like the following.

<?php
        //Set DB Variables
        $dbc = mysql_connect(host, username, password);
        $db = mysql_select_db(database);
        $results= mysql_query("SELECT tbl_games.game_ID, tbl_games.game_date, tbl_teams.team_name FROM tbl_teams, tbl_games
            WHERE 'tbl_games.team1_ID' = 'tbl_teams.team_ID'  AND 'tbl_games.team1_score' IS NULL AND 'tbl_games.team2_score' IS NULL");
        while ($row = mysql_fetch_array($results)) {
            foreach ($row as $columnName => $results) {
                echo 'Column name: '.$columnName.' Column data: '.$columnData.'<br/>';
            }
        }
    ?>

What's wrong with my join statement? It returns BOOLEAN in PHP

4 Answers4