0
\$\begingroup\$

Is it possible to automate the array inside the while loop and automatically assign the key name based on the row so I don't have to spell out each one?

$stmt = $conn->prepare("SELECT * FROM tickets_info WHERE ticket = ?");
$stmt->execute(array($_POST['ticket']));

if ($stmt->rowCount()) {

    while ($row = $stmt->fetch()) {

        $ticket = [
            'name'      => $row['name'],
            'company'   => $row['company'],
            'email'     => $row['email'],
            'phone'     => $row['phone'],
            'address'   => $row['address'],
        ];

    }

}
\$\endgroup\$
2
\$\begingroup\$

There is no identifiable difference between your table's column names and your preferred keys, but if you want to alter the keys, just build that into your SELECT clause -- this is what "aliases" are for.

Suppose you wanted to capitalize each column name, here's how that could look:

$stmt = $conn->prepare("SELECT name AS Name,
                               company AS Company,
                               email AS Email,
                               phone AS Phone,
                               address AS Address
                        FROM tickets_info
                        WHERE ticket = ?");
$stmt->execute([$_POST['ticket']]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

Note, I am not using a loop. Your snippet is overwriting itself in the loop, so I am interpreting that to mean that ticket is a PRIMARY/UNIQUE column.


If you want to extract all columns from your table and use the column names as keys, you don't need to change your original query.

$stmt = $conn->prepare("SELECT * FROM tickets_info WHERE ticket = ?");
$stmt->execute([$_POST['ticket']]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I meant to mark your solution as accepted as the second answer works as I intended. Thanks! \$\endgroup\$ – user-3857385 Jan 17 at 21:09
1
\$\begingroup\$

Moving data from one array to another just makes no sense. As $row variable already contains the data you need you can use the fetch() result right away.

$stmt = $conn->prepare("SELECT * FROM tickets_info WHERE ticket = ?");
$stmt->execute(array($_POST['ticket']));
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

This way you can do :


$stmt = $conn->prepare("SELECT * FROM tickets_info WHERE ticket = ?");
$stmt->execute(array($_POST['ticket']));

if ($stmt->rowCount()) { 
    while ($row = $stmt->fetch()) {
      foreach($row as $key => $value) {
        $ticket[$key] = $value;
      }
    }

}
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ @user I am downvoting this one because $rows[0] doesn't exist when you call array_keys() on it. This code simply will not work. Even if it did, what sense would it make to manually feed the column names to the output array as keys when that can be done normally by fetching the result set associatively? I can't believe this answer is accepted. \$\endgroup\$ – mickmackusa Jan 16 at 21:54
  • \$\begingroup\$ agree with you point but second approach works well. Thanks for pointing this. \$\endgroup\$ – aviboy2006 Jan 17 at 6:49
  • \$\begingroup\$ While you have adjusted your snippet to work, surely you can see that there is absolutely no benefit in using your snippet versus mine. This is all needless overhead. \$\endgroup\$ – mickmackusa Jan 17 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.