[SOLVED] Query is forgetting to do something

[legohead6]

hi, havent been around in awhile, been busy doing some other stuff. Anyways i am working on a marketplace site's listing catalog and have just rethought the way i organize it, it used to show all the listings untill you did a search witch narrowed it down by what u searched and what catagory u searched, and i have know made it so u select your catagory before you see the items and then when you search it just searches inside that catagory but for some reason the query is forgetting the part about only in the catagory ONLY when i do a search, when it brings up all the results in the catagory it WORKS!!! and this is really messed because the querys are almost exactly the same!!!! heres the code ive put 2 stars infrom of the if that finds if its searched or not... those ARE NOT there in the real code


****UPDATE!!: I took away the OR and one of the field things to search so it only searches one and know it works!!! why would that be? i still want the other one htere!!!*****


[code]<?php
session_start();

$user=$_SESSION['user'];
include 'header.php';

$username="**********";
$password="**********";
$database="***********";
$connect = mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if(empty($_GET['catagory'])){
echo "<h1 align=center>Catagorys</h1>";
$cata1="SELECT * FROM listingcat";
$catar= mysql_query($cata1);
while($cata = mysql_fetch_row($catar)){
$catc=str_replace("_"," ", $cata[1]);
echo "<p align=center><a href=browse.php?catagory=$cata[1]>$catc</a>";
}
echo "<br><br><br>";
exit();
}else{
echo "<div align=center><a href=browse.php>Return to List of Catagorys</a><br><br>";
$display=10;
if(isset($_GET['s'])){
$start=$_GET['s'];
}else{
$start=0;
}
$ncat=$_GET['catagory'];
$pvs=$_GET['pvs'];
**if(isset($_POST['submit']) OR $pvs != ''){
if(!empty($pvs)){
$search=$pvs;
}else{
$search=$_POST['search'];
}
$ncat=$_GET['catagory'];
$counting = "SELECT COUNT(*) FROM listings WHERE title LIKE '%$search%' OR  description LIKE '%$search%' AND cat='$ncat' ORDER BY id ASC";
$showing = "SELECT * FROM listings WHERE title LIKE '%$search%' OR  description LIKE '%$search%' AND cat='$ncat'  LIMIT $start, $display";

}else{
$ncat=$_GET['catagory'];
$counting = "SELECT COUNT(*) FROM listings WHERE cat='$ncat' AND sold='0' ORDER BY id ASC";
$showing = "SELECT * FROM listings WHERE cat='$ncat' AND sold='0' LIMIT $start, $display";
}
$page = "$counting";
$resultp = mysql_query($page);
$row = mysql_fetch_array($resultp, MYSQL_NUM);
$num_records = $row[0];
if($num_records == '0'){
echo "<p align=center>There are No Listings</p>";
}else{
echo "<p align=center>There are $num_records Listings</p>";
}
if(isset($_GET['no problem'])){
$num_pages = $_GET['no problem'];
}else{
$page = "$counting";
$resultp = mysql_query($page);
$row = mysql_fetch_array($resultp, MYSQL_NUM);
$num_records = $row[0];
if($num_records > $display){
$num_pages = ceil($num_records/$display);
}else{
$num_pages = 1;
}
}
$query2="$showing";


echo "<table border=1>";
$result2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error());
$num = mysql_num_rows ($result2);
while($row = mysql_fetch_row($result2)){
$member1="SELECT * FROM members WHERE username='$row[5]'";
$resultm = mysql_query($member1) or die ("Error in query: $member. ".mysql_error());
while($mem = mysql_fetch_row($resultm)){
$title = str_replace(' ', '_', $row[1]);

echo "<tr><td width=300><p align=center><a href=listing.php?id=$row[0]&pvs=$search&c=$catagory>$title</a><br><font size=2>Location: $mem[10]</td><td width=100>Price: $$row[2]</p></td></tr>";
}
}
?>
<form method="POST">
<p>Search:<input type="text" name="search" size="30">
<input type="submit" value="Search" name="submit"></p>
<?php
echo "</form>";
if ($num_pages > 1){
echo '<br><p>';
$current_page = ($start/$display) + 1;
if($current_page != 1){
echo '<a href="browse.php?s'.($start - $display).'$no problem='.$num_pages.'">Previous</a> ';
}
for($i =1; $i <= $num_pages; $i++){
if ($i != $current_page){
echo '<a href="browse.php?s=' . (($display*($i - 1))) . '&no problem=' . $num_pages . '">' . $i . '</a> ';
}else{
echo $i.'';
}
}
if($current_page != $num_pages){
echo '<a href="browse.php?s='.($start + $display).'&no problem='.$num_pages . '">Next</a> ';
}
}
mysql_close($connect);
?>
<html><body><div align=center></div>
<!-- Begin PayPal Logo --><A HREF="https://www.paypal.com/row/mrb/pal=N46WFHASFN2R8" target="_blank"><IMG  SRC="http://images.paypal.com/en_US/i/bnr/paypal_mrb_banner.gif" BORDER="0" ALT="Sign up for PayPal and start accepting credit card payments instantly."></A><!-- End PayPal Logo -->
</body></html>
<?
}
?>[/code]

