Sort by last name

[rscott7706]

Hi all, I have a non-profit chamber website. 

I put the members in a database and display it with the
following code (logon info deleted):

<?php
echo "<font face=\"verdana\">";
    echo "<br />";
$db = mysql_connect("localhost", "");
mysql_select_db("lakeside_chamber");
$query = "SELECT * FROM members GROUP by Company_Name";

$result = mysql_query($query) or die(mysql_error());
echo "<font face=\"arial\">";
while($row = mysql_fetch_array($result)){
    echo $row['Company_Name'];
    echo "<br />";
    echo $row['First_Name']. " " . $row['Last_Name'];
    echo "<br />";
    echo $row['Address']. " " . $row[''];
    echo $row['City']. ", " . $row['State']. " " . $row['Zip_Code'];
    echo "<br />";
    echo $row['Phone_Number'];
    echo "<br />";
    echo "<a href=mailto:'$row[Web_Email]'>".$row['Web_Email']."</a>";
    echo "<br />";
    echo "<a href='$row[Web_Site_Address]'>".$row[Web_Site_Address]."</a>";
    echo "<br />";
    echo "<br />";
}
?>

Which displays a long list of company info. 

I would like to display a page like the following:

A-F (html so I can link to it)

(PHP displays all records with a last name of the letter a to f)

G-L (again, html so I can link to it)

(PHP displays all records with a last name of the letter g to l)

and on for the whole alphabet

Now, if we could just tweak the

$query = "SELECT * FROM members GROUP by Company_Name";

would be great, then I would put several instances of the code
corresponding to the letter series I am trying to display.

Any ideas?

[redarrow]

ASC or DESC at the end of the query

[rscott7706]

Right, but I guess my problem is just selecting names that start with the letter A-F in the query.  Not so much sort order.

I want to have section one, for instance, query on the names that start with a,b,d, e, or f - no other letter.

[redarrow]

try that while i get some perfect code ok
$query = "SELECT * FROM members GROUP by Company_Name a TO F ";

[redarrow]

try this ok.

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN 'a' AND 'f' ";

[rscott7706]

Get a parse error:

Parse error: parse error, unexpected T_STRING in /home/lakeside/public_html/members_data.php on line 80

That is the query line.

I think the code is close though - I will work it, if you could also, i would appreciate it!!

[redarrow]

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN "a" AND "f" ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN a AND f ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN "'a'" AND "f" ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN '"a"' AND "f" ";

god luck tell me what works theo cheers

[rscott7706]

Still gives parse error

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN "a" AND "f" ";

Do we need to identify that a to f is the first letter of Company_Name?

[redarrow]

try this ok.
$query = "SELECT  Company_NAME FROM members WHERE Company_NAME='$Company_NAME' order by a, f ";

or this

$query = "SELECT Company_NAME FROM members WHERE Company_NAME='$Company_NAME' BETWEEN a AND f ";

[rscott7706]

Here is the message:

Lakeside Chamber of Commerce - 2006 Member Listing

Unknown column 'a' in 'where clause'

I think you have put enough for a day on this.  I truely appreciate your quick response, but I don't want to bother you anymore today on this.  let's let it sit and stew for a while.  Meanwhile i will do some web searches.

Again, THANKS!!

[litebearer]

Hmmm, looks like 'hammer time'...

Maybe this may be of some use.

(file name is letters.php)

[code]
<?PHP
########################
# set page number
########################
$this_is_page = 0;
if(isset($_GET['what_page'])) {
$this_is_page = $_GET['what_page'];
}else{
$this_is_page = 0;
}

###########################################
# define what letters are in which group
###########################################

$letters[0] = array ('a','b','c','d','e');
$letters[1] = array ('f','g','h','i','j');
$letters[2] = array ('k','l','m','n','o');
$letters[3] = array ('p','q','r','s','t');
$letters[4] = array ('u','v','w','x','y','z');

##########################
# get the info from the database
# ordering on last name
##########################
$db = mysql_connect("localhost", "");
mysql_select_db("lakeside_chamber");
$query = "SELECT * FROM members ORDER by Last_Name";
$result = mysql_query($query) or die(mysql_error());

##############################################
# loop thru the results
# if the data is in the current letter group, display it
###############################################
while($row = mysql_fetch_array($result)){
$findme = strtolower(substr($row['Last_Name'],0,1));
if(in_array($findme,$letters[$this_is_page])) {
$this_record = $row['Company_Name'] . "<br>" . $row['Last_Name'] . ", " . $row['First_Name'] . "<br>";
$this_record = $this_record . $row['Address'] . "<br>" . $row['City'] . ", " . $row['State']. " " . $row['Zip_Code'];
$this_record = $this_record . $row['Phone_Number'] . "<br>" . $row['City'] . ", " . $row['State']. " " . $row['Zip_Code'] . "<br>";
$this_record = $this_record . $row['Web_Email'] . "<br>" . $row['Web_Site_address'];
echo $this_record ."<br><hr>";
}
}
?>
<!-- ################################# -->
<!-- # display a menu for the different pages -->
<!-- ################################## -->
<a href="letters.php?what_page=0">View last names beginning with A - E</a><br>
<a href="letters.php?what_page=1">View last names beginning with F - J</a><br>
<a href="letters.php?what_page=2">View last names beginning with K - O</a><br>
<a href="letters.php?what_page=3">View last names beginning with P - T</a><br>
<a href="letters.php?what_page=4">View last names beginning with U - Z</a><br>
<?PHP
?>[/code]

Lite...

[king arthur]

[quote author=redarrow link=topic=103047.msg409943#msg409943 date=1154740619]
$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN "a" AND "f" ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN a AND f ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN "'a'" AND "f" ";

or

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN '"a"' AND "f" ";

god luck tell me what works theo cheers
[/quote]

These give a parse error because you are using double quotes inside a double-quoted string.

[rscott7706]

litebearer - your code works great.  I have some coding work to do to make it user friendly, but you have given me a great start.

Thanks!!