0

Hi,

I have a page that lists the names of the Artists in one column, and the title of their song in the next column. I've included a sort section that allows users to view the artist (with the corresponding title) that starts with whatever letter they press. I've now come to a point where I need to add in a check for numbers.
(View attached image to understand what we're talking about)

So if the artist name starts with any number between 0-9, it will be shown in order on the page once a user clicks on "#". I've tried doing it various ways, but can't seem to get it just right.
Here is the code (I took out all my attempts to get the numeric sort working):

<?php
session_start();

include_once('inc/connect.php');


if (isset($_SESSION['username'])){
$loginstatus = "logout";
}
else{
$loginstatus = "login";
}
if(!isset($_SESSION['sort_counter']))
{$_SESSION['sort_counter'] = 1;}

if(($_SESSION['sort_counter']%2) == 0){ //test even value
  $sortcount = "DESC";
}else{ //odd value
  $sortcount = "";
}

$result = mysql_query("SELECT * FROM sheets ORDER BY artist");
$sheetscount = mysql_num_rows($result);
$sortletteris = $_GET['letter'];
$downloadclick = $_GET['downloadclick'];
$show = $_GET['show'];
$today = date("Y-m-d");

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" type="text/css" href="styles/style.css" />
</head>
<body bgcolor="#343331">

<!-- Header -->
<div id="header">
	<div id="headerleft"></div>
	<div id="headermiddle"><a href="index.php"><img src="img/logo.png"></a></div>
	<div id="headerright">
	
	</div>
	
</div>

<!-- Content Top -->
<div id="contenttop">
	<div id="links">
	
<!-- 92x30 -->
	</div>
</div>

<!-- Content Middle -->
<div id="contentmiddle">
<div id="content">
<div id="sort">
	<?php 
	echo "<center>".$sheetscount." Sheets Available<br />";
	echo "<a href='newlyadded.php'>New Sheets</a><span>&nbsp; | &nbsp;</span><a href='request.php'>Request a Sheet</a></center>";
	$letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	echo "<center><div id='letters'>";
	$i = 0;
	while ($i<26){
	$sortletter = $letters[$i];
	echo "<a href='index.php?letter=".$i."'>".$letters[$i]."&nbsp;</a>";
	$i += 1;
	}
	echo "&nbsp;<a href='index.php'>All</a></div></center>";

	if (($sortletteris)!=""){
		
		// The letter that was clicked is set to this variable
		$mysortletter = $letters[$sortletteris];
		//echo $mysortletter;
		// lname LIKE '$letter%'
		$result = mysql_query("SELECT * FROM sheets WHERE artist REGEXP '^[$mysortletter]' ORDER BY artist $sortcount"); 
		
	$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
	}
	
	elseif (($sortletteris)==""){
	
	$result = mysql_query("SELECT * FROM sheets ORDER BY artist $sortcount"); 
		
	$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
	
	}

	  $greenboxleft = "greenboxleft";
	  $greenboxright = "greenboxright";
	  $grayboxleft = "grayboxleft";
	  $grayboxright = "grayboxright";
	  $colorvalue = 0;
	  
	echo "<br /><table width='600px' align='center' style='border-collapse:separate;
border-spacing:0px;'><th style='background-color: #cccccc; border-bottom-style: solid; border-color: #6aa504;'>Artist</th><th style='background-color: #cccccc; border-bottom-style: solid; border-color: #6aa504;'>Title</th>";
	while($row = mysql_fetch_array($result))
	  {
	  if(($colorvalue%2)==0){
	  $styleleft = $greenboxleft;
	  $styleright = $greenboxright;
	  }
	  else{
	  $styleleft = $grayboxleft;
	  $styleright = $grayboxright;
	  }
	  
	  
	echo "<tr>";
	  echo "<td align='center' width='250' id='$styleleft'><div id='songsboxleft'>". ucwords($row['artist']). "</div></td>";
	  echo "<td align='center' width='250' id='$styleright'><div id='songsboxright'><a target='_blank' name='downloadclick' href='".$row['url']."'>" .ucwords($row['title']). "</a></div></td>";
	
	echo "</tr>";
	$colorvalue++;
	}
	echo "</table>";
	
	?>
	</div>


</div>

</div>

<!-- Content Bottom -->
<div id="contentbottom">

</div>

</body>
</html>
Attachments #.png 21 KB
2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by Smudly
0

I would change the SQL statement to use LIKE instead of REGEX. You can change the where clause as follows:

From:

WHERE artist REGEXP '^[$mysortletter]'

To:

WHERE artist LIKE '$mysortletter%'

It might even be better on the database editor as it won't have to try and evaluate a REGEX expression. The only caveat is that Access uses the '*' instead of the '%'.

0

Cool, thanks for the tip.

I added in a '#' before the string of 26 letters (ABCDEF ...)

Now I'm trying to get it to display and sort all artist's whose name begins with a number when clicking on '#'. What do I need to modify?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.