Show MySQL results from one column into multiple columms
Results 1 to 13 of 13

Thread: Show MySQL results from one column into multiple columms

  1. #1
    Junior Member
    Join Date
    Aug 2017
    Posts
    8

    Show MySQL results from one column into multiple columms

    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]

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,531
    PHP Code:
    $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.
    PHP Code:
    $data array_column($data'mine'); // I think you also forgot what the fetch function returns.
    $data array_chunk($data3);
    echo 
    "<table>";
    foreach(
    $data as $triple)
    {
        echo 
    "<tr>";
        foreach(
    $triple as $mine)
        {
            echo 
    "<td>$mine</td>";
        }
        echo 
    "</tr>";
    }
    echo 
    "</table>"
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    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]

  4. #4
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    8,521
    Please note that the PHP tags here are [php][/php].

    Can you show the data in the table?

    Something like this?

    Code:
    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 |
    +----+------+---------------+
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  5. #5
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    Quote Originally Posted by dalecosp View Post
    Please note that the PHP tags here are [php][/php].

    Can you show the data in the table?

    Something like this?

    Code:
    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.

    Results | Results | Results |
    Results | Results | Results |
    Results | Results | Results |
    Results | Results | Results |
    Results | Results | Results |

  6. #6
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    8,521
    OK; I'm referring to PHPBuilder.com's board software, post your PHP code inside [php][/php] 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.

    PHP Code:
    $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 == 0) {
                 
    $htmlout .= "</tr>\n<tr>\n";
             }
             
    $counter++;
          }
    }
    $htmlout .= "</tr></table>";

    echo 
    $htmlout
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  7. #7
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,531
    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.
    Last edited by Weedpacket; 08-17-2017 at 05:38 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  8. #8
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    Quote Originally Posted by dalecosp View Post
    OK; I'm referring to PHPBuilder.com's board software, post your PHP code inside [php][/php] 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.

    PHP Code:
    $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 == 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[

    PHP Code:

    $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 == 0) { 
                 
    $htmlout .= "</tr>\n<tr>\n"
             } 
             
    $counter++; 
          } 

    $htmlout .= "</tr></table>"

    echo 
    $htmlout
    [/QUOTE]

  9. #9
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    8,521
    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:

    PHP Code:
    $htmlout .= "<td><a href='" $row['mine'] ."'> $mines </a></td>\n"
    The exception is, in some circumstances, you can use curly braces:

    PHP Code:
    $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:
    PHP Code:
    \$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).
    Last edited by dalecosp; 08-17-2017 at 06:44 PM.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  10. #10
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    Quote Originally Posted by dalecosp View Post
    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:

    PHP Code:
    $htmlout .= "<td><a href='" $row['mine'] ."'> $mines </a></td>\n"
    The exception is, in some circumstances, you can use curly braces:

    PHP Code:
    $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:

    PHP Code:
    $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.

  11. #11
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    8,521
    Well, hey, it's your variable. I just put it in a correctly formatted string for ya.

    You have this:

    PHP Code:
    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.....
    Last edited by dalecosp; 08-18-2017 at 12:33 PM.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  12. #12
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    Quote Originally Posted by dalecosp View Post
    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.

    PHP Code:
      $htmlout .= "<td><a href='" $mines ."'> $mines </a></td>\n"

  13. #13
    Junior Member
    Join Date
    Aug 2017
    Posts
    8
    Quote Originally Posted by Weedpacket View Post
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •