I lol'd reading this post.


So is Raiztlin still available or what? Cause I could use a help with PHP tables.

 

-----signature-----

shoot.

 

-----signature-----

Raiz is pimp. My new buddy.


..... So just an update - my 'server' has been crashing, 3 times this week so far. It is just a 5.5 year old regular desktop, after all. It gave me these very problems a few years back which is why we built our new(er) desktop to begin with. So, anyway - I found a 2 or 3 year old entry level PowerEdge I'm picking up tonight. This would be why you haven't heard from me after being promised I'd enlist your aide/advice with the SQL stuff. I gotta get this taken care of first.


HOPEFULLY by the end of the night, if not surely mid weekend, everything will be setup and operational and I can drive on. But, let's knock on wood with that because we all know how that can go, I'm sure.

 

-----signature-----

hehe, good luck with that

 

-----signature-----

Awesome. Haha, sorry to "use" your thread for this, Phase.


I recently learned about the Next/Previous links too, but now this is for query sorting using links in the table headers. This is the test page for it live.


By default my table is sorted by ID descending. The command:

I want to make the table headers to be clickable. When user clicks on "Character", the table should be sorted by the character's name alphabetically ascending. When "Character" is clicked again, the alphabetical sorting should go descending.


When user clicks on "Average", the table should be sorted from the highest number to lowest (descending). When "Average" is clicked again, it would sort from lowest to highest (ascending).


And same for LastUpdated, TotalVotes, and the other columns you get the idea...


It's pretty much the same exact concept as this, where you can have the table sorted by whatever is clicked in the table header.


I tried some codes found at Google but they just won't work, perhaps cause my table is structurally different. My difficulty is pretty much how to apply it for my page.

< The php file here >


Thanks.

 

-----signature-----

There are generally 3 ways you can go about making a table sortable.


The most straightforward is to make the headers links to the current page but with a parameter telling which column to sort by (and optionally, whether it's descending or not):

Quote:

---

<a href="<?=$_SERVER['SCRIPT_NAME']?>?p=<?=$pageNum?>&orderby=Rank&desc=0">Rank</a>

---

In the PHP code, check for the orderby command, verify that it's one of the values you expect, then change your SQL statement to order by that field. Let me just re-iterate that you should verify anything given to you by the user, especially if you're going to use it in an SQL statement; if you don't, then you're vulnerable to SQL injection attacks, among others.


For example:

Quote:

---

$valid_orderby = array("ID", "FeaturedCharacter", "Rank", "Average", "TotalVotes", "DateUpdated";

$orderby = $valid_orderby[0];

if (isset($_GET['orderby']) && in_array($_GET['orderby'], $valid_orderby))

{

$orderby = $_GET['orderby'];

}


$desc = "";

if ($_GET['desc'])

{

$desc = "DESC";

}


$query = "SELECT * FROM TsundereOrNot ORDER BY $orderby $desc LIMIT $offset, $rowsPerPage";

---

To make it so clicking on a header toggles DESC, one way is to use a function:

Quote:

---

function is_desc($header)

{

global $orderby;


if ($header == $orderby)

{

return !$_GET['desc'];

}

return false; // Optionally you can specify defaults per-column here

}

---

And then in the URL above, use &desc=<?=is_desc('Rank'?>

(I haven't tested any of this code, so there may be bugs or typos).


Another way is to put all of the table data in a javascript array, and use javascript to generate the table and do the sorting. This has the benefit that it's fast -- it doesn't require a page reload to sort the data. However, it can only be used if all of the data fits on one page, which doesn't seem to be the case here.


The most complicated way that combines the best of options 1 and 2, is to use AJAX to retrieve the data from the server in either XML or JSON format. Whenever you want to sort or change pages, you just make an AJAX request with the new parameters, then repopulate the table using javascript. I'd only recommend this if you like playing around with new stuff and want to learn about AJAX. It could take a long time to get it working if you've never used AJAX or more advanced javascript before.


If you want to do options 2 or 3, you should consider using a javascript library that makes it much easier to do complicated things with javascript, like generating the table or doing the AJAX request. There are dozens of good javascript libraries -- jQuery is one of the more popular ones.

 

-----signature-----

[LotRO] Digero (Guardian), Digrim (Burglar), Dignite (LM), Azrea (Hunter) - Landroval
[AC] Digero, Lyera, Draxxe - Leafcull (Retired)
[CoH] Devil's Zealot, Scinta, Izzard - Guardian (Retired)
Digero's AC Decal Plugins: http://decal.acasylum.com

I want your brain Raiztlin!!

 

-----signature-----

The first method is what I need for now, but I'll keep an eye on the other two when I learn more of those language. Right now, I know just enough to run a simple table like this.


Oh yeah, almost forgot one of the challenges here is to keep the <td> links code working while maintaining the next/previous links.


Just to be certain here, where do I need to plug in the $orderby command? Does it have to be before the pagination or anywhere after that? I tried it for the Rank and getting a query error. < php code here >

 

-----signature-----

Lol


It's not that difficult really. and 3 years of schooling for that kinda thing helps


As for you ChiBKey, listen to Digero, he's giving you solid advice :] I'd help you out, but right now I have to be at work in 3 hours and the new wow expansion has kept me up far far far far too long :<


quick edit: if you use http://pastecode.org/ your code is color-coded, so its easyer to read

 

-----signature-----

Hey I don't mind the thread being used for whatever php/sql help.


Just to give an update because I know you're all dying to know - the server update went well! Got my site back up in a couple hours. Woulda been less but I ran into a couple issues (like .. cough.. not havng a USB keyboard in my pile of keyboards), etc. Then used today to troubleshoot other little problems I got from restoring everything. Databases, home directories, etc.


Got another UPS yesterday for it so I'll install that in the morning, but overall - done and success!


Will get back to my site and the stuff Raiz knows is coming before too long. Heh. Here's my current to do list:


-comment system

-perhaps automating my archive page

-setting up email server and letting people subscribe for updates

-feedback system, survey/poll style.


....and I'm acting like my site is popular or something.

 

-----signature-----