convert the following php to SQL only

Question

function fetch_Actor($ActorID){
    global $pdo;
    $stmt = $pdo->prepare('SELECT * FROM actors WHERE ActorID = ?');
    $stmt->execute([$ActorID]);
    return $stmt->fetch();
}
$actors = fetch_actor($actor['ActorID']);   
foreach ($actors as $actor){
    if($actor['ParentID'] > 0){
    $actor = fetch_actor($actor['ParentID']);
    }
}

The code above is something I wrote to override duplicate actors in my database. I believe what I am trying to do in php can be done with one statement in SQL. I just do not know where to begin. I am looking for some guidance on how to do this in one sql statement and get rid of my unnecessary foreach() loop to fix stuff.

I am trying to include in my sql statment this condition:

if ParentID > 0 then fetch results using ParentID
else fetch results using ActorID

Table Structure

Actors 
ActorID    |    Name    | ParentID
      1    |    Abel    | 0
      2    |    John    | NULL
      3    |    Abel    | 1

What I want to be returned if $ActorID = 1

Actors 
ActorID    |    Name    | ParentID
      1    |    Abel    | 0

What I want to be returned if $ActorID = 2

Actors 
ActorID    |    Name    | ParentID
      2    |    John    | NULL

What I want to be returned if $ActorID = 3

Actors 
ActorID    |    Name    | ParentID
      1    |    Abel    | 0

I have tried this, but this does not seem to work:

IF((SELECT ParentID FROM actors WHERE ActorID = ?) > 0) 
 SELECT * FROM actors WHERE ParentID = ? 
ELSE 
 SELECT ParentID FROM actors WHERE ActorID = ?

We can't see your table structure/data. Do you just want to establish a backup value (or default) if one is missing? How about something like this? https://stackoverflow.com/a/34758622/2943403 — mickmackusa, Oct 21 '17 at 22:50
What if there is a long chain of parents, should we follow it? Something like: 4 Abel 3 ? From the code at the top it looks like answer is yes. — Elhana, Oct 21 '17 at 23:25
@Elhana I made sure that the parent stays a parent. my scenario has only child and parent. — syrkull, Oct 21 '17 at 23:26
@shnisaka please disambiguate your table data, so that your question is clear about `Name` when `ActorID` equals `1` and `3`. (just give `ActorID` `3` a different `Name` value) If you found my linked answer "helpful" you probably know what to do. — mickmackusa, Oct 21 '17 at 23:49

Elhana · Answer 1 · 2017-10-21T23:40:15.797

1

Probably not ideal, but should work:

SELECT a.* 
FROM actors a 
inner join (SELECT ParentID,ActorID FROM actors WHERE ActorID = ?) q 
on (((q.ParentID = 0 or q.ParentID is Null) and a.ActorID = q.ActorID) 
OR (q.ParentID > 0 and a.ActorID = q.ParentID))

http://sqlfiddle.com/#!9/a5a228/7

p.s. unlike the code it is not recursive, but accorsing to comments it is not required.

edited Oct 21 '17 at 23:40

answered Oct 21 '17 at 23:37

Elhana

309
3
9

Your code works thanks! I found an easier solution though =) – syrkull Oct 21 '17 at 23:39
Well, I'm not sure if mysql has something like oracles CONNECT BY. Whatever works for you :) – Elhana Oct 21 '17 at 23:47

score 1 · Accepted Answer · answered Oct 22 '17 at 00:03

This is another way without the UNION - LIMIT technique:

SELECT * FROM actors WHERE (actorid=? AND parentid<1) OR actorid=(SELECT parentid FROM actors WHERE actorid=?)

SQLFiddle Demo

This assumes there is no need to use LIMIT because there can logically only be one row in the resultset.

syrkull · Answer 3 · 2017-10-21T23:57:29.327

0

The UNION statement probably works best for this as suggested by @mickmackusa:

SELECT * FROM Actors WHERE ActorID = 1 AND (ParentID = 0 OR ParentID IS NULL)
 UNION 
SELECT * FROM Actors WHERE ActorID = (SELECT ParentID FROM Actors WHERE ActorID = 1)

http://sqlfiddle.com/#!9/a5a228/10/0

edited Oct 21 '17 at 23:57

answered Oct 21 '17 at 23:40

syrkull

2,295
4
35
68

convert the following php to SQL only

3 Answers3