Show MySQL results from one column into multiple columms

Onion1944

I would like to show results from one column into four columns using PHP on Wordpress using the insert_php plugin but cannot get any results. I would simply like the results to just show in four columns. I've been trying all sorts of suggestions but nothing seems to work.

Here is the latest:

[insert_php]

$servername = "localhost";
$username = "login";
$password = "pw";
$database = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

$result = mysqli_query($conn,"SELECT mine FROM mines ORDER BY mine");
$data = array();

while ($row = mysqli_fetch_array($result)) $data[] = $row;

for ($i = 0; $i < count($data) / 3; $i++){

echo '<table><tr>';

for ($j = 0; $j < 3; $j++){
echo '<td>' . $data[ $i + $j * 3] . '</td>';
}

echo '</tr><tr>'
}
echo '</tr></table>';

[/insert_php]

Weedpacket

$data[ $i + $j * 3]

Reckon you got your $i and $j mixed up there.

It might be easier to think about to get the data in the form you want and then display it.

$data = array_column($data, 'mine'); // I think you also forgot what the fetch function returns.
$data = array_chunk($data, 3);
echo "<table>";
foreach($data as $triple)
{
    echo "<tr>";
    foreach($triple as $mine)
    {
        echo "<td>$mine</td>";
    }
    echo "</tr>";
}
echo "</table>";

Onion1944

I changed the functions and am still not getting an results in the table. Am I missing something?

$result = mysqli_query($conn,"SELECT mine FROM mines ORDER BY mine");
$data = array();

$data = array_column($data, 'mine'); //array and name of column to choose
$data = array_chunk($data, 3); // array and number of columns

echo "<table>";

foreach($data as $triple)
{
echo "<tr>";
foreach($triple as $mine)
{
echo "<td> $mine </td>";
}
echo "</tr>";
}
echo "</table>";

[/insert_php]

dalecosp

Please note that the PHP tags here are [noparse]

[/noparse].

Can you show the data in the table?

Something like this?

mysql> select * from products;
+----+------+---------------+
| id | pid  | descr         |
+----+------+---------------+
|  1 |    1 | shoes         |
|  2 |    2 | socks         |
|  3 |    3 | ties          |
|  4 |    4 | belts         |
|  5 |    5 | bras          |
|  6 |    6 | trousers      |
|  7 |    7 | skirts        |
|  8 |    8 | shirts        |
|  9 |    9 | jackets       |
| 10 |   10 | straitjackets |
+----+------+---------------+

Onion1944

dalecosp;11063317 wrote:

Please note that the PHP tags here are [noparse]

[/noparse].

Can you show the data in the table?

Something like this?

mysql> select * from products;
+----+------+---------------+
| id | pid  | descr         |
+----+------+---------------+
|  1 |    1 | shoes         |
|  2 |    2 | socks         |
|  3 |    3 | ties          |
|  4 |    4 | belts         |
|  5 |    5 | bras          |
|  6 |    6 | trousers      |
|  7 |    7 | skirts        |
|  8 |    8 | shirts        |
|  9 |    9 | jackets       |
| 10 |   10 | straitjackets |
+----+------+---------------+

I cannot see any data at all when I run it. I am using the insert PHP plugin for WP so my PHP tags are [insert_php] and [/insert_php] but can change for ease of use. I am actually trying to take results from just one column from a mysql table and display it as four columns. In the past, I can see the results in a table but just cannot display as multiple columns. I hope my example below posts correctly. This is what I want it to look like. Thanks for your help.

dalecosp

OK; I'm referring to PHPBuilder.com's board software, post your PHP code inside [noparse]

[/noparse] BBCode tags and all of us will see helpful things like syntax highlighting which can help reveal some bugs.

Here's an example from a working site that does something similar to what you are asking about. $db is a MySQLI object.

$qry = "select distinct value from test where cat = 334 and name='Brand';";

$result = $db->query($qry);

if ($result) {
   $brands = array();
   while($row = $result->fetch_assoc()) {
      $brands[] = $row['value'];
   }
}

if (is_array($brands) && count($brands)) { // if array's not empty, create HTML

  //create a table & header row
  $htmlout="<table><tr>
     <th colspan='4'>Brands</th></tr>
     <tr>
  ";

   $counter = 1; //keep count of data

   foreach ($brands as $brand) {
     //table cell for datum
     $htmlout .= "<td>$brand</td>\n";

     //rows of 4
     if ($counter % 4 == 0) {
         $htmlout .= "</tr>\n<tr>\n";
     }
     $counter++;
  }
}
$htmlout .= "</tr></table>";

echo $htmlout;

Weedpacket

Back in the day I wrote a piece on this subject because at the time it was being asked so often.

For this forums's formatting tags, see its FAQ.

Onion1944

dalecosp;11063323 wrote:
OK; I'm referring to PHPBuilder.com's board software, post your PHP code inside [noparse]
[/noparse] BBCode tags and all of us will see helpful things like syntax highlighting which can help reveal some bugs.

Here's an example from a working site that does something similar to what you are asking about. $db is a MySQLI object.
$qry = "select distinct value from test where cat = 334 and name='Brand';";

$result = $db->query($qry);

if ($result) {
   $brands = array();
   while($row = $result->fetch_assoc()) {
      $brands[] = $row['value'];
   }
}

if (is_array($brands) && count($brands)) { // if array's not empty, create HTML

  //create a table & header row
  $htmlout="<table><tr>
     <th colspan='4'>Brands</th></tr>
     <tr>
  ";

   $counter = 1; //keep count of data

   foreach ($brands as $brand) {
     //table cell for datum
     $htmlout .= "<td>$brand</td>\n";

     //rows of 4
     if ($counter % 4 == 0) {
         $htmlout .= "</tr>\n<tr>\n";
     }
     $counter++;
  }
}
$htmlout .= "</tr></table>";

