[FunkyM]

Hi, I have recently built a client management system for my partner and had to import all of the CSV data into a MYSQL table. Once I got this across OK I use PHP to query the BD to get a DISTINCT list of the clients last names.

The problem I am having is that the existing clients last names in the table are all coming back in ording in the form of a drop down list. However when I add a new customer to the table and then go back to the drop down list I find that the new client has been added but their last name is at the bottom of the List instead of in alphabetical order like the rest of the distinct last names.

Here is the query I am using:

$query = "select DISTINCT last_name from customers order by last_name";

and here is the table structure:

CREATE TABLE customers (
customer_id int(11) NOT NULL auto_increment,
first_name varchar(50) NOT NULL default '',
last_name text NOT NULL,
phone varchar(20) NOT NULL default '',
mobile varchar(20) NOT NULL default '',
work_phone varchar(20) NOT NULL default '',
city varchar(80) NOT NULL default '',
other_city varchar(80) NOT NULL default '',
post_code int(5) default '0',
address varchar(255) NOT NULL default '',
comments text NOT NULL,
alt_name varchar(100) NOT NULL default '',
alt_phone varchar(20) NOT NULL default '',
PRIMARY KEY (customer_id)
) TYPE=MyISAM;


Even when I query the table with the same query in phpMyAdmin I get the same result the last customer I entered is at the end of the list instead of being in alphabetical order like the rest of them.

I have used this type of query plenty of times before and have never had a problem. This has got me stumped.

Any help would be really appreciated.

Cheers to all

Funkym

[AmitkRathi]

Originally Posted by FunkyM

Hi, I have recently built a client management system for my partner and had to import all of the CSV data into a MYSQL table. Once I got this across OK I use PHP to query the BD to get a DISTINCT list of the clients last names.

The problem I am having is that the existing clients last names in the table are all coming back in ording in the form of a drop down list. However when I add a new customer to the table and then go back to the drop down list I find that the new client has been added but their last name is at the bottom of the List instead of in alphabetical order like the rest of the distinct last names.

Here is the query I am using:

$query = "select DISTINCT last_name from customers order by last_name";

and here is the table structure:

CREATE TABLE customers (
customer_id int(11) NOT NULL auto_increment,
first_name varchar(50) NOT NULL default '',
last_name text NOT NULL,
phone varchar(20) NOT NULL default '',
mobile varchar(20) NOT NULL default '',
work_phone varchar(20) NOT NULL default '',
city varchar(80) NOT NULL default '',
other_city varchar(80) NOT NULL default '',
post_code int(5) default '0',
address varchar(255) NOT NULL default '',
comments text NOT NULL,
alt_name varchar(100) NOT NULL default '',
alt_phone varchar(20) NOT NULL default '',
PRIMARY KEY (customer_id)
) TYPE=MyISAM;


Even when I query the table with the same query in phpMyAdmin I get the same result the last customer I entered is at the end of the list instead of being in alphabetical order like the rest of them.

I have used this type of query plenty of times before and have never had a problem. This has got me stumped.

Any help would be really appreciated.

Cheers to all

Funkym

i think problem should be with last_name text NOT NULL,

Change field type to text to varchar

last_name varchar(50) NOT NULL,

[FunkyM]

Well this seems to be a bit of a bugger. I tried what you said AmitkRathi but it didn't make any difference.

Any new entries are still showing up at th bottom of the list.

This is very weird, I have never seen this happen before, but their must be a logical reason for it.

Here is my new adjusted table structure:

customer_id int(11) NOT NULL auto_increment,
first_name varchar(50) NOT NULL default '',
last_name varchar(50) NOT NULL default '',
phone varchar(20) NOT NULL default '',
mobile varchar(20) NOT NULL default '',
work_phone varchar(20) NOT NULL default '',
city varchar(80) NOT NULL default '',
other_city varchar(80) NOT NULL default '',
post_code int(5) default '0',
address varchar(255) NOT NULL default '',
comments text NOT NULL,
alt_name varchar(100) NOT NULL default '',
alt_phone varchar(20) NOT NULL default '',
PRIMARY KEY (customer_id)
) TYPE=MyISAM;

To tell you the truth this is about to drive me insain. I have tried everything I know and have looked over the code 30 times looking for mistakes but can't see if I have done anything wrong.

Actually here is the code as well just in case anyone can see a mistake that I can't.

----------------------------------------------------------------------

include("../db_connect.php");

mysql_select_db("$database");
$query = "select DISTINCT last_name from customers order by last_name";
$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);

echo "<form method=post action=../../customer_edit.php?search_type=name_search>\n<selec t name=lname>\n";
echo "<option selected>Last Name</option>\n";
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
$lname = stripslashes($row["last_name"]);

echo "<option value=\"".$lname."\">$lname</option>\n";

}
echo "</select>\n <input type=submit value=submit></form>\n";

----------------------------------------------------------------------

thanks to all for your help on this I truly appreciate it

Funkym

[Acecool]

Why not use:
$query = "SELECT last_name FROM customers ORDER BY last_name";

[FunkyM]

Thanks for your quick comments Acecool but the problem is I am dealing with about 2300 customers and you might have 10 people with the last name so firt of all I need to choose the customers last name and then if there is only one customer with that particular last name it displays it but if there ar 10 with that last name then it displays a list of these people with their first name and last name so I can choose the one I want to work with.

Cheers

FunkyM

[Acecool]

WHERE last_name='this...'

[FunkyM]

Still not working, I even tried searching for a last name in phpMyAdmin under the SQL section and it brought back nothing yet their are definately 3 people with that last name in the table. The query executed sucessfully yet no results.

heres the query I used:

SELECT * FROM customers WHERE last_name = 'Alderton';

Also I better let you know I am running the latest version of MYSQL ON A WINDOWS PC

[mdhall]

What about...

ORDER BY last_name ASC

...?

[FunkyM]

Nah, tried that and no good, what I really want to do is do is have the option of a LIKE query or a DISTINCT query. I know how to do this be for some reason when I do a LIKE it won't bring anything back either even though the word I am searching for is in the feild I am searching in.

I am now wondering if this is some how related to running MYSQL on windows?????

I think I may upload this to my web server and see if I have the same problems there.

If so I can tackle this further if the problems are fixed them it must be something in my local installation.

Funkym

[FunkyM]

OK I uploaded my db and scripts to my web server and found that the problem still exsists so that rules out a problem with my MYSQL installation.

Please help this is driving me nuts.

Funkym