Assign key name based on row while loop

Question

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'],
 ];
 }
}

Answer 1

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) ?: [];

• user-3857385: I meant to mark your solution as accepted as the second answer works as I intended. Thanks!

Answer 2

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);

Answer 3

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;
 }
 }
}

• mickmackusa: @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.
• Avinash Dalvi: agree with you point but second approach works well. Thanks for pointing this.
• mickmackusa: 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.