Jump to content


Photo

Sort by last name


  • Please log in to reply
12 replies to this topic

#1 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 05 August 2006 - 12:46 AM

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?


#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 05 August 2006 - 12:50 AM

ASC or DESC at the end of the query
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 05 August 2006 - 01:00 AM

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.



#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 05 August 2006 - 01:08 AM

try that while i get some perfect code ok
$query = "SELECT * FROM members GROUP by Company_Name a TO F ";
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 05 August 2006 - 01:11 AM

try this ok.

$query = "SELECT * FROM members WHERE "Company_NAME" BETWEEN 'a' AND 'f' ";
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#6 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 05 August 2006 - 01:14 AM

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!!

#7 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 05 August 2006 - 01:16 AM

$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
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#8 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 05 August 2006 - 01:20 AM

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?



#9 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 05 August 2006 - 01:28 AM

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 ";

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#10 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 05 August 2006 - 01:43 AM

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!!

#11 litebearer

litebearer
  • Members
  • PipPipPip
  • Advanced Member
  • 2,357 posts
  • Locationwhite lake michigan

Posted 05 August 2006 - 11:46 AM

Hmmm, looks like 'hammer time'...

Maybe this may be of some use.

(file name is letters.php)

<?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
?>

Lite...


all the brothers were valiant!

[br][br]The truely intelligent people are not those who create the dots; rather they are they ones with the ability to connect the dots into a coherent picture

#12 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 05 August 2006 - 03:32 PM

$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


These give a parse error because you are using double quotes inside a double-quoted string.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#13 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 08 August 2006 - 01:10 AM

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!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users