Build results page 1 search field multiple column search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Build results page 1 search field multiple column search

Post 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);
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post by requinix »

I don't see anything obviously wrong...

Echo out $query_rsSearch, check it for problems, and try running it yourself manually.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post by requinix »

...

The field is called "Product", not "searchID".
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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);
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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".
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post by jonnyfortis »

Very sorry about that, I dont know how that happened, thanks so much for your help
Post Reply