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]

Re: Displaying database results alphabetically -- Please Help!

Hi Robert!

On 18 Feb 2004 17:25:12 -0800, gojuka@si.rr.co m (Robert) wrote:
[color=blue]
>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.
>[/color]

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]

Re: Displaying database results alphabetically -- Please Help!

On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:
[color=blue]
> All I want to do now is sort them alphabetically.[/color]

ORDER BY

See:

http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#SQL_Syntax

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

[Robert]

Re: Displaying database results alphabetically -- Please Help!

Jeffrey Silverman <jeffrey@jhu.ed u> wrote in message news:<pan.2004. 02.19.01.41.19. 123685@jhu.edu> ...[color=blue]
> On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:
>[color=green]
> > All I want to do now is sort them alphabetically.[/color]
>
> ORDER BY
>
> See:
> http://www.mysql.com/documentation/m...tml#SQL_Syntax[/color]

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_resu lt(): 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]

Re: Displaying database results alphabetically -- Please Help!

On Thu, 19 Feb 2004 13:38:56 -0800, Robert wrote:
[color=blue]
> I cannot figure out where to put it in the query. I get errors like:[/color]

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_RESUL T] [SQL_BIG_RESULT] [SQL_BUFFER_RESU LT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expressi on,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_reference s
[WHERE where_definitio n]
[GROUP BY {unsigned_integ er | col_name | formula} [ASC | DESC], ...
[WITH ROLLUP]]
[HAVING where_definitio n]
[ORDER BY {unsigned_integ er | 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_nam e,', ',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]

Re: Displaying database results alphabetically -- Please Help!

In message <742c30b4.04021 91338.750402b5@ posting.google. com>, Robert
<gojuka@si.rr.c om> writes[color=blue]
>Jeffrey Silverman <jeffrey@jhu.ed u> wrote in message
>news:<pan.2004 .02.19.01.41.19 .123685@jhu.edu >...[color=green]
>> On Wed, 18 Feb 2004 17:25:12 -0800, Robert wrote:
>>[color=darkred]
>> > All I want to do now is sort them alphabetically.[/color]
>>
>> ORDER BY
>>
>> See:
>> http://www.mysql.com/documentation/m...tml#SQL_Syntax[/color]
>
>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_resu lt(): 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="SELE CT * FROM $TableName ORDER BY WHERE nameLIKE
>'%".$searchter m."%' "
>and
>$Query = "SELECT * FROM $TableName WHERE name LIKE '%".$searchterm ."%'
>ORDER BY name"[/color]

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]

Re: Displaying database results alphabetically -- Please Help!

I noticed that Message-ID: <pan.2004.02.19 .22.05.33.95765 3@jhu.edu> from
Jeffrey Silverman contained the following:
[color=blue]
>Example 2:
>SELECT CONCAT(last_nam e,', ',first_name) AS full_name
> FROM mytable ORDER BY full_name;
>
>hmmm...This one has an ORDER BY clause in it. Hmmm...[/color]

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]

Re: Displaying database results alphabetically -- Please Help!

On Fri, 20 Feb 2004 01:23:19 +0000, Geoff Berrow wrote:
[color=blue]
> I noticed that Message-ID: <pan.2004.02.19 .22.05.33.95765 3@jhu.edu> from
> Jeffrey Silverman contained the following:
>[color=green]
>>Example 2:
>>SELECT CONCAT(last_nam e,', ',first_name) AS full_name
>> FROM mytable ORDER BY full_name;
>>
>>hmmm...This one has an ORDER BY clause in it. Hmmm...[/color]
>
> 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[/color]

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