Selecting MySql table data into an array using PDO class?

Question

I have found some info here, but can comment to ask additional info. So my problem is: I want to select my data from mySQL. I have two tables: customers (id,name,ak,numeris) prekes (id, customer_id, prek_name, prek_value)

id in both tables is auto incremented.

I try to fill array?

I have only one value passed (customers.id). there are 5 records with same prekes.customer_id.

$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM prekes WHERE customer_id=' . $pirkejas . ''; //$pirkejas = id passed via $_POST.
$q = $pdo->prepare($sql);
foreach ($pdo->query($sql) as $row) {
    //      if ($row['prek_pav'] != '') {
    $prekes = array($row['prek_name'], $row['prek_value']);

    Database::disconnect();

How to fill array $prekes in correct way?

Edit:

I want to print value in my form:

<table class="table-bordered">
<tr>
<td><input class="input-medium" name="prekes[1][pavadinimas]" type="text"  placeholder="Prekė" value=""></td>
<td><input class="input-medium" name="prekes[1][kaina]" type="text"  placeholder="Kaina" value=""></td>
</tr>
<tr>
<td><input class="input-medium" name="prekes[2][pavadinimas]" type="text"  placeholder="Prekė" value=""></td>
<td><input class="input-medium" name="prekes[2][kaina]" type="text"  placeholder="Kaina" value=""></td>
</tr>
<tr>
<td><input class="input-medium" name="prekes[3][pavadinimas]" type="text"  placeholder="Prekė" value=""></td>
<td><input class="input-medium" name="prekes[3][kaina]" type="text"  placeholder="Kaina" value=""></td>
</tr>
<tr>
<td><input class="input-medium" name="prekes[4][pavadinimas]" type="text"  placeholder="Prekė" value=""></td>

I my action does:

$pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO customers (name,pavarde,ak,data, numeris) values(?, ?, ?, ?,?)";
    $q = $pdo->prepare($sql);
    $q->execute(array($name, $pavarde, $ak, date("Y-m-d H:i:s", time()), $numeris));

    $pirkejo_id = $pdo->lastInsertId();

    foreach ($prekes as $preke) {
        //prekiu uzpildymas
        $sql = "INSERT INTO prekes (customer_id,prek_name,prek_value) values(?, ?, ?)";
        $q = $pdo->prepare($sql);
        $q->execute(array($pirkejo_id, $preke['pavadinimas'], $preke['kaina']));
    }

    Database::disconnect();
    header("Location: default.php");

I don't know how to get all values from database,

depends on what you consider to be "the correct way". What do you want the array to look like exactly ? — Tularis
– Tularis, Commented Mar 6, 2014 at 23:32
I updated my question. I am making this array to set values in input forn to edit them and put to database again. — Aipo
– Aipo, Commented Mar 6, 2014 at 23:42

Phil · Accepted Answer · 2014-03-06 23:49:37Z

2

Don't inject values into your SQL queries. Use parameter binding instead.

$q = $pdo->prepare('SELECT id, prek_name, prek_value FROM prekes WHERE customer_id = ?');
$q->execute([$pirkejas]); // if PHP < 5.4, use array($pirkejas)
$prekes = $q->fetchAll(PDO::FETCH_ASSOC);

Now $prekes will be an array of rows where each row is an associative array.

<?php foreach ($prekes as $row) : ?>
<tr>
    <td>
        <input name="prekes[<?= (int) $row['id'] ?>][pavadinimas]"
               value="<?= htmlspecialchars($row['prek_name']) ?>">
    </td>
    <td>
        <input name="prekes[<?= (int) $row['id'] ?>][kaina]"
               value="<?= htmlspecialchars($row['prek_value']) ?>">
    </td>
</tr>
<?php endforeach ?>

edited Mar 6, 2014 at 23:49

answered Mar 6, 2014 at 23:32

Phil

166k25 gold badges265 silver badges269 bronze badges

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

2 Comments

Barmar Over a year ago

How were his quotes messed up? He had an unnecessary ` . ''` at the end, but they didn't hurt anything.

Phil Over a year ago

@Barmar Removed that note. I see two single quotes and I think SQL escaping :)

Barmar · Accepted Answer · 2014-03-06 23:32:52Z

1

PDO has a method that does this for you. Also, you shouldn't substitute variables directly into the query, you should use parameters.

$q = $pdo->prepare('SELECT * FROM prekes WHERE customer_id= :id');
$q->execute(array(':id' => $pirkejas));
$prekes = $q->fetchAll();

answered Mar 6, 2014 at 23:32

Barmar

789k57 gold badges554 silver badges669 bronze badges

8 Comments

Barmar Over a year ago

Fixed it -- it's so easy to get mixed up between PDO and mysqli.

Rahly Over a year ago

I think he meant you are not checking the return value of execute, to see if the query failed. if( $q && $q->execute() ) $prekes = $q->fetchAll();

Barmar Over a year ago

The question has ERRMODE_EXCEPTION set, so I don't think it's necessary to test it.

Rahly Over a year ago

In general it isn't, but ERRMODE_EXCEPTION only throws on an error. Sometime a failed query isn't an error, or at least that has been my experience. i.e. execute returns false, but errorInfo() returns no error codes (looks successful).

Barmar Over a year ago

I think you may be confusing queries that return no rows with queries that get an error.

|

Collectives™ on Stack Overflow

Selecting MySql table data into an array using PDO class?

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related