Quick PDO subsitute for mysql commands

[greenace92]

I am trying to get a bascic website up and running but I'm having a problem with the retrievingt of data. In particular substituting bind_result and pulling single pieces of data.

 

With bind_result, I was able to list out individual columnds and echo them where needed.

 

What is the simple equivalent of:

$value = bind_result($value);

This link says there is no equivalent

 

http://stackoverflow.com/questions/18706771/what-is-the-equivalent-of-bind-result-on-pdo

 

Where I'm looking for one single result from a query "SELECT DISTINCT"

 

To simply dump an entire collumn I did

while ( $row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo htmlspecialchars($row['entry']).'<br>'.'<br>'), ENT_QUOTES, 'UTF-8');
}

I need individual rows

 

I know RTFM as people say

 

I'm hoping to get this thing done today but maybe I will have to wait till I thoroughly understand and can substitute the new PDO code.

Edited December 3, 2015 by greenace92

[greenace92]

I think I've got it, I'm using FETCH_COLUMN

 

so

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

Edited December 3, 2015 by greenace92

[maxxd]

What results are you trying to get exactly?

 

You talk about needing rows, but you use a DISTINCT clause - which, of course, can return more than one row in the result set - but you also say you need a single result. And then, fetching a column is the answer? What does the query you're running look like, and what is the desired outcome?

 

Just trying to get at the actual issue at hand...

 

That having been said, if you're just looking to grab all the values of a specific column, have you tried PDO::fetchColumn()? Depending on your actual needs, could be the answer.

[Zane]

I've read your question about 3 times and I still can't figure out what you're asking.

[greenace92]

In this particular case, I was trying to check the existence of an account by doing a simple comparison, my query was:

$stmt = $link->prepare("SELECT DISTINCT company FROM companies where company=:company");
$stmt->bindParam(':company', $test, PDO::PARAM_STR);
$stmt->execute();
$test = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
return $test;

[Jacques1]

That must be the most cumbersome existence check I've ever seen.

 

Why do you want fetchAll() when there's at most one row? Why do you care so much about the fetch style when you just want to know if the result set is empty or not?

 

You just have to check the return value of fetch(). If it's false, there's no row, otherwise there is:

$testCompany = 'foo';

$companyStmt = $databaseConnection->prepare('
    SELECT 1
    FROM companies
    WHERE company = :company
');
$companyStmt->execute([
   'company' => $testCompany
]);
$companyExists = (boolean) $companyStmt->fetch();

var_dump($companyExists);

Or a slightly cleaner approach using an IN query which already yields a boolean:

$testCompany = 'foo';

$companyStmt = $databaseConnection->prepare('
    SELECT :company IN (
      SELECT company
      FROM companies
    )
');
$companyStmt->execute([
   'company' => $testCompany
]);
$companyExists = (boolean) $companyStmt->fetchColumn();

var_dump($companyExists);

[greenace92]

Hey Jacques1,

 

I was curious if the format you have shown is a standard format eg. used by professional developers?

 

That's not meant to be offensive to you, I don't know what the standard is and would like to engrain it for good formatting habits.

 

I'm going to follow through your examples and see the difference.

 

I have a bad habit of "just get it to work"

 

Haven't done much lately these last couple of weeks been vegging out, being unproductive.

 

I forget how the web is this blank canvas waiting for anyone to create. The joy of seeing your services in use by others.

 

Anyway rambling now.

Thanks for your help as always.

[ginerjm]

I really think that if you did RTFM you would have a better mental outlook on how a query works and how various functions enable you to get what you want out of it.  If you think that you can actually become a 'good' programmer without ever availing yourself of the specific knowledge available in the documentation for the tools that you may use in that learning process, you are sorely disillusioned.

[greenace92]

I really think that if you did RTFM you would have a better mental outlook on how a query works and how various functions enable you to get what you want out of it.  If you think that you can actually become a 'good' programmer without ever availing yourself of the specific knowledge available in the documentation for the tools that you may use in that learning process, you are sorely disillusioned.

 

No you are right, I don't know if I should really try to be a web developer, I don't think my heart is in it.

I'm not sure what I actually want to do aside from trying to make money hoping that will solve my problems.

 

It is nice to have a clue about the internet though and not just take it for what it is.

[sn00pers]

Jacques1

What do you use to decide between sending the parameter into the statement via an array in the execute statement vs using bindParam() (Or bindValue() if you don't want a reference)?  Is it creating the variable inside the code/script vs using input from a web form? Is one faster than the other, or one more secure from injection than the other?

bindParam allows you to use PDO constants to specify Int,String, etc, but many times I have caught myself using the wrong one only to see no difference (example using PDO::PARAM_STR when I meant to use PDO::PARAM_INT due to copy/pasting). I think I also read that all parameters going through the execute array are strings, but not sure when that matters if true.

If it's not too off topic, do you mind touching on that?

[Jacques1]

Using bindParam() or bindValue() together with an explicit type is definitely the more correct variant. While it's usually fine to just use strings for all parameters (that's indeed the default type), there are some nasty edge cases.

 

To be honest, the only reason why I use the array variant is because it's a lot shorter. As a compromise, one could keep using arrays but add explicit type casts to the query itself.