Query For Description Doesn't Work/ ID Does

[justlukeyou]

Hi,

 

I have a query based on ID number which works fine.  However, I am now trying to use it to query the description in the same manner.  However, when I use the description it doesn't echo anything at all and produces no errors.

 

This doesn't work.  The table is productdbase and the field is description.

 

<a href="/products/productsqueryresults.php?description=bed" rel="nofollow"  class='articlesfilterlink'>Bed</a>

 

<?php
if (isset($_GET['description']))
$ID = mysql_real_escape_string($_GET['description']);
$sql = "SELECT * FROM productdbase WHERE description = '$description'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res); // no need to loop since you are retrieving only one row
$num_rows = mysql_num_rows($res); // check to see if any results were found, just in case someone puts an ID in the url without clicking on your link
?>

 

This does work.  The table is articles and the field is ID.

 

<?php
if (isset($_GET['ID']))
$ID = mysql_real_escape_string($_GET['ID']);
$sql = "SELECT * FROM articles WHERE ID = '$ID'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res); // no need to loop since you are retrieving only one row
$num_rows = mysql_num_rows($res); // check to see if any results were found, just in case someone puts an ID in the url without clicking on your link
?>

 

I cant see why the ID works and not the description without creating an error.

[.josh]

for starters, you assign your description to $ID but use $description in your query.  But also, post what the error is.

[justlukeyou]

Hi, I changed the mistake of the ID to description but it still doesnt work.  The problem is it doesnt create an error, just doesn't echo anything.

 

Any ideas please?

 

<?php
if (isset($_GET['description']))
$description = mysql_real_escape_string($_GET['description']);
$sql = "SELECT * FROM productdbase WHERE description = '$description'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res); // no need to loop since you are retrieving only one row
$num_rows = mysql_num_rows($res); // check to see if any results were found, just in case someone puts an description in the url without 

clicking on your link
?>

[.josh]

please clarify what you mean by "it doesn't do anything".  did you echo $row or $num_rows out to see if you have something from the query?

[justlukeyou]

Hi,

 

This is the echo.  Im really puzzled because it works fine with ID but when I use description it doesn't echo anything or create any errors.

 

<?php echo $row['description']; ?>

[.josh]

so if you echo $description and $_GET['description'] are those blank?

[justlukeyou]

Hi,

 

When I echo $_GET['description']  it echoes whather I term I put into here:

 

/productsqueryresults.php?description=test

 

In this case it is 'test'

 

Im quite puzzled by that, when I run it on ID it works fine.

[.josh]

 

echo out your $sql variable and run it directly in your database (like through phpmyadmin). Do you get results?

 

[spiderwell]

and does the fact its different tables make any point?

[justlukeyou]

 

echo out your $sql variable and run it directly in your database (like through phpmyadmin). Do you get results?

 

 

Hi, how do I that please Josh.  I have echoed query strings but I am not sure how to echo the sql variable.

[.josh]

$sql = "SELECT * FROM productdbase WHERE description = '$description'";
echo $sql; // <--- add this line here

 

Now copy and paste what it is output.  Login to your mysql database, like through phpmyadmin.  Go to the database and productdbase table and click on the SQL button and paste that query in and run the query.  Do you get results returned?

[justlukeyou]

Thanks mate,

 

This is what it echoes:

 

SELECT * FROM productdbase WHERE description = 'lukas'

 

This is what MySQl returns "MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0046 sec )"

 

I find this really strange because it works on the ID and I have a search code which searches the description but querying the description doesn't seem to work.  Very puzzling.

[.josh]

I really wish you'd stop with the "..but it works with ID!" it's starting to get pretty annoying...  the code is similar but different in many ways, it is NOT the same code.  And on that note, you need to focus here and catch on to the whole debugging process.  Line by line, compare the differences between the two scripts, setup points to test at each difference.  Test by echoing out the relevant variables, etc..

 

So you have queried your database directly and got 0 results.  As spiderwell mentioned, are you querying the right table? do you see a value of 'lukas' in your description column in your table (example, SELECT description FROM productdbase to get a dump of all description values with no where clause)

[justlukeyou]

Apologies,

 

I was expecting it to work straight away so I was annoyed when it didn't work.

 

When I search 'lukas' this echoes from my search code but when I query it nothing comes out.

[.josh]

okay, well "search" is arbitrary, what code is behind this action?  Compare your search code to this new code.  Is it using the same database connection info? same database? same table? etc..

[justlukeyou]

Aha, the problem is that if the description is just one word and I use that one word then it will find it.  So 'widget' and 'widget' will work but 'red widget' wont be found.

 

Is there a way to query using one single word and for it to search everything. I tried the following in MySQL that didnt work.

 

SELECT * FROM productdbase WHERE description = '%lukas%'

 

I thought % was way to ignore what was left or right?

[.josh]

You have got to be shitting me.  Why didn't you mention this before?

[justlukeyou]

Because I didn't know.  Its a learning curve.

 

So how do I pick out a single word.  I thought % does this?

[.josh]

No offense, but neglecting to mention what you are trying to do, what values you are testing is not a coding issue, it's a common sense issue.  You told me you were testing with "test"!  You are not helping me help you!

 

= is an exact match operator in SQL . % only works with regex or 'contains' type operators, for example

 

SELECT * FROM productdbase WHERE description LIKE '%lukas%'

 

What exactly are you wanting to do here, return results for any of the words, like "foo bar" will return

 

"some foo description"

"some bar description"

 

or does it have to contain both words, but can occur anywhere, like

 

"some foo description bar"

[justlukeyou]

Thanks .josh for your great help.

 

Like foo bar would be fine so it picks up either word.

 

I tried both of the following % inserts but neither returned anything.  Does the link from the previous page need to be changed in anyway?

 

if (isset($_GET['description']))
$description = mysql_real_escape_string($_GET['description']);
$sql = "SELECT * FROM productdbase WHERE description = '%$description%'";

 

if (isset($_GET['description']))
$description = mysql_real_escape_string($_GET['description']);
$sql = "SELECT * FROM productdbase WHERE description = '%$lukas%'";

[.josh]

if (isset($_GET['description'])) {
  $description = explode(" ",urldecode($_GET['description']));
  $description = array_map('mysql_real_escape_string',$description);
  $description = "'".implode("','",$description)."'";
  $sql = "SELECT * FROM productdbase WHERE description IN ($description)";
  echo $sql;
}

[.josh]

here is a more complete example:

 

if (isset($_GET['description'])) {
  $description = explode(" ",urldecode($_GET['description']));
  $description = array_map('mysql_real_escape_string',$description);
  $description = "'".implode("','",$description)."'";
  $sql = "SELECT * FROM productdbase WHERE description IN ($description)";
  $res = mysql_query($sql) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    echo $row['description'] . "<br/>";
  }
}

[.josh]

actually wait I take that back, that's a list of exact matches...1s

[.josh]

Okay, this will perform a query that searches the description column for a value that contains the value you pass in description query string param.  If the value is more than one word, it will return results for each word.  So for example,

 

yoursite.com/yourscript.php?description=foo%20bar

 

will return

 

some foo description

some bar description

some foo bar description

 

 

if (isset($_GET['description'])) {
  $description = explode(" ",urldecode($_GET['description']));
  $description = array_map('mysql_real_escape_string',$description);
  $description = "'%" . implode("%' OR LIKE '%",$description). "%'";
  $sql = "SELECT * FROM productdbase WHERE description LIKE $description";
  $res = mysql_query($sql) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    echo $row['description'] . "<br/>";
  }
}

[justlukeyou]

Brilliant thanks,

 

This works a treat, it echos all the descrptions from the echo.

 

I am now trying it with DIVS but it kicks errors for the HTML.  If HTML is set inside a {} how should it be dealt with, do I need to enclose it commas or syntax?