Search Code - Giving Column Name Keywords?

[justlukeyou]

I have some search code which I have been struggling with so I added a or die(mysql_error()); and it came back with the following message:

 

SELECT `description`, `fulldescription` FROM `productdbase` WHERE `keywords` LIKE '%tokyo%'Unknown column 'keywords' in 'where clause'

 

So I take it I am calling the column name 'keywords' where I as I should be calling it 'description'.  I have tried around 5 or 6 variations but I cant see where its called keywords.  Im confused as to what is call it keywords.

 

<?php
if (isset($_POST['keywords'])){
$keywords = mysql_real_escape_string (htmlentities(trim($_POST['keywords'])));
}

$errors = array();

$results = null;

if (empty($keywords)) {
$errors[] = 'Please enter a search term';
} else if (strlen($keywords)<3) {
$errors[] = 'Your search must be three or more characters';
} else if (($results = search_results($keywords)) === false) {
$errors[] = 'Your search for '.$keywords.' returned no results';
}

if (empty($errors))  {
foreach($errors as $error) {
echo $error, '</br>';
}


search_results ($keywords);

} else{
foreach($errors as $error) {
echo $error, '</br>';
}
}
    ?>


<?php
function search_results ($keywords) {
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword) {
$where .= "`keywords` LIKE '%$keyword%'";
if ($key != ($total_keywords - 1)) {
$where .= " AND ";

}
}

$query_string = "SELECT `description`, `fulldescription`  FROM `productdbase` WHERE $where";
echo $query_string;

$query = mysql_query($query_string) or die(mysql_error());


$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;




if ($results_num === 0) {
return false;
}else{

echo 'something.';

}
}
?>

[justlukeyou]

What I have done now is to add a keywords column to my database and add keywords to the query and it still not reading the test keywords which I have added to the DB.

 

$query_string = "SELECT `keywords`, `description`, `fulldescription`  FROM `productdbase` WHERE $where";

 

Its strange, even when add a keywords column to test its still not working.

[Jessica]

