how to search recordset

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

how to search recordset

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post 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>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post 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 :(
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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);
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post 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?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post by timoteo »

Parse error: syntax error, unexpected T_VARIABLE in /home/biomagn1/public_html/recommendingpeople.com/testing.php on line 6
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post by social_experiment »

Your existing code looks free of errors, is that all the code for testing.php ?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: how to search recordset

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to search recordset

Post 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*
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to search recordset

Post 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)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply