current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

Afternoon everyone, quick question on this fetch query. 

<?php
    $sql = "SELECT * FROM products ";
        if(isset($_POST['Submit'])){
            if(empty($_POST['Search'])){
                $error = true;
            }else{
            $searchq = mysql_real_escape_string($_POST['Search']);
            $searchq = preg_replace("#[^0-9a-z]#i","",$searchq);
            $sql .= "WHERE type LIKE '%$searchq%' or name LIKE '%$searchq%'";       
        } 
    } $query = mysql_query($sql) or die(mysql_error());

        $sql1 = "SELECT * FROM products ";
        if(isset($_GET['q'])){
            $categories = mysql_real_escape_string($_GET['q']);
            $sql1 .= "WHERE type LIKE '%$categories%'";       
        } $query1 = mysql_query($sql1) or die(mysql_error());  

     ?>





<?php while ($row = mysql_fetch_array($query) and $row = mysql_fetch_array($query1)) { ?>
        <div class="prod_box">
           <div class="top_prod_box"></div>
            <div class="center_prod_box">
              <div class="product_title"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><b><?php echo $row['name']?></a></div>
              <div class="product_img"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><img src="<?php echo $row['picture']?>" height="100" width="120" /></a></div>
              <div class="prod_price"><span class="reduce"><?php if($row['rprice'] > 0) {echo "£"; echo $row['rprice'];}?></span> <span class="price"><big style="color:green">£<?php echo $row['price']?></big></span></div>
            </div>
            <div class="bottom_prod_box"></div>
            <div align="center" class="prod_details_tab"> <input type="button" value="Add to Cart" onclick="addtocart(<?php echo $row['serial']?>)" /></td> </div>
            </div>
            <?php } ?></p>

Anyone see anything wrong ere? The query fetches fine but, not properly, it will fetch parts of the other query even when the forms not even submitted. :\

share|improve this question
1  
you are over-writing $row with 2 fetch in the same condition !! –  Abhik Chakraborty Mar 6 at 14:30
    
Hmm, I see. Is there no way of writing while loop ere without changing $row? Or am I heading in the wrong direction? –  Rickshaw Mar 6 at 14:34
    
well the while loop with 2 fetch does not make sense.. what if first query returns 10 row and second 5 so as per the condition the loop will break at 5 so if you want to display 10 data from first query you can not with the above condition. –  Abhik Chakraborty Mar 6 at 14:37
    
