How to print PDO CRUD Statements and Values of bindParams -PHP PDO

Question

I am new to PDO PHP. I have created a post data function in order to achieve the insertion functionality. Here is my code.

public function PostData($conn, $table, $data)
    {
        try {
            $query = "INSERT INTO " . $table . "(";
            $arraylen = count($data);
            $keyloopcount = 0;
            foreach ($data as $key => $values) {
                $query .= $key;
                if ($keyloopcount != $arraylen - 1) {
                    $query .= ",";
                }
                $keyloopcount++;
            }

            $valloopcount = 0;
            $query .= ") VALUES (";
            foreach ($data as $key => $values) {
                $query .= "':" . $key . "'";
                if ($valloopcount != $arraylen - 1) {
                    $query .= ",";
                }
                $valloopcount++;
            }

            $query .= ")";

            $stmt = $conn->prepare($query);

            foreach ($data as $key => &$values) {
                $stmt->bindParam(':'.$key, $values, PDO::PARAM_STR);
            }


            if ($stmt->execute()) {
                $stmt->debugDumpParams();
                echo "me";
            } else {
                echo "die";
            }
        } catch (PDOException $ex) {
            echo $ex->getMessage();
        }
    }

When I execute this query. It doesn't show any exceptions. but when I look into my Database. It shows inserted records as follows.

I am wondering if there is a way I can debug what are the values in the binded params. I have also looked for $stmt->debugDumpParams(); as shown in the code. But it doesn't shows the values of params.

Need Guidance. TIA.

Does this answer your question? [In PHP with PDO, how to check the final SQL parametrized query?](https://stackoverflow.com/questions/1786322/in-php-with-pdo-how-to-check-the-final-sql-parametrized-query) — CBroe, Oct 14 '21 at 07:45
NOPE! As mentioned earlier, I am working on a live server hence can not modify the php.ini file. further tried the other solution and it didn't work. — Asad Mehmood, Oct 14 '21 at 07:46
_"As mentioned earlier, I am working on a live server"_ - mentioned where? Not inside the current question above. — CBroe, Oct 14 '21 at 07:57
Looks like the problem is that you are building your query the wrong way. You are wrapping the placeholder names in single quotes, making them into _text literals_. — CBroe, Oct 14 '21 at 07:58
Step one... don't work on a live server. Download the non-working code and try to get it working locally instead, where you can do proper debugging and testing. — M. Eriksson, Oct 14 '21 at 08:03
Not sure what you mean by your comment. 1. I haven't up or downvoted this question at all. 2. Debugging code on a live server should be avoided when ever possible since it's easy to cause more harm that way. The issue you had should be pretty straight forward to test and debug locally, where you can change your ini-file and output values to make debugging _much_ easier and safer. That might also have been faster than posting here and asking us to read through and debug your code for you. That's all my comment said. — M. Eriksson, Oct 14 '21 at 08:15
@MagnusEriksson Sorry, I mistakenly posted the comment here. — Asad Mehmood, Oct 14 '21 at 08:26

score 1 · Accepted Answer · answered Oct 14 '21 at 08:06

I sorted it out. I was making a silly mistake. The solution was so simple and easy.

Solution The problem was lying in the part of the code.

foreach ($data as $key => $values) {
                $query .= "':" . $key . "'";
                if ($valloopcount != $arraylen - 1) {
                    $query .= ",";
                }
                $valloopcount++;
            }

Changed the code to:

foreach ($data as $key => $values) {
                $query .= ":" . $key;
                if ($valloopcount != $arraylen - 1) {
                    $query .= ",";
                }
                $valloopcount++;
            }

Resultant Query Before Amendment:

INSERT INTO cmp_categories(cat_name,cat_slug,cat_desc) VALUES (':cat_name',':cat_slug',':cat_desc')

Resultant Query AfterAmendment:

INSERT INTO cmp_categories(cat_name,cat_slug,cat_desc) VALUES (:cat_name,:cat_slug,:cat_desc)

More Clearly,

Removed ' from VALUES clause. ':cat_desc' => :cat_desc

How to print PDO CRUD Statements and Values of bindParams -PHP PDO

1 Answers1