[trq]

queries don't forget anything, youve forgotten to put something in your query, now, can we have a clearer description of your problem?

[legohead6]

when i use the search box to search for an item it returns a search correctly but it doesnt limit it to the catagory the user is in! so it finds items from other catagorys... but the part thats making it seem like the query is forgetting is when you first load that catagory it displays correct stuff! it is only when u make a search it goes outside the catagory and does the search and brings up stuff from other catagorys

umm heres the page if it help

http://www.zelocalz.com/browse.php

if you want to try it go to large appliances and search 'com' it will come up with computer stuff and you will see what i mean

[trq]

Which query is responsible for this. Im sorry, but your code is very hard to follow without any indentation.

[legohead6]

here is the statement! this is where the mistake is happening! its both $counting and $showing that are screwing up because pagination and the total number of listings is showing correct to the search I have echoed $ncat and it is displaying correctly

[code]if(isset($_POST['submit']) OR $pvs != ''){
if(!empty($pvs)){
$search=$pvs;
}else{
$search=$_POST['search'];
}
$ncat=$_GET['catagory'];
$counting = "SELECT COUNT(*) FROM listings WHERE title LIKE '%$search%' OR  description LIKE '%$search%' AND cat='$ncat' ORDER BY id ASC";
$showing = "SELECT * FROM listings WHERE title LIKE '%$search%' OR  description LIKE '%$search%' AND cat='$ncat'  LIMIT $start, $display";

}else{
$ncat=$_GET['catagory'];
$counting = "SELECT COUNT(*) FROM listings WHERE cat='$ncat' AND sold='0' ORDER BY id ASC";
$showing = "SELECT * FROM listings WHERE cat='$ncat' AND sold='0' LIMIT $start, $display";
}
[/code]

[legohead6]

$pvs is previous search so when a buying comes back from a listing they stay with the same results

[legohead6]

Breakthrough! i deleted the OR in the query and the search through title and know it works... but why cant i have the or there?

[Jessica]

Have you tried adding parenthesis so it looks like this

[code]$counting = "SELECT COUNT(*) FROM listings WHERE (title LIKE '%$search%' OR  description LIKE '%$search%') AND cat='$ncat' ORDER BY id ASC";
$showing = "SELECT * FROM listings WHERE (title LIKE '%$search%' OR  description LIKE '%$search%') AND cat='$ncat'  LIMIT $start, $display";[/code]

It should be prioritized that way automatically, but just in case, try this.

[legohead6]

THANKS!!! that worked!!!!

[Jessica]

No problem. Whenever I have more than one clause like that, I add parenthesis just to help me keep track of what each part is.

[legohead6]

sometimes the simplest things solve the wierdest problems

[Jessica]

Well it's like order of operations. If you have 2+2*5 - what do you do? It helps to add clarification, as (2+2)*5 is different than 2+(2*5) right? You do this in conditional statements in php also, like if($x==5 || ($x==2 && $y==2)).