使用PDO类在数组中选择MySql表数据?

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)

我在这里找到了一些信息，但可以评论询问更多的信息。我的问题是，我想从mySQL中选择数据。我有两个表:customer (id,name,ak,numeris) prekes (id, customer_id, prek_name, prek_value)

id in both tables is auto incremented.

两个表中的id都自动递增。

I try to fill array?

我尝试填充数组?

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

我只传递了一个值(customers.id)。有5条记录具有相同的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?

如何正确填充数组$prekes ?

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,

我不知道如何从数据库中获取所有值，

2 个解决方案

#1

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

不要将值注入到SQL查询中。使用参数绑定。

$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.

现在$prekes将是一个行数组，其中每一行都是一个关联数组。

<?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 ?>

#2

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

PDO有这样的方法。此外，不应该将变量直接替换到查询中，应该使用参数。

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

#1