beginner needing a push in the right direction - ordering a table of results

[jaimec]

Hi, I'm creating a flash site for a client who has now asked me to integrate a simple jobs board script written in PHP. I've pretty much got the two working together but can't seem to get the jobs appearing latest first

 

I've altered the following snippet of code to change the order from ASC to DESC

 

 

function QuerySelectLimit($table,$fields,$where_clause,$start,$count,$pm = TRUE,$order_by = "",$order_dir = "DESC",$return_type = DB_RT_ARRAY) {

// check if $count is empty just to be safe

$count = ($count == "") ? 0 : $count;

 

// recompute $start if page modifier set

$_start = ($pm == TRUE) ? ((($start == 0) ? 1 : $start) * $count - $count) : $start;

 

// setup order clause

$order_clause = ($order_by != "") ? "ORDER BY $order_by " . (in_array($order_dir,array("ASC","DESC")) ? "$order_dir " : "") : "";

 

// setup where clause

$where_clause = ($where_clause != "") ? "WHERE $where_clause " : "";

 

// limit clause

$limit_clause = ($start >= 0) ? "LIMIT $_start,$count" : "";

 

// build query

$query = "SELECT $fields FROM `$table` {$where_clause}{$order_clause}{$limit_clause}";

 

// fetch rows

return $this->QFetchRowArray($query,$return_type);

}

 

but am getting no joy - when the latest jobs are called up, they appear in the order they were entered into toeh MYSQL database, ie. most recent last.

 

Am I missing something spectacularly obvious?

 

Any advice or pointers would be gratefully received as I'm completely new to php & mysql.

 

The job script in question is a free script that can be found http://www.recruitment-agency-software.com/recruitment-agency-software-download.php

[Psycho]

Have you tried echo'ing the $query tot he page to see if it contains what you expect?

[jaimec]

The script is built of php config pages with xml template pages for layout etc so so I'm not sure where to

 

echo $query

 

to see what it contains

[Psycho]

Add it after you build the query:

 

<?php

      // build query
      $query = "SELECT $fields FROM `$table` {$where_clause}{$order_clause}{$limit_clause}";

     // ADD THIS LINE
     echo $query;

?>

 

 

[jaimec]

Thanks for that -

 

I added the line and get the output

 

SELECT * FROM `job_list` LIMIT 0,20

 

(the table of results is created in blocks of 20), the output on page 2 of the results was

 

SELECT * FROM `job_list` LIMIT 20,20

 

job_list is the table in the database where the jobs are stored.

[Psycho]

OK, now we are getting somewhere. Looking at your code above it "appears" that $order_clause should be getting set - IF you are passing a value for $order_by. Have you verified that hte call to the function is passing a value for $order_by?

 

Try this, add the following lines before and after the line where $order_clause is defined:

 

<?php

echo 'Order By = '.$order_by.'<br>';
$order_clause = ($order_by != "") ? "ORDER BY $order_by " . (in_array($order_dir,array("ASC","DESC")) ? "$order_dir " : "") : "";
echo 'Order Clause = '.$order_clause.'<br>';

?>

 

What results do you get?

[jaimec]

Thanks for thr response

 

I've added the code and here's the output - looks as though nothing's being passed!?

 

 

Order By =

Order Clause =

Order By =

Order Clause =

SELECT * FROM `job_list` LIMIT 0,20

 

 

 

here's how I added the code

 

 

 

 

function QuerySelectLimit($table,$fields,$where_clause,$start,$count,$pm = TRUE,$order_by = "",$order_dir = "DESC",$return_type = DB_RT_ARRAY) {
	// check if $count is empty just to be safe
	$count = ($count == "") ? 0 : $count;

	// recompute $start if page modifier set
	$_start = ($pm == TRUE) ? ((($start == 0) ? 1 : $start) * $count - $count) : $start;


	echo 'Order By = '.$order_by.'<br>';
$order_clause = ($order_by != "") ? "ORDER BY $order_by " . (in_array($order_dir,array("ASC","DESC")) ? "$order_dir " : "") : "";
echo 'Order Clause = '.$order_clause.'<br>';

	// setup order clause
	$order_clause = ($order_by != "") ? "ORDER BY $order_by " . (in_array($order_dir,array("ASC","DESC")) ? "$order_dir " : "") : "";

	// setup where clause
	$where_clause = ($where_clause != "") ? "WHERE $where_clause " : "";

	// limit clause
	$limit_clause = ($start >= 0) ? "LIMIT $_start,$count" : "";



	// build query
	$query = "SELECT $fields FROM `$table` {$where_clause}{$order_clause}{$limit_clause}";

			echo 'Order By = '.$order_by.'<br>';
$order_clause = ($order_by != "") ? "ORDER BY $order_by " . (in_array($order_dir,array("ASC","DESC")) ? "$order_dir " : "") : "";
echo 'Order Clause = '.$order_clause.'<br>';

	// ADD THIS LINE
     echo $query;

	// fetch rows
	return $this->QFetchRowArray($query,$return_type);
}

[Psycho]

OK, that's the function. The function looks like it "should" handle the $order_by value ok. So, the next question would be: are you passing a value for $order_by when you call the function? I suspect not, otherwise the first echo would show a value.

[jaimec]

Here's the code snippets where the function is called

 