echo $htmlout;

Thank you! I really appreciate your help. This is working for me. I do have one question, related but tangential. I can post seperately if you recommend.

When showing the results, I want them to appear as a hyperlink. For example:

mine name: Argon
desired url: http://weburl/Argon

Here is what I have so far but it is returning http://weburl/$row[


$sql = "SELECT mine FROM mines ORDER by mine";
$result = mysqli_query($conn, $sql);

if ($result) { 
   $data = array(); 
   while($row = $result->fetch_assoc()) { 
      $data[] = $row['mine']; 
   } 
} 

if (is_array($data) && count($data)) { // if array's not empty, create HTML 

  //create a table & header row 
  $htmlout="<table><tr> 
     <th colspan='4'>Mine</th></tr> 
     <tr> 
  "; 

   $counter = 1; //keep count of data 

   foreach ($data as $mines) { 
     //table cell for datum 
     $htmlout .= "<td><a href='\$row['mine']'> $mines </a></td>\n"; 
     //rows of 4 
     if ($counter % 4 == 0) { 
         $htmlout .= "</tr>\n<tr>\n"; 
     } 
     $counter++; 
  } 
} 
$htmlout .= "</tr></table>"; 

echo $htmlout;

[/QUOTE]

dalecosp

You're welcome to our help. We were all "newbs" at one time. One of the guys I'm very grateful to is the other poster in this thread.

Here is what I have so far but it is returning http://weburl/$row[

You have a complex string there. You can't use $foo['bar'] or $foo->bar inside double quotes (with an exception), or really within any quotes. Instead, use:

$htmlout .= "<td><a href='" . $row['mine'] ."'> $mines </a></td>\n";

The exception is, in some circumstances, you can use curly braces:

$htmlout .= "<td><a href='{$row['mine']}'> $mines </a></td>\n";

We tend to call these things "quoting nightmares" (although this one is actually fairly simple), and they're pretty common in lots of languages.

As for this:

\$row['mine']

A logic bomb there ... when you type "\$" you're telling it to escape the $ (or print it directly instead of using it to indicate the variable). You don't often want to do that (one reason I can think of would be to actually write PHP code to the screen in, say, a tutorial).

Onion1944

dalecosp;11063339 wrote:
You're welcome to our help. We were all "newbs" at one time. One of the guys I'm very grateful to is the other poster in this thread.

You have a complex string there. You can't use $foo['bar'] or $foo->bar inside double quotes (with an exception), or really within any quotes. Instead, use:
$htmlout .= "<td><a href='" . $row['mine'] ."'> $mines </a></td>\n";
The exception is, in some circumstances, you can use curly braces:
$htmlout .= "<td><a href='{$row['mine']}'> $mines </a></td>\n";
We tend to call these things "quoting nightmares" (although this one is actually fairly simple), and they're pretty common in lots of languages.

As for this:

A logic bomb there ... when you type "\$" you're telling it to escape the $ (or print it directly instead of using it to indicate the variable). You don't often want to do that (one reason I can think of would be to actually write PHP code to the screen in, say, a tutorial).

Thanks again! You've really been a huge help. The explanations are really useful. Unfortunately, the code you proposed is above is not returning anything in the hyperlink. For example:

$htmlout .= "<td><a href='" . $row['mine'] ."'> $mines </a></td>\n";

This code returns the result with the correct mine name as in $mines with a hyperlink, but the link only shows http://weburl/ and just redirects the link to the page the results are shown on.

The other code with the curly brackets does the same thing. I'm wondering if I should try an echo instead of the htmlout approach. Any ideas.

dalecosp

Well, hey, it's your variable. I just put it in a correctly formatted string for ya. 😉 😃

You have this:

foreach ($data as $mines) {

So, $row isn't even really a "thing" at this point (well, it is, but it's not relevant to the loop the code is running).

So, what part of $mines (which is one item of the array $data), has the URL in it?

You might do a "print_r($data)" and show us what it says. Of course, I think I know that it doesn't say too much, because the SQL that formed it was simply "select mine from mines" ... I don't know what "mine" is, but often-times when you have both a name and a URL you have a query with two items in it, like "select mine_name, mine_url from mines". Alternatively, if the URL is constructable using only the mine "name" (or whatever "mine" is), then you'll have to construct it prior to assigning it to htmlout or echo()'ing it.....

Onion1944

dalecosp;11063343 wrote:
Well, hey, it's your variable. I just put it in a correctly formatted string for ya. 😉 😃

You have this:

So, $row isn't even really a "thing" at this point (well, it is, but it's not relevant to the loop the code is running).

So, what part of $mines (which is one item of the array $data), has the URL in it?

You might do a "print_r($data)" and show us what it says. Of course, I think I know that it doesn't say too much, because the SQL that formed it was simply "select mine from mines" ... I don't know what "mine" is, but often-times when you have both a name and a URL you have a query with two items in it, like "select mine_name, mine_url from mines". Alternatively, if the URL is constructable using only the mine "name" (or whatever "mine" is), then you'll have to construct it prior to assigning it to htmlout or echo()'ing it.....

Thank you once again! I finally got it working. I really appreciate your help.

  $htmlout .= "<td><a href='" . $mines ."'> $mines </a></td>\n";

Onion1944

Weedpacket;11063335 wrote:
Back in the day I wrote a piece on this subject because at the time it was being asked so often.

For this forums's formatting tags, see its FAQ.

Thanks so much for the references. I'm a newb and so glad there are still folks around with patience.