shorthand PDO query

Question

Currently to perform a query with PDO, I use the following lines of code:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

And after some research, I found a shorter way of executing the same command:

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$stmt_test->execute([$id])->fetchAll(PDO::FETCH_ASSOC);
$result = $stmt_test->fetchAll(PDO::FETCH_ASSOC);

From there I thought I could possibly make it even shorter with the following code:

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$result = $stmt_test->execute([$id])->fetchAll(PDO::FETCH_ASSOC);

But I get the following error:

Fatal error: Call to a member function fetchAll() on a non-object in /home/.../index.php on line 20

QUESTION: Why am I getting this error? From my understanding, $stmt_test->execute([$id]) should be executing first, then the result of that would execute the ->fetchAll(PDO::FETCH_ASSOC) and from there return the array to $result, but since the error is happening, something must be flawed in my logic. What am I doing wrong? Also, does anyone know a better shorthand method to perform the previous query?

Possible duplicate of [PDO - Call to a member function fetch() on a non-object](http://stackoverflow.com/questions/12126193/pdo-call-to-a-member-function-fetch-on-a-non-object) — johannes, May 28 '16 at 23:23
@johannes Even though the error has a similar name, the situations in which the error is caused is different. — Webeng, May 28 '16 at 23:26
Possible duplicate of [PDO: Call to a member function fetch() on a non-object?](http://stackoverflow.com/questions/3349612/pdo-call-to-a-member-function-fetch-on-a-non-object) — miken32, May 29 '16 at 02:38

Your Common Sense · Accepted Answer · 2020-11-10T07:59:16.080

7

So you've got an answer for the question "Why I am getting this error", but didn't get one for the "shorthand PDO query".

For this we will need a bit of a thing called "programming".

One interesting thing about programming is that we aren't limited to the existing tools, like with other professions. With programming we can always create a tool of our own, and then start using it instead of a whole set of old tools.

And Object Oriented Programming is especially good at it, as we can take an existing object and just add some functionality, leaving the rest as is.

For example, imagine we want a shorthand way to run a prepared query in PDO. All we need is to extend the PDO object with a new shorthand method. The hardest part is to give the new method a name.

The rest is simple: you need only few lines of code

class MyPDO extends PDO
{
    public function run($sql, $bind = NULL)
    {
        $stmt = $this->prepare($sql);
        $stmt->execute($bind);
        return $stmt;
    }
}

This is all the code you need. You may store it in the same file where you store your database credentials. Note that this addition won't affect your existing code in any way - it remains exactly the same and you may continue using all the existing PDO functionality as usual.

Now you have to change only 2 letters in PDO constructor, calling it as

$conn = new MyPDO(...the rest is exactly the same...);

And immediately you may start using your shiny new tool:

$sql = "SELECT * FROM myTable WHERE id = :id";
$result = $conn->run($sql, ['id' => $id])->fetchAll(PDO::FETCH_ASSOC);

Or, giving it a bit of optimization,

$result = $conn->run("SELECT * FROM myTable WHERE id = ?", [$id])->fetchAll();

as you can always set default fetch mode once for all, and for just a single variable there is no use for the named placeholder. Which makes this code a real shorthand compared to the accepted answer,

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$stmt_test->execute([$id]);
$result = $stmt_test->fetchAll(PDO::FETCH_ASSOC);

and even to the best answer you've got so far,

$result = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$result->execute([$id]);

not to mention that the latter is not always usable, as it fits for getting an array only. While with a real shorthand any result format is possible:

$result = $conn->run($sql, [$id])->fetchAll(); // array
$result = $conn->run($sql, [$id])->fetch(); // single row
$result = $conn->run($sql, [$id])->fetchColumn(); // single value
$result = $conn->run($sql, [$id])->fetchAll(PDO::FETCH_*); // dozens of different formats

edited Nov 10 '20 at 07:59

answered May 29 '16 at 04:42

Your Common Sense

156,878
40
214
345

lol point well taken, that was a very complete answer and no doubt deverse the honorable green checkmark – Webeng May 29 '16 at 06:38
1

Thank you. This mark is actually not for me, but for the other devs looking for the shorthand PDO function. As, I suppose, my solution is the only one that really answers the question, not just admits the problem. – Your Common Sense May 29 '16 at 06:43
Creating a wrapper class for PDO seems like overkill imo, definitely for creating one method which simply combines two methods into one. The answer is that there is no faster way executing prepared statements using PDO. It's a big answer tho, have to agree with you on that one. – JasonK May 29 '16 at 09:21
1

@JasonK it is not a wrapper class. It is a class extension - a thing that is as natural for OOP as a breathing for a human being. Would you call a breathing an overkill? – Your Common Sense May 29 '16 at 11:45
@YourCommonSense Would you happen to know a lot about transactions in pdo / mysql? The following question is in dire need of a pdo wizard: http://stackoverflow.com/questions/37557110/understanding-pdo-mysql-transactions – Webeng May 31 '16 at 23:34
@YourCommonSense i usually upvote correct answers and that what i have considered yours, next time i will stick more to what the OP wanted, for example in this question he asked if he/she could shorten the above query. I failed of course and practically answered the same query the OP posted. You think that you are inside the subject presenting a class.? Is your idea of a shortening solution to make the 3 lines of code 30? To write using classes etc is something the OP maybe should have done, but really is that your answer for shortening? Try not to be arrogant dude it is not good – Jun 01 '16 at 05:59
@PeterDarmis There are three wrong premises that make your conclusions wrong: 1. The OP wanted to shorten the way to run *all* their queries, not this particular one. 2. First time you were not only posed the same code, but the same code that *fails*. 3. Nowhere there are 30 lines in my solution but only 9, which - most important - have to be written only **once.** Given it is saving you 2 lines per each query, you will start getting profit after writing only 5 queries. All other queries will be shorten for free. This is called programming. – Your Common Sense Jun 01 '16 at 07:22
this is an amazing answer – Dan Nov 10 '20 at 07:44
Never extend PDO. Dot. Cannot change class of an instance. So if I already have a PDO instance I cannot use that method. – slepic Nov 26 '20 at 05:44

score 4 · Answer 2 · answered May 28 '16 at 23:27

4

$stmt_test->execute([$id]) returns a boolean value. That mean that

$result = $stmt_test->execute([$id])->fetchAll(PDO::FETCH_ASSOC);

isn't valid. Instead you should do

$stmt_test->execute([$id]);
$result = $stmt_test->fetchAll(PDO::FETCH_ASSOC);

answered May 28 '16 at 23:27

Federkun

36,084
8
78
90

perfect answer. I'll accept this answer when the time limitation expires. Didn't realise the `$stmt_test` was returning a boolean, hence `fetchAll` was being applied to that return value and not `$stmt_test` itself. – Webeng May 28 '16 at 23:30
1

@Webeng I wouldn't call this answer perfect as nowhere it makes your code any shorter. – Your Common Sense May 29 '16 at 05:32

score 1 · Answer 3 · answered May 28 '16 at 23:30

1

The error you're getting comes form the way PDO was designed. PDOStatement::execute() doesn't return the statement, but a boolean indicating success. The shortcut you want therefore isn't possible.

See function definition in http://php.net/manual/en/pdostatement.execute.php

Additionally let me add that forEach() often (not always) is a code smell and takes relatively much memory as it has to store all rows as PHP values.

answered May 28 '16 at 23:30

johannes

15,807
3
44
57

Oh, so would you recommend me changing to `while ($row = $stmt_test->fetch(PDO::FETCH_ASSOC)){...}` so that i don't do `foreach($result as ...)` with my above code? – Webeng May 28 '16 at 23:35
PDOStatement implements traversable, so you can do `foreach ($stmt_test as ...)` (while I must admit, I'm no sure which mode this uses, might be PDO::FETCH_BOTH) with that approach you're always just have the current element in memory and iterate it just once vs. twice – johannes May 29 '16 at 00:49
and which approach is the one that does it twice? xD I'm not sure I understood if it was code that I was doing that is doing it twice and now im really interested in knowing hahah – Webeng May 29 '16 at 01:22
@webeng you can specify the fetch mode at instantiation by specifying the fourth options argument to the constructor as, for example, `[PDO::ATTR_DEFAULT_FETCH_MODE=> PDO::FETCH_ASSOC]` – miken32 May 29 '16 at 02:44
"foreach is a code smell" - so, to get rid of the smell I will stop passing arrays to templates and start echoing HTML right after the query execution. Good advise indeed, will use it from now on. – Your Common Sense May 29 '16 at 06:40
@YourCommonSense, no, not foreach is the issue, fetchAll is and you i.e. might pass the Iterator into your template engine or do your preccessing and then pass the final result or whatever you need. – johannes May 29 '16 at 15:46
To me, it is rather using an iterator from a database layer is a code smell. I would prefer to close all database resources before starting for a template. Especially because no sane amount of data that can be displayed on a regular web page can make any visible impact on the memory consumption. – Your Common Sense Jun 01 '16 at 03:52

JasonK · Answer 4 · 2016-05-29T09:16:36.233

0

I believe PDO's execute() method returns either true or false. As the error already tells you: fetchAll() expects an object. Using three lines of code would be the shortest way.

Another option is to use an ORM like propel, it works really smooth and will save you alot of time.

edited May 29 '16 at 09:16

answered May 28 '16 at 23:29

JasonK

5,214
9
33
61

Answers that recommend a tool will always benefit from the real life example applicable to the OP's needs. Otherwise they will receive the no attention. – Your Common Sense May 29 '16 at 06:58

shorthand PDO query

4 Answers4

Linked