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