Jump to content

Archived

This topic is now archived and is closed to further replies.

rscott7706

Sort by last name

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
try that while i get some perfect code ok
$query = "SELECT * FROM members GROUP by Company_Name a TO F ";

Share this post


Link to post
Share on other sites
try this ok.

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

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites
$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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 ";

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.