problem with search query and pagination

[da_marek]

hi!
i have a problem with my search results. i have two pages (index.php and searchform.php) that are almooost identical. only difference is in sql query:
query from index.php is:

$query = "SELECT * FROM mcs";

and query from searchform.php is:

$search = explode(' ', $pojam);
$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
	while (list(,$pojam) = each($search)){
		$query .= " OR $kategorija LIKE '%$pojam%'";
		$i++;
	}
 $query .= " LIMIT $start,$pagelimit";

$pojam is search string and $kategorija was selection from specified column in database

i know that problem is in lines

while (list(,$pojam.........

and below because when i comment that part everything is fine but i cant display results for two or more words search.
is my syntax wrong? is whole procedure wrong? i want when somebody search for two or more words to display records which are related to ALL of the words... please help + thanks!

[da_marek]

yes, and i forgot to say that im getting some stupid errors like:

Warning: Supplied argument is not a valid MySQL result resource in /home/virtual/site45/fst/var/www/html/v310/searchform.php on line 36

Warning: Supplied argument is not a valid MySQL result resource in /home/virtual/site45/fst/var/www/html/v310/searchform.php on line 86

Warning: Supplied argument is not a valid MySQL result resource in /home/virtual/site45/fst/var/www/html/v310/searchform.php on line 182

line 36: $totalrows = mysql_num_rows($result);
line 86: while($row = mysql_fetch_array($result)) {
line 182: mysql_free_result($result);

i suppose that is because my query fails:

$search = explode(' ', $pojam);
$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
	while (list(,$pojam) = each($search)){
		$query .= " OR $kategorija LIKE '%$pojam%'";
		$i++;
	}
 $query .= " LIMIT $start,$pagelimit";

$result = mysql_query($query);
$totalrows = mysql_num_rows($result); // this is LINE 36

is it?

[cgraz]

I briefly skimmed over this, but here's my thoughts. in your code

$search = explode(' ', $pojam);
$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
    while (list(,$pojam) = each($search)){
        $query .= " OR $kategorija LIKE '%$pojam%'";
        $i++;
    }
 $query .= " LIMIT $start,$pagelimit";

You split the words using explode into an array called search. So the first word would be $search["0"], the second word would be $search["1"], but you're using $pojan in your query, not the actual search terms they selected. to see what i mean, print out your query before you execute it.

Right after this line: $query .= " LIMIT $start,$pagelimit";

put this

print $query . "<br>";

that will show you what the string being executed in the query looks like.

The errors you are getting are because your query is probably failing. By printing the query, you'll be able to see why it's not working.

I haven't used much of the list or each functions, but printing the query and also adding mysql_error() to your die when you execute the query will help you solve the problem.

Cgraz

[da_marek]

ok, i solved that part but now i have other problems 🙂
fisrt look at my code:

//connection stuff here//
	$pagelimit = "10";
if ($page==''){
	$page='1';
}

$start = ($page-1) * $pagelimit;

$pagenums = ceil ($totalrows/$pagelimit);

$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$search = explode(' ', $pojam);
$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
	while (list(,$pojam) = each($search)){
		$query .= " OR $kategorija LIKE '%$pojam%'";
		$i++;
	}
 $query .= " LIMIT $start, $pagelimit";

$result = mysql_query($query);
echo $query . "<br>";
$totalrows = mysql_num_rows($result);


echo "total found <font color='#FF0000'><b>";
echo $totalrows;
echo "</b></font> records...<br><br>";
echo "displaying from $starting_no to $end_count,<br>";
echo "and there's more: ";

for ($j=1; $j<=$pagenums; $j++) {
        if ($j!=$page) {
            echo " <a href='searchform.php?page=$j'>$j</a> ";
        }
        else {
            echo " <b>$j</b>";
        }
    }

this code works good with my index.php where query looks like

	$query = "SELECT * FROM mcs";

but in searchform.php records are ALWAYS limited to 10. go here mesmerized.ws/v310 and try to search for something and you will see what am i talking about.
example: i type 'a' in search form and im 100% shure that there are 34 records with letter 'a' but query says there are only 10 and only 10 is displaying
another weird thing is that when i comment line

 $query .= " LIMIT $start, $pagelimit";

and try again to search for 'a' search engine finds all 34 records but then its not paginated.

[gtibok]

hi ...
You search $kategorija as a mysql field ... but there can't be such field, starting with $ ...
you should try

$query = "SELECT * FROM mcs WHERE kategorija LIKE '%$pojam%'";

[da_marek]

thanks for help but kategorija must be $kategorija because it is a string passed from a form on index.php page

<form action=searchform.php method=GET>
      <input name="pojam" type="text" class="INPUT" size=25>
      <select name="kategorija" class="INPUT">
        <option value="artist_1">bendu</option>
        <option value="title_1">albumu</option>
        <option value="price_kn">cijeni</option>
      </select>
      <input type=submit class="BUTTON" value="GO!">
  </form>

by the way... can i in this option tag put more than one column? i mean something like this:

<option value="artist_1, artist_2, artist_3...">bendu</option>

[cgraz]

You need 2 queries.

the first query is identical to what you have, minus the LIMIT part. You count how manyr esults there are, and do all your calculations using that first query. THEN

run the query again using the LIMIT, then display those results. You're only getting 10 results because the query that does all the calculations has a LIMIT of 10. Try something like this

$pagelimit = "10";

//connection stuff here//
    $pagelimit = "10";
if ($page==''){
    $page='1';
}

$search = explode(' ', $pojam);
$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
    while (list(,$pojam) = each($search)){
        $query .= " OR $kategorija LIKE '%$pojam%'";
        $i++;
    }
$result = mysql_query($query) or die(mysql_error());

$totalrows = mysql_num_rows($result);

$start = ($page-1) * $pagelimit;

$pagenums = ceil ($totalrows/$pagelimit);

$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$query2 = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
    while (list(,$pojam) = each($search)){
        $query .= " OR $kategorija LIKE '%$pojam%'";
        $i++;
    }
 $query2 .= " LIMIT $start, $pagelimit";

$result2 = mysql_query($query2);
echo $query2 . "<br>";


echo "total found <font color='#FF0000'><b>";
echo $totalrows;
echo "</b></font> records...<br><br>";
echo "displaying from $starting_no to $end_count,<br>";
echo "and there's more: ";

for ($j=1; $j<=$pagenums; $j++) {
        if ($j!=$page) {
            echo " <a href='searchform.php?page=$j'>$j</a> ";
        }
        else {
            echo " <b>$j</b>";
        }
    }

Also, when you search for a, your query looks like this:

SELECT * FROM mcs WHERE artist_1 LIKE '%a%' OR artist_1 LIKE '%a%' LIMIT 0, 10

you specify that you want artist_1 to be LIKE %a% twice.

And if you want to select everything that STARTS with an A, then don't do LIKE '%a%'

do LIKE 'a%'

Cgraz

[diego25]

can i in this option tag put more than one column? i mean something like this:

You can, but then you'd have to modify your code to work with that.

Diego

[da_marek]

wow! thanks a lot cgraz that was really helpful! i did everything like you said and its working great now! i have just few more questions:
do i do mysql_fetch_array($result) or mysql_fetch_array($result2)?
in first case results are not limited (obviously) and in second case results are limited but then the problem is in this line:

echo " <a href='searchform.php?page=$j'>$j</a> ";

because i cant browse other results... how do i put link to self page with those things B[/B] and also add page=$j???
about that query thing B[/B]... its okay like this because when i search for 'a' i want to display all records that have a letter 'a'... but thanks for advice 😉

hey diego25 can you help me with that part (i hope its not complicated a lot?) because i have two 'artist' columns and when somebody searches for artist name i want to search both columns (artist_1 and artist_2)... same thing is with title_1 and title_2 etc...

thanks a lot everybody! you guys are great 😉

[diego25]

You can use
$multiple = explode(',', $kategorija);

And then use a for loop to create the query for each element of $multiple ($multiple is an array).

So it'd end up like

WHERE title_1 = 'something' OR title_2 = 'something'

Diego

[da_marek]

hey diego!
thanks a lot, but i must admit that i dont understand how that would help me? im not sure if you understood me? i dont know english very good but ill try to explain it again...

so, i have table with two columns: title_1 and title_2.
on my index.php page i have a form that looks like this:

<form action=searchform.php method=GET>
      <input name="pojam" type="text" class="INPUT" size=25>
      <select name="kategorija" class="INPUT">
        <option value="artist_1">bendu</option>
        <option value="title_1">albumu</option>
        <option value="price_kn">cijeni</option>
      </select>
      <input type=submit class="BUTTON" value="GO!">
</form>

now, when somebody want to search for title he will select albumu option. in my case search looks only in title_1 column but i want it to look in title_1 and title_2 columns.

thats right that my query at the end must look like this:

SELECT * FROM mcs WHERE title_1 LIKE '%$pojam%' OR title_2 LIKE '%$pojam%'

but you see... i cant put in query just title_1 and title_2!!! what if somebody wants to search for artist? then my query must look like this:

SELECT * FROM mcs WHERE artist_1 LIKE '%$pojam%' OR artist_2 LIKE '%$pojam%'

so, my conclusion is this:
instead of title_1 and title_2 or artist_1 and artist_2 or whatever, in my query MUST be $kategorija.

ok, if this is correct then i think that the problem is in that form on index.php page. is it??? i think that i have to tell to the form to search in more that one column... how to do that? oh my god...
THANKS!

[cgraz]

mysql_fetch_array($result2)?

result2 becuase that's the query that you're actually using. The first query is just to make some simple calculations.

because i cant browse other results... how do i put link to self page with those things (searchform.php?pojam=a&kategorija=artist_1) and also add page=$j???

echo " <a href='searchform.php?page=$j&pojam=$pojam&kategorija=$kategorija'>$j</a> ";

You may need to modify that slightly to fit your code, but that's the logic behind it.

Cgraz

[da_marek]

yes, thats exactly what i did but when i go to second or third or whatever page there is no more $pojam
this is link for second page:

searchform.php?page=2&pojam=&kategorija=artist_1

why there is no pojam=a??? where did he go??

[cgraz]

The below code will create your query dynamically depending on what was selected.

Replace

$query = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
    while (list(,$pojam) = each($search)){
        $query .= " OR $kategorija LIKE '%$pojam%'";
        $i++;
    }

with

$query = "SELECT * FROM mcs";

foreach($_POST as $key => $value) {
   if ($query == "SELECT * FROM mcs") {
      $query .= " WHERE $key='$value'";
   } else {
      $query .= " AND $key='$value'";
   }
} // you will probably want to echo the query to make sure this works right

Then replace this

$query2 = "SELECT * FROM mcs WHERE $kategorija LIKE '%$pojam%'";
    while (list(,$pojam) = each($search)){
        $query .= " OR $kategorija LIKE '%$pojam%'";
        $i++;
    }
 $query2 .= " LIMIT $start, $pagelimit";

$result2 = mysql_query($query2);
echo $query2 . "<br>";

with this

$query2 = "SELECT * FROM mcs";

foreach($_POST as $key => $value) {
   if ($query2 == "SELECT * FROM mcs") {
      $query2 .= " WHERE $key='$value'";
   } else {
      $query2 .= " AND $key='$value'";
   }
} 

$query2 .= " LIMIT $start, $pagelimit";

$result2 = mysql_query($query2) or die(mysql_error());
echo $query2 . "<br>";

Cgraz

[da_marek]

hey cgraz!!!
i really appreciate your very quick help but now im totally lost and confused! look, im working with php for a few weeks so i dont understand all php fundamentals. so, please excuse me if im difficult.

ok, i tried to do what you told me but when i put this

$query = "SELECT * FROM mcs";

foreach($_POST as $key => $value) {
   if ($query == "SELECT * FROM mcs") {
      $query .= " WHERE $key='$value'";
   } else {
      $query .= " AND $key='$value'";
   }
}

i get an error that looks like this:
Warning: Invalid argument supplied for foreach() in /home/virtual/site45/fst/var/www/html/v310/searchform.php on line 33
.
line 33 is foreach($_POST............
also im not really sure if $key is $kategorija in my case and if $value is $pojam????
god damn! my headache is so strong now! 🙁

[cgraz]

Ah, I just noticed this. The method on your form is GET. change it to POST

<form action=searchform.php method=POST>

or if you're set on using GET, then change the foreach to

foreach($_GET as $key => $value) {

$key would be the name of the form field and $value would be the value to search for.

Cgraz

[da_marek]

hee hee! i tried to be stupid but i won!!! 🙂 thanks again for help but i managed to do it myself... and i think its so stupid and lame way 🙂

what i did is this:

//form stuff//
<option value="artist_1 OR artist_2">bendu</option>
<option value="title_1 OR title_2">albumu</option>
//form stuff//

and now my query looks like this:
SELECT * FROM mcs WHERE artist_1 OR artist_2 LIKE '%sodnji%' OR artist_1 OR artist_2 LIKE '%sodnji%'
i know that this is okay because sodnji is located in artist_2 column. but i have noticed that whatever i search for i always get first record from database as search result... i search for sodnji and im 100% sure that there's only one record with that name but theres always that stupid 3 way cum record displaying... why???
you can check that out on this location:
http://www.mesmerized.ws/v310/
try to serach for sodnji and you will see...

[cgraz]

Not sure, but your code needs work. I searched for sodnji and this was the output of the query

SELECT * FROM mcs WHERE artist_1 OR artist_2 LIKE '%sodnji%' OR artist_1 OR artist_2 LIKE '%sodnji%' THIS IS QUERY

when it should be

SELECT * FROM mcs WHERE artist_1 OR artist_2 LIKE '%sodnji%'

Don't know if that will make any difference, but it's redundant and may cause problems in the future.

Cgraz

[da_marek]

well, thanks for advice, ill try to work more on that f***ing search engline so we will se... but for now its just fine 🙂