cant get advance search to work

[dunskii]

Hi there,

I'm trying to create an advanced search process, where the data has been collect via text boxes and checkboxes.

With the results, it will be limited by the user.

The errors say that there is some thing wrong with the sql (in red)...but i cant see it


Heres the code i'm using;

<?
function db_connect() {

$db = mysql_connect(&quot;localhost&quot;, &quot;root&quot;
mysql_select_db(&quot;fjc&quot;,$db);
return $db;
}

$order_by = $orderby;
$stories_per_page=$per_page;
$limit = $stories_per_page;

$result = mysql_query(&quot;SELECT * FROM barristers, areas, admissions WHERE (barrsiters.Lst_name LIKE '%$barrister%') OR (barristers.Fst_name LIKE '%$barrister%') OR (admissions.Admission LIKE '%$admission%') OR (areas.Area LIKE '%$area%') ORDER BY '$order_by'&quot;,$db);

return $result;

if (!$page):
$offset = 0;
else :
$offset = (($stories_per_page * $page)-$stories_per_page);
endif;

$sqlstr = &quot;SELECT * FROM barristers, areas, admissions WHERE (barrsiters.Lst_name LIKE '%$barrister%') OR (barristers.Fst_name LIKE '%$barrister%') OR (admissions.Admission LIKE '%$admission%') OR (areas.Area LIKE '%$area%') ORDER BY '$order_by'&quot;;
$sqlstr_wo_limits = $sqlstr;
$sqlstr.= &quot; LIMIT &quot;.$offset.&quot;, &quot;.$limit;

$result = query_database($sqlstr);

print '<center><table width=\&quot;100%\&quot; align=\&quot;center\&quot; cellspacing=\&quot;0\&quot;><tr>';

do {

print '<td align=&quot;left&quot;><li><a href=&quot;../barristers/details.php?bar_id=$bar_id&quot;><spa n class=\&quot;text\&quot;>'. $myrow[&quot;barristers.lst_name&quot;] .', '. $myrow[&quot;barristers.fst_name&quot;] .', '. $myrow[&quot;barristers.qc_bc&quot;] .'</span></a></td></tr><tr>' ;
print '<td align=&quot;left&quot;><li><a href=&quot;../barristers/details.php?bar_id=$bar_id&quot;><spa n class=\&quot;text\&quot;>'. $myrow[&quot;barristers.lst_name&quot;] .', '. $myrow[&quot;barristers.fst_name&quot;] .', '. $myrow[&quot;barristers.qc_bc&quot;] .'</span></a></td>';


} while ($myrow = mysql_fetch_array($result));

print '</tr></table></center>';


$total_records = query_database($sqlstr_wo_limits);
$count = mysql_num_rows($total_records);

if(!$page) {$page=1;}

$offset = ($page-1)*$stories_per_page;

$page_count = ($count-($count%$stories_per_page)) / $stories_per_page + 1;

$nextpage=$page+1;

$i = 1;
$output_string.=&quot;Page : &quot;;
while ($i <= $page_count) {

if($i != $page)

{

$output_string.=&quot;<a href=\&quot;$PHP_SELF?page=$i\&quot;>$i</a>\n& quot;;

} else {$output_string.=&quot;<b>$i</b>&quot;;}

$i++;
}


if ($page < $page_count) {
$output_string.=&quot;<a href=\&quot;$PHP_SELF?page=$nextpage\&quot;>&quot;;
$output_string.=&quot;Next $stories_per_page record(s)...</a>&quot;;
} else {
$output_string.=&quot;No more records to display!&quot;;
}
?>

The errors say that there is some thing wrong with the sql...but i cant see it

Thanks for your help...

dunskii

 

[ladams9999]

I found this (in red) in both queries:

&quot;SELECT * FROM barristers, areas, admissions WHERE (barrsiters.Lst_name LIKE '%$barrister%') OR (barristers.Fst_name LIKE '%$barrister%') OR (admissions.Admission LIKE '%$admission%') OR (areas.Area LIKE '%$area%') ORDER BY '$order_by'&quot;

However, even if this is fixed I wouldn't expect this query to work, at least not the way it's intended. Let me know if I'm wrong...

 

[dunskii]

no, i'm still getting the same error, i'm going to try using some arrays or something.

thanks,

dunskii

 

[gerrygerry]

what error does it give you?

also, i noticed the return $result... this will return a resource identifier, but to what? what are you returning it to? it doesn't appear to be any part of a function! -gerrygerry
Go To

http://quickfreewebsite.com!

 

[ladams9999]

$result is located in the do...while loop. If I'm not mistaken, this will cause all the variables in the first pass to be empty:

do { 

print '<td align=&quot;left&quot;><li><a href=&quot;../barristers/details.php?bar_id=$bar_id&quot;><spa n class=\&quot;text\&quot;>'. $myrow[&quot;barristers.lst_name&quot;] .', '. $myrow[&quot;barristers.fst_name&quot;] .', '. $myrow[&quot;barristers.qc_bc&quot;] .'</span></a></td></tr><tr>' ; 
print '<td align=&quot;left&quot;><li><a href=&quot;../barristers/details.php?bar_id=$bar_id&quot;><spa n class=\&quot;text\&quot;>'. $myrow[&quot;barristers.lst_name&quot;] .', '. $myrow[&quot;barristers.fst_name&quot;] .', '. $myrow[&quot;barristers.qc_bc&quot;] .'</span></a></td>'; 


} while ($myrow = mysql_fetch_array(

$result

));

I see 3 problem areas in the query:
1. SELECT * needs to be expanded to SELECT barristers.*, areas.*, admissions.*
2. There is no join established between the tables. Making a guess on their relationships, try WHERE barristers.Area=areas.Area AND barristers.bar_id=admissions.bar_id AND ((barristers.Lst_name LIKE '%$barrister%') OR (barristers.Fst_name LIKE '%$barrister%') OR (admissions.Admission LIKE '%$admission%') OR (areas.Area LIKE '%$area%'))
3. The ORDER BY '$order_by' should be ORDER BY $order_by (with the columnname in $order_by using dot notation to specify table, &quot;barristers.Lst_name&quot