PHP MySQL vertical table

Question

I have a horizontal table using PHP and MySQL now

table

How can I make a vertical table from this code?

<div class="content-loader">
  <table cellspacing="0" width="100%" id="rank2" class="table table-striped table-hover table-responsive">
    <thead>
      <tr>
        <th>Nick</th>
        <th>Kredity</th>
        <th>Body1</th>
        <th>Body2</th>
        <th>Cas</th>
        <th>online</th>
      </tr>
    </thead>
    <tbody>
      <?php
      require_once 'dbconfig.php';
      $stmt = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance
        FROM ranks
        INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId
        LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId
        WHERE ranks.steamId = ?");
      $stmt->execute(array($steamprofile['steamid']));

      while($row = $stmt->fetch(PDO::FETCH_ASSOC))
      {
          echo "<td>". $row['lastDisplayName']."</td><td>". $row['balance'] ."</td><td>". $row['points'] ."</td><td>". $row['points2'] ."</td><td>". $row['points2'] ."</td>";
      }
      ?>
    </tbody>
  </table>
</div>

It is unclear what you actually ask. What _exactly_ do you mean by vertical and horizontal here? You have a table with a few columns. Fine. Apparently you try to output those columns into a html table. Where does the "vertical" and "horizontal" come into play? — arkascha, Nov 29 '16 at 17:06
@arkascha OP needs to list `th`s vertically, I suppose. So, every `tr` will have `td`s with values of one type — u_mulder, Nov 29 '16 at 17:10
@u_mulder Might be, might be, but the question is vague in that... — arkascha, Nov 29 '16 at 17:13
yes, vertically list. How to edit this code and make something like this https://ctrlv.cz/shots/2016/11/29/4irk.png — Seuss, Nov 29 '16 at 17:17
`fetchAll()` then transpose. [Transposing multidimensional arrays in PHP](https://stackoverflow.com/q/797251/2943403) — mickmackusa, Jun 27 '22 at 22:03

Xorifelse · Answer 1 · 2016-11-29T19:57:45.980

1

When generating tables, fetch() works on a row by row basis, works very well for horizontally printed tables. But in your case its better to fetchAll() the data before printing it out:

<?php

  function unite(string $prefix, string $suffix, array $array){
    $str = '';
    foreach($array as $value){
      $str.= $prefix . $value . $suffix;
    }

    return $str;
  }

  if($stmt->execute(array($steamprofile['steamid']))){
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
  } else {
    die('query failed');
  }

?>

<table>
  <tbody>
    <tr>
      <th>Nick</th><?php echo unite('<td>', '</td>', array_column($rows, 'lastDisplayName')) ?>
    </tr>
    <tr>
      <th>Kredity</th><?php echo unite('<td>', '</td>', array_column($rows, 'balance')) ?>
    </tr>    
  </tbody>
</table>

This way you can grab columns and print them out in 1 go. If you're not expecting any more columns than 1, you can also simply do the following:

<?php

  if($stmt->execute(array($steamprofile['steamid']))){
    if(!is_array($row = $stmt->fetch(PDO::FETCH_ASSOC))){
      die('no results');
    }
  } else {
    die('query failed');
  }

?>

<tr>
  <th>Nick</th><td><?php echo $row['lastDisplayName'] ?></td>
</tr>

edited Nov 29 '16 at 19:57

answered Nov 29 '16 at 17:54

Xorifelse

7,878
1
27
38

this might do better than mine thought. i dont know how to use prepared statement and pdo yet. i just tried thinking of its logic part. – Vincent Dapiton Nov 29 '16 at 18:36
Read up on it, its actually very easy when you get the hang of it. Much safer as well. – Xorifelse Nov 29 '16 at 18:41
yeah. i know its much safer but im just new at this. our school doesn't teach this to us. even php. we just have to learn by our selves. hehehe.. – Vincent Dapiton Nov 29 '16 at 18:45
this replaces my original code? Im newbie, I must add require_once 'dbconfig.php', "SELECT", FROM", "INNER JOIN" etc? – Seuss Nov 29 '16 at 19:22
Correct, only add the include and your line of code where you prepare the query. The only difference is that I verify if the query runs successfully, if it does not the script stops executing. So no table is shown. – Xorifelse Nov 29 '16 at 19:25
If you're only expecting 1 result, as your code shows. You can leave out the function `unite`, just remove it. – Xorifelse Nov 29 '16 at 19:38
Since that wasn't clear in your question, you might want to change the following lines then: http://pastebin.com/77s30pu1 – Xorifelse Nov 29 '16 at 19:47

Vincent Dapiton · Accepted Answer · 2016-11-29T17:44:38.623

You could try this one

Just copy and paste the prepare and change the variables, same goes to execute

<div class="content-loader">

<?php
  require_once 'dbconfig.php';
  $stmt1 = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance FROM ranks INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId WHERE ranks.steamId = ?");
  $stmt2 = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance FROM ranks INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId WHERE ranks.steamId = ?");
  $stmt3 = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance FROM ranks INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId WHERE ranks.steamId = ?");
  $stmt4 = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance FROM ranks INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId WHERE ranks.steamId = ?");
  $stmt5 = $db_con->prepare("SELECT ranks.steamId, ranks.points, ranks.lastDisplayName, ranks.lastUpdated, ranksrussia2.points AS points2, uconomy.balance FROM ranks INNER JOIN ranksrussia2 ON ranks.steamId = ranksrussia2.steamId LEFT JOIN uconomy ON ranks.steamId = uconomy.steamId WHERE ranks.steamId = ?");

  $stmt1->execute(array($steamprofile['steamid']));
  $stmt2->execute(array($steamprofile['steamid']));
  $stmt3->execute(array($steamprofile['steamid']));
  $stmt4->execute(array($steamprofile['steamid']));
  $stmt5->execute(array($steamprofile['steamid']));

  ?>

<table cellspacing="0" width="100%" id="rank2" class="table table-striped table-hover table-responsive">
<thead>
<tr>
<td>Nick</td>
<?php 
while($row = $stmt1->fetch(PDO::FETCH_ASSOC))
  {
      echo "<td>". $row['lastDisplayName']."</td>";
  }
 ?>
</tr>
<tr>
<td>Kredity</td>
<?php 
while($row = $stmt2->fetch(PDO::FETCH_ASSOC))
  {
      echo "<td>". $row['balance'] ."</td>";
  }
 ?>

</tr>
<tr>
<td>Body1</td>
<?php 
while($row = $stmt3->fetch(PDO::FETCH_ASSOC))
  {
      echo "<td>". $row['points'] ."</td>";
  }
 ?>

</tr>
<tr>
<td>Body2</td>
 <?php 
while($row = $stmt4->fetch(PDO::FETCH_ASSOC))
  {
      echo "<td>". $row['points2'] ."</td>";
  }

 ?>


</tr>
<tr>
<td>Cas</td>
 <?php 
while($row = $stmt5->fetch(PDO::FETCH_ASSOC))
  {
      echo "<td>". $row['points2'] ."</td>";
  }

 ?>

</tr>
<tr>
<td>Online</td>

</tr>
</thead>
<tbody>

</tbody>
</table>

</div>

so then i think u should create different queries for each of the table header. u know what i mean? — Vincent Dapiton, Nov 29 '16 at 17:32
I dont know, im sorry. Its still php? i cant edit mysql cullums — Seuss, Nov 29 '16 at 17:36
if that work. just modify ur sql to select only one fields. so it won't be so long. — Vincent Dapiton, Nov 29 '16 at 17:46
this https://ctrlv.cz/shots/2016/11/29/STWK.png is same like this https://ctrlv.cz/shots/2016/11/29/adho.png ? — Seuss, Nov 29 '16 at 18:17
yes . i just copy paste ur query and change the $stmt variables — Vincent Dapiton, Nov 29 '16 at 18:22
thought u might want study the easy ways . i just give you an idea on how to do it on logic part. i dont know how to use prepared statement and PDO yet. but thanks for accepting it any way — Vincent Dapiton, Nov 29 '16 at 18:38
Oh deary me, no! You recommend doing a separate trip to the db for each user's details? I certainly do not endorse this advice. — mickmackusa, Jun 27 '22 at 21:59

PHP MySQL vertical table

2 Answers2