//cheking if is a normal browse or a search method
		if ($search) {

			$items = $this->db->QuerySelectLimit($this->tables[$form["table"]],"*", "`" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"),(int) $_GET["page"],$form["items"],TRUE,"","DESC");
			$count = $this->db->RowCount($this->tables[$form["table"]] , " WHERE `" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"));

		} else {

		}
	}

	if (is_array($form["sql"])) {

		if (is_array($form["sql"]["vars"])) {
			foreach ($form["sql"]["vars"] as $key => $val) {
				//echeking if the default must be evaluated
				if ($val["action"] == "eval") {
					eval("\$val[\"import\"] = " . $val["default"] .";");
				}

				switch ($val["type"]) {
					case "eval":
						eval("\$sql_vars[\"$key\"] = " . $val["import"] . ";");
					break;

					case "var":
						$sql_vars[$key] = $val["import"];
					break;
				}													
			}
			foreach ($sql_vars as $key => $val) {							
				$this->templates->blocks["Temp"]->input = $val;							
				$sql_vars[$key] = $this->templates->blocks["Temp"]->Replace($sql_vars);
			}	

			//doing a double replace, in case there are unreplaced variable sfom "vars" type
			$this->templates->blocks["Temp"]->input = $form["sql"]["query"];
			$sql = $this->templates->blocks["Temp"]->Replace($sql_vars);

			$items = $this->db->QFetchRowArray($sql);
			//$items = $this->Query						

			//processing the counting query
			if (is_array($form["sql"]["count"])) {

				//if no table is set then i use the default table'
				$form["sql"]["count"]["table"] = $form["sql"]["count"]["table"] ? $form["sql"]["count"]["table"] : $form["table"];

				foreach ($form["sql"]["count"] as $key => $val) {
					$this->templates->blocks["Temp"]->input = $val;							
					$form["sql"]["count"][$key] = $this->templates->blocks["Temp"]->Replace($sql_vars);
				}						
			}

			$count = $this->db->RowCount($form["sql"]["count"]["table"] , $form["sql"]["count"]["condition"]);

		}


	} else {				
		if (!is_array($items)) {	
			$items = $this->db->QuerySelectLimit($this->tables[$form["table"]],"*","",(int) $_GET["page"],$form["items"],TRUE,"","DESC");
			$count = $this->db->RowCount($this->tables[$form["table"]]);
		}
	}

 

and

 

//if i got no elements from preloader functions, then i load it manualy
	if (!is_array($items)) {
		//cheking if is a normal browse or a search method
		if (isset($this->forms["uridata"]["search"]) && ($_GET[$this->forms["uridata"]["action"]] == $this->forms["uridata"]["search"])) {

			$items = $this->db->QuerySelectLimit($this->tables[$this->forms["forms"]["list"]["table"]],"*", "`" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"),(int) $_GET["page"],$this->forms["forms"]["list"]["items"]);
			$count = $this->db->RowCount($this->tables[$this->forms["forms"]["list"]["table"]] , " WHERE `" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"));

		} else {

			$items = $this->db->QuerySelectLimit($this->tables[$this->forms["forms"]["list"]["table"]],"*","",(int) $_GET["page"],$this->forms["forms"]["list"]["items"]);
			$count = $this->db->RowCount($this->tables[$this->forms["forms"]["list"]["table"]]);
		}
	}

	$_GET["page"] = $_GET["page"] ? $_GET["page"] : 1;
	//auto index the element
	$start = $this->forms["forms"]["list"]["items"] * ($_GET["page"] - 1 );

	if (is_array($items)) {
		foreach ($items as $key => $val) {
			$items[$key]["_count"] = ++$start;
		}			
	}		

	//$data = new CForm($this->templates["generic_form"], &$this->db , &$this->tables);
	return $this->form->SimpleList($this->forms["forms"]["list"] , $items , $count , $this->extra["list"]);
}

 

 

does this make anything clearer - I'm trying to learn as I go along - but am very appreciative for your help so far. Thanks

[Psycho]

As I suspected none of your calls to the function are passing an $order_by value. The only two calls to the function that even pass anything for that variable are passing an empty string. I have to color code the linked values in order to make sense of it. But, you can see the BOLD MAROON text in the function and the first two function calls that the $order_by value is null. The last two calls don't even pass a value for that variable.

 

QuerySelectLimit($table,$fields,$where_clause,$start,$count,$pm = TRUE,$order_by = "",$order_dir = "DESC",$return_type = DB_RT_ARRAY)

 

$items = $this->db->QuerySelectLimit($this->tables[$form["table"]],"*", "`" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"),(int) $_GET["page"],$form["items"],TRUE,"","DESC");

 

$items = $this->db->QuerySelectLimit($this->tables[$form["table"]],"*","",(int) $_GET["page"],$form["items"],TRUE,"","DESC");

 

$items = $this->db->QuerySelectLimit($this->tables[$this->forms["forms"]["list"]["table"]],"*", "`" . $_GET["what"] . "` " . ( $_GET["type"] == "int" ? "='" . $_GET["search"] . "'" : "LIKE '%" . $_GET["search"] . "%'"),(int) $_GET["page"],$this->forms["forms"]["list"]["items"]);

 

$items = $this->db->QuerySelectLimit($this->tables[$this->forms["forms"]["list"]["table"]],"*","",(int) $_GET["page"],$this->forms["forms"]["list"]["items"]);

 

[jaimec]

I understand - the problem I have is that there are a number of tables in the database, eg job category, job type etc so if I entered job_id between the speech marks then it won't work for job_type etc - (its a free simple script so I can't expect the earth)

 

The solution I've come up with is a simple

 

mysql_query("ALTER TABLE  `job_list` ORDER BY  `job_id` DESC"); 

 

embedded into the page so when the client adds a new job in the admin section - the latest jobs appear first. Its a hack but there are only ever going to be around 40 jobs maximum on the board as its really a promotional thing (hence the client's desire to use a free script)

 

But thanks for all your help anyway - it was vey much appreciated

 

j

 

 

 

 

[Psycho]

The idea is you add job_id on the function call - not the function itself. You shuold know on the function call what you want to sort by.