[Query] Why isn't this working

[toxicandy]

I have a basic query I was working on with optional search parameters. The query code is:

$query = $db->prepare("SELECT * FROM VolunDB " . $whereClause . " 1 = 1");
   $query->execute();
   $result = $query->fetch(PDO::FETCH_OBJ);
   $table = $result->fname . " " . $result->lname;

and $whereClause equals: WHERE general = General AND fillmore = Fillmore AND

but it is selecting the wrong name from my database. I have 3 test names in my database, two of which have General and Fillmore matching and one with neither. The First entry in the database is the one with neither matching but it is the only one to get pulled. To pull multiple entries do I have to change something with the fetch(PDO::FETCH_OBJ) or is there something else? But the big question is why is the code pulling the only wrong thing in the database when there are others right after it that match the set parameters.

[pritaeas]

Post the entire query you are executing.

[toxicandy]

Here is the early PHP, I am going to condense it into functions as soon as I get the basic query working: http://pastebin.com/Rr2LcDr5

[pritaeas]

Ugh...

You are not using single quotes around your string values.

You really should look into parameter binding for one.

Ha ha ha. That made me laugh. Almost wet myself. Sorry toxicandy. More to do with the "Ugh..."

[toxicandy]

Yeah I am aware I have terrible coding practice right now, I am working on getting better but you have to make mistakes here and there to understand what to do to correct them. I thought about using prepared statements earlier but I kept hitting one snag, it's probably terribly easy for most people but my problem came when thinking about how to handle the binding. This method, as ugly as it is makes a optional where clause based on information inputted from the forum. I could easily write it so it generates the where clause with the parameters that need binded. But the part I can't get past is the part where you bind the parameters, how do you know which to bind, surely if there is no parameter there and you try to bind to it you get an error. I am looking here: http://php.net/manual/en/pdostatement.bindvalue.php

This is the code I've been looking at, I know I can write the statement to make the "WHERE Blah = :Blah" but I don't know how to do the bindValue(:Blah, $Blah, PDO::PARAM_STR); only for the variables in the statement and not any that are left out. I am not sure if any of this makes sense but I am aware that my code could give anyone a good laugh.

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?

I do apologise TA, that was extremely rude of me.

You can take advantage of the anonymous placeholders or even named ones and build up an array for the execute statement...

$whereClause = array();
$bindArray = array();

if(some condition){
    $whereClause[] = '`somefield` = :field1';
    $bindArray[':field1'] = $somevalue; 
}
...more of the same...

$stmtString = 'SELECT name, colour, calories FROM fruit';
if(!empty($whereClause)) $stmtString .= ' WHERE ' . implode(' AND ', $whereClause);

$stmt = $dbh->prepare($stmtString);
$stmt->execute($bindArray); //not sure if it will run with empty array if not, use a conditional.    

[toxicandy]

Yeah, I don't think I would have ever gotten that far without an example like that. I didn't know you could do that with the $whereClause being on a different line then the Select Statement. Thanks, I'll try and see how far I can get.

[toxicandy]

I tried getting it all set up and I think I have it right for the most part. I am getting an error though.
Code: http://pastebin.com/vZ8wnxSK
Error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in /home/petrzilk/public_html/Database/version.php:377 Stack trace: #0 /home/petrzilk/public_html/Database/version.php(377): PDOStatement->execute(Array) #1 {main} thrown in /home/petrzilk/public_html/Database/version.php on line 377

My other question is why are the fields in single quotes too?

$whereClause[] = '`somefield` = :field1';

the somefield has its own set of single quotes surrounding it. are they needed? If so why? I never knew you had to include them.

Ok a little reworking may be in order with your "multiple" fields like county1-6.Will have a better look later on if I have time. The code is very verbose / repetitive - I'm sure that we can cut it down to a fraction of its size.

fields aren't in single quotes, they're in backticks.

[toxicandy]

I assume a function could cut it down, I've tried writing one for this problem but always seem to scrap it an hour in or so because I can't get it figured out.

Maybe something like this...

$whereClause = array();
$countyList = array();
$bindArray = array();

//is the name dist or dis??

