Page 2 of 3

Re: how to search recordset

Posted: Thu Jan 20, 2011 4:16 pm
by timoteo
connects to my server. without it nothing works

Re: how to search recordset

Posted: Fri Jan 21, 2011 1:24 am
by social_experiment
I went through the code and typed out the variables and the problem disappeared :crazy: I don't know what happened with it but try typing the values out instead of copying them from here.

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 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

Re: how to search recordset

Posted: Fri Jan 21, 2011 6:30 am
by timoteo
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?

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);?>

Re: how to search recordset

Posted: Fri Jan 21, 2011 8:48 am
by John Cartwright
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

Posted: Fri Jan 21, 2011 5:55 pm
by timoteo
still not there :banghead:

Re: how to search recordset

Posted: Sat Jan 22, 2011 12:23 am
by social_experiment
timoteo wrote:The error gets throught to line 17 which is the $query
Is it an sql error? (If you do receive an sql error please paste it :) )

Re: how to search recordset

Posted: Sat Jan 22, 2011 5:17 am
by timoteo
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:

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);?>
Then I tried with the code as using sprint f as generically made by dreamweaver mx2004. Here is the complete code:

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);?>
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
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.
and sometimes this:
Unknown column 'm' in where clause
. ('m' being data in my db)

Re: how to search recordset

Posted: Sat Jan 22, 2011 9:57 am
by Neilos
timoteo wrote:The error gets throught to line 17 which is the $query.
timoteo 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) ."' ";
There is no variable $businesscat defined
timoteo wrote:Parse error: syntax error, unexpected T_STRING in /home/biomagn1/public_html/recommendingpeople.com/testing1.php on line 3
lol @ line 3, this is a comment?! wtf.

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';"
Also get rid of all the junk like the html stuff that isn't needed right now. Also try not using the GET variables, just write them in manually for now until you get it working.

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

Posted: Sat Jan 22, 2011 3:50 pm
by timoteo
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. :banghead:
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

Posted: Sat Jan 22, 2011 3:51 pm
by timoteo
Parse error: syntax error, unexpected T_STRING in /home/biomagn1/public_html/recommendingpeople.com/testing2.php on line 5

Re: how to search recordset

Posted: Sat Jan 22, 2011 4:26 pm
by Neilos
Where are these being set?

Code: Select all

$database_recommendingpeople, $recommendingpeople
Try setting them in this php file.

This doesn't seem to be doing anything;

Code: Select all

$row_businessdetails = mysql_fetch_assoc($businessdetails);
Try this code;

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

Posted: Sat Jan 22, 2011 5:59 pm
by timoteo
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:

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'];
}


?>
This would suggest that database connection is good, no?

Re: how to search recordset

Posted: Sat Jan 22, 2011 6:41 pm
by timoteo
Got it!! :D 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

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'];
}


?>
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.

Re: how to search recordset

Posted: Sat Jan 22, 2011 8:16 pm
by timoteo
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):

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>&nbsp;</p>
      <table width="80%"  border="10" align="center" cellspacing="10" bgcolor="#FFFFFF" id="results">
        <tr class="style8">
          <td>&nbsp;</td>
          <td>Business:</td>
          <td colspan="2">Address:</td>
          <td>e-mail, telephone </td>
        </tr>
        <tr>
           <?php do { ?>
		   <td>&nbsp;</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>&nbsp;</td>
          <td><?php echo $row['businessname']?></td>
          <td><?php echo $row['townborough']?></td>
          <td><?php echo $row['city']?></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td><?php echo $row['county']?></td>
          <td><?php echo $row['state']?></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td><?php echo $row['postcode']?></td>
          <td><?php echo $row['country']?></td>
          <td>&nbsp;</td>
        </tr>
		  <?php } while ($row_rsrecommendations = mysql_fetch_assoc($rsrecommendations)); ?>
      </table>
    </form>
?>

Re: how to search recordset

Posted: Sat Jan 22, 2011 8:33 pm
by timoteo
wow.. nearly there and that's by myself :
I changed this

Code: Select all

<?php } while ($row_rsrecommendations = mysql_fetch_assoc($rsrecommendations)); ?>
to this

Code: Select all

 <?php } while ($row = mysql_fetch_assoc($result)); ?>
. 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?