Issue deleting parent and his sub-categories with PHP

Question

I am trying to convert mysqli code to PDO way but I run into an issue.

It should behave: If deleted main parent delete his sub categories also, so that there are no "orphans" left in database.

The main parents are parent 0 in database, while sub categories are linked with parents ID. This is working mysqli example :

// Delete
if(isset($_GET['delete']) && !empty($_GET['delete'])) {
    $delete_id = (int)$_GET['delete'];
    $delete_id = sanitize($delete_id);
    /* Deleting a parent and its children to avoid orphaned categories in the database. */
    $result = $db->query("SELECT * FROM categories WHERE id = '{$delete_id}'");
    $category = mysqli_fetch_assoc($result);
    if($category['parent'] == 0) {
        $db->query("DELETE FROM categories WHERE parent = '{$delete_id}'");
        header("Location: categories.php");
    }

    $db->query("DELETE FROM categories WHERE id = '{$delete_id}'");
    header("Location: categories.php");
}

What I have tried with PDO way:

//delete Category
if(isset($_GET['delete']) && !empty($_GET['delete'])){
$delete_id = (int)$_GET['delete'];
$delete_id = sanitize($delete_id);

//Deleting sub-categories if parent is deleted
 $sql= $veza->prepare ("SELECT * FROM categories WHERE id = '$delete_id'");
 $result = $sql->execute();
 $category = $result->fetch(PDO::FETCH_ASSOC);
if($category['parent'] == 0){

$sql = "DELETE FROM categories WHERE parent = '$delete_id'";
$sql->execute();
 }

 $dsql=$veza->prepare("DELETE FROM categories WHERE id = '$delete_id'");
 $dsql->execute($_GET);
  header("location: categories.php");
}

I can't find the solution.

I have Uncaught Error: Call to a member function fetch() on boolean

You will need to fetch from the statement object. Execute will return boolean value. In your case it ia the `$sql` var — frz3993, Mar 20 '18 at 19:32
If I am doing it correctly this gives me Uncaught Error: Call to a member function execute() on string , inside if code block — Lortnok, Mar 20 '18 at 19:40
Possible duplicate of [Call to a member function on a non-object](https://stackoverflow.com/questions/54566/call-to-a-member-function-on-a-non-object) — CBroe, Mar 20 '18 at 19:40
Because after that you replaced the var with string and directly execute it. It goes like this, prepare -> bind parameters -> execute -> fetch. — frz3993, Mar 20 '18 at 19:42

score 0 · Answer 1 · answered Mar 20 '18 at 19:36

0

Try google prepared statement.

You have boolean in the $result

Usage:

$prep = $conn->prepare("SELECT * FROM table WHERE id=?"); The ? is not replaced!

Then use: $prep->bind_params('i', $id); $prep->execute() And the result: $result = $prep->get_result(); Check if it is not false.

answered Mar 20 '18 at 19:36

Adam Perinay

35
4

after trying to do code line : $sql ->bind_params('i','$id'); I get : Fatal error: Uncaught Error: Call to undefined method PDOStatement::bind_params(). – Lortnok Mar 20 '18 at 19:56
params('i','$delete_id') if I understood correctly it replaces with the '?' after binding – Lortnok Mar 20 '18 at 20:10
Well, you need to have established connection to the DB via PDO (`$conn = new mysqli(host, usr, pass, db)`), Then need to call $conn for preparing and then call var with that prepared statement. Next, do not use ' ' when you are parsing arguments to the bind_params (except that first one, which is definition of var types) – Adam Perinay Mar 20 '18 at 20:36
I am using PDO way, not mysqli (the above mysqli code is example that works on mysqli way and below is my code), I have established connecting inside configuration.php with PDO way, and included it in my categories.php (file that I work on now). – Lortnok Mar 20 '18 at 20:48

score 0 · Accepted Answer · answered Mar 20 '18 at 21:05

I found the solution by doing :

//delete Category
if(isset($_GET['delete']) && !empty($_GET['delete'])){
$delete_id = (int)$_GET['delete'];
$delete_id = sanitize($delete_id);

//Deleting sub-categories if parent is deleted
$sql= $veza->prepare ("SELECT * FROM categories WHERE id = $delete_id");
$sql->execute(array('delete_id' => $delete_id));
$category = $sql->fetch(PDO::FETCH_ASSOC);
if($category['parent'] == 0){

$sql= $veza->prepare("DELETE FROM categories WHERE parent = $delete_id");
$sql->execute();

}

$dsql=$veza->prepare("DELETE FROM categories WHERE id = '$delete_id'");
$dsql->execute($_GET);
 header("location: categories.php");


}

Issue deleting parent and his sub-categories with PHP

2 Answers2