Page 1 of 3

how to search recordset

Posted: Mon Jan 17, 2011 5:41 am
by timoteo
Hi, I have a basic question. I would like to know the script to use to filter a recordset from URL variables.
I have a table (businessdetails) with fields: userid, businesscategory, businessname, numbername, street, townborough, city, county, state, postcode, country, tel1 and on one page a table with user entered search parameter businesscategory, businessname, townorborough, city, statecounty, country. These fields maybe left blank by the user apart from business category (a user may enter a city but leave country blank.)

How do I filter my recordset using the information supplied by the user and display my results with results ordered by most relevant (bestmatch).
My search table works and provides the URL for the results page, but I haven't been able to use this URL to filter the recordset and display results.
thanks for any help in advance.

Re: how to search recordset

Posted: Mon Jan 17, 2011 10:47 am
by social_experiment
What does the url look like? If i understand correctly, you want to use values from the url (passed along in a query string) to display results. If so, getting results from the url is done by using $_GET
Read more here More about $_GET

Re: how to search recordset

Posted: Mon Jan 17, 2011 2:30 pm
by timoteo
HI thanks for responding.

The URL that is sent ends

results.php?businesscategory=&selectbusinesscat=gwg&businessname=dd&townorborough=rths&city=fd&statecounty=ryuj&country=rujr&Submit=Submit

