Mysql – Views with parameters, is it possible

MySQLmysql-5.6parameterview

I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and PHP MySQL native functions which I don't like.

Without more here is one of the queries I want to convert into a view:

SELECT
  COUNT(*) AS 'rec',
  CONCAT(
      IF(agreement_list.ActiveFlag, '', 'Agreement is Inactive.'),
      IF(agreement_type.ActiveFlag, '', 'Agreement Type is Inactive.'),
      IF(distributor.ActiveFlag, '', 'License Distributor is InActive.'),
      IF(agreement_distributor.ActiveFlag, '', 'Agreement Distributor is InActive.'),
      IF(customer.ActiveFlag, '', 'Customer is Inactive.'),
      IF(cf_program_level.ActiveFlag, '', 'Program Level is Inactive.')
  ) AS errormessage,
  IF((agreement_list.ActiveFlag + agreement_type.ActiveFlag + distributor.ActiveFlag + agreement_distributor.ActiveFlag + customer.ActiveFlag + cf_program_level.ActiveFlag) < 6, 1, 0 ) AS error
FROM
  license
  JOIN agreement_list ON (agreement_list.AgreementTypeID = license.AgreementTypeID AND agreement_list.CustomerSiteID = license.CustomerSiteID AND agreement_list.Source = license.Source)
  JOIN customer ON (customer.id = license.CustomerSiteID AND license.source = customer.Source)
  JOIN distributor ON (distributor.DistributorID = license.DistributorID AND license.source = distributor.Source)
  JOIN distributor AS agreement_distributor ON (agreement_distributor.DistributorID = agreement_list.DistributorID AND agreement_list.source = agreement_distributor.Source)
  JOIN agreement_type ON (agreement_type.AgreementTypeID = license.AgreementTypeID AND license.source = agreement_type.Source)
  JOIN cf_program_level ON (cf_program_level.CFProgramLevelID = '{$CFProgramLevelID}' AND license.source = cf_program_level.Source)
WHERE
  license.AgreementTypeID = '{$AgreementTypeID}'
  AND license.CustomerSiteID = '{$CustomerSiteID}'
  AND license.Source = '{$Source}'

$CFProgramLevelID, $AgreementTypeID, $CustomerSiteID, $Source are parameters coming from PHP and that's my only problem. How do I pass a parameter to a view if it's possible?

I am using MySQL 5.6 at the moment.

Best Answer

Seems simple. Build the VIEW without the last 5 lines. Then use those 5 lines when you use the VIEW as if it were a TABLE.

If those JOINs are not "many:one", you will get an inflated COUNT(*).