Yevgeniy Bagackiy Yevgeniy Bagackiy - 3 months ago 19
jQuery Question

Alphabet accordion from database jquery

So what im trying to do is to display data from database in alphabetical order using accordion. here is my code:

<head>
<title>HeadCount - Create New Project</title>
<link href="jquery-ui/jquery-ui.css" rel="stylesheet" />
<script src="jquery-ui/jquery.js"></script>
<script src="jquery-ui/jquery-ui.js"></script>
<script>
$(document).ready(function(){
$("#myAccordion"). accordion({heightStyle:"content"});
$(".source li").draggable({helper:"clone"});
$("#cart").droppable({drop:function(event,ui){
$("#items").append($("<li></li>").text(ui.draggable.text()));
}});
});
</script>
<style>
#myAccordion
{
width:400px;
float:left;
margin:25px;
}
li
{
margin-top:5px;
}
#cart
{
width:300px;
height:400px;
border:2px solid black;
float:left;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "users";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
</head>
<body>

<h2>Items</h2>
<div id="myAccordion">
<?php for($i=321; $i<347; $i++)
{
echo "<h3>".chr($i)."</h3>";
echo '<ul class="source">';
$sql = "SELECT username FROM user WHERE username LIKE '".chr($i+32)."%' ";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
$name= $row["username"];
echo"<li class='ui-state-highlight'>". $name ."</li>";
//echo "<br>". $name. "<br>";
}
} else
{
echo "0 results";
}
'</ul>';
}
?>
</div>

<div id="cart">
<h2>Project</h2>
<ol id="items"></ol>
</div>


</body>


For now i was trying to add each letter manually and select letter by letter from database. but i wonder if there can be easier way to do this. thanx for ur help.

So after editing code its names are sorted by letters, however now its displaying under the same accordion tab


Answer

Yes you could simple use a for loop to do it... something like: You can use chr function to get the character form char code.. usually;

chr(256 + 65) = A and chr(256 + 97) = a
chr(256 + 66) = B and chr(256 + 98) = b
chr(256 + 67) = C and chr(256 + 99) = c
and so on ...

So you can try something like:

<?php for($i=321; $i<347;$i++){
echo "<h3>".chr($i)."</h3>";
echo '<ul class="source">';

    $sql = "SELECT username FROM user WHERE username LIKE '".chr($i+32)."%' ";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
         // output data of each row
         while($row = $result->fetch_assoc()) 
         {  
            $name= $row["username"];    
            echo"<li class='ui-state-highlight'>". $name ."</li>";
            //echo "<br>". $name.  "<br>";
         }
    } else 
    {
         echo "0 results";
    }
?>  
</ul>

EDIT after looking at the comments below for performance issues you could try like following:

$sql = "SELECT username FROM user ORDER BY username ASC";
$result = $conn->query($sql);
$row = mysql_fetch_row($result);
for($i=321; $i<347;$i++){
    echo "<h3>".chr($i)."</h3>";
    echo '<ul class="source">';
    foreach($row as $checkrow) {
        if($checkrow == chr(i) || $checkrow == chr(i+32)) {
            echo"<li class='ui-state-highlight'>". $checkrow ."</li>";
            unset($row[key($row)]);
        }
    }
 ?>
 </ul>     
Comments