Page 1 of 1

mysql: Search 2 fields w one search bar

Posted: Wed Jul 22, 2009 4:42 pm
by ninethousandfeet
hi,

this is coming back to a problem i had before and thought was resolved. it was working okay and now it is not.
the problem: the query is bringing up only one result from the table every time (and it's the same result every time)... i have the proper do ... while so that isn't it. it has to be something with the second part of the WHERE statement. if i remove the second part with postingTable.post_title, then users can search the one field of product_name and it works.
the problem with that is i need the search to run through two different fields to look for any matches.

Code: Select all

 
$var8 = $_POST['product_name'];
 
SELECT postingTable.post_id, postingTable.product_name, postingTable.buy_or_share, postingTable.user_id, postingTable.post_title, postingTable.post_date, DATE_FORMAT(post_date, '%c/%e') AS dateOFpost, postingTable.valid
FROM postingTable
WHERE postingTable.buy_or_share = 'share' AND postingTable.product_name LIKE %var8% OR postingTable.post_title LIKE %var8%
ORDER BY postingTable.post_date DESC
 
p.s. i tried replacing the OR with || and it made no difference

Re: mysql: Search 2 fields w one search bar

Posted: Wed Jul 22, 2009 8:19 pm
by califdon
You need to put quotes around the values you are searching for, unless they are numeric. I hope you are not feeding a Post variable directly into your database! That's extremely dangerous. Use mysql_real_escape_string().

Re: mysql: Search 2 fields w one search bar

Posted: Wed Jul 22, 2009 11:29 pm
by superdezign
ninethousandfeet wrote:the query is bringing up only one result from the table every time (and it's the same result every time)...
ninethousandfeet wrote:

Code: Select all

WHERE postingTable.buy_or_share = 'share' AND postingTable.product_name LIKE %var8% OR postingTable.post_title LIKE %var8%
As ~califdon mentioned, you need quotation marks around your values. Not having this should result in an error, so if this is the case and you are getting any sort of result, it is not from this query. Also, when you do put in quotation marks, you will also need to use a dollar sign ($) to denote variables.

Don't be so shy with your code. We won't steal it. :P
If you want our help, post the code so we can actually see what it is that you are doing and help you find the error.

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 8:46 am
by ninethousandfeet
Thanks for the responses. This is the full code, I shouldn't have left the bottom part out. I believe I have the quotes established so that isn't the problem.

When you mention
I hope you are not feeding a Post variable directly into your database! That's extremely dangerous. Use mysql_real_escape_string().
... How can I implement that here? Should I just use the $_GET method in this case?

Full code for this search query:

Code: Select all

 
$var8_getProduct = "-1";
if (isset($_POST['product_name'])) {
  $var8_getProduct = $_POST['product_name'];
}
mysql_select_db($database_connUser, $connUser);
$query_getProduct = sprintf("SELECT postingTable.post_id, postingTable.product_name, postingTable.buy_or_share, postingTable.user_id, postingTable.post_title, postingTable.post_date, DATE_FORMAT(post_date, '%%c/%%e') AS dateOFpost, postingTable.valid FROM postingTable WHERE postingTable.buy_or_share = 'share' AND postingTable.product_name OR postingTable.post_title LIKE %s ORDER BY postingTable.post_date DESC", GetSQLValueString("%" . $var8_getProduct . "%", "text"));
$getProduct = mysql_query($query_getProduct, $connUser) or die(mysql_error());
$row_getProduct = mysql_fetch_assoc($getProduct);
$totalRows_getProduct = mysql_num_rows($getProduct);
 

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 11:39 am
by califdon
ninethousandfeet wrote:Thanks for the responses. This is the full code, I shouldn't have left the bottom part out. I believe I have the quotes established so that isn't the problem.

When you mention
I hope you are not feeding a Post variable directly into your database! That's extremely dangerous. Use mysql_real_escape_string().
... How can I implement that here? Should I just use the $_GET method in this case?
Definitely NOT. Read the manual: http://us2.php.net/mysql_real_escape_string.
Full code for this search query:

Code: Select all

 
$var8_getProduct = "-1";
if (isset($_POST['product_name'])) {
  $var8_getProduct = $_POST['product_name'];
}
mysql_select_db($database_connUser, $connUser);
$query_getProduct = sprintf("SELECT postingTable.post_id, postingTable.product_name, postingTable.buy_or_share, postingTable.user_id, postingTable.post_title, postingTable.post_date, DATE_FORMAT(post_date, '%%c/%%e') AS dateOFpost, postingTable.valid FROM postingTable WHERE postingTable.buy_or_share = 'share' AND postingTable.product_name OR postingTable.post_title LIKE %s ORDER BY postingTable.post_date DESC", GetSQLValueString("%" . $var8_getProduct . "%", "text"));
$getProduct = mysql_query($query_getProduct, $connUser) or die(mysql_error());
$row_getProduct = mysql_fetch_assoc($getProduct);
$totalRows_getProduct = mysql_num_rows($getProduct);
 
I was going to comment that you are making your code incredibly complicated, using sprintf(), repeating the table name for every column, etc. And then I noticed GetSQLValueString(), which I don't recognize, so I looked it up, and it appears that you may be using Dreamweaver. If that is the case, you needed to tell us that at the beginning, because that makes a big difference in trying to help you. I will back out of this thread because I have no experience with Dreamweaver and wouldn't want to give you misleading advice.

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 8:42 pm
by ninethousandfeet
I am using Dreamweaver.

The GetSQLValueString() is a function dw creates to prevent magic quotes.

What would you recommend that I do given my current code? I can take out the above mentioned function and code it slightly differently to how you are recommending and I run into the same problem.

The sql works 100% okay until I add the second part in the WHERE clause: OR postingTable.post_title LIKE "$var8"

I'm open for ideas, thank you!

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 8:50 pm
by califdon
I suspect you are getting trapped in the quoted strings in your sprintf() function, but since I never use that, and have never used Dreamweaver, I simply can't offer any suggestions. No doubt someone else with DW experience will be able to help you.

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 10:19 pm
by ninethousandfeet
Okay, I'll see if I can find some other DW forums to get answers.

Going back to your original statement of POST without using the mysql_real_escape_string()... I am trying to read up on POST v. GET and I thought it was standard practice to use GET for search pages. I don't have to use the DW standards if there is a better and more secure way to do it, I am just new to all of this so anyone more suggestions in this matter would help.

I also took a look at the link you provided, should I be using that instead of mysql_query($query_getSellerSearch, $connUser) or die(mysql_error());

I'll keep looking for answers, thanks for the help.

Re: mysql: Search 2 fields w one search bar

Posted: Thu Jul 23, 2009 10:42 pm
by ninethousandfeet
also, this might help someone to understand my problem better... code that leads into my sql code:

Code: Select all

 
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
 
any ideas on this last part with the quotes? this seems to be everyones recommendation is that there is something wrong with the quotes.

thanks.