Question: I have a dataframe which contains post and comments.
Every comment has an id and a parent id (identifies the comment or post which the comment was a response to).
Posts only have an id, since they don't answer to anything.

| submission | id | parent id |
| --- | --- | --- |
| post1 | 1 |  |
| comment1 | 2 | 1 |
| comment2 | 3 | 1 |
| comment3 | 4 | 2 |
| comment4 | 5 | 4 |
| post2 | 6 |  |
| comment5 | 7 | 6 |

I would like to retrieve the id of the original post for every comment and obtain something like that:

| submission | id | parent id | ancestor id |
| --- | --- | --- | --- |
| post1 | 1 |  |  |
| comment1 | 2 | 1 | 1 |
| comment2 | 3 | 1 | 1 |
| comment3 | 4 | 2 | 1 |
| comment4 | 5 | 4 | 1 |
| post2 | 6 |  |  |
| comment5 | 7 | 6 | 2 |

to do so I tried to loop from the end of the dataframe to the beginning, iteratively tracing back the parent\_id of the parent\_id until I found an empty parent\_id cell.
On the test dataframe it works, but on the main one is too slow. Is there a way to make it more efficient?

Here my original code:

```
#creating a column for the id of the original post
df["ancestor"] = df.id
#obtaining the id of the original post for every comment
for i in reversed(range(len(df.id))): #looping trough the comments
  id = df["parent_id"][i] #variable to initialize the future loop
  parent = id
  while parent != "": #only looping trough comments
    df.ancestor[i] = id
    parent = df.parent_id[df.id == id].values[0]
    id = parent

```

Answer: 
You can use placehoders on prepare statements, this is why we use them to prevent sql injection

One other thing never use column names as variables names, databases can not differentiate

```
DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode_ VARCHAR(20), contractCode_ VARCHAR(20)) 
BEGIN
    SET @t1 = "SELECT * FROM InsuranceContract 
               WHERE enquiryCode = ? 
                     AND contractCode = ?;";
  PREPARE param_stmt FROM @t1;
  SET @a = enquiryCode_;
 SET @b = contractCode_;

  EXECUTE param_stmt USING @a, @b;
  DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

```
When you say

```
WHERE enquiryCode = enquiryCode

```

you compare that named column to itself. The result is true always (unless the column value is NULL).

Change the names of your SP's parameters, so you can say something like

```
WHERE enquiryCode_param = enquiryCode

```

and things should work.

Notice that you have no need of a MySql "prepared statement" here. In the MySql / MariaDb world prepared statements are used for dynamic SQL. That's for constructing statements within the server from text strings. You don't need to do that here.