foreach($keywords as $key=>$keyword) {

$where .= "`keywords` LIKE '%$keyword%'";

if ($key != ($total_keywords - 1)) {

$where .= " AND ";

 

You're the one who is saying to search the keywords column.

[justlukeyou]

So what do I need to change? How do I say I want to read column description?

 

The thing is I have added a test column called 'keywords' and its still not finding the test terms I placed in there.

 

 

 

[mrMarcus]

What does

 

echo $query_string;

 

return?

[justlukeyou]

 

SELECT `keywords`, `description`, `fulldescription` FROM `productdbase` WHERE `keywords` LIKE '%cat%'Your search for cat returned no results

[Jessica]

You see the code I copied from yours? Where you have keywords as the column name, change it.

[justlukeyou]

Without the test column 'keywords' in it returns this:

 

SELECT `keywords`, `description`, `fulldescription` FROM `productdbase` WHERE `keywords` LIKE '%cat%'Unknown column 'keywords' in 'field list

[Jessica]

In your where clause, you are telling to to search the COLUMN `keywords`. Do you see that?

[justlukeyou]

Okay so I changed it to:

 

foreach($keywords as $key=>$keyword) {

$where .= "`description` LIKE '%$keyword%'";

if ($key != ($total_keywords - 1)) {

$where .= " AND ";

 

and it came back with this:

 

SELECT `keywords`, `description`, `fulldescription` FROM `productdbase` WHERE `description` LIKE '%tokyo%'Your search for tokyo returned no results

 

I take it im changing the wrong part.

[Jessica]

No, you changed the right part.

 

Next problem:

 

$query_string = "SELECT `description`, `fulldescription`  FROM `productdbase` WHERE $where";
echo $query_string;

$query = mysql_query($query_string) or die(mysql_error());

$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;

 

You are running the query, then getting the number of results based on another string: $results which does not exist.

 

You will always get 0.

[justlukeyou]

They are product titles.

 

On my search page I have them echoed out as product display, but when I try to search for them it says nothing found.

 

So frustrating, I want some good code that work slol

[justlukeyou]

Okay so do I need to change $results to $query_results?

[mrMarcus]

Okay so do I need to change $results to $query_results?

 

Why would you do that?

[Jessica]

Why would you pick $query_results? Is that defined anywhere?

 

Look at the code and read it as if it's a sentence. Does $query_results make sense to use anywhere?

The entire last line makes no sense anyway.

$results_num = mysql_num_rows($query); would return 0 if it's 0, you don't need a complicated ternary that makes no sense.

[justlukeyou]

OMG Im so stressed about this.  Its really getting to me.

 

But the amazing thing is I just changed one thing which I cant even remember and its just returned "something.something." which is exactly what it should return.  Even the test column works.

 

And whats shocked me even me even more, when I enter 'toad' which isn't in my db it returns Your search for toad returned no results.

 

Im so pleased I think I just wet myself.

 

Thank you ever so much guys.

[justlukeyou]

Oh this is killing me.  It no longers, I tried to remove the query string and now it returns 'Query was empty'.

 

Could someone please kindly point out where its going wrong.  You know those times when you get incredibly stressed, im incredibly stressed lol

 

if (isset($_POST['keywords'])){
$keywords = mysql_real_escape_string (htmlentities(trim($_POST['keywords'])));
}

$errors = array();

$results = null;

if (empty($keywords)) {
$errors[] = 'Please enter a search term';
} else if (strlen($keywords)<3) {
$errors[] = 'Your search must be three or more characters';
} else if (($results = search_results($keywords)) === false) {
$errors[] = 'Your search for '.$keywords.' returned no results';
}

if (empty($errors))  {
foreach($errors as $error) {
echo $error, '</br>';
}


search_results ($keywords);

} else{
foreach($errors as $error) {
echo $error, '</br>';
}
}
    ?>


<?php
function search_results ($keywords) {
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword) {
$where .= "`description` LIKE '%$keyword%'";
if ($key != ($total_keywords - 1)) {
$where .= " AND ";

}
}

$results = "SELECT `description`, `fulldescription`  FROM `productdbase` WHERE $where";
$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;

$query = mysql_query($query_string) or die(mysql_error());


$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;




if ($results_num === 0) {
return false;
}else{

echo 'something.';

[Jessica]

The entire last line makes no sense anyway.

$results_num = mysql_num_rows($query); would return 0 if it's 0, you don't need a complicated ternary that makes no sense.

[justlukeyou]

Okay so I have removed this part " ? mysql_num_rows($results) : 0" and now it returns the following:

 

So now I need to shop echoing the query?

 

SELECT `description`, `fulldescription` FROM `productdbase` WHERE `description` LIKE '%clearance%'something.SELECT `description`, `fulldescription` FROM `productdbase` WHERE `description` LIKE '%clearance%'something.

 

With this should I just show echo something?

 

if ($results_num === 0) {

return false;

}else{

 

echo 'something.';

 

}

[justlukeyou]

I could SCREAM

 

Now when I enter 'dippledopple' it still returns something. something.

 

jesirose, is incredibly appreciated but I just dont understand what you mean.  Could you please explain a bit further what the issues.

 

Im going round in big circles with this.

 

Once again, thanks for all your help.

[Jessica]

Post your current code.

[justlukeyou]

 

 

<?php
if (isset($_POST['keywords'])){
$keywords = mysql_real_escape_string (htmlentities(trim($_POST['keywords'])));
}

$errors = array();

$results = null;

if (empty($keywords)) {
$errors[] = 'Please enter a search term';
} else if (strlen($keywords)<3) {
$errors[] = 'Your search must be three or more characters';
} else if (($results = search_results($keywords)) === false) {
$errors[] = 'Your search for '.$keywords.' returned no results.  Please place another search.';
}

if (empty($errors))  {
foreach($errors as $error) {
echo $error, '</br>';
}


search_results ($keywords);

} else{
foreach($errors as $error) {
echo $error, '</br>';
}
}
    ?>


<?php
function search_results ($keywords) {
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword) {
$where .= "`description` LIKE '%$keyword%'";
if ($key != ($total_keywords - 1)) {
$where .= " AND ";

}
}

$query_string = "SELECT `description`, `fulldescription`  FROM `productdbase` WHERE $where";
echo $query_string;

$query = mysql_query($query_string) or die(mysql_error());


$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;




if ($results_num === 0) {
return false;
}else{

echo 'something.';

}
}
?>

[justlukeyou]

Oh how stupid could I be, when I use this code without the query string it works.  At least it appears to.  British people sometimes!

 

<?php
if (isset($_POST['keywords'])){
$keywords = mysql_real_escape_string (htmlentities(trim($_POST['keywords'])));
}

$errors = array();

$results = null;

if (empty($keywords)) {
$errors[] = 'Please enter a search term';
} else if (strlen($keywords)<3) {
$errors[] = 'Your search must be three or more characters';
} else if (($results = search_results($keywords)) === false) {
$errors[] = 'Your search for '.$keywords.' returned no results.  Please enter another search.';
}

if (empty($errors))  {
foreach($errors as $error) {
echo $error, '</br>';
}


search_results ($keywords);

} else{
foreach($errors as $error) {
echo $error, '</br>';
}
}
    ?>


<?php
function search_results ($keywords) {
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword) {
$where .= "`description` LIKE '%$keyword%'";
if ($key != ($total_keywords - 1)) {
$where .= " AND ";

}
}

$results = "SELECT `keywords`, `description`, `fulldescription`  FROM `productdbase` WHERE $where";



$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;




if ($results_num === 0) {
return false;
}else{

echo 'something.';

}
}
?>

[justlukeyou]

Yeah its definately working now.  It put in 'fulldescription' into here it will only search the fulldescription column.

 

foreach($keywords as $key=>$keyword) {

$where .= "`fulldescription` LIKE '%$keyword%'";

if ($key != ($total_keywords - 1)) {

$where .= " AND ";

 

However, if I do this

 

foreach($keywords as $key=>$keyword) {

$where .= "`fulldescription' , `description'  LIKE '%$keyword%'";

if ($key != ($total_keywords - 1)) {

$where .= " AND ";

 

it wont search anything. Can I get it two search 2 columns?