ORDER BY challenge..

[freecrm]

php based...

I have a fairly simple sql query

SELECT blah de blah... ORDER BY X

I am trying to produce a dynamic table with column headers with a link back to the same page, but including

?ordby=whatevercolumnisclicked 

so that if the column header is clicked, the page refreshes, ordered by that column...

So I tried to set a variable with $_GET

$varordby_RSOpps = "COMPANY";
if (isset($_GET['ordby'])) {
  $varordby_RSOpps = $_GET['ordby'];
} 

The query is then:

$query_RSOpps = sprintf("SELECT CONTACTS.*, OPPS.* FROM CONTACTS INNER JOIN OPPS ON CONTACTS.CONTID=OPPS.OPCONTIDL WHERE CONTACTS.GROUPCODE LIKE %s AND OPPS.OPMANAGER LIKE %s AND OPPS.OPCOMPLETED LIKE %s ORDER BY %s DESC", GetSQLValueString($workgroup_RSOpps, "text"),GetSQLValueString("%" . $varmanager_RSOpps . "%", "text"),GetSQLValueString($varcompleted_RSOpps, "text"),GetSQLValueString($varordby_RSOpps, "text")); 

So by default, it will order by COMPANY which works great.

If there is a value in the URL, it should pick it up

BUT.....

This is part of a mixed form/results page and when the link is clicked, it just refreshes the whole code, re-setting all the other variables.

How do I do this correctly?

[mattura]

I'm not entirely sure what you mean, is it:

Your page shows results, and then a user can click to order them differently, running the same query with an 'ORDER BY'
?


If you want other variables to be saved, maybe add them to the URL string:

"page.php?orderby=name&variable2=setting2&variable 3=setting3...."

[freecrm]

I'm not entirely sure what you mean, is it:

Your page shows results, and then a user can click to order them differently, running the same query with an 'ORDER BY'
?


If you want other variables to be saved, maybe add them to the URL string:

"page.php?orderby=name&variable2=setting2&variable 3=setting3...."

Good point matt.

I wonder how long I can make a URL...

The only trouble i can see is the section:

$varordby_RSOpps = "COMPANY";
if (isset($_GET['ordby'])) {
  $varordby_RSOpps = $_GET['ordby'];
} 

Because the variable will be re-set.

I suppose I could make it...

if (isset($varordby_RSOpps)) 
    {
       $varordby_RSOpps = "COMPANY";
       if (isset($_GET['ordby']))
      {
          $varordby_RSOpps = $_GET['ordby'];
       }
    }
    else
    {

    }
...

!!

{}
]



. 

..... omg - getting confused now! - need to think...

[natsuki]

i don't understand what you want to do with the code, when you reload the url all the vars will be gone unless you appended them to the query url.

And those are long variable names maybe put them in an assoc array so it's easier to manage them.

[mattura]

where do you set these other variables?

Another option is to use a PHP session. You can save the state of variables across page loads/reloads. But is this really necessary?

[freecrm]

... unless you appended them to the query url.

... how do i start to attempt this? I'm not even sure I understand what it means lol.

I'll be more specific.

Mattura was correct in that I am trying to refresh the page (and query), adding a new $_GET variable from the URL.
Edit:

where do you set these other variables?

most variables are $_POST

Another option is to use a PHP session. You can save the state of variables across page loads/reloads. But is this really necessary?

Not sure.. I need to clarify what Natsuki means first before I start going down the sessions route.

[mattura]

Why not include the column order in the POST?

<?php
$var1=$_POST['variable1'];
...
$orderby=$_POST['orderby'];
$query="SELECT * FROM `table` ORDER BY $orderby";

//echo results;

?>
<form action='this.php' method='post'>
Variables: <input name='variable1' />
...
Order by:
<input type='radio' name='orderby' value='variable1' />variable1<br/>
<input type='radio' name='orderby' value='variable2' />variable2<br/>
<input type='submit' />
</form>

[natsuki]

is what mattura said is the query string in the url:

href="page.php?order=name<?= htmlspecialchars(urlencode(&var1=value&var2..)) ?>"></a>

but that means you have gotten all the data into some variables (meaning the form has been posted at least once, else for larger data, use sessions instead.

[orczas]

Well, from what I see here, I think that freecrm forgot that http is state-less protocol. Which by definition means, that each time user requests any page (or refresh the same page), server processes it as if the user have done it for the first time.
Because of that, whatever variables you've set once will be lost when user requests another page.
At least, that was the design idea. Advantage of this approach is that there is no continuity, so server do not have to check in any way if the user was here before or not. Also, this scales nicely, because you can be served the same page from 10 different servers, depending on their available resources and you won't see any difference.
However, sometimes, especially with complex web applications, you might find that you need server to 'remember' certain data about user.

Then there are two ways of achieving that:
1. By appending all the relevant data to the query string every time you request anything (sorting for example).
That might be very tedious at times, especially when interaction is more complex.

The alternative is:
2. Using sessions. Sessions are the mechanism to make server 'remember' certain variables you want it to associated with the given client.
After you start session in php, server store enough data to recognise the client when it comes again and will be able to pass some data to it.
You achieve it with $_SESSION variable. It is an assoc array in which you can store things you want to be remembered across requests.
However, be aware that after session is finished data will be lost, so for cross-session storage use databases.

That gets us to your case: sorting.
To start session use

session_start() 

Do that in the beginning of every request using sessions.
Then, you can use following code:

//lets say you want your ordby variable

//first you check whether it was included in the query
if(isset($_REQUEST['ordby']))$_SESSION['ordby']=$_REQUEST['ordby'];

//then, if session stores any value, use it, alternatively use
//default
if(isset($_SESSION['ordby']))$ordby = $_SESSION['ordby'];
else $ordby = 'default'; 

This way, you have $ordby use the new value, or if not available then old one and if even that is not existent, default value.

You could use a function for that, say:

function getValue($id, $default){
   if(isset($_REQUEST[$id]))
      $_SESSION[$id]=$_REQUEST[$id];
   return isset($_SESSION[$id])?$_SESSION[$id]:$default;
} 

And use it like:

$ordby = getValue('ordby','COMPANY'); 

Well, that was quite long, if anyone gets to this point, thanks for your time;)

[ghotel]

i know the reason why but dont feel like telling you