how to search recordset
Moderator: General Moderators
how to search recordset
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.
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.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
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
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
Re: how to search recordset
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
The values will be available in the following $_GET variables
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
Code: Select all
$_GET['selectbusinesscat'] == 'gwg';
$_GET['businessname'] == 'dd';
$_GET['townorborough'] == 'rths';
// etc
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
Re: how to search recordset
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>
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
You can make this less complicated, magic_quotes_gpc() has been removed in php 6.0.0
I don't understand why you are using sprintf() to create the SQL query.
Code: Select all
<?php
$businessname = addslashes($_GET['businessname']);
$townorborough = addslashes($_GET['townorborough']);
$city = addslashes($_GET['city']);
$businesscat = addslashes($_GET['businesscat']);
?>“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
Re: how to search recordset
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
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.timoteo wrote:I'm using php 5.2.11 so I guess I still need magic quotes - would that be correct?
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
Re: how to search recordset
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:
any ideas?
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>
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
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.
Hth
Code: Select all
<?php
$businesscategory = addslashes($_GET['businesscategory']);
$businesscategory = addslashes($_GET['businesscat']);
?>Can you post the error?timoteo wrote: I tried to put them together but got a parse error on line 6 - can't see what it is
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
Re: how to search recordset
Parse error: syntax error, unexpected T_VARIABLE in /home/biomagn1/public_html/recommendingpeople.com/testing.php on line 6
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
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
Re: how to search recordset
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?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: how to search recordset
What is wrong with sprintf()? Placeholders are a great way to keep your SQL code clean from variable substitution. Cough *PDO*social_experiment wrote: I don't understand why you are using sprintf() to create the SQL query.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
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).John Cartwright wrote:What is wrong with sprintf()?
Now he knows 2 ways of doing this.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.
@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