I have this table myTABLE
ID | ReferenceID
---------------------
1 | NULL
2 | NULL
3 | 1
4 | 1
5 | 2
6 | 2
7 | 5
And I have a recursion function to display the results.
$query = mysql_query("SELECT * FROM myTABLE WHERE ReferenceID =$1 ");
So this query will return
ReturningArray = 3,4
They stop there. No entry have them as a reference.
But
$query = mysql_query("SELECT * FROM myTABLE WHERE ReferenceID =$2 ");
returns
ReturningArray = 5,6
and 5 has a further level.
So how can I check if the ReturningArray has more levels of reference or it just stops there?
If you want just one level of reference check, you can do it with the following SQL:
$dbLink = mysqli_connect(); //host, user, pass etc go here as arguments
$refId = 1; // your ReferenceID
$refIdEscaped = mysqli_escape_string($refId); //always remember to escape input
$query = "SELECT main.*,
CASE WHEN ref.ReferenceID IS NULL THEN 1 ELSE 0 END as recursion_exists
FROM myTABLE main
LEFT JOIN myTABLE ref ON main.ReferenceId = ref.ID
WHERE main.ReferenceID = '{$refIdEscaped}'";
You'll get all data from your table with an additional column (recursion_exists) with values of 1 (if recursion exists) or 0 (if it does not exist).
For multiple recursions you can also do it with sql, adding more LEFT JOINs like in the example above.