multiple sql queries for tab navigation UI

[reveret]

I am new to web development, so be patient please 

I am working with a tab navigation system.  It happens to be bootstrap, but this question is not specific to bootstrap.  There are four tabs; for the sake of simplicity we will call them tabs "A", "B", "C", and "D."  I have a single SQL table and one of the columns --we will call it "Letter"-- identifies each row with one of the four letters.  When a user clicks on "A" tab, they should see all the rows where "Letter" = "A".  The same for tabs "B", "C", and "D."

Right now I am running four separate queries, one for each tab.  This seems ridiculous.  There must be a way to grab all the data from the table with one query, sort the rows based on their "Letter" into four separate multi dimensional arrays, and then loop through the appropriate array to display the data for a particular "Letter" when a user selects that tab.

I cannot figure out how to do this.  Any suggestions would be most helpful.  Thanks.      

[Ingolme]

One query should be enough, since the letter is a parameter, add it in a WHERE condition as such:

SELECT * FROM table WHERE letter = ?

Where "?" is a placeholder for the letter you're searching for.

What does your code look like?

If what you're saying is that you're loading all the data at once and having Javascript change the tabs, then it can be done with one query. While looping through the rows, check to see that an array with the specified key exists and, if it doesn't, create it. Sorting by letter would probably make things more efficient.

$output = [];
while($row = $query->fetch()) {
  $key = $row['letter'];
  if(!isset($output[$key])) {
    $output[$key] = [];
  }
  $output[$key][] = $row;
}

 

[reveret]

Thanks for your quick response.

I should have been clearer.  I am using the query you suggested:

SELECT * FROM table WHERE letter = ?

But I am using it four times, once for each letter and thus one for each tab.  I have a process file (process.php), and in that file I do the four database queries:

 

<?php

require_once('config.php');

// Connect to database //
$mysql = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// get data for A //
$A_data = $mysql->query("SELECT * FROM profiles WHERE letter = 'A'");
    while($row = $A_data->fetch_object()) {
        $A_results[] = $row;
    }

// get data for B //
$B_data = $mysql->query("SELECT * FROM profiles WHERE letter='B'");
    while($row = $B_data->fetch_object()) {
        $B_results[] = $row;
    }

... and the same for C and D.  
 

In index.php, I require process.php and in each of the four tab-pane sections, I use a foreach loop.  So a user clicks on tab A, and each "A" row is output as a bootstrap card as shown below.

<div class="container">
     <div class="row">
     <?php foreach ($A_results as $A_result) : ?>
          <div class='col-md-4 col-sm-6 col-lg-3'>
               <div class='card mt-5'>
                    <div class='card-body'>
                         <p><?php echo($A_result->name); ?></h4>
                         <p>Date:   <?php echo ($A_result->date); ?></p>
                         <p>Address:  <?php echo($A_result->address); ?></p>
                     </div>
              </div>
          </div>
     <?php endforeach; ?>
     </div>
</div>

I do the same thing for B, C, and D.  This works fine, but it seems hopelessly redundant.   Now that you have seen my code, would you still suggest I go with the code you provided?  Or is there something else I could try?

Thanks again for your help. 

[Ingolme]

Yes, using the technique in the second half of my post, select all rows in the table and use the while() loop to separate it into the four different arrays.

[reveret]

Thanks again.  I'll try it out.