I need to create a custom query using specific characters from database

Question

What i have is a rather large database of flight numbers based on divisions. the flight number is entered as following eg: CCC-NOC001 where the number breaks down as follows CCC airline Code NOC division Code and 001 actual flight number.

Now i am trying to create a filter that will select flight number from the schedule based on the division code. now to clarify the way the flight numbers are entered into the database is in 2 fields the first being code which is always entered as CCC and the flight number which would be NOC001 etc.

So what i need if it is possible is to strip the numbers from the end of the flight number so that all that is searched for is the NOC or one of the other division codes currently there are 9 separate divisions. And i would like to be able to filter the queries so that if from the drop down list they select NOC then only the flight numbers with NOC will show in the table.

CCC-NOC001 CCC-NOC002 .... Any help you can give me would be greatly appreciated

Answer 1

Hi, just a suggestion: why didn't you use different mysql columns for all parts-the final result will be simply concat and you'll not have any troubles. MySQL has not preg_replace... Best Regards.

Answer 2

$dbh = new PDO($dsn, $user, $password);
$query = "select * from [TableName] where [ColumnName] like '%NOC%'";
$stmt = $dbh->prepare($query);
$stmt->execute();
if ($stmt->rowCount() > 0) {  
   $resultset = $stmt->fetchAll(PDO::FETCH_ASSOC);
   foreach ($resultset as $row) {
       $flight = $row['ColumnName'];
       $flightNum = str_replace('CCC-NOC', '', $flight);
   }
}

Answer 3

You can use REGEXP in SQL query

SELECT * FROM [Table] WHERE [flighNumber] REGEXP '.{3}-[selected NOC]\d{3}'

.{3} is for the code (CCC) , replace [selected NOC] with the one which is selected by user, \d{3} is for the last three digit code (001, 002 etc).