$fields = array( 'bhv','mrc','general','evv','allCounties','fillmore','johnson','richardson', 'otoe','seward','butler','gage','lancaster','pawnee','saline','thayer','cass','jefferson','nemaha','polk','saunders','york','aro','clergy','intskills','bus','child','law','cpr','data','security','emergency','computer','mechanical','administration','firstaid','translation','construction','basicclean','foodprep','animalcare','heavy','license1','license2','license3','license4','license5','dist1','dist2','dist3','dist4','dist5'
); 

$counties = array('country1','country2','country3','country4','country5','country6');

foreach($fields as $field)
{
    if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)){
        $whereClause[] = "`$field` = :$field";
        $bindArray[":$field"] =  $san;  
    }
}

foreach($counties as $county)
{
    if($san = filter_input(INPUT_POST, $county, FILTER_SANITIZE_STRING)){
         $countyList[] = ":$county";
         $bindArray[":$county"] = $san;
    }
}

$sql = "SELECT * FROM VolunDB";

if(!empty($countyList)) $sqlC = '`county` IN(' . implode(',', $countyList) . ')';
if(!empty($whereClause)) $sqlW = implode(' AND ', $whereClause);
$stmtString = $sql;
if(isset($sqlC) && isset($sqlW)){
    $stmtString .= ' WHERE ' . $sqlC . ' AND ' . $sqlW; 
}elseif(isset($sqlC)){
    $stmtString .= ' WHERE ' . $sqlC;
}elseif(isset($sqlW)){
    $stmtString .= ' WHERE ' . $sqlW;
}

echo $stmtString;

$dbh = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8', 'petrzilk_dbAdmin', 'xxxxxxxxx');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $dbh->prepare($stmtString);
$stmt->execute($bindArray);

Not able to test.

[toxicandy]

Yeah, I would say I am definetly a noobie coder as compared to you guys. Thanks, I am looking around it because it does no good just to see it but you also need to know how it works if you want to learn. The Counties thing isn't working so I am using the top as a reference and debugging it to get it to work.

[toxicandy]

I fixed the County thing now I am just working on finding the best way to get multiple results, not just one row. There could be hundreds of rows that fit. Is the best way to do that fetchAll? It looks messy so if it is the best way is there a way to format the output?

fetchAll is prob. best. Why is it messy?

[pritaeas]

it looks messy so if it is the best way is there a way to format the output?

You control how it looks by applying HTML tags and CSS styling.

[toxicandy]

So if this is my output:

Array ( [0] => John [1] => Oliver )

can I remove the Array(), [0], [1], and => and just have John and Oliver?

fetchAll code:

$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
print_r($result);

print_r will give you the output of the entire $result array.

The PDO::FETCH_COLUMN will return an array of a single column (field) of all results if you use it with fetchAll The PDO::FETCH_COLUMN is zero-based, so '1' will return an array of all the second field values.

To get the data out as you want it, you have a few options, but the easiest is probably a while or foreach loop:

foreach($result as $firstname)
{
    echo $firstname . "<br />";
}

This is a pretty intensive loop with many potential echo commands. Concatenation of data followed by a single echo outside the loop could be used instead

$list = '';
foreach($result as $firstname)
    $list .= $firstname . "<br />";
}
echo $list;

or alternatively...

echo implode("<br />", $result);

Perhaps you can imagine how to format this in a myriad different ways.

[toxicandy]

So if I wanted to fetch multiple columns I could make more fetchAll(PDO::FETCH_COLUMN, 1);
and then just do something like

foreach($lastName as $lname){
    $list .= $lname . "<br />";
}

paired along with the the the other

foreach($result as $firstname){ ect....}

Is that the best way of doing it? or is there away to keep it in one foreach?

That is an insane way of doing it!

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

Do you need separate arrays for firstname and lastname? That's a bit odd. The entire data is now in the $data variable as an array of arrays, e.g.

array(
    0 => array("firstname"=>"Ben", "lastname"=>"Jones", "nickname"=>"duw"),
    1 => array("firstname"=>"Al", "lastname"=>"Evans", "nickname"=>"snowman")
);

So you can manipulate the output anyway you want with foreach or while or any other loop, e.g.

foreach($data as $person)
{
    echo $person['firstname'] . ' ' . $person['lastname'] . '<br />';
}

or use concatenation (better).