Question 8106 in PHP Versions Question posted in
You can find a list of all versions here.

How to convert CTE to normal query? – PHP Versions

How can I convert this to normal query?

WITH cte AS (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test
)
SELECT agentID
FROM cte
WHERE flag = 3;

I need to convert this because I think mariadb is not compatible with cte. I am not really familiar with cte too and I don’t have any idea how to break this down to normal sql query in php.

UPDATE:

I tried doing this to run the cte

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT agentID, bonus FROM (WITH cte AS (
    SELECT DISTINCT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM sample_tbl
)) where agentID = '10710' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>

but it is not working, the result shows

"Fatal error: Uncaught mysqli_sql_exception: You have an error in your
SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ‘) where agentID = ‘10710’ &&
flag = 3′ at line 7 in C:xampphtdocstryindex.php:16 Stack trace:
#0 C:xampphtdocstryindex.php(16): mysqli->query(‘SELECT agentID,…’) #1 {main} thrown in C:xampphtdocstryindex.php on
line 16"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Update Again:

    It is now working for me, here is my final code:

    <?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT DISTINCT agentID FROM (SELECT DISTINCT agentID, 
SUM(bonus > 0) OVER (PARTITION BY agentID 
                     ORDER BY `year` * 12 + `month`
                     RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag from sample_tbl) as cte where agentID = '61599' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>

  2. 0

    Indeed MariaDB is compatible with CTEs, however if you don’t want to deal with ctes for whatever reason, you can always transform it into a subquery:

    SELECT agentID
FROM (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test ) agents_with_summed_bonus
WHERE flag = 3;

    If this query, in place of the one built with cte, doesn’t work for you, then it means that you’re initial query has some mistakes in relation of your tables.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search