从数据库中的列创建数组，我可以在html中回显

Im sorry if this has been answered before but I am new to PHP and MySQL and I can't figure this out.

Pretty much every time I alter my code to include an array I get a fatal error. What I am trying to do is display all the data in 3 columns from my table.

I have my site set up where you log in and I store that user's name as a "code" in a session. I have a table that has multiple user form entries that are differentiated by the user's code because in my form, I grab the code as a hidden field and add it to the entry in the table.

So far I have been able to isolate those entries by the users code, in one column I have the sum of all of the user's numerical data and I am able to echo this as a total.

I want the other 3 columns to display all the values in their columns and for each value have a line break in between them. And I am trying to print or echo these results in specific parts on a confirmation page.

I have seen examples with PDO using fetch_all and other examples of storing arrays but I can't seem to figure it out with my existing code.

Here is my existing code:

<?php
$user = *****;
$pass = *****;
$dbh = new PDO('mysql:host=localhost;dbname=*****', $user, $pass);

$stmt = $dbh->prepare("SELECT sum(price),part_number,location,price FROM products WHERE code = :usercode");
$stmt->bindParam(':usercode', $_SESSION['MM_Username']);

if ($stmt->execute()) {
$user = $stmt->fetch(PDO::FETCH_ASSOC);

}

?>

And here is where I want to display the results:

<table style="margin:0 auto;" cellspacing="7" width="100%">
<tbody>

<tr>
  <td><?php echo $user['part_number']; ?></td><!--all column values-->
  <td><?php echo $user['location']; ?></td><!--all column values-->
  <td><?php echo $user['price']; ?></td><!--all column values-->
  <td><?php echo "Total:",  $user['sum(price)']; ?><br></td><!--this is ok-->

</tr>


 </tbody>
</table>

Change to this

<? echo

to

<?php echo

Try this:

...
$keys = array_keys($user);
foreach ($keys as $k) :
?>
    <td><?= $user[$k]?></td>
<?php endforeach?>

Try like this:

<table style="margin:0 auto;" cellspacing="7" width="100%">
<tbody>
    if ($stmt->execute()) {
     while($user = $stmt->fetch( PDO::FETCH_ASSOC )){ 
      <tr>
       <td><? echo $user['part_number']; ?></td><!--all column values-->
       <td><? echo $user['location']; ?></td><!--all column values-->
       <td><? echo $user['price']; ?></td><!--all column values-->
       <td><? echo "Total:",  $user['sum(price)']; ?><br></td><!--this is ok-->

      </tr>
    }

    }

 </tbody>
</table>

<table>
<tbody>
  if ($stmt->execute()) {
   while($user = $stmt->fetch( PDO::FETCH_ASSOC )){ 
  <tr>
   <td><?php echo $user['part_number']; ?></td><!--all column values-->
   <td><?php echo $user['location']; ?></td><!--all column values-->
   <td><?php echo $user['price']; ?></td><!--all column values-->
   <td><?php echo "Total:",  $user['sum(price)']; ?><br></td><!--this is ok-->

  </tr>
}

}

</tbody>
</table>

There are a few things in your question that jumped out at me.

  • It looks like you're attempting to display both raw data (each row) and aggregate data (the sum of prices). It can be simpler to fetch the information separately instead of in the same request.
  • You had mentioned fetch_all in PDO, but the method is fetchAll.
  • Instead of working with PDO within the HTML (like iterating through while calling fetch), write code so that you're simply iterating over an array.
  • Based on your description of the problem, it sounds like you want to separate the total price from the raw data, so you can reduce your table down to three columns and use the table footer to show the total price.

Based on those, I have the following solution that

  • Separates the calls to get data into descriptive functions
  • Use money_format to better display prices
  • Removes any database-specific manipulation from the view itself.

 

<?php

function getTotalPriceForUser(PDO $database_handler, $user_code)
{
    // If no rows are returned, COALESCE is used so that we can specify a default
    // value. In this particular case, if there aren't any products that would
    // match, we'd still get a result with a value of 0.
    $sql = 'SELECT COALESCE(SUM(price), 0) FROM products WHERE code = ?';
    $stmt = $database_handler->prepare($sql);
    $stmt->execute(array($user_code));

    // This fetches the first row of the result; the result is given as an array with numerical keys.
    $result = $stmt->fetch(PDO::FETCH_NUM);

    // [0] refers to the first column
    return $result[0];
}

function getProductsForUser(PDO $database_handler, $user_code)
{
    $sql = 'SELECT part_number, location, price FROM products WHERE code = ?';
    $stmt = $database_handler->prepare($sql);
    $stmt->execute(array($user_code));

    // fetchAll returns all rows, with each row being an associative array (where part_number, location and price are the keys)
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Set up the database information    
$user = '*****';
$pass = '*****';
$dbh = new PDO('mysql:host=localhost;dbname=*****', $user, $pass);

// money_format to use the below money formatting; this makes sure there's a dollar sign to represent USD, for example
setlocale(LC_MONETARY, 'en_US.UTF-8');

// Store $_SESSION['MM_Username'] in a local variable
$user_code = $_SESSION['MM_Username'];

// Get the list of products associated with this user code
$products = getProductsForUser($dbh, $user_code);

// Get the total cost of the products
$total_cost = getTotalPriceForUser($dbh, $user_code);

?>
<table style="margin:0 auto;" cellspacing="7" width="100%">
    <thead>
    <tr>
        <th>Part Number</th>
        <th>Location</th>
        <th>Cost</th>
    </tr>
    </thead>
    <tfoot>
    <tr>
        <td style="text-align: right" colspan="2">Total:</td>
        <td style="text-align: right; border-top: 1px solid #999"><?= money_format('%.2n', $total_cost) ?></td>
    </tr>
    </tfoot>
    <tbody>
        <?php foreach($products as $product): ?>
        <tr>
            <td><?= $product['part_number'] ?></td>
            <td><?= $product['location'] ?></td>
            <td style="text-align: right"><?= money_format('%.2n', $product['price']) ?></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>