Displaying database results alphabetically -- Please Help!

[Robert]

I have a php/mysql query working like so:

$Query = "SELECT * FROM $TableName WHERE name LIKE '%".$searchterm."%'
"

All I want to do now is sort them alphabetically. By using the above
current query, the results are listed in the order in which they were
placed in the database.

TIA
Rob

[Jochen Daum]

Hi Robert!

On 18 Feb 2004 17:25:12 -0800, go****@si.rr.com (Robert) wrote:

I have a php/mysql query working like so:

$Query = "SELECT * FROM $TableName WHERE name LIKE '%".$searchterm."%'
"

All I want to do now is sort them alphabetically. By using the above
current query, the results are listed in the order in which they were
placed in the database.

Check out order by.

BTW.: Is is just incidential that they are returned as they are
stored. Don't bet on it. Entries in a database are not ordered as
such.

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Jeffrey Silverman]

On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:

All I want to do now is sort them alphabetically.

ORDER BY

See:
http://www.mysql.com/documentation/m...tml#SQL_Syntax

--
-------------------------
| Jeffrey Silverman |
| jeffrey-AT-jhu-DOT-edu|
-------------------------

[Robert]

Jeffrey Silverman <je*****@jhu.edu> wrote in message news:<pa****************************@jhu.edu>...

On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:

All I want to do now is sort them alphabetically.

ORDER BY

See:
http://www.mysql.com/documentation/m...tml#SQL_Syntax

I cannot figure out where to put it in the query. I get errors like:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /var/www/html/finalsite/results.php on line 284
Warning: mysql_free_result(): supplied argument is not a valid MySQL
result resource in /var/www/html/finalsite/results.php on line 319

Where exactly do I put the ORDER BY statement? If i put it where the
manual says, I get the above error. Here are the 2 places I have
tried:

$Query="SELECT * FROM $TableName ORDER BY WHERE nameLIKE
'%".$searchterm."%' "
and
$Query = "SELECT * FROM $TableName WHERE name LIKE '%".$searchterm."%'
ORDER BY name"

[Jeffrey Silverman]

On Thu, 19 Feb 2004 13:38:56 -0800, Robert wrote:

I cannot figure out where to put it in the query. I get errors like:

1) Did you *read* the MySQL manual page?
2) Did you *test* your SQL query before you used it?

I realize that a manual can be hard reading for a newbie becasue many
manuals assume a certain amount of previous knowledge. But the answer *is*
in the manual page link I gave you previously. Oh, well, here it is,
explained out...

==============================
SELECT SYNTAX(From the Manual):
==============================

SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
[WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
===========================

Now, what does all that mean?

Well, first and foremost, everything in square brackets -- [] - is
*optional*. (So yes, nearly everything is optional!) Secondly, the order
that things appear in this syntax description is the order in which they
must appear in your query. Thirdly, white space is ignored -- but you must
have at least one space between items. It's just that one space is
equivalent to two spaces or a tab. And Fourthly, and most importantly for
you, *immediately* *after* *the* *syntax* is a bunch of *EXAMPLES*.

Example 1:
SELECT 1 + 1;

not useful... skip it.

Example 2:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

hmmm...This one has an ORDER BY clause in it. Hmmm...I wonder if there
are other examples with ORDER BY clauses?

The rest I leave up to you to figure out.

bye!

--
Jeffrey D. Silverman | jeffrey AT jhu DOT edu
Website | http://www.wse.jhu.edu/newtnotes/

[Five Cats]

In message <74**************************@posting.google.com >, Robert
<go****@si.rr.com> writes

Jeffrey Silverman <je*****@jhu.edu> wrote in message
news:<pa****************************@jhu.edu>.. .

On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:

> All I want to do now is sort them alphabetically.

ORDER BY

See:
http://www.mysql.com/documentation/m...tml#SQL_Syntax

I cannot figure out where to put it in the query. I get errors like:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /var/www/html/finalsite/results.php on line 284
Warning: mysql_free_result(): supplied argument is not a valid MySQL
result resource in /var/www/html/finalsite/results.php on line 319

Where exactly do I put the ORDER BY statement? If i put it where the
manual says, I get the above error. Here are the 2 places I have tried:

$Query="SELECT * FROM $TableName ORDER BY WHERE nameLIKE
'%".$searchterm."%' "
and
$Query = "SELECT * FROM $TableName WHERE name LIKE '%".$searchterm."%'
ORDER BY name"

I suspect the second is almost right (the first is clearly wrong), but
you need to do some error handling which will show you what the contents
of $Query actually was - the chances are there is something small wrong
with it.

Something like:
$Result = mysql_query ($Query)
or die ( $Query . "<p>" . mysql_error() );

This assumes it's going into an HTML page hence the <p> bit. You could
experiment from the DOS box to get the right syntax for the query, then
write the PHP to write the query.

See:

http://www.mysql.com/doc/en/SELECT.html

--
Five Cats
Email to: cats_spam at uk2 dot net

[Geoff Berrow]

I noticed that Message-ID: <pa****************************@jhu.edu> from
Jeffrey Silverman contained the following:

Example 2:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

hmmm...This one has an ORDER BY clause in it. Hmmm...

With the same syntax as one of his failed attempts...

$Query = "SELECT * FROM $TableName
^ is this supposed to be here?
WHERE name LIKE '%".$searchterm."%'ORDER BY name";
^ is name one of your fields?
LIKE '%".$searchterm."%'ORDER BY name";
^ add a space

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Jeffrey Silverman]

On Fri, 20 Feb 2004 01:23:19 +0000, Geoff Berrow wrote:

I noticed that Message-ID: <pa****************************@jhu.edu> from
Jeffrey Silverman contained the following:

Example 2:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

hmmm...This one has an ORDER BY clause in it. Hmmm...

With the same syntax as one of his failed attempts...

$Query = "SELECT * FROM $TableName
^ is this supposed to be here?
WHERE name LIKE '%".$searchterm."%'ORDER BY name";
^ is name one of your fields?
LIKE '%".$searchterm."%'ORDER BY name";
^ add a space

Hmm.

Well, the OP really oughtta test the SQL queries first.

Hear that, OP? Use a MySQL client and run the SQL queries first, get them
working, then cut and paste 'em into your PHP.

Later...

--
-------------------------
| Jeffrey Silverman |
| jeffrey-AT-jhu-DOT-edu|
-------------------------