Page 1 of 2

Build results page 1 search field multiple column search

Posted: Wed Apr 24, 2013 8:18 am
by jonnyfortis
i have a search box on a page

Code: Select all

            <form id="form1" name="form1" method="GET" action="search.php">
              <table width="270" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td><input name="Product" type="text" size="32"/></td>
                  <td><input type="submit" name="button" id="button" value="SEARCH" /></td>
                </tr>
              </table>
            </form>


i need the results to search 3 columns in the database and return the results. I have set up search to search just one column but cant get multiple columns to work

here is what i have so far in the results page

the table is called LOTTIE_products and i want to search columns called "Product", "Product" and one that isnt in the SQL at present that i will add when i get it working is ""colour"

$var1_rsSearch = "-1";
if (isset($_GET['searchID'])) {
$var1_rsSearch = $_GET['searchID'];
}
mysql_select_db($database_lotties, $lotties);
$query_rsSearch = sprintf("SELECT * FROM LOTTIE_products WHERE LOTTIE_products.Product LIKE %s AND LOTTIE_products.`Description` LIKE %s", GetSQLValueString("%" . $var1_rsSearch . "%", "text"),GetSQLValueString("%" . $var1_rsSearch . "%", "text"));
$rsSearch = mysql_query($query_rsSearch, $lotties) or die(mysql_error());
$row_rsSearch = mysql_fetch_assoc($rsSearch);
$totalRows_rsSearch = mysql_num_rows($rsSearch);

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 12:42 pm
by requinix
You shouldn't be AND-ing things, otherwise the search term has to be present in every single column. Otherwise I don't see any problems with it.
What did you try when you added the color?

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 1:05 pm
by jonnyfortis
You shouldn't be AND-ing things, otherwise the search term has to be present in every single column. Otherwise I don't see any problems with it.
what do i put in the and place?


What did you try when you added the color? i havent added colour yet

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 2:14 pm
by requinix
WHERE LOTTIE_products.Product LIKE %s AND LOTTIE_products.`Description` LIKE %s
If you're having problems with the current search, and it's not that it works now but not with color added like I thought you were saying, then the AND thing could definitely be it.

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 2:19 pm
by jonnyfortis
what i meant was i will add the colours and others if i can get it to search two columns of the database then add to it, i removed the AND and added OR but am getting no results

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 2:26 pm
by requinix
I don't see anything obviously wrong...

Echo out $query_rsSearch, check it for problems, and try running it yourself manually.

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 3:16 pm
by jonnyfortis
Echo out $query_rsSearch, check it for problems, and try running it yourself manually.
i added the following to the search.php

Code: Select all

<?php echo $query_rsSearch ?>
and got the following

Code: Select all

SELECT * FROM LOTTIE_products WHERE LOTTIE_products.Product LIKE '%-1%' OR LOTTIE_products.`Description` LIKE '%-1%' 
and try running it yourself manually.
what do i do for this?

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 4:04 pm
by requinix
...

The field is called "Product", not "searchID".

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 4:15 pm
by jonnyfortis
thats correct

<input name="Product" type="text" size="32"/>

and the sql on the search.php is

Code: Select all

$var1_rsSearch = "-1";
if (isset($_GET['Product'])) {
  $var1_rsSearch = $_GET['Product'];
}
mysql_select_db($database_lotties, $lotties);
$query_rsSearch = sprintf("SELECT * FROM LOTTIE_products WHERE LOTTIE_products.Product LIKE %s OR LOTTIE_products.`Description` LIKE %s", GetSQLValueString("%" . $var1_rsSearch . "%", "text"),GetSQLValueString("%" . $var1_rsSearch . "%", "text"));
$rsSearch = mysql_query($query_rsSearch, $lotties) or die(mysql_error());
$row_rsSearch = mysql_fetch_assoc($rsSearch);
$totalRows_rsSearch = mysql_num_rows($rsSearch);

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 4:49 pm
by requinix
Okay... Are there any other differences between your actual code and the stuff you've posted here?

Assuming not, what is the URL of the page you're sent to when you submit the search form?

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 5:00 pm
by jonnyfortis
No that is the code i have on my page the reason a part of it change to product was i was seeing if that made a difference.,,but it didnt

do u want the full url of search.php?

Re: Build results page 1 search field multiple column searc

Posted: Wed Apr 24, 2013 5:53 pm
by requinix
To make things easier, sure. But all I want to see is that the URL looks like "...search.php?Product=searchstring&button=SEARCH".

Re: Build results page 1 search field multiple column searc

Posted: Thu Apr 25, 2013 1:13 am
by jonnyfortis
To make things easier, sure. But all I want to see is that the URL looks like "...search.php?Product=searchstring&button=SEARCH".
no it doesnt.... looks like

http://www...../beta/search.php

just noticed the feild was set to "POST". just changed it to GET and now show results....

need to test on what columns it returns now

Re: Build results page 1 search field multiple column searc

Posted: Thu Apr 25, 2013 1:53 am
by requinix
(sigh)

See, this is why I asked if there were any more differences. You posted a form that had method=get and it turns out that it actually had method=post. That's important to know. So please, when people ask you if stuff you've posted actually is the stuff you have, don't just dismiss it. We're asking because it matters.

Re: Build results page 1 search field multiple column searc

Posted: Thu Apr 25, 2013 2:37 am
by jonnyfortis
Very sorry about that, I dont know how that happened, thanks so much for your help