retrieving mysql data with table joins using one query

[unistake]

Hi all,

 

I am trying to get information from mysql by firstly joining a couple of tables such as:

 

"SELECT Latitude, Longitude  FROM airports INNER JOIN rosters ON airports.IATA = rosters.Dep WHERE rosters.Dep = 'BGY'";

 

I would also however like to select another airports.Latitude and airports.Longitude in the same query.

Something like...

 

"SELECT Latitude AS departure_lat, Longitude AS depatrure_lng FROM airports INNER JOIN rosters ON airports.IATA = rosters.Dep WHERE rosters.Dep = 'BGY'

AND

SELECT airports.Latitude AS arrival_lat, airports.Longitude AS arrival_lng FROM airports INNER JOIN rosters ON airports.IATA = rosters.Arr WHERE rosters.Arr = 'CRL'";

 

So basically, I am trying to get the departure and destination latitude and longitudes from an airport where departure = BGY and arrival = CRL using one query.

 

Thanks for any help!

[Barand]

try

SELECT 
  d.Latitude AS departure_lat
, d.Longitude AS depatrure_lng 
, a.Latitude AS arrival_lat
, a.Longitude AS arrival_lng 
FROM rosters 
INNER JOIN airport a ON a.IATA = rosters.Arr 
INNER JOIN airport d ON d.IATA = rosters.Dep 
WHERE rosters.Arr = 'CRL'
	AND rosters.Dep = 'BGY'

[unistake]

Thanks Barand,

 

although I dont quite have the grasp of it. I knew I should be using the 'AS'.

 

This is what I have so far:

$sql = "SELECT * FROM rosters WHERE Code = '$Code' AND SectorDate >= '$today' AND Arr != '' GROUP BY RostersID ASC";
$result = mysqli_query($cxn,$sql) or die ("Cant do it!");
while($row=mysqli_fetch_assoc($result)) {
	$sql = "SELECT 
	  d.Latitude AS departure_lat
	, d.Longitude AS depatrure_lng 
	, a.Latitude AS arrival_lat
	, a.Longitude AS arrival_lng 
	FROM rosters 
	INNER JOIN airport a ON a.IATA = rosters.Arr 
	INNER JOIN airport d ON d.IATA = rosters.Dep 
	WHERE rosters.Arr = '{$row['Arr']}'
		AND rosters.Dep = '{$row['Dep']}'";
	echo $sql;
	$result = mysqli_query($cxn,$sql)
		or die ("no can do");
	while($row=mysqli_fetch_assoc($result)) {
		echo $row['departure_lat'];
	}
}

`rosters` table =

RostersID      Dep      Arr       SectorDate

1                     BGY      CRL      2016-01-22

2                     CRL      BGY      2016-01-22

 

`airports` table =

AirportsID     IATA      Latitude          Longitude     

1                     BGY     48.5320           -3.2400

2                     CRL     50.3400           -1.4200

[Barand]

Don't run queries inside loops - it eats server resources. Combine the two queries into one.

SELECT 
  d.Latitude AS departure_lat
, d.Longitude AS depatrure_lng 
, a.Latitude AS arrival_lat
, a.Longitude AS arrival_lng 
FROM rosters 
INNER JOIN airport a ON a.IATA = rosters.Arr 
INNER JOIN airport d ON d.IATA = rosters.Dep 
WHERE rosters.Arr = 'CRL'
	AND rosters.Dep = 'BGY';
	AND Code = '$Code' 
	AND SectorDate >= '$today' 
ORDER BY RostersID 

[unistake]

hmm, still not working here.

 

I changed a few spelling mistakes above and input 'rosters.' inside a couple of the WHERE clauses.

 

Still get the error 'no can do'

$sql = "SELECT 
  d.Latitude AS departure_lat
, d.Longitude AS departure_lng 
, a.Latitude AS arrival_lat
, a.Longitude AS arrival_lng 
FROM rosters 
INNER JOIN airports a ON a.IATA = rosters.Arr 
INNER JOIN airports d ON d.IATA = rosters.Dep 
WHERE rosters.Arr = 'CRL'
	AND rosters.Dep = 'BGY'
	AND rosters.Code = '$Code' 
	AND rosters.SectorDate >= '$today' 
