SQL Query - Multiple columns

[HarryMW]

Hi there,

 

This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself.

 

1. Pull all the records from my database.

2. Assign their unique user id's, callsigns and destinations.

3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'.

 

For example if three people are going to Heathrow, then it would return 3 results.

4. Then do a mysql_num_rows of the result.

5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier.

 

All in a loop of course.

 

// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){

// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_destinations = $atc_arrivals_row['planned_destaiport'];
$atc_callysign = $atc_arrivals_row['callsign'];

// Searches the database and selects all records where the callsign is LIKE the destination airport.
$atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'");

// Counts previous query results.
$atc_arrivals_count = mysql_num_rows($atc_arrivals_check);

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}

 

Concerned columns are these:

"cid" - Unique client id.

"atcarrivals" - Number of arrivals from count. (This is the problem.)

"planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow.

 

Thanks.

[kickstart]

Hi

 

Should be possible without the inner select with something like this:-

 

<?php

// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT a.*, COUNT(b.cid) AS matchcount FROM CLIENTS a LEFT OUTER JOIN CLIENTS b ON a.callsign = b.callsign AND b.clienttype = 'ATC' GROUP BY a.*");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery))
{
// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_arrivals_count = $atc_arrivals_row['matchcount'];

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}

?>

 

Unfortunately mysql doesn't support an update based on a select the the table it is updating.

 

All the best

 

Keith

[HarryMW]

Hi

 

Should be possible without the inner select with something like this:-

 

<?php

// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT a.*, COUNT(b.cid) AS matchcount FROM CLIENTS a LEFT OUTER JOIN CLIENTS b ON a.callsign = b.callsign AND b.clienttype = 'ATC' GROUP BY a.*");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery))
{
// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_arrivals_count = $atc_arrivals_row['matchcount'];

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}

?>

 

Unfortunately mysql doesn't support an update based on a select the the table it is updating.

 

All the best

 

Keith

 

Hiya,

 

I've tried it but get the following error: "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/assuranc/public_html/testing/test7.php on line 138"

 

Reffering to this line in the code:

while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery))

 

Regards.

[kickstart]

Hi

 

Replace where is says a.* with the fields you want to bring back (each qualified with the a. , and do both in the field list and the group by clause).

 

All the best

 

Keith

[HarryMW]

Hi

 

Replace where is says a.* with the fields you want to bring back (each qualified with the a. , and do both in the field list and the group by clause).

 

All the best

 

Keith

 

Hi there,

 

I have placed in these:

 

$atc_arrivals_selectquery = mysql_query("SELECT a.callsign, a.planned_destairport, a.cid, a.atcarrivals, COUNT(b.cid) AS matchcount FROM CLIENTS a LEFT OUTER JOIN CLIENTS b ON a.callsign = b.callsign AND b.clienttype = 'ATC' GROUP BY a.callsign, a.planned_destairport, a.cid, a.atcarrivals");

 

Unfortunately, when you run the script it just returns the value: "1", for everything. Which is not correct, I am positive.

[kickstart]

Hi

 

Mmm, I have just noticed you are using a LIKE in the original SQL.

 

That might stop my idea working

 

Can you export out the table declarations and a small bit of data for me to have a play with. Need to know a bit more about what call sign is and how it matches.

 

All the best

 

Keith

[HarryMW]

Hi

 

Mmm, I have just noticed you are using a LIKE in the original SQL.

 

That might stop my idea working

 

Can you export out the table declarations and a small bit of data for me to have a play with. Need to know a bit more about what call sign is and how it matches.

 

All the best

 

Keith

 

I've pm'd you the schema and a typical entry.

[kickstart]

Hi

 

I think this gives you what you want:-

 

<?php

// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT a.* , IFNULL( b.atc_arrivals_count, 0 ) AS atc_arrivals_count
FROM CLIENTS a
LEFT OUTER JOIN (
SELECT CONCAT( planned_destairport, '%' ) AS LikeDest, COUNT( cid ) AS atc_arrivals_count
FROM CLIENTS
WHERE clienttype = 'PILOT'
GROUP BY LikeDest
)b ON a.callsign LIKE b.LikeDest");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery))
{
// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_arrivals_count = $atc_arrivals_row['atc_arrivals_count'];

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}

?>

 

However this strikes me as really nasty. This is doing a JOIN on a LIKE, but I wouldn't be convinced that the partial destination is unique.

 

It rather looks like the table is just a dump of an extract. I would be inclined to try and split it into several different tables if possible, one for each client type and with each table only containing the columns relevant to that client type. I would also try and derive a value for the destination column on the ATC table which misses out the extra characters on the end for the call sign.

 

All the best

 

Keith