[drupal-devel] Passing sql variables to a paged query

[Sergio]

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 at 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
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://drupal3.drupal.org/pipermail/development/attachments/20051019/93741b6a/attachment.htm