Using Array to sort DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Using Array to sort DB

Post by Cateyes »

Ok I will hopefully get this I am trying to sort everything alphabetically so that once I have it working at the top I will have the alpha bet just so that they can skip down to the letter they want homwever I suck at understanding the exact code for variables I think I am headed in the right direction just not using the right quotes or something here is my code

Code: Select all

<?php require_once('../Connections/NWRC.php'); ?>
<?php
$alpha =  array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
mysql_select_db($database_NWRC, $NWRC);
for ($n=0;$n<26;$n++)
	{
	$query_rs_students_$alpha[$n] = "SELECT * FROM student_accounts WHERE last_name LIKE '$n%' ORDER BY last_name ASC";
	$rs_students_$alpha[$n] = mysql_query($query_rs_students_$alpha[$n], $NWRC) or die(mysql_error());
	$row_rs_students_$alpha[$n] = mysql_fetch_assoc($rs_students_$alpha[$n]);
	$totalRows_rs_students_$alpha[$n] = mysql_num_rows($rs_students_$alpha[$n]);
	}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>List Students</title>
</head>

<body>
<table width="200" border="1">
  <tr>
    <td nowrap="nowrap">Last Name </td>
    <td nowrap="nowrap">First Name </td>
    <td nowrap="nowrap">User Name </td>
    <td>Password</td>
  </tr>
  <tr>
    <td nowrap="nowrap">&nbsp;</td>
    <td nowrap="nowrap">&nbsp;</td>
    <td nowrap="nowrap">&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td nowrap="nowrap">F</td>
    <td nowrap="nowrap">&nbsp;</td>
    <td nowrap="nowrap">&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <?php 
  for ($i=0;$i<26;$i++)
  	{ ?>
  <?php do { ?>
    <tr>
      <td><?php echo $row_rs_students_'$alpha[$i]'['last_name']; ?></td>
      <td><?php echo $row_rs_students_'$alpha[$i]'['first_name']; ?></td>
      <td><?php echo $row_rs_students_'$alpha[$i]'['user_name']; ?></td>
      <td><?php echo $row_rs_students_'$alpha[$i]'['password']; ?></td>
    </tr>
    <?php } while ($row_rs_students_'$alpha[$i]' = mysql_fetch_assoc($rs_students_'$alpha[$i]')); ?>
	<?php } ?>
</table>
</body>
</html>
<?php
for ($free=0;$free<26;$free++)
    {
     mysql_free_result($rs_students_'$alpha[$i]');
     }
?>
I get the following error message Parse error: syntax error, unexpected T_VARIABLE in c:\htdocs\NWRC\Students\list_students_new_way.php on line 7 which is the start of using the array.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

I think your technique is a little more complex than is necessary. It appears that you are doing a query for every letter and storing the results in an array. It's much easier to get all the entries in one query, then sort it out later.

Code: Select all

$query = "SELECT * FROM student_accounts ORDER BY last_name";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
  $last_name = $row['last_name'];
  //using curly braces here allows you to treat a string as an array
  // this lets you get the first character
  $first_letter = $last_name{0};
  $sorted_entries[$first_letter][] = $row;
}
$sorted_entries will then have an element for each letter. Each one of those elements will have an entry for each appropriate row. This get the job done in 1 query rather than 26.


To answer your question - "$query_rs_students_$alpha[$n]" isn't valid - you can't create variables like that. Why not just throw everything in an array rather than using unique variables for everything, such as: $query_rs_students[$alpha[$n]] = ....
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Post by Cateyes »

Tested the code it works the way you say it will not sure how to incorpoate it though I will look at it some more I am just missing something this is how I want it to look in output mode

A
Ashton Whatever

B
Blueboy
Burns

C
Calliwell
Caldwell
Colin
etc not sure if your code does that or just some of that I will look at it and see if I can figure it out I just thought by using an array would simplify everything by making an array for each letter of the alphabet pre sorted that way.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

have a look at the second entry in Useful Posts.
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Post by Cateyes »

Well this is my final code I will tweak moew later but for now it sorts with the alphbet as well as putting anchors into place here is the code I need to clean it up kept making changes I believe some stuff isnt even uded now :)

Code: Select all

<?php require_once('../connect/db.php'); ?>
<?php
$alpha =  array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
mysql_select_db($database_db, $db);
$query_rs_students = "SELECT * FROM student_accounts ORDER BY last_name ASC";
$rs_students = mysql_query($query_rs_students, $db) or die(mysql_error());
$row_rs_students = mysql_fetch_assoc($rs_students);
$totalRows_rs_students = mysql_num_rows($rs_students);
$a=0;
$n=0;
$c=0;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<?php
$count=0; 
while ($count<26)
	{
	echo "<a href='#Section".$alpha[$count]."'>".$alpha[$count]."</a>&nbsp;&nbsp;";
	$count++;
	}	
?>
<br />
<table width="461" border="1">
      <tr>
        <td width="166" nowrap="nowrap">Last Name </td>
        <td width="172" nowrap="nowrap">First Name </td>
        <td width="293">Password</td>
      </tr>
<?php do { ?>
<?php
$firstletter = $row_rs_students['last_name']{0};
if ($firstletter == $alpha[$n])
	{
	echo "<tr><td><A name='Section".$alpha[$n]."'>".$alpha[$n]."</td></tr>";
	echo "<tr><td>".$row_rs_students['last_name']."</td>";
	echo "<td>".$row_rs_students['first_name']."</td>";
	echo "<td>".$row_rs_students['user_name']."</td>";
	$n=$n+1;
	$b=$n-1;
	}
	elseif ($firstletter == $alpha[$b])
	{
	echo "<tr><td>".$row_rs_students['last_name']."</td>";
	echo "<td>".$row_rs_students['first_name']."</td>";
	echo "<td>".$row_rs_students['user_name']."</td>";
	}
	else
	{
	while ($firstletter != $alpha[$n])
		{
		$n=$n+1;
		}
		echo "<tr><td><A name='Section".$firstletter."'>".$firstletter."</td></tr>";
		echo "<tr><td>".$row_rs_students['last_name']."</td>";
		echo "<td>".$row_rs_students['first_name']."</td>";
		echo "<td>".$row_rs_students['user_name']."</td>";
		$n=$n+1;
		$b=$n-1;
	}
?> 
  <?php } while ($row_rs_students = mysql_fetch_assoc($rs_students)); ?>
  </table>
</body>
</html>
<?php
mysql_free_result($rs_students);
?>
Post Reply