how to search recordset
Moderator: General Moderators
Re: how to search recordset
connects to my server. without it nothing works
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
I went through the code and typed out the variables and the problem disappeared
I don't know what happened with it but try typing the values out instead of copying them from here.
The value of the second $businesscategory will make the first one disappear because you assign a new value to it. In the commented variables you see that i renamed it to $businesscategory2 because you are using two seperate sources ($_GET['businesscategory'] and $_GET['businesscat']) to determine the value for each of them. If you want to use both (im going with 'yes i do') then you need two different variables, one for each value.
The second point (POINT 2): You create $sql but you don't use it (in this script). Where do you use this variable?
Hth
Code: Select all
<?php
<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
// the comment variables yielded the error until i typed
// them out. The commented section is the original
// values, below them is the 'new' typed out version
/*
$businesscategory = addslashes($_GET['businesscategory']);
$businesscategory2 = 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']);*/
$businesscategory = addslashes($_GET['businesscategory']);
// change this variable name -- POINT 1
$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) ."' ";
// purpose of this -- POINT 2
$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>
?>The second point (POINT 2): You create $sql but you don't use it (in this script). Where do you use this variable?
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
I tried retyping the variables and removed the extra variable (point 2 - point taken). The double variable is because I have 2 fields to question the database - a drop down menu and a text field, so now I put it as 'OR' function - one or the other however I plan to have the menu feeding the text field (when I figure out how
)
Here is the code I am now running with these adaptions. The error gets throught to line 17 which is the $query. (the variables go OK it seems. What's up now?
Here is the code I am now running with these adaptions. The error gets throught to line 17 which is the $query. (the variables go OK it seems. What's up now?
Code: Select all
<?php require_once('Connections/recommendingpeople.php'); ?>
<?php $businesscategory = addslashes($_GET['businesscat']);
$businessname = addslashes($_GET['businessname']);
$townborough = addslashes($_GET['townorborough']);
$city = addslashes($_GET['city']);
$statecounty = addslashes($_GET['statecounty']);
$country = addslashes($_GET['country']);
mysql_select_db($database_recommendingpeople, $recommendingpeople);
$query = "SELECT * FROM businessdetails WHERE businesscategory = '". mysql_real_escape_string($businesscategory) ."' OR businesscategory = '". mysql_real_escape_string($businesscat)."'
AND businessname = '". mysql_real_escape_string($businessname) ."' AND townborough =
'". mysql_real_escape_string($townborough) ."' AND city = '". mysql_real_escape_string($city) ."' ";
$result = mysql_query($query, $recommendingpeople) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows_businessdetails = mysql_num_rows($businessdetails);
if (!$result) {
echo "Got nothing";
} else {
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>
}
<?php mysql_free_result($businessdetails);?>- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: how to search recordset
Right. It comes down to personal preference, so I wouldn't be inclined to call it bad practice to use sprintf(), just as long as were clear on that.
Re: how to search recordset
still not there 
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: how to search recordset
Is it an sql error? (If you do receive an sql error please paste ittimoteo wrote:The error gets throught to line 17 which is the $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
Parse error: syntax error, unexpected T_STRING in /home/biomagn1/public_html/recommendingpeople.com/testing1.php on line 3
This was using this code (and typing out all the variables:
Then I tried with the code as using sprint f as generically made by dreamweaver mx2004. Here is the complete code:
When I ran this byitself it worked fine with no errors - sending out the first line from my database, however when I put in variables I got errors - sometimes this:
Not Acceptable
This was using this code (and typing out all the variables:
Code: Select all
<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
// the comment variables yielded the error until i typed
// them out. The commented section is the original
// values, below them is the 'new' typed out version
/*
$businesscategory = addslashes($_GET['businesscategory']);
$businesscategory2 = 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']);*/
$businesscategory = addslashes($_GET['businesscat']);
$businessname = addslashes($_GET['businessname']);
$townborough = addslashes($_GET['townorborough']);
$city = addslashes($_GET['city']);
$state = addslashes($GET_['statecounty']);
$county = addslashes($_GET['county']);
$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) ."' AND state = '". mysql_real_escape_string($state) ."'
AND county = '". mysql_real_escape_string($county) . "' AND country = '". mysql_real_escape_string($country)."'";
$businessdetails = mysql_query($query_businessdetails, $recommendingpeople) or die(mysql_error());
$row_businessdetails = mysql_fetch_assoc($businessdetails);
$totalRows_businessdetails = mysql_num_rows($businessdetails);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?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>
</body>
</html>
<?php mysql_free_result($businessdetails);?>Code: Select all
<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
$businessname_businessdetails = "1";
if (isset($_GET['businessname'])) {
$businessname_businessdetails = (get_magic_quotes_gpc()) ? $_GET['businessname'] : addslashes($_GET['businessname']);
}
$country_businessdetails = "1";
if (isset($_GET['country'])) {
$country_businessdetails = (get_magic_quotes_gpc()) ? $_GET['country'] : addslashes($_GET['country']);
}
$state_businessdetails = "1";
if (isset($_GET['state'])) {
$state_businessdetails = (get_magic_quotes_gpc()) ? $_GET['state'] : addslashes($_GET['state']);
}
$businesscategory_businessdetails = "1";
if (isset($_GET['businesscategory'])) {
$businesscategory_businessdetails = (get_magic_quotes_gpc()) ? $_GET['businesscategory'] : addslashes($_GET['businesscategory']);
}
$townborough_businessdetails = "1";
if (isset($_GET['townborough'])) {
$townborough_businessdetails = (get_magic_quotes_gpc()) ? $_GET['townborough'] : addslashes($_GET['townborough']);
}
mysql_select_db($database_recommendingpeople, $recommendingpeople);
$query_businessdetails = sprintf("SELECT * FROM businessdetails WHERE %s = '%s' OR %s = '%s' OR %s = '%s' OR %s = '%s' OR %s = '%s'", $businesscategory_businessdetails,$businesscategory_businessdetails,$businessname_businessdetails,$businessname_businessdetails,$townborough_businessdetails,$townborough_businessdetails,$state_businessdetails,$state_businessdetails,$country_businessdetails,$country_businessdetails);
$businessdetails = mysql_query($query_businessdetails, $recommendingpeople) or die(mysql_error());
$row_businessdetails = mysql_fetch_assoc($businessdetails);
$totalRows_businessdetails = mysql_num_rows($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>
<?php mysql_free_result($businessdetails);?>Not Acceptable
and sometimes this:An appropriate representation of the requested resource /testing.php could not be found on this server.
Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
. ('m' being data in my db)Unknown column 'm' in where clause
Re: how to search recordset
timoteo wrote:The error gets throught to line 17 which is the $query.
There is no variable $businesscat definedtimoteo wrote:Code: Select all
$businesscategory = addslashes($_GET['businesscat']); $businessname = addslashes($_GET['businessname']); $townborough = addslashes($_GET['townorborough']); $city = addslashes($_GET['city']); $statecounty = addslashes($_GET['statecounty']); $country = addslashes($_GET['country']); $query = "SELECT * FROM businessdetails WHERE businesscategory = '". mysql_real_escape_string($businesscategory) ."' OR businesscategory = '". mysql_real_escape_string($businesscat)."' AND businessname = '". mysql_real_escape_string($businessname) ."' AND townborough = '". mysql_real_escape_string($townborough) ."' AND city = '". mysql_real_escape_string($city) ."' ";
lol @ line 3, this is a comment?! wtf.timoteo wrote:Parse error: syntax error, unexpected T_STRING in /home/biomagn1/public_html/recommendingpeople.com/testing1.php on line 3
timoteo, your errors are the most confusing ever lol.
Might I suggest breaking it all up into smaller chunks, ie one line per command and lots of echo'ing to pinpoint the problem. Maybe even make the query simpler to get some results from the database, for instance;
Code: Select all
"SELECT * FROM businessdetails WHERE businesscategory = 'plumber';"You had a problem like this before, and the problem was your connection to your database and selecting the correct table. If all the posted code seems fine then it must be some other code.
Re: how to search recordset
Neilos my hero!! Thanks for the comment.
Ok bare basics. I tried using the model you showed me last time but I can't get passed the query. I guess this is the same problem all along.
Here is the code:
Ok bare basics. I tried using the model you showed me last time but I can't get passed the query. I guess this is the same problem all along.
Here is the code:
Code: Select all
<?php require_once('Connections/recommendingpeople.php'); ?>
<?php
$businesscategory = "b";
mysql_select_db($database_recommendingpeople, $recommendingpeople);
$query = "SELECT * FROM businessdetails WHERE businesscategory = '$businesscategory';";
$result = mysql_query($query, $recommendingpeople) or die(mysql_error());
$row_businessdetails = mysql_fetch_assoc($businessdetails);
$row = mysql_fetch_assoc($result);
if (!$result) {
echo "Got nothing";
} else {
echo $row['businesscategory'];
?>
<?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>
</body>
</html>
<?php mysql_free_result($businessdetails);?>Re: how to search recordset
Parse error: syntax error, unexpected T_STRING in /home/biomagn1/public_html/recommendingpeople.com/testing2.php on line 5
Re: how to search recordset
Where are these being set?
Try setting them in this php file.
This doesn't seem to be doing anything;
Try this code;
Code: Select all
$database_recommendingpeople, $recommendingpeople
This doesn't seem to be doing anything;
Code: Select all
$row_businessdetails = mysql_fetch_assoc($businessdetails);Code: Select all
<?php
//connect to the database here, change these please
$dbhost = 'localhost';
$dbname = 'your db name';
$dbuser = 'your db username';
$dbpass = 'your db password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $conn);
if (!$conn) {
die('Could not connect: ' . mysql_error());
}
$businesscategory = 'b';
$query = "SELECT * FROM businessdetails WHERE businesscategory='$businesscategory';";
$result = mysql_query($query);
if (!$result) {
echo "Got nothing";
} else {
// need the while statement as there is the possibility of more than one row returned
while ($row = mysql_fetch_array($result, MYSQL_ASSOC) {
echo $row['userid'] . '<br />';
echo $row['street'] . '<br />';
echo $row['businesscategory'] . '<br />';
echo $row['numbername'] . '<br />';
echo $row['businessname'] . '<br />';
echo 'That was a result set' . '<br />';
}
}
?>
Re: how to search recordset
that didn't work
However I wrote a code using what you had told me before (Nielos last week) as a model and it worked perfectly - echoeing out relevant country. Now how can I adapt this code to a multiple query where users may leave a field blank if they wish - if they don't wish to search city field they leave it blank and still get results on other fields with the results listed in order of best match?
Here is the code that worked:
This would suggest that database connection is good, no?
However I wrote a code using what you had told me before (Nielos last week) as a model and it worked perfectly - echoeing out relevant country. Now how can I adapt this code to a multiple query where users may leave a field blank if they wish - if they don't wish to search city field they leave it blank and still get results on other fields with the results listed in order of best match?
Here is the code that worked:
Code: Select all
<?php
require_once('Connections/recommendingpeople.php');
// I have put this equal to 'french' I assume that this is a valid username in rsusername, if not please change!
$businesscategory = addslashes($_GET['selectbusinesscat']);
mysql_select_db($database_recommendingpeople, $recommendingpeople);
$query = "SELECT * FROM businessdetails WHERE businesscategory='$businesscategory';";
$result = mysql_query($query, $recommendingpeople) or die(mysql_error());
$row = mysql_fetch_assoc($result);
if (!$result) {
echo "Got nothing";
} else {
echo $row['country'];
}
?>
Re: how to search recordset
Got it!!
The problem seemed to be in the variable from a drop down menu which included 'select' in the name - I need to be more vigilant.
this is the code
Now I have one further question - how can I get the results displayed in order of relevance. - i.e. there are 6 fields to search - if an entry matches the 6 fields it comes first then if it matches 5 out of six fields, then 4 out of 6 fields(with 2 mismatches if you see what I mean) etc.etc.
this is the code
Code: Select all
<?php
require_once('Connections/recommendingpeople.php');
$businesscategory = addslashes($_GET['businesscategory']);
$businesscat = addslashes($_GET['selectbusinesscat']);
$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 = '$businesscat' OR businesscategory='$businesscategory' OR businessname = '$businessname' OR townborough = '$townborough' OR city = '$city' OR state = '$statecounty' OR county = '$county' OR country = '$country';";
$result = mysql_query($query, $recommendingpeople) or die(mysql_error());
$row = mysql_fetch_assoc($result);
if (!$result) {
echo "Got nothing";
} else {
echo $row['country'];
echo $row['businesscategory'];
echo $row['businessname'];
echo $row['numbername'];
echo $row['street'];
echo $row['townborough'];
echo $row['city'];
echo $row['county'];
echo $row['state'];
echo $row['country'];
echo $row['postcode'];
echo $row['country'];
}
?>
Re: how to search recordset
New problem? - At the moment the recordset gets the first result it finds and repeats it 10 times. I need it not to repeat the same data over and over but to display 10 diferent record sets if they match the search in anyway. (displayed in order of best match.) How can I do it?
Here is my code (slightly edited to avoid excess html and css):
Here is my code (slightly edited to avoid excess html and css):
Code: Select all
<?php require_once('Connections/recommendingpeople.php'); ?>$maxRows = 10;
$pageNum = 0;
if (isset($_GET['pageNum'])) {
$pageNum = $_GET['pageNum'];
}
$startRow = $pageNum * $maxRows;
$businesscategory = addslashes($_GET['businesscategory']);
$businesscat = addslashes($_GET['selectbusinesscat']);
$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 = '$businesscat' OR businesscategory='$businesscategory' OR businessname = '$businessname' OR townborough = '$townborough' OR city = '$city' OR state = '$statecounty' OR county = '$county' OR country = '$country';";
$result = mysql_query($query, $recommendingpeople) or die(mysql_error());
$row = mysql_fetch_assoc($result);
if (isset($_GET['totalRows'])) {
$totalRows = $_GET['totalRows'];
} else {
$all = mysql_query($query);
$totalRows = mysql_num_rows($all);
}
$totalPages = ceil($totalRows/$maxRows)-1;
<form name="form2" method="post" action="">
<table width="60%" border="10" align="center" cellspacing="10" bgcolor="#FFFFFF">
<tr>
<td class="style8">You searched for: </td>
</tr>
<tr>
<td><?php echo $_GET['businesscategory']?>
<?php echo $_GET['selectbusinesscat']?>
<?php echo $_GET['townorborough']?>
<?php echo $_GET['city']?>
<?php echo $_GET['statecounty']?>
<?php echo $_GET['country']?>
</td>
</tr>
</table>
<p> </p>
<table width="80%" border="10" align="center" cellspacing="10" bgcolor="#FFFFFF" id="results">
<tr class="style8">
<td> </td>
<td>Business:</td>
<td colspan="2">Address:</td>
<td>e-mail, telephone </td>
</tr>
<tr>
<?php do { ?>
<td> </td>
<td><?php echo $row['businesscategory']?></td>
<td><?php echo $row['numbername']?></td>
<td><?php echo $row['street']?></td>
<td><?php echo $row['tel1']?></td>
</tr>
<tr>
<td> </td>
<td><?php echo $row['businessname']?></td>
<td><?php echo $row['townborough']?></td>
<td><?php echo $row['city']?></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><?php echo $row['county']?></td>
<td><?php echo $row['state']?></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><?php echo $row['postcode']?></td>
<td><?php echo $row['country']?></td>
<td> </td>
</tr>
<?php } while ($row_rsrecommendations = mysql_fetch_assoc($rsrecommendations)); ?>
</table>
</form>
?>Re: how to search recordset
wow.. nearly there and that's by myself :
I changed this to this. I need to watch those copy and paste - very dangerous.
There is one more issue to fix if anyone can help me out - My results are coming out in alphabetical order whereas I need them in order of best match. Any ideas?
I changed this
Code: Select all
<?php } while ($row_rsrecommendations = mysql_fetch_assoc($rsrecommendations)); ?>Code: Select all
<?php } while ($row = mysql_fetch_assoc($result)); ?>There is one more issue to fix if anyone can help me out - My results are coming out in alphabetical order whereas I need them in order of best match. Any ideas?