[drupal-devel] Passing sql variables to a paged query
Hi all, I am trying to creat a useable profile search for an alumni site. The sql searches on up to 9 different profile values, which are selected in a form. I have used a paged query and the first page returns fine. However on page 2 the whole user list (2500 users) is returned because no variables are passed to the query. Is there any way I can pass the variables (as hidden form values?) to the pager query so that the pages return correctly. The sql is below: $sql = "SELECT u.uid, "; $sql .= "v1.value as given_name, "; $sql .= "v2.value as last_name, "; $sql .= "v3.value as degree, "; $sql .= "v4.value as college, "; $sql .= "v5.value as matriculation_year, "; $sql .= "v6.value as company, "; $sql .= "v7.value as town, "; $sql .= "v8.value as nationality, "; $sql .= "v9.value as sector, "; $sql .= "v10.value as position "; $sql .= "FROM users u "; $sql .= "LEFT OUTER JOIN profile_values v1 ON u.uid = v1.uid AND v1.fid = '2' "; $sql .= "LEFT OUTER JOIN profile_values v2 ON u.uid = v2.uid AND v2.fid = '4' "; $sql .= "LEFT OUTER JOIN profile_values v3 ON u.uid = v3.uid AND v3.fid = '45' "; $sql .= "LEFT OUTER JOIN profile_values v4 ON u.uid = v4.uid AND v4.fid = '44' "; $sql .= "LEFT OUTER JOIN profile_values v5 ON u.uid = v5.uid AND v5.fid = '42' "; $sql .= "LEFT OUTER JOIN profile_values v6 ON u.uid = v6.uid AND v6.fid = '73' "; $sql .= "LEFT OUTER JOIN profile_values v7 ON u.uid = v7.uid AND v7.fid = '13' "; $sql .= "LEFT OUTER JOIN profile_values v8 ON u.uid = v8.uid AND v8.fid = '6' "; $sql .= "LEFT OUTER JOIN profile_values v9 ON u.uid = v9.uid AND v9.fid = '72' "; $sql .= "LEFT OUTER JOIN profile_values v10 ON u.uid = v10.uid AND v10.fid= '24' "; $sql .= "WHERE v10.value LIKE '%%' "; $sql .= "AND v1.value LIKE '%" . $_POST['profile_given_name'] . "%' "; $sql .= "AND v2.value LIKE '%" . $_POST['profile_last_name'] . "%' "; $sql .= "AND v3.value LIKE '%" . $_POST['profile_degree'] . "%' "; $sql .= "AND v4.value LIKE '%" . $_POST['profile_college'] . "' "; $sql .= "AND v5.value LIKE '%" . $_POST['profile_matriculation_year'] . "%' "; $sql .= "AND v6.value LIKE '%" . $_POST['profile_company'] . "%' "; $sql .= "AND v7.value LIKE '%" . $_POST['profile_town'] . "%' "; $sql .= "AND v8.value LIKE '%" . $_POST['profile_nationality'] . "%' "; $sql .= "AND v9.value LIKE '%" . $_POST['profile_sector'] . "%' "; $sql .= "ORDER BY last_name"; Any ideas would be very much appreciated! Richard Grieve etribes Limited www.etribes.com Online community & Internet consultancy, design, development and hosting solutions -------------------------------------------- Tel: 0845 458 1880 Mobile: 07876 500606 www.midentity.com/richardgrieve Registered Office: Skyview Centre, Churchfield Road, Sudbury, Suffolk, CO10 2YA
On 18.10.2005 12:37, Richard Grieve wrote:
to the query. Is there any way I can pass the variables (as hidden form values?) to the pager query so that the pages return correctly.
http://drupaldocs.org/api/head/function/theme_pager: Parameters [...] $attributes An associative array of query string parameters to append to the pager links. eg. theme('pager', array (), 10, 0, array ('foo' => $foo, 'bar' => $bar)) would pass foo and bar through the paged pages. -- ax
On 18.10.2005 17:37, Axel Kollmorgen wrote:
eg. theme('pager', array (), 10, 0, array ('foo' => $foo, 'bar' => $bar))
possibly (probably?) preceded by http://drupaldocs.org/api/head/function/pager_query : Parameters [...] ... A variable number of arguments which are substituted into the query (and the count query) using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments. eg. pager_query('SELECT ... WHERE ... %s AND ... %s', 10, 0, NULL, array ($foo, $bar, ...)) $foo and $bar are substituted into the query for the %s. -- ax
Hello, This is a personal something that I do when I have a problem similar to yours... It's not syntax-proof since I'm writing it on gmail... here it goes: $data = array(); $l = array('profile_given_name', 'profile_last_name', 'profile_degree', 'profile_college', 'profile_matriculation _year', 'profile_company', 'profile_town', 'profile_nationality', 'profile_sector'); if (!array_key_exists('profile_given_name', $_POST)) { $x = explode(':|:', $_SESSION['query_data']); $i = 0; foreach ($l as $k) { $data[$k] = $x[$i]; $i++; } } else { foreach ($l as $k) { $data[$k] = $_POST[$k]; } $x = implode(':|:', $data); $_SESSION['query_data'] = $x; } $sql = "SELECT u.uid, "; $sql .= "v1.value as given_name, "; $sql .= "v2.value as last_name, "; $sql .= "v3.value as degree, "; $sql .= "v4.value as college, "; $sql .= "v5.value as matriculation_year, "; $sql .= "v6.value as company, "; $sql .= "v7.value as town, "; $sql .= "v8.value as nationality, "; $sql .= "v9.value as sector, "; $sql .= "v10.value as position "; $sql .= "FROM users u "; $sql .= "LEFT OUTER JOIN profile_values v1 ON u.uid = v1.uid AND v1.fid = '2' "; $sql .= "LEFT OUTER JOIN profile_values v2 ON u.uid = v2.uid AND v2.fid = '4' "; $sql .= "LEFT OUTER JOIN profile_values v3 ON u.uid = v3.uid AND v3.fid = '45' "; $sql .= "LEFT OUTER JOIN profile_values v4 ON u.uid = v4.uid AND v4.fid = '44' "; $sql .= "LEFT OUTER JOIN profile_values v5 ON u.uid = v5.uid AND v5.fid = '42' "; $sql .= "LEFT OUTER JOIN profile_values v6 ON u.uid = v6.uid AND v6.fid = '73' "; $sql .= "LEFT OUTER JOIN profile_values v7 ON u.uid = v7.uid AND v7.fid = '13' "; $sql .= "LEFT OUTER JOIN profile_values v8 ON u.uid = v8.uid AND v8.fid = '6' "; $sql .= "LEFT OUTER JOIN profile_values v9 ON u.uid = v9.uid AND v9.fid = '72' "; $sql .= "LEFT OUTER JOIN profile_values v10 ON u.uid = v10.uid AND v10.fid='24' "; $sql .= "WHERE v10.value LIKE '%%' "; $sql .= "AND v1.value LIKE '%" . $data['profile_given_name'] . "%' "; $sql .= "AND v2.value LIKE '%" . $data['profile_last_name'] . "%' "; $sql .= "AND v3.value LIKE '%" . $data['profile_degree'] . "%' "; $sql .= "AND v4.value LIKE '%" . $data['profile_college'] . "' "; $sql .= "AND v5.value LIKE '%" . $data['profile_matriculation_year'] . "%' "; $sql .= "AND v6.value LIKE '%" . $data['profile_company'] . "%' "; $sql .= "AND v7.value LIKE '%" . $data['profile_town'] . "%' "; $sql .= "AND v8.value LIKE '%" . $data['profile_nationality'] . "%' "; $sql .= "AND v9.value LIKE '%" . $data['profile_sector'] . "%' "; $sql .= "ORDER BY last_name"; It may need a little tweaking, but it should work... Also, it's E_ALL compilant... No warnings or notices... On 10/18/05, Richard Grieve <richardg@etribes.com> wrote:
Hi all,
I am trying to creat a useable profile search for an alumni site. The sql searches on up to 9 different profile values, which are selected in a form. I have used a paged query and the first page returns fine. However on page 2 the whole user list (2500 users) is returned because no variables are passed to the query. Is there any way I can pass the variables (as hidden form values?) to the pager query so that the pages return correctly.
The sql is below:
$sql = "SELECT u.uid, "; $sql .= "v1.value as given_name, "; $sql .= "v2.value as last_name, "; $sql .= "v3.value as degree, "; $sql .= "v4.value as college, "; $sql .= "v5.value as matriculation_year, "; $sql .= "v6.value as company, "; $sql .= "v7.value as town, "; $sql .= "v8.value as nationality, "; $sql .= "v9.value as sector, "; $sql .= "v10.value as position "; $sql .= "FROM users u "; $sql .= "LEFT OUTER JOIN profile_values v1 ON u.uid = v1.uid AND v1.fid = '2' "; $sql .= "LEFT OUTER JOIN profile_values v2 ON u.uid = v2.uid AND v2.fid = '4' "; $sql .= "LEFT OUTER JOIN profile_values v3 ON u.uid = v3.uid AND v3.fid = '45' "; $sql .= "LEFT OUTER JOIN profile_values v4 ON u.uid = v4.uid AND v4.fid = '44' "; $sql .= "LEFT OUTER JOIN profile_values v5 ON u.uid = v5.uid AND v5.fid = '42' "; $sql .= "LEFT OUTER JOIN profile_values v6 ON u.uid = v6.uid AND v6.fid = '73' "; $sql .= "LEFT OUTER JOIN profile_values v7 ON u.uid = v7.uid AND v7.fid = '13' "; $sql .= "LEFT OUTER JOIN profile_values v8 ON u.uid = v8.uid AND v8.fid = '6' "; $sql .= "LEFT OUTER JOIN profile_values v9 ON u.uid = v9.uid AND v9.fid = '72' "; $sql .= "LEFT OUTER JOIN profile_values v10 ON u.uid = v10.uid AND v10.fid= '24' "; $sql .= "WHERE v10.value LIKE '%%' "; $sql .= "AND v1.value LIKE '%" . $_POST['profile_given_name'] . "%' "; $sql .= "AND v2.value LIKE '%" . $_POST['profile_last_name'] . "%' "; $sql .= "AND v3.value LIKE '%" . $_POST['profile_degree'] . "%' "; $sql .= "AND v4.value LIKE '%" . $_POST['profile_college'] . "' "; $sql .= "AND v5.value LIKE '%" . $_POST['profile_matriculation_year'] . "%' "; $sql .= "AND v6.value LIKE '%" . $_POST['profile_company'] . "%' "; $sql .= "AND v7.value LIKE '%" . $_POST['profile_town'] . "%' "; $sql .= "AND v8.value LIKE '%" . $_POST['profile_nationality'] . "%' "; $sql .= "AND v9.value LIKE '%" . $_POST['profile_sector'] . "%' "; $sql .= "ORDER BY last_name";
Any ideas would be very much appreciated!
Richard Grieve etribes Limited www.etribes.com <http://www.etribes.com>
Online community & Internet consultancy, design, development and hosting solutions
--------------------------------------------
Tel: 0845 458 1880 Mobile: 07876 500606 www.midentity.com/richardgrieve <http://www.midentity.com/richardgrieve>
Registered Office: Skyview Centre, Churchfield Road, Sudbury, Suffolk, CO10 2YA
participants (3)
-
Axel Kollmorgen -
Richard Grieve -
Sergio