这个连接语句有什么问题？

Question

I'm working with a MySQL server Server version: 5.6.42-cll-lve - MySQL Community Server (GPL) and I'm having some major issues with CONCAT()

       public function get_urls() {
        // Create query
        $query = "SELECT a.Name, a.PrimaryVersion, e1.URL
        FROM " . $this->table . " a
        INNER JOIN
        FunnelsFlows b ON a.Funnel_ID = b.Funnel_ID
        INNER JOIN
        BackendFlows c ON b.BackendFlow_ID = c.BackendFlow_ID
        INNER JOIN
        BackendLevels d ON CONCAT(c.Level, :lv) = d.BackendLevel_ID
        LEFT JOIN
        BackendPages e1 ON d.Upsell = e1.BackendPage_ID
        LEFT JOIN
        BackendPages e2 ON d.Downsell1 = e2.BackendPage_ID
        LEFT JOIN
        BackendPages e3 ON d.Downsell2 = e3.BackendPage_ID
        WHERE                    
        a.Funnel_ID = :fid 
        LIMIT 0,1";
        // Prepare statement
        $stmt = $this->conn->prepare($query);
        // Bind ID
        $stmt->bindParam(':fid', $this->fid, PDO::PARAM_INT);
        $stmt->bindValue(':lv', $this->lv, PDO::PARAM_STR);
        // Execute query
        $stmt->execute();

Running this code throws the following error: PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.Level' in 'on clause' in ...

The entire database structure you can view here:
https://app.sqldbm.com/MySQL/Share/wBAF2JMRFFSoPPjIPdYZc0GFrngIE8md_DYjF4jNYw0

Answer 1

Your query cannot work like that. You are trying to concat a field content CONCAT(c.Level, :lv) with a parameter :lv. This results in a string if there were a column Level. However, you rather want to build a dynamic column name like Level1 / Level2 aso.

Unfortunately this cannot be done by parameters. This is owing to the fact, that query plans are prepared. Changing column names at execution time would alter the query plan.

You either need to build dynamic SQL in such a case or write a complex ON clause.

The ON clause:

ON 
(
  (:lv = 1 AND c.Level1 = d.BackendLevel_ID)
  OR
  (:lv = 2 AND c.Level2 = d.BackendLevel_ID)
  -- and so on
)

Dynamic SQL:

$sql = 'ON (c.Level' . inval($this->lv) . ' = d.BackendLevel_ID)'

Since PHP's intval function is considered to be safe, I would prefer the latter example in order to improve SQL performance.