just out of curiosity, can you not execute your two queries in one ? Then you have one fetch to handle. It is also faster and you would avoid data loss ( like if $query has 5 and $query1 has 10, then 5 products will be displayed, ignoring other 5 from $query1. –  KarelG Mar 6 at 14:45
    
I think it is possible, but clearly it has been proven difficult for me :\ –  Rickshaw Mar 6 at 14:48

2 Answers 2

up vote 2 down vote accepted

You can run the query one time. Just combine the GET and POST when creating a query.

$noWhere = true;
$sql = "SELECT * FROM products ";

if(isset($_POST['Submit'])){
    if(empty($_POST['Search'])){
        $error = true;
    }else{
        $searchq = mysql_real_escape_string($_POST['Search']);
        $searchq = preg_replace("#[^0-9a-z]#i","",$searchq);
        $sql .= "WHERE type LIKE '%$searchq%' or name LIKE '%$searchq%'";
        $noWhere = false;   
    }
} 
// check if categories got sent
if(isset($_GET['q'])){
    $categories = mysql_real_escape_string($_GET['q']);
    if($noWhere) $sql .= "WHERE type LIKE '%$categories%'";  
    else $sql .= " OR type LIKE '%$categories%'";       
}
$query = mysql_query($sql) or die(mysql_error()); 

?>

<?php while ($row = mysql_fetch_array($query)) { ?>

Although i have to add a remark. You are using mysql() functions, which is officially deprecated. I should check PDO for using db from PHP.

share|improve this answer
    
Genius! Really appreciate your help mate, thanks a bunch! –  Rickshaw Mar 6 at 15:01
up vote 1 down vote

Try using to different fetch variables:

 <?php while ($row = mysql_fetch_assoc($query) and $row2 = mysql_fetch_assoc($query1)) { ?>
    <div class="prod_box">
       <div class="top_prod_box"></div>
        <div class="center_prod_box">
          <div class="product_title"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><b><?php echo $row['name']?></a></div>
          <div class="product_img"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><img src="<?php echo $row['picture']?>" height="100" width="120" /></a></div>
          <div class="prod_price"><span class="reduce"><?php if($row['rprice'] > 0) {echo "£"; echo $row['rprice'];}?></span> <span class="price"><big style="color:green">£<?php echo $row['price']?></big></span></div>
        </div>
        <div class="bottom_prod_box"></div>
        <div align="center" class="prod_details_tab"> <input type="button" value="Add to Cart" onclick="addtocart(<?php echo $row['serial']?>)" /></td> </div>
        </div>
        <?php } ?></p>

Use for example $row for the first and $row2 for the second query.

If you want only $row in my opinion than you have to build a query which outputs the result of query1 and query2

First run your query in your if statements and the output too. Try this code:

<?php
    $sql = "SELECT * FROM products ";
        if(isset($_POST['Submit'])){
            if(empty($_POST['Search'])){
                $error = true;
            }else{
            $searchq = mysql_real_escape_string($_POST['Search']);
            $searchq = preg_replace("#[^0-9a-z]#i","",$searchq);
            $sql .= "WHERE type LIKE '%$searchq%' or name LIKE '%$searchq%'";  
            $query = mysql_query($sql) or die(mysql_error());
            while ($row = mysql_fetch_assoc($query)) { ?>
        <div class="prod_box">
           <div class="top_prod_box"></div>
            <div class="center_prod_box">
              <div class="product_title"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><b><?php echo $row['name']?></a></div>
              <div class="product_img"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><img src="<?php echo $row['picture']?>" height="100" width="120" /></a></div>
              <div class="prod_price"><span class="reduce"><?php if($row['rprice'] > 0) {echo "£"; echo $row['rprice'];}?></span> <span class="price"><big style="color:green">£<?php echo $row['price']?></big></span></div>
            </div>
            <div class="bottom_prod_box"></div>
            <div align="center" class="prod_details_tab"> <input type="button" value="Add to Cart" onclick="addtocart(<?php echo $row['serial']?>)" /></td> </div>
            </div>
            <?php } ?></p>
<?php
        } 
    }

        $sql1 = "SELECT * FROM products ";
        if(isset($_GET['q'])){
            $categories = mysql_real_escape_string($_GET['q']);
            $sql .= "WHERE type LIKE '%$categories%'";
            $query = mysql_query($sql1) or die(mysql_error());
            while ($row = mysql_fetch_assoc($query)) { ?>
                <div class="prod_box">
           <div class="top_prod_box"></div>
            <div class="center_prod_box">
              <div class="product_title"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><b><?php echo $row['name']?></a></div>
              <div class="product_img"><a href="productsview.php<?php echo '?id='.$row['serial']; ?>"><img src="<?php echo $row['picture']?>" height="100" width="120" /></a></div>
              <div class="prod_price"><span class="reduce"><?php if($row['rprice'] > 0) {echo "£"; echo $row['rprice'];}?></span> <span class="price"><big style="color:green">£<?php echo $row['price']?></big></span></div>
            </div>
            <div class="bottom_prod_box"></div>
            <div align="center" class="prod_details_tab"> <input type="button" value="Add to Cart" onclick="addtocart(<?php echo $row['serial']?>)" /></td> </div>
            </div>
            <?php } ?></p>
<?php           
        }   

     ?>

I am not sure if this works like you expected but give it a try.

share|improve this answer
    
build a query which outputs the result of query1 and query2 This! –  Ian Brindley Mar 6 at 14:37
    
Ah, was really aiming to avoid that :( –  Rickshaw Mar 6 at 14:41
    
@Rickshaw i don't know your querys but those are the two options you have ;) –  Pgr456 Mar 6 at 14:42
    
Here is the query, but it's pretty difficult putting those querys together since one is based $_POST while the other one is $_GET :( –  Rickshaw Mar 6 at 14:44
    
@Rickshaw edited my answer. Hope this is the solution :) –  Pgr456 Mar 6 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.