Cannot make row table values into column fields in PHP queries

Question

I have an array value in the database like the table below, I want to try to sort the data based on the value of the value of field A to be the column and field B as the value of column A, but I cannot parse it

    NO|  field_A    |field_B
   ---------------------------
    1 | username    | brian
    1 | email       | [email protected]
    1 | date_birth  | 1996-06-09
    1 | place_birth | Chichago
    2 | username    | adams
    2 | email       | [email protected]
    2 | date_birth  | 1990-07-11
    2 | place_birth | Manhattan
    3 | username    | john
    3 | email       | [email protected]
    3 | date_birth  | 1988-10-02
    3 | place_birth | Miami

I expected

    No  |  Username     |  Email           | Date Birth     | Place Birth   | 
    -------------------------------------------------------------------------
    1   |  brian        |   [email protected]   | 1996-06-09    |   Chichago    |
    2   |  adams        |   [email protected]    | 1990-07-11    |   Manhattan   |
    3   |  john         |   [email protected]    | 1988-10-02    |   Miami       |

myscript

<table class="table table-striped table-bordered table-hover">
    <thead>
        <tr>
            <th>NO</th>
            <th>Username </th>
            <th>Email </th>
            <th>Date Birth</th>
            <th>Place Birth</th>
        </tr>
    </thead>
    <tbody>

    <?php
    $q = mysqli_query($server, "SELECT * FROM my_tabel ") or die ($server->error);
    $result = array();
    while ($row = mysqli_fetch_array($q)) {
          $result[] = $row;
    }
        foreach($result as $key => $rowx) {
            echo "<tr>";                  
            echo "<td>" .$rowx['username']. "</td>" ; 
            echo "<td>" .$rowx['email']. "</td>"; 
            echo "<td>" .$rowx['date_birth']. "</td>"; 
            echo "<td>" .$rowx['place_birth']. "</td>";     
            echo "</tr>";
        }      
    ?>
    </tbody>
</table>

I got the database from 3rd party plugins, and I want to try to retrieve it from that data easily — irwan dwiyanto
– irwan dwiyanto, Commented Mar 14, 2019 at 19:27
The real problem here is likely your query.... which you haven't really posted here. I assume you are using a JOIN? Make sure that the table you are joining in only has 1 possible result. — Nicholas Summers
– Nicholas Summers, Commented Mar 14, 2019 at 19:28
I answered a question somewhat similar to this using nothing but SQL. Not sure if it's helpful or not to your case...I would recommend avoiding this sort of thing altogether as it's needlessly complex and just sort of weird. — cteski
– cteski, Commented Mar 14, 2019 at 19:38
Whatever the 3rd party plugins are they are just as bad a those Microsoft access/jet VBA database programs that everyone needs to stop using. — drtechno
– drtechno, Commented Mar 14, 2019 at 20:28

Sindhara · Accepted Answer · 2019-03-14 19:39:18Z

You could rearrange your array this way: But... yeah. It's neither nice nor performant

$databaseValues = [
    [
        'no' => 0,
        'field_a' => 'username',
        'field_b' => 'brian'
    ],
    [
        'no' => 1,
        'field_a' => 'email',
        'field_b' => '[email protected]'
    ],
    [
        'no' => 1,
        'field_a' => 'username',
        'field_b' => 'adam'
    ],
    [
        'no' => 0,
        'field_a' => 'email',
        'field_b' => '[email protected]'
    ]
];

$table = [];
foreach ($databaseValues as $row) {
    ['no' => $userId, 'field_a' => $columnName, 'field_b' => $columnValue] = $row;
    if (!isset($table[$userId])) {
        $table[$userId] = ['id' => $userId];
    }

    $table[$userId][$columnName] = $columnValue;
}

/* you can sort it; e.g. */
uasort($table, function($left, $right) {
    return $left['username'] <=> $right['username'];
});

/* output for demonstration */
foreach ($table as $user) {
    echo $user['username'] . PHP_EOL;
    echo $user['email'] . PHP_EOL;
}

cteski · Accepted Answer · 2019-03-14 21:29:30Z

As I mentioned in the comments, this sort of database design is nothing but pain and suffering...and there's not many people with as big as a "HURT ME MORE" attitude in regards to SQL than me. So of course I mocked up your table and values, and proceeded to do some research to find a way to do this entirely in SQL.

In another comment I mentioned a vaguely similar question I had answered, and your requirements are much simpler and can be addressed by just doing a pivot in SQL. MySQL doesn't have proper pivots, but one can get crafty apparently:

SELECT NO,
    MAX(CASE WHEN (field_A = 'username') THEN field_b END) AS username,
    MAX(CASE WHEN (field_A = 'email') THEN field_B END) AS email,
    MAX(CASE WHEN (field_A = 'date_birth') THEN field_B END) AS date_birth,
    MAX(CASE WHEN (field_A = 'place_birth') THEN field_B END) AS place_birth
FROM my_tabel1
GROUP BY NO

With this you can do your data manipulation hijinks completely in SQL code (where it belongs), and PHP can just grab and display as usual. Although I still stress that this sort of design is really not recommended, especially considering that the sample table's pseudo columns (username, email, etc) look rather concrete. I'd probably go looking for a better third party plugin for whatever you're doing.

Disclaimer: This was only mocked up and tested with SQL Server and SQL Fiddle as I don't have any machines available with MySQL at the moment.

likwidfire2k · Accepted Answer · 2019-03-14 19:32:42Z

0

Your database is designed poorly if I'm not mistaken. You can print_r your $result to see what is stored but it looks like you have 3 columns for your $rowx NO, Field A and Field B. You should redo your database if possible and have your table headers be your table columns, then you can use your code currently. Right now you need to echo the fields in your loop.

  echo "<td>" .$rowx['NO']. "</td>" ; 
  echo "<td>" .$rowx['Field A']. "</td>"; 
  echo "<td>" .$rowx['Field B']. "</td>";

answered Mar 14, 2019 at 19:32

likwidfire2k

1951 silver badge11 bronze badges

Comments

drtechno · Accepted Answer · 2019-03-14 20:21:40Z

If the pattern is the same in field 1 for each entry, then building it w/o dynamic association will work. like so:

<?php
 /// lets get our col names looks like the col No is not a field 
$q = "SELECT DISTINCT field_A FROM my_tabel";
$result = $server->query($q)or die (mysqli_error($server));
echo '<table>';
echo '<tr><th>No.</th>';
//now I loopout the names 
 while ($line = $result->fetch_row()) {
 echo '<th>';
 for ($cell=0; $cell<$result->field_count; $cell++) {
 echo $line[$cell];
 }
 echo '</th>';
 }
 echo '</tr>';
 // now we draw the rest of it with two loopouts 
 // first query defines lines the other loops in the cells
 $qq = "SELECT DISTINCT NO FROM my_tabel";
 $rr = $server->query($qq)or die (mysqli_error($server));
      while ($li = $rr->fetch_row()) {
  echo '<tr>';
   for ($cl=0; $cl<$rr->field_count; $cl++) {
 echo $li[$cl];
 // now I get my data for the table cells at each distinct count of the NO col
  $xx="SELECT field_B from my_tabel where NO='".$li[$cl]."'"
  $rrr = $server->query($xx)or die (mysqli_error($server));
   while ($ccc = $rrr->fetch_row()) {
   echo '<td>';
    for ($sss=0; $sss<$rrr->field_count; $sss++) {
   echo '$ccc[$sss]';
   }
   echo '</td>';
    }

    }
  echo '</tr>';
  }

   echo '</table>';

Collectives™ on Stack Overflow

Cannot make row table values into column fields in PHP queries

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related