with values from form submission (GET). ( I know it is all nonsense values but they are real - there are some 50 entrances on my db and I can't be bothered to think of real words ... This search should return results.)

I did code with the $_GET command to get the results but I deleted it all in frustration, so I no longer have it to post here. I couldn't get anything displayed on the results page.
cheers in advance

Re: how to search recordset

Posted: Tue Jan 18, 2011 12:40 am
by social_experiment
The values will be available in the following $_GET variables

Code: Select all

 $_GET['selectbusinesscat'] == 'gwg';
 $_GET['businessname'] == 'dd';
 $_GET['townorborough'] == 'rths';
 // etc
The first value businesscategory doesn't have a value because after the first = is an ampersand which indicates the start of a new pair of values. An example:
filename.php?id=4&name=John&town=SomeTown

Re: how to search recordset

Posted: Tue Jan 18, 2011 3:56 am
by timoteo
OK, this is the code I have been trying to use but it shows no results!!!

Code: Select all

<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
$colname2_businessdetails = "1";
if (isset($_GET['businessname'])) {
  $colname2_businessdetails = (get_magic_quotes_gpc()) ? $_GET['businessname'] : addslashes($_GET['businessname']);
}
$colname3_businessdetails = "1";
if (isset($_GET['townorborough'])) {
  $colname3_businessdetails = (get_magic_quotes_gpc()) ? $_GET['townorborough'] : addslashes($_GET['townorborough']);
}
$colname4_businessdetails = "1";
if (isset($_GET['city'])) {
  $colname4_businessdetails = (get_magic_quotes_gpc()) ? $_GET['city'] : addslashes($_GET['city']);
}
$colname_businessdetails = "1";
if (isset($_GET['businesscat'])) {
  $colname_businessdetails = (get_magic_quotes_gpc()) ? $_GET['businesscat'] : addslashes($_GET['businesscat']);
}
mysql_select_db($database_recommendingpeople, $recommendingpeople);
$query_businessdetails = sprintf("SELECT * FROM businessdetails WHERE businesscategory = '%s' AND businessname = '%s' AND townborough = '%s' AND city = '%s'", $colname_businessdetails,$colname2_businessdetails,$colname3_businessdetails,$colname4_businessdetails);
$businessdetails = mysql_query($query_businessdetails, $recommendingpeople) or die(mysql_error());
$row_businessdetails = mysql_fetch_assoc($businessdetails);
$totalRows_businessdetails = mysql_num_rows($businessdetails);


mysql_free_result($businessdetails);

?>
<?php echo $row_businessdetails['userid']; ?>
<p><?php echo $row_businessdetails['street']; ?></p>
<p><?php echo $row_businessdetails['businesscategory']; ?></p>
<p><?php echo $row_businessdetails['numbername']; ?></p>
<p><?php echo $row_businessdetails['businessname']; ?></p>
<p><?php echo $row_businessdetails['townborough']; ?></p>
<p><?php echo $row_businessdetails['city']; ?></p>
<p><?php echo $row_businessdetails['county']; ?></p>
<p><?php echo $row_businessdetails['state']; ?></p>
<p><?php echo $row_businessdetails['postcode']; ?></p>
<p><?php echo $row_businessdetails['tel1']; ?></p>

Re: how to search recordset

Posted: Tue Jan 18, 2011 11:26 am
by social_experiment
You can make this less complicated, magic_quotes_gpc() has been removed in php 6.0.0

Code: Select all

<?php
 $businessname = addslashes($_GET['businessname']);
 $townorborough = addslashes($_GET['townorborough']);
 $city = addslashes($_GET['city']);
 $businesscat = addslashes($_GET['businesscat']);
 ?>
I don't understand why you are using sprintf() to create the SQL query.

Re: how to search recordset

Posted: Tue Jan 18, 2011 2:42 pm
by timoteo
Hi, thanks for your help. I'm using php 5.2.11 so I guess I still need magic quotes - would that be correct?
and about sprintf - I have no idea as to why I am using it either, in fact I have no idea what it means :?
My coding comes from a mix of what dreamweaver does for me and what I am bit by bit learning from forums etc.
But this one stumped me - don't know why it is not working :(

Re: how to search recordset

Posted: Tue Jan 18, 2011 5:01 pm
by social_experiment
timoteo wrote:I'm using php 5.2.11 so I guess I still need magic quotes - would that be correct?
Well using them if the version of php supports it isn't a problem but why use them at all if you can simply use addslashes() or mysql_real_escape_string()? If you migrate your script onto a server that has php 6.0.0 your script will be at risk.

Try using the following as a query

Code: Select all

<?php
 $query = "SELECT * FROM businessdetails WHERE businesscategory = '". mysql_real_escape_string($businesscat) ."' AND 
businessname = '". mysql_real_escape_string($businessname) ."' AND townborough = 
'". mysql_real_escape_string($townorborough) ."' AND city = '". mysql_real_escape_string($city) ."' ";
 $sql = mysql_query($query);
?>

Re: how to search recordset

Posted: Wed Jan 19, 2011 5:23 pm
by timoteo
thanks for your suggestions. I tried to put them together but got a parse error on line 6 - can't see what it is :(
Here is the code as it stands:

Code: Select all

<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
  
  $businesscategory = addslashes($_GET['businesscategory']);
  $businesscategory = addslashes($_GET['businesscat']);
  $businessname = addslashes($_GET['businessname']);
  $townborough = addslashes($_GET['townorborough']);
  $city = addslashes($_GET['city']);
  $state = addslashes($_GET['statecounty']);
  $county = addslashes($_GET['statecounty']);
  $country = addslashes($_GET['country']);


mysql_select_db($database_recommendingpeople, $recommendingpeople);

 $query = "SELECT * FROM businessdetails WHERE businesscategory = '". mysql_real_escape_string($businesscategory) ."' AND 
 businessname = '". mysql_real_escape_string($businessname) ."' AND townborough = 
 '". mysql_real_escape_string($townborough) ."' AND city = '". mysql_real_escape_string($city) ."' ";
  $sql = mysql_query($query);

$businessdetails = mysql_query($query_businessdetails, $recommendingpeople) or die(mysql_error());
$row_businessdetails = mysql_fetch_assoc($businessdetails);
$totalRows_businessdetails = mysql_num_rows($businessdetails);


mysql_free_result($businessdetails);

?>
<?php echo $row_businessdetails['userid']; ?>
<p><?php echo $row_businessdetails['street']; ?></p>
<p><?php echo $row_businessdetails['businesscategory']; ?></p>
<p><?php echo $row_businessdetails['numbername']; ?></p>
<p><?php echo $row_businessdetails['businessname']; ?></p>
<p><?php echo $row_businessdetails['townborough']; ?></p>
<p><?php echo $row_businessdetails['city']; ?></p>
<p><?php echo $row_businessdetails['county']; ?></p>
<p><?php echo $row_businessdetails['state']; ?></p>
<p><?php echo $row_businessdetails['postcode']; ?></p>
<p><?php echo $row_businessdetails['tel1']; ?></p>
any ideas?

Re: how to search recordset

Posted: Wed Jan 19, 2011 11:59 pm
by social_experiment
There are 2 variables with the same name here, the one declared second will be the one that is used. See which $_GET variable holds the value for $businesscategory and assign only one to the variable, if both are needed ($_GET['businesscategory'] and $_GET['businesscat']) change the variable names.

Code: Select all

<?php
$businesscategory = addslashes($_GET['businesscategory']);
$businesscategory = addslashes($_GET['businesscat']);
?>
timoteo wrote: I tried to put them together but got a parse error on line 6 - can't see what it is
Can you post the error?

Hth

Re: how to search recordset

Posted: Thu Jan 20, 2011 3:07 am
by timoteo
Parse error: syntax error, unexpected T_VARIABLE in /home/biomagn1/public_html/recommendingpeople.com/testing.php on line 6

Re: how to search recordset

Posted: Thu Jan 20, 2011 10:42 am
by social_experiment
Your existing code looks free of errors, is that all the code for testing.php ?

Re: how to search recordset

Posted: Thu Jan 20, 2011 2:58 pm
by timoteo
yes that is the complete code for 'testing'. I just want to understand the mechanics to this without unrelated html issues. A I missing something to make it work?

Re: how to search recordset

Posted: Thu Jan 20, 2011 3:11 pm
by John Cartwright
social_experiment wrote: I don't understand why you are using sprintf() to create the SQL query.
What is wrong with sprintf()? Placeholders are a great way to keep your SQL code clean from variable substitution. Cough *PDO*

Re: how to search recordset

Posted: Thu Jan 20, 2011 4:06 pm
by social_experiment
John Cartwright wrote:What is wrong with sprintf()?
Probably nothing but seeing as the user is asking a basic question i thought i'd assist them in doing it in a way that's easier to understand (for him).
timoteo wrote:and about sprintf - I have no idea as to why I am using it either, in fact I have no idea what it means
My coding comes from a mix of what dreamweaver does for me and what I am bit by bit learning from forums etc.
Now he knows 2 ways of doing this. :)

@timoteo : What does the file do that you include (Connections/recommendingpeople.php)