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&variable3=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&variable3=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

 

[freecrm]

Whoaaaa....

I have a lot of responses I didn't think I'd get!!!

Thanks Guys

Orczas - I know you've spent a lot of time on this post and I appreciate it but I did think about using sessions variables (As I already use sessions) but I was hoping for a simpler solution..

Something Mattura said made life very simple...

I'm going to put in a drop-down menu and use the post value.

If I can use the $_POST to re-use as a default value... like I do with with text fields.

I'll keep you posted.

 

[natsuki]

It definitely can be done with javascript, but I dunno how to do it so that the page doesn't reset
hm.. maybe if you can use javascript to make an sql query and repopulate the table/list with it.

 

[freecrm]

It definitely can be done with javascript, but I dunno how to do it so that the page doesn't reset
hm.. maybe if you can use javascript to make an sql query and repopulate the table/list with it.

This would be good but will probably be quite complex.

Looks like I might be moving to session variables after all. - my $_POST script ain't workin'!!!

Here is my current garbage! (I haven't included the whole script - just the key bits..)

in header..

$workgroup_RSOpps = "gobbledegook";
if (isset($_SESSION['MM_GroupCode'])) {
  $workgroup_RSOpps = $_SESSION['MM_GroupCode'];
}
$vsortby_RSOpps = "COMPANY";
if (isset($_POST['sortby'])) {
  $vsortby_RSOpps = $_POST['sortby'];
}
$varmanager_RSOpps = "gobbledegook";
if (isset($_POST['manager'])) {
  $varmanager_RSOpps = $_POST['manager'];
}
$varcompleted_RSOpps = "N";
if (isset($_POST['completed'])) {
  $varcompleted_RSOpps = $_POST['completed'];
}
mysql_select_db($database_freecrm, $freecrm);
$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", GetSQLValueString($workgroup_RSOpps, "text"),GetSQLValueString("%" . $varmanager_RSOpps . "%", "text"),GetSQLValueString($varcompleted_RSOpps, "text"),GetSQLValueString($vsortby_RSOpps, "text"));
$RSOpps = mysql_query($query_RSOpps, $freecrm) or die(mysql_error());

The GetSQLValueString function validates difference between varchar, double, int etc.

form in body...

<form id="form1" name="form1" method="post" action="">
 
<input name="manager" type="text" id="manager" value="<?php echo $_POST['manager'];?>" />
 
<select name="sortby" id="sortby">
        <option value="<?php echo $_POST['sortby'];?>" selected="selected"><?php echo $_POST['sortby'];?></option>
        <option value="COMPANY">Company</option>
        <option value="OPTITLE">Title</option>
        <option value="OPPRIORITY">Priority</option>
        <option value="OPPSTAGE">Stage</option>
        <option value="OPPROB">Probability</option>
        <option value="OPQUANTITY">Quantity</option>
        <option value="OPUNITPRICE">Unit Price</option>
        <option value="OPUNITCOST">Unit Cost</option>
        <option value="OPUNITGP">Unit Gross Profit</option>
        <option value="OPTOTALVAL">Total Value</option>
        <option value="OPTOTALGP">Total Gross Profit</option>
        <option value="OPEXPORDDATE">Expected Order Date</option>
        </select>
 
<input name="completed" type="checkbox" id="completed" value="Y" />
 
<input type="submit" name="button" id="button" value="Filter" />
 
</form>

It is returning the recordset correctly but not sorting it by the posted value from the drop-down. I have checked the values but no joy.

Can anyone spot a stupid mistake?

 

[mattura]

I'll have a look later tonight (unless somebody else get's there first)
gotta go now!

 

[freecrm]

Yay - cracked it.

It didn't like the %s variable within the function. I just the variable directly into the mysql query and it works a treat.

$workgroup_RSOpps = "gobbledegook";
if (isset($_SESSION['MM_GroupCode'])) {
  $workgroup_RSOpps = $_SESSION['MM_GroupCode'];
}
$vascdesc_RSOpps = "ASC";
if (isset($_POST['ascdesc'])) {
  $vascdesc_RSOpps = $_POST['ascdesc'];
}
$vsortby_RSOpps = "COMPANY";
if (isset($_POST['sortby'])) {
  $vsortby_RSOpps = $_POST['sortby'];
}
$varmanager_RSOpps = "gobbledegook";
if (isset($_POST['manager'])) {
  $varmanager_RSOpps = $_POST['manager'];
}
$varcompleted_RSOpps = "N";
if (isset($_POST['completed'])) {
  $varcompleted_RSOpps = $_POST['completed'];
}
mysql_select_db($database_freecrm, $freecrm);
$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 $vsortby_RSOpps $vascdesc_RSOpps", GetSQLValueString($workgroup_RSOpps, "text"),GetSQLValueString("%" . $varmanager_RSOpps . "%", "text"),GetSQLValueString($varcompleted_RSOpps, "text"));
$RSOpps = mysql_query($query_RSOpps, $freecrm) or die(mysql_error());

No problem with the form...

Thanks for your efforts guys.

PLEASE CLOSE THIS THREAD

 

[natsuki]

I found out it CAN actually be done using AJAX without refreshing or resending the page.

I used the same concept on my test stuffs here:
http://kiddragon.co.cc/test/ro

I still have to fix a few bugs regarding character set encoding and stuffs though, but it really works. You don't loose your POST data (by putting them in javascript vars (using PHP) when the page is first loaded after a post) .

If you are using sessions instead you would require that your users are logged in or something, but using AJAX makes it asynchronous without ever reloading the page.

This is the html code with inline php which proves that the page doesn't reload and the data is still intact.

You have entered the following details:<br />
	<ul>
		<li>Name: <?= $name_; ?></li>
		<li>Gender: <?= $gender_; ?></li>
		<li>Server: <?= $server_; ?></li>
		<li>Level: <?= $lv_; ?></li>
		<li>Job: <?= $job_; ?></li>
		<li>Job Level: <?= $jlv_; ?></li>
		<li>Guild: <?= $guild_; ?></li>
	</ul>

 

[freecrm]

I found out it CAN actually be done using AJAX without refreshing or resending the page.

I used the same concept on my test stuffs here:
http://kiddragon.co.cc/test/ro

I still have to fix a few bugs regarding character set encoding and stuffs though, but it really works. You don't loose your POST data (by putting them in javascript vars (using PHP) when the page is first loaded after a post) .

If you are using sessions instead you would require that your users are logged in or something, but using AJAX makes it asynchronous without ever reloading the page.

This is the html code with inline php which proves that the page doesn't reload and the data is still intact.

You have entered the following details:<br />
    <ul>
        <li>Name: <?= $name_; ?></li>
        <li>Gender: <?= $gender_; ?></li>
        <li>Server: <?= $server_; ?></li>
        <li>Level: <?= $lv_; ?></li>
        <li>Job: <?= $job_; ?></li>
        <li>Job Level: <?= $jlv_; ?></li>
        <li>Guild: <?= $guild_; ?></li>
    </ul>

Cool - Thanks Natsuki