PHP/PDO Select queries

Question

I am using the following queries to select rows from a mysql database using PDO.

I am using this code to select multiple rows:

<?php
        $contact = $pdo_conn->prepare("SELECT * from contacts WHERE company_sequence = :company_sequence AND contactstatus = :contactstatus ");
        $contact->execute(array(':contactstatus' => '', ':company_sequence' => $ticket["company"]));
        ?>
        <select name="contactsequence" id="contactsequence">
        <?php foreach($contact as $contacts) {
            echo '<option value="'.$contacts["sequence"].'" ';
            if($ticket["contact"] == $contacts["sequence"]) {
                echo 'selected="selected"';
            }
            echo '>'.$contacts['forename'].' '.$contacts["surname"].'</option>';
        }
        ?>
        </select>

And this for selecting a single row:

$stmt = $pdo_conn->prepare("select * from tickets where ticketnumber = :seq ");
$stmt->execute(array(':seq' => $_GET["seq"]));
$ticket = $stmt->fetch();

Is the the correct way to run PDO select queries? (Preventing sql injection etc) I have been looking online but I just wanted to double check

Other things aside, you are close, but on the first set you are attempting to loop on the STMT statement. Might need to change that foreach to atleast be foreach ($contact->fetchAll(PDO::FETCH_ASSOC) as $contacts) { /// }. Is this question related to an error? Is it not working? — Rottingham
– Rottingham, Commented Jan 2, 2014 at 23:04
Could I do $contact->fetchAll(PDO::FETCH_ASSOC) then leave my loop as foreach($contact as $contacts) { ... } — charlie
– charlie, Commented Jan 2, 2014 at 23:09
I am very new to PDO so you may have to be patient. I started learning it 2/3 days ago lol :) — charlie
– charlie, Commented Jan 2, 2014 at 23:09
No, the function of ::fetchAll() is to return a set of results. Make it $results = $contact->fetchAll(PDO::FETCH_ASSOC); foreach ($results as $contact) { /// } — Rottingham
– Rottingham, Commented Jan 2, 2014 at 23:10
The documentation online has ample examples to bring you up to speed. us3.php.net/manual/en/pdostatement.fetchall.php — Rottingham
– Rottingham, Commented Jan 2, 2014 at 23:11

Rottingham · Accepted Answer · 2014-01-02 23:18:59Z

1

Charlie,

You can convert to this bit of code for a couple reasons. A: Readable. B: Error Checking

Your foreach(...) will throw an error if you pass it an empty result set. foreach trusts that you are feeding it a legitimate array.

$query = "SELECT *
FROM `contacts`
WHERE `company_sequence` = :companySequence AND
`contactstatus` = :contactStatus";

$stmt = $pdo_conn->prepare($query);
$stmt->execute(array(
    ':contactStatus' => '', 
    ':companySequence' => $ticket["company"])
);

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

if (!$records) {
    // NO RECORDS FOUND
    die('No records found.');
}

foreach ($records as $contact) {
    // Do what you want with each result
}

This is a little cleaner way to do things and provides some very minimal error / no result found checking.

answered Jan 2, 2014 at 23:18

Rottingham

2,6341 gold badge15 silver badges14 bronze badges

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

Comments

jeroen · Accepted Answer · 2014-01-02 23:31:15Z

1

The way you are doing it, is right although I prefer to separate my database stuff from the html output stuff.

You do not have to fetch all rows in a big result set and you can loop directly over the $contact object because the PDOStatement object implements Traversable, making it suitable to loop over with a foreach loop.

One thing I would recommend though, is to add error handling. Using exceptions, you can put your database calls in a try - catch block, making it easy to detect and take action when things go wrong.

answered Jan 2, 2014 at 23:31

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

1 Comment

Rottingham Over a year ago

Thanks for the update @Jeroen! I don't know how many times I've looked at the PDOStatement docs and never picked up on the Traversible. That's handy.

Collectives™ on Stack Overflow

PHP/PDO Select queries

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related