How to make an accordion in Jquery that shows data with PHP from MySQL Database?

Question

How would I make an accordion in JQuery that shows data from my mysql database using PHP. So I would like it to show the data by alphabetical order like this:

... So until the user clicks on the "A" it doesn't show the data and same for b and so on..

So far I have this :

using Which is not in an accordion. Can someone please help me get started on this. It will be really appreciated.

[Code]

<div id="accordion">
<font face="Calibri" style="italic" size='10px' color="white">
  <?php
    mysql_connect("host","user","pass!") or die("Could not connect to localhost");
mysql_select_db("db") or die( "Could not connect to database");
?>
<?php
$result = mysql_query("SELECT * FROM table ORDER BY name ASC");
echo "<div class='scroll'>";
while ($row = mysql_fetch_array($result))
  {
   echo "<div style='margin: 0 auto;'>
                    <span style='display:inline-block; width:200px; text-align:left;'>" . ucwords($row['name']) . "</span>
                    <span style='display:inline-block; text-align:left;'>" . ucwords($row['number']) . "</span>
                </div>
                <br>";
  }
  echo "</div>";
?>
</div>

Answer 1

@isJustMe has the best solution, especially the recommendation to move to PDO instead of using mysql_* functions.

To help get you started, here is how you can use your current code, with the caveat that you should update to mysqli_* or PDO

<div id="accordion">
<font face="Calibri" style="italic" size='10px' color="white">
<?php
    mysql_connect("host","user","pass!") or die("Could not connect to localhost");
    mysql_select_db("db") or die( "Could not connect to database");
?>
<?php
    $headers = array(); // create array to hold <h3></h3> headers
    $result = mysql_query("SELECT * FROM table ORDER BY name ASC");
    while ($row = mysql_fetch_array($result)){

           // if 1st letter is not in array
           if(!in_array($row['name'][0],$headers){

                // add 1st letter
                $headers[] = $row['name'][0];

                // close the last 'content panel' if it is not the first
                if(count($headers) > 0){
                   echo "</div>";
                }

                // echo 1st letter as header
                echo "<h3>{$row['name'][0]}</h3>";

                // start the 'content panel'
                echo "<div style='margin: 0 auto;'>";

           }
           echo "<span style='display:inline-block; width:200px; text-align:left;'>" . ucwords($row['name']) . "</span>
                 <span style='display:inline-block; text-align:left;'>" . ucwords($row['number']) . "</span>";
    }

   // close the last 'content panel'
   echo "</div>";
?>
</div>

Answer 2

you can ajax of jquery :

1) make a fetchdata.php file, in which basically you fire select query and built a json format output.

2) use $().ajax(): in this you can invoke the fetchdata.php file, and pass a GET or POST parameter to it, e.g passed_param_name = 'A' in your case. This argument will be retrieved in fetchdata.php like $_REQUEST['passed_param_name']. Make sure to call this function (ajax) onclick of element 'A' or 'B' .

3) then in fetchdata.php fire a SELECT query with WHERE clause consisting of column_name LIKE '%A%' (or something like this) . This will return you data, make sure to convert it in json format : you can use json_encode php function for it. and print $json_output; exit; .

4) in $().ajax() , success response, you will get the json output, which you can BE traverseD and set in DIV or UL LI structure, and make it display : block .

Answer 3

You can do something like this.

$names = ['alex', 'adam', 'bob', 'bryan'];
asort($names); // The list wasn't sorted, if you don't want sorting you can just remove this line.

// Prepare list for accordion.
$accordionData = [];
foreach($names as $name) {
  $accordionData[substr($name, 0, 1)][] = $name;
}

// Print accordion, change the echoes to reflect your accordion html.
foreach($accordionData as $index => $names) {
  echo strtoupper($index).'<br />';
  foreach($names as $name) {
    echo ucfirst($name).'<br />'; // ucfirst changes the first letter to upper case.
  }
}

output:

A
Adam
Alex
B
Bob
Bryan

If your question included how to make the actual accordion, just try the code available here: http://jqueryui.com/accordion/

And here is a working example based on jquery ui

// You should replace this with your DB data.
$names = ['alex', 'adam', 'bob', 'bryan'];

// The list wasn't sorted, if you don't want sorting you can just remove this line.
asort($names);

// Prepare list for accordion.
$accordionData = [];
foreach($names as $name) {
  $accordionData[substr($name, 0, 1)][] = $name;
}

?>
<!doctype html>

<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>jQuery UI Accordion - Collapse content</title>
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css" />
  <script>
  $(function() {
    $( "#accordion" ).accordion({
      collapsible: true,
      active: false
    });
  });
  </script>
</head>
<body>
<div id="accordion">
<?php
// Print accordion, change the echoes to reflect your accordion html.
foreach($accordionData as $index => $names) {
?>

  <h3><?php echo strtoupper($index); ?></h3>
  <div>
  <?php
  foreach($names as $name) {
  ?>
    <p><?php echo ucfirst($name); ?></p>
  <?php
  }
  ?>
  </div>
  <?php
}
?>
</div>

Answer 4

enter link description hereUse the code provided by jquery UI :

<!doctype html>

<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>jQuery UI Accordion - Default functionality</title>
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css" />
  <script>
  $(function() {
    $( "#accordion" ).accordion();
  });
  </script>
</head>
<body>

<div id="accordion">
  <h3>Section 1</h3>
  <div>
    <p>
    Mauris mauris ante, blandit et, ultrices a, suscipit eget, quam. Integer
    ut neque. Vivamus nisi metus, molestie vel, gravida in, condimentum sit
    amet, nunc. Nam a nibh. Donec suscipit eros. Nam mi. Proin viverra leo ut
    odio. Curabitur malesuada. Vestibulum a velit eu ante scelerisque vulputate.
    </p>
  </div>
  <h3>Section 2</h3>
  <div>
    <p>
    Sed non urna. Donec et ante. Phasellus eu ligula. Vestibulum sit amet
    purus. Vivamus hendrerit, dolor at aliquet laoreet, mauris turpis porttitor
    velit, faucibus interdum tellus libero ac justo. Vivamus non quam. In
    suscipit faucibus urna.
    </p>
  </div>
  <h3>Section 3</h3>
  <div>
    <p>
    Nam enim risus, molestie et, porta ac, aliquam ac, risus. Quisque lobortis.
    Phasellus pellentesque purus in massa. Aenean in pede. Phasellus ac libero
    ac tellus pellentesque semper. Sed ac felis. Sed commodo, magna quis
    lacinia ornare, quam ante aliquam nisi, eu iaculis leo purus venenatis dui.
    </p>
    <ul>
      <li>List item one</li>
      <li>List item two</li>
      <li>List item three</li>
    </ul>
  </div>
  <h3>Section 4</h3>
  <div>
    <p>
    Cras dictum. Pellentesque habitant morbi tristique senectus et netus
    et malesuada fames ac turpis egestas. Vestibulum ante ipsum primis in
    faucibus orci luctus et ultrices posuere cubilia Curae; Aenean lacinia
    mauris vel est.
    </p>
    <p>
    Suspendisse eu nisl. Nullam ut libero. Integer dignissim consequat lectus.
    Class aptent taciti sociosqu ad litora torquent per conubia nostra, per
    inceptos himenaeos.
    </p>
  </div>
</div>


</body>

And replace your static html with your PHP calls, you can use something like this:

<? $pdo = new PDO('mysql:host=localhost; dbname=db01', $username, $password);
    $pdo->exec("SET CHARACTER SET utf8");
    $sql= "select name,age,height,weight from infoTable"
    $result = $pdo->query($sql);

    foreach($result as $row) {
             $accordion .= '                 
               <h3>'.$row['Name'].'</h3>
                <div>
               <p>                
                my age is ' .$row['Age']. '
                my height is' .$row['Height']. '
            </p></div>';
}
?>

Just make sure you put in the right place. Also, as a suggestion please note that Original MySQL API is deprecated , from the docs:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.