Page 1 of 2

call "ORDER BY" command from php

Posted: Mon Aug 16, 2004 1:18 am
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>

Posted: Mon Aug 16, 2004 1:34 am
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.

Posted: Mon Aug 16, 2004 1:49 am
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;

?>

Posted: Mon Aug 16, 2004 1:56 am
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?

Posted: Mon Aug 16, 2004 2:07 am
by protokol
feyd told you how to process the variables from the URL... just use HTML to make the links

Posted: Mon Aug 16, 2004 2:17 am
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.

Posted: Mon Aug 16, 2004 2:21 am
by feyd
$result = mysql_query('SELECT * FROM phoneList', $conn);

fix that to use $sql

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

Posted: Mon Aug 16, 2004 2:42 am
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

Posted: Mon Aug 16, 2004 3:26 am
by feyd
more like:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());

Posted: Mon Aug 16, 2004 3:39 am
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

Posted: Mon Aug 16, 2004 3:44 am
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');

Posted: Mon Aug 16, 2004 3:47 am
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

Posted: Mon Aug 16, 2004 3:52 am
by feyd
add a space in front of order, in the string..

Posted: Mon Aug 16, 2004 3:57 am
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##

Posted: Mon Aug 16, 2004 4:32 am
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"