PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Oct 22, 2018 8:26 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 23 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Mon Aug 16, 2004 1:18 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
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

Syntax: [ Download ] [ Hide ]
<!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>


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 1:34 am 
Offline
Forum Contributor
User avatar

Joined: Fri Jun 21, 2002 7:00 pm
Posts: 353
Location: Cleveland, OH
Syntax: [ Download ] [ Hide ]
// 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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 1:49 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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:
Syntax: [ Download ] [ Hide ]
<?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;



?>


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 1:56 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
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?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 2:07 am 
Offline
Forum Contributor
User avatar

Joined: Fri Jun 21, 2002 7:00 pm
Posts: 353
Location: Cleveland, OH
feyd told you how to process the variables from the URL... just use HTML to make the links


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 2:17 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
i tried what he gave me and it didnt do a thing... no error... no change

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

Syntax: [ Download ] [ Hide ]
<?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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 2:21 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
$result = mysql_query('SELECT * FROM phoneList', $conn);

fix that to use $sql

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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 2:42 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
like so??


Syntax: [ Download ] [ Hide ]
//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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:26 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
more like:

Syntax: [ Download ] [ Hide ]
$result = mysql_query($sql) or die(mysql_error());


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:39 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
well, that seemed to have moved this thing further... now i get an error instead of nothing....

Quote:
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.

Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:44 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
replace the line:

Syntax: [ Download ] [ Hide ]
$addon .= (empty($addon) ? 'ORDER BY ' : ', ') . ($recognized&#1111;$k]&#1111;0]) . ($recognized&#1111;$k]&#1111;1 + (int)(bool)($v)] ? ' ASC' : ' DESC');


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:47 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
hmm... same issue.. except this time there is spaces...

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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:52 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
add a space in front of order, in the string..


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 3:57 am 
Offline
Forum Newbie

Joined: Mon Aug 16, 2004 1:18 am
Posts: 14
Location: Olympia, WA
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...

Syntax: [ Download ] [ Hide ]
$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##


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 16, 2004 4:32 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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"


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 23 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group