ORDER BY rosters.RostersID";
	//echo $sql;
	$result = mysqli_query($cxn,$sql)
		or die ("no can do");
	while($row=mysqli_fetch_assoc($result)) {
		echo $row['departure_lat'];
	}

Edited January 23, 2016 by unistake

[Barand]

try outputting a meaningful, helpful message, like $cxn->error

[unistake]

I have the error,

 

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /customers/5/5/7/website.com/httpd.www/page.php on line 56 Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /customers/5/5/7/website.com/httpd.www/page.php on line 40

$sql = "SELECT * FROM rosters WHERE Code = '$Code' AND SectorDate >= '$today' AND Arr != '' GROUP BY RostersID ASC";
$result = mysqli_query($cxn,$sql) or die ("Cant do it!");
while($row=mysqli_fetch_assoc($result)) {  // LINE 40
	$sql = "SELECT 
	  d.Latitude AS departure_lat
	, d.Longitude AS departure_lng 
	, a.Latitude AS arrival_lat
	, a.Longitude AS arrival_lng 
	FROM rosters 
	INNER JOIN airports a ON a.IATA = rosters.Arr 
	INNER JOIN airports d ON d.IATA = rosters.Dep 
	WHERE rosters.Arr = 'ALC'
		AND rosters.Dep = 'MAN'
		AND rosters.Code = '$Code' 
		AND rosters.SectorDate >= '$today' 
	ORDER BY rosters.RostersID";
	$result = mysqli_query($cxn,$sql)
		or $cxn->error;
	while($row=mysqli_fetch_assoc($result)) {  //LINE 56
		echo $row['departure_lat'];
	}
}

[Barand]

you were supposed be outputting $cxn_error instead of "no can do"

[unistake]

i did as above. you the errors i got i showed above.

[Barand]

Those errors are not from $cxn_>error.

 

Where you had

or die("no can do");

change to

or die($cxn->error);

Is that simple enough?

[unistake]

sorry i missed one error!

 

Now i have this, The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

[Barand]

Change

die ("Cant do it!");

to

die ($cnx->error);

 

Then we can see why the first query is failing.

 

You should not be running that first query any more. I said you should NOT run queries inside loops and you are still doing it. You should just be running the one combined query and process it's results.

 

 

EDIT : How many rows in your tables?

Edited January 23, 2016 by Barand

[unistake]

about 20,000 rows in `rosters`, 260 in `airports`

[Barand]

Those files are not particularly large, so I am baffled by that MAX_JOIN_SIZE error. The default is 18,446,744,073,709,551,615 which is a few more more than 1,352,000,000 (the cartesian product of your query, which is the number of rows returned if every record in the tables was joined with every record in the other tables ie 20000 x 260 x 260).

 

Did you get an error message for that first query?

[unistake]

no that first query is fine.

[Barand]

You use $result for the result of the first query and then use that same variable again inside the loop. Another reason not to be calling queries inside loops.

 

As I said, get rid of that first query and replace it with the second, and just process those results.

 

Are you running on a live host?

[unistake]

I disabled the first query and still have the same error message.

 

I am not yet sure how to put the two queries together but i do not think that is the problem.

[Barand]

I gave you the combined query.

 

Can you attach an SQL dump of those two tables?

[Barand]

And run this query (in phpMyAdmin or similar) and see what the result is

SHOW VARIABLES LIKE 'max_join%';

EG

mysql> SHOW VARIABLES LIKE 'max_join%';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+

[unistake]

max_join_size - 33554432.

 

There are now just over 50,000 rows in the table.

Edited January 23, 2016 by unistake

[Barand]

what about

mysql> show variables like 'sql_big_sel%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
+-----------------+-------+

If it is OFF, try running the query "SET SQL_BIG_SELECTS = 1" before running your query.

[unistake]

sql_big_selects was OFF. You mean to set the big selects to 1 before my php query i am trying to get working?

[Barand]

yes

[Barand]

Where did you get your data? Your coordinates for airport CRL

2                     CRL     50.3400           -1.4200

 

place it in the middle of the English Channel