PDO error with foreach and SQL

Question

Tried to make a search for items in our database, here's the code.

$SQL = $yhteys->prepare("SELECT *, COUNT(*) AS Maara FROM TuoteTiedot 
INNER JOIN VarastoTuoteLista ON TuoteTiedot.TuoteID=VarastoTuoteLista.TuoteID 
INNER JOIN Varastopaikat ON VarastoTuoteLista.PaikkaID=Varastopaikat.PaikkaID
WHERE '?' LIKE '?' OR '?' LIKE '?'");

$SQL->execute(array($hakuvalinta1, $haku1, $hakuvalinta2, $haku2));
$tiedot = $SQL->fetchAll();
if($SQL->execute(array($haku1, $hakuvalinta1, $haku2, $hakuvalinta2))){
    if($SQL->fetchColumn() > 0){
        $SQL2 = $yhteys->prepare("SELECT * FROM TuoteTiedot 
        INNER JOIN VarastoTuoteLista ON TuoteTiedot.TuoteID=VarastoTuoteLista.TuoteID 
        INNER JOIN Varastopaikat ON VarastoTuoteLista.PaikkaID=Varastopaikat.PaikkaID
        WHERE '?'  LIKE '?' OR '?' LIKE '?'");

        $SQL2->execute(array($haku1, $hakuvalinta1, $haku2, $hakuvalinta2));
        $SQL2->fetchAll();
        foreach($yhteys->execute() as $row){ //this is line 55

            echo $row['Nimi'] + " ";
            echo $row['TuoteSeloste'] + " " ;
            echo $row['Myyntihinta'];

        }
    }
    echo "</table>";        
} else {
    echo 'Nothing found';
}

It gives an error on the page and it looks like this:

Fatal error: Call to undefined method PDO::execute() in (removed address) index.php on line 55

I have no idea what's wrong with it, but apparently the foreach doesn't like PDO or something like that. Kinda new with PDO but other people working on the site wanted to use it so I kinda have to use it too.

I've checked that the query works, and the previous executes work just fine.

Hanky Panky · Accepted Answer · 2016-01-18 10:24:25Z

4

$yhteys->execute

We don't execute PDO objects, we execute only prepared statements. $yhteys is an object of type PDO, you are correctly using prepared statements elsewhere on that object but in your foreach loop you are directly trying to call execute on that very PDO object hence the error.

Most probably you meant this

foreach($SQL2 as $row){

answered Jan 18, 2016 at 10:24

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niko Matikainen Over a year ago

I don't know how to prepare it for the foreach, and have the array bind into the $SQL.

Hanky Panky Over a year ago

You are already putting all rows in $SQL2 with fetchAll so you can just loop over that. And also check @Alex's answer about another issue with your query

Niko Matikainen Over a year ago

Accepted the answer because it solved my biggest problem, the one that gave me the error.

Alex Andrei · Accepted Answer · 2016-01-18 10:27:46Z

3

You are trying to replace column names with parameters, here

WHERE '?'  LIKE '?' OR '?' LIKE '?'
       ^                ^

Table and column names cannot be replaced by parameters.

answered Jan 18, 2016 at 10:27

Alex Andrei

7,2763 gold badges30 silver badges44 bronze badges

6 Comments

Hanky Panky Over a year ago

And Yep that too!, after fixing the described issue.

Niko Matikainen Over a year ago

Okay, how am I supposed to change that then? I tried WHERE ".$hakuvalinta1." LIKE '?' OR ".$hakuvalinta2." LIKE '?'

RiggsFolly Over a year ago

You also do not need quotes around any of the ? parameter markers even if the data is text!

Alex Andrei Over a year ago

@NikoMatikainen what happened when you tried that? did you get an error message?

Niko Matikainen Over a year ago

No error, no return. It didn't echo any of the items at all. EDIT: one of my searches returned "005892.52005892.52005892.52", and it has NOTHING to do with my database. So something is still wrong.

|

RiggsFolly · Accepted Answer · 2016-01-18 11:09:40Z

Nobody seems to have covered all the issues so I will attempt to do so.

First: You cannot use parameters in a query for anything other than data, no table names or column names etc.

If you think about this its quite logical. You issue the query to the database for compilation, optimization and to create an execution plan BEFORE you replace the parameters with data. So the database cannot compile a query if it does not know the table names and column names involved in the query.

You also do not need the single quotes around the '?' a simple ? is enough

So this query will not ->prepare

$SQL2 = $yhteys->prepare("SELECT * FROM TuoteTiedot 
                           INNER JOIN VarastoTuoteLista ON     
                                TuoteTiedot.TuoteID=VarastoTuoteLista.TuoteID 
                           INNER JOIN Varastopaikat ON 
                                VarastoTuoteLista.PaikkaID=Varastopaikat.PaikkaID
                          WHERE '?'  LIKE '?' OR '?' LIKE '?'");

You must use real column names, I dont know what your column names are so this is just an example

$SQL2 = $yhteys->prepare("SELECT * FROM TuoteTiedot 
                           INNER JOIN VarastoTuoteLista ON     
                                TuoteTiedot.TuoteID=VarastoTuoteLista.TuoteID 
                           INNER JOIN Varastopaikat ON 
                                VarastoTuoteLista.PaikkaID=Varastopaikat.PaikkaID
                          WHERE COLUMN1  LIKE ? OR COLUMN2 LIKE ?");

Now we come to your ->fetchAll(). The ->fetchAll returns an array containing all the rows from your result set so you need to put that data into a variable and the loop over that array

Also if you use the parameter PDO::FETCH_ASSOC you will get an assoc array only, the default is an assoc array and a 0 based index as well. In other words the array returned will be double the size. This can matter in large queries.

So use

    $rows = $SQL2->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row){
        echo $row['Nimi'] . " ";
        echo $row['TuoteSeloste'] . " " ;
        echo $row['Myyntihinta'];

    }

I also solved my last problem, which was the echo. echo $row['TuoteSeloste'] + " " ; should be echo $row['TuoteSeloste'] . " " ; since php uses . for strings and not +. Thank you everyone for help. :)

devpro · Accepted Answer · 2016-01-19 05:38:31Z

0

If you want to print all data using foreach loop than you use like that:

$SQL2->execute(array($haku1, $hakuvalinta1, $haku2, $hakuvalinta2));
$data = $SQL2->fetchAll();
foreach($data as $row){ //this is line 55    
    echo $row['Nimi']." ";
    echo $row['TuoteSeloste']." " ;
    echo $row['Myyntihinta'];    
}

edited Jan 19, 2016 at 5:38

answered Jan 18, 2016 at 10:31

devpro

16.1k3 gold badges29 silver badges39 bronze badges

2 Comments

RiggsFolly Over a year ago

Plus check @AlexAndrei Answer for the error in the SQL statement

devpro Over a year ago

yes i have checked and also give him a comment , Alex mentioned one issue, and i mentioned the second one. @RiggsFolly

Collectives™ on Stack Overflow

PDO error with foreach and SQL

4 Answers 4

3 Comments

6 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related