call "ORDER BY" command from php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

call "ORDER BY" command from php

Post by sryuuza »

right now i am trying to build what http://downloadanime.org has, i want to make a list and be able to organize it by catagories...

here is what i have so far....

http://therandomjoe.com/DJM/idea4/address.php

i want to be able to organize by the names at the top "lastName", "email", etc....

the command to do this in the mysql prompt is....

SELECT * FROM phoneList ORDER BY lastName ASC;

how would i call this up from php?

here is the contents of address.php

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<h1>Show Address's</h1>
<?php

//make the database connection
$conn = mysql_connect("localhost", "user", "pass");
mysql_select_db('db_name', $conn);


//create a query
$result = mysql_query('SELECT * FROM phoneList', $conn);

print "<table border =1>\n";

//get field names
print "<tr>\n";
while ($field = mysql_fetch_field($result)){
print "  <th>$field->name</th>\n";
} //end while
print "</tr>\n\n";

//get row data as an associative array
while ($row = mysql_fetch_assoc($result)){
	print "<tr>\n";
	//look at each field
	foreach ($row as $col=>$val){
		print "  <td>$val</td>\n";
	} //end foreach
	print "</tr>\n\n";
}//end while

print "</table>\n";
?>
</body>
</html>
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

Code: Select all

// Wrong
$result = mysql_query('SELECT * FROM phoneList', $conn);
// Correct
$result = mysql_query('SELECT * FROM phoneList ORDER BY lastName ASC', $conn);
You just change the query to order your results. Then just use the PHP functions to iterate over the results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

pass in the fieldname, or some shortname for it, through the url. then analyze the contents of the $_GET array to determine which field and how to order it.. like:

Code: Select all

<?php

$sql = 'SELECT * FROM foo';

$recognized = array('lname'=>array('lastName',0,1),'fname'=>array('firstName',1,0));  // add recognized names in here..

$addon = '';
foreach($_GET as $k => $v)
{
  if(isset($recognized[$k]))
    $addon .= (empty($addon) ? 'ORDER BY ' : ', ') . ($recognized[$k][0]) . ($recognized[$k][1 + (int)(bool)($v)] ? 'ASC' : 'DESC');
}

$sql .= $addon;

?>
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

i know how to auto organize it.... but i want to be able to have the user select how to organize it, by clicking on the name of each column.

how would i go about making those column names links anyhow?
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

feyd told you how to process the variables from the URL... just use HTML to make the links
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

i tried what he gave me and it didnt do a thing... no error... no change

http://therandomjoe.com/DJM/idea4/address2.php

Code: Select all

<?php

//make the database connection
$conn = mysql_connect("localhost", "user", "pass");
mysql_select_db('db_name', $conn);

//organize data
$sql = 'SELECT * FROM phoneList'; 

$recognized = array('lname'=>array('lastName',0,1),'fname'=>array('firstName',1,0));  // add recognized names in here.. 

$addon = ''; 
foreach($_GET as $k => $v) 
{ 
  if(isset($recognized[$k])) 
    $addon .= (empty($addon) ? 'ORDER BY ' : ', ') . ($recognized[$k][0]) . ($recognized[$k][1 + (int)(bool)($v)] ? 'ASC' : 'DESC'); 
} 

$sql .= $addon; 

//create a query
$result = mysql_query('SELECT * FROM phoneList', $conn);

print "<table border =1>\n";

//get field names
print "<tr>\n";
while ($field = mysql_fetch_field($result)){
print "  <th>$field->name</th>\n";
} //end while
print "</tr>\n\n";

//get row data as an associative array
while ($row = mysql_fetch_assoc($result)){
	print "<tr>\n";
	//look at each field
	foreach ($row as $col=>$val){
		print "  <td>$val</td>\n";
	} //end foreach
	print "</tr>\n\n";
}//end while

print "</table>\n";
?>
am i missing something? my guess to organize by last name i would type
http://therandomjoe.com/DJM/idea4/addre ... ?sql=lname

but that doesnt do a thing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$result = mysql_query('SELECT * FROM phoneList', $conn);

fix that to use $sql

then: http://therandomjoe.com/DJM/idea4/address2.php?lname=0
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

like so??

Code: Select all

//create a query
$result = mysql_query("SELECT * FROM phoneList ORDER BY '$sql'", $conn);


here is a much better example then what i linked first (downloadanime.com)
http://animesuki.com/index.php?sortname=0

these people seem to be using the SAME code you are talking about.. i just have to get mine to work :D lol
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

more like:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

well, that seemed to have moved this thing further... now i get an error instead of nothing....
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY lastNameDESC' at line 1
that was when i went to this link....

http://therandomjoe.com/DJM/idea4/address2.php?lname=0

btw, thank you for all your help. hopefull we can get this done tonight :D
Last edited by sryuuza on Mon Aug 16, 2004 3:45 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

replace the line:

Code: Select all

$addon .= (empty($addon) ? 'ORDER BY ' : ', ') . ($recognized&#1111;$k]&#1111;0]) . ($recognized&#1111;$k]&#1111;1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

hmm... same issue.. except this time there is spaces...
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY lastName DESC' at line 1
i would suspect that the code should be printing...

SELECT * FROM phoneList ORDER BY lastName DESC;

but it is having its issues
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

add a space in front of order, in the string..
sryuuza
Forum Newbie
Posts: 14
Joined: Mon Aug 16, 2004 1:18 am
Location: Olympia, WA
Contact:

Post by sryuuza »

WOW... the smallest things.... you are true guru :D

now, just one more short question...

lets say i want to add a "sort by email" option... do i just type...

Code: Select all

$recognized = array('lname'=>array('lastName',0,1),'fname'=>array('firstName',1,0),'email'=>array('email',2,0);
or do i change something else... i am a little unclear about the numbering thing.

also, how do i make it to where i can use ASC? and DESC?


##EDIT##
WOW... you even thought ahead... 0 selects ASC and 1 selects DESC...
SWEET!!! but how do i make it to where if they click it once, it uses ASC, and if they click again, it uses DESC?
##EDIT##
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

when echoing the link, check the $_GET for its variable.. and flip the 0 to a 1 and 1 to a 0.. if it exists.. otherwise.. 0 for your "default"
Post Reply