I'm trying to print from a db the categories in a hierarchy order. I have developed a mysql code that sort of works in dbfiddle (https://dbfiddle.uk/_oUW-eW_), but I am not able to make it run in PHP.
The mysql code is the following:
with recursive cteBottomToTop
AS (select id, name, title, parentid
from $treedb
where id = $start
union all
select c.id, c.name, c.title, c.parentid
from $treedb c
inner join cteBottomToTop cte
on c.id = cte.parentid)
select *
from cteBottomToTop
The PHP code used is the following:
$sqltree = ("with recursive cteBottomToTop
AS (select id, name, title, parentid
from $treedb
where id = $start
union all
select c.id, c.name, c.title, c.parentid
from $treedb c
inner join cteBottomToTop cte
on c.id = cte.parentid)
select *
from cteBottomToTop");
$result = mysql_query($sqltree);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "n";
$error = $error.$message;
}else{
$number = mysqli_num_rows($result);
if ($number < 1) {
$error = $error."<p>Problemi nel caricare dal DB_tree</p>";
}else{
while ($row = mysqli_fetch_array($result)) {
$name_print[] = $row["name"];
$title_print[] = $row["title"];
$parentid_print[] = $row["parentid"];
}
}
}
The code returns the following error:
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive cteBottomToTop AS (select id, name, title, parentid from ' at line 1n
Please note that the version of my MySql server is 5.7.30
Any help?
