query not working???

[fife]

ok Im building a search page.  The user can enter a name, county, category, address into my search field.  The results page will show the details based on that.  It works great so here is a snippet of the working code.

 

$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.*FROM Clubs

INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) "

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"));

 

This code works great no matter what I enter.  the problem Im having is I only want to show results based on the logged in users county.  I have a variable stored under $user['county']  which is the ID found in the County table under countyID

 

I have tried writing the code so many different ways.  Here is my latest attemped but it doesnt work.  It breaks the whole query and nothing is displayed.

 

$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}

$County= "-1";
if (isset($user['county'])) {
  $County= $user['county'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.* FROM Clubs
INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) AND County.countyID = %s"

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text")
String($County. "%", "int"));

 

Thanks

 

Danny

[Psycho]

I don't "see" any problems with the query (although you went overboard on the parens). Have you validated that $country is getting set? Per the sprintf() function country should be an int - is that correct? Where is $user['county'] set?

 

Assuming the query is not failing it is probably finding zero results due to a problem with the country value not being set correctly or not correct for what is in the database. Also, you are not sanitizing the user input which leaves you open to SQL Injection attacks.

 

Try the following code to help debug the problem:

$result = "-1";
if (isset($_POST['searchField'])) {
  $search_term = mysql_real_escape_string(trim($_POST['searchField']));
}

$County= "-1";
if (isset($user['county'])) {
  $County = $user['county'];
}

$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat,
                   Category.*, County.*
                   FROM Clubs
                   INNER JOIN Category ON Clubs.cat = Category.catID
                   INNER JOIN County ON Clubs.county = County.countyID
                   WHERE ( Clubs.name Like %s OR Clubs.cat LIKE %s OR County.county LIKE %s OR Clubs.area LIKE %s OR Clubs.postcode LIKE %s OR Category.categorys LIKE %s )
                     AND County.countyID = %s",
                   String($search_term . "%", "text"),
                   String($search_term . "%", "text"),
                   String($search_term . "%", "text"),
                   String($search_term . "%", "text"),
                   String($search_term . "%", "text"),
                   String($search_term . "%", "text")
                   String($County. "%", "int")
                 );

echo "DEBUG:<br>";
echo " - 'result' is set as {$result}<br>\n";
echo " - 'County' is set as {$County}<br>\n";
echo " - Query:<br>{$result}<br>\n";

 

What is output from the debug code?

[fife]

Thanks mjdamato,

 

You were correct.  the variable $user was empty because I had not started the page with session_start(); so none of my functions were not working

 

Again thank you so much.