Calling two querys in mysql_fetch_array?

Question

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. :\

you are over-writing $row with 2 fetch in the same condition !! — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 6, 2014 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
– Rickshaw, Commented Mar 6, 2014 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
– Abhik Chakraborty, Commented Mar 6, 2014 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
– KarelG, Commented Mar 6, 2014 at 14:45
I think it is possible, but clearly it has been proven difficult for me :\ — Rickshaw
– Rickshaw, Commented Mar 6, 2014 at 14:48

Community · Accepted Answer · 2023-11-14 22:27:54Z

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.

Alesfatalis · Accepted Answer · 2014-03-06 14:50:05Z

1

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.

edited Mar 6, 2014 at 14:50

answered Mar 6, 2014 at 14:34

Alesfatalis

7772 gold badges13 silver badges34 bronze badges

6 Comments

Insyte Over a year ago

build a query which outputs the result of query1 and query2 This!

Rickshaw Over a year ago

Ah, was really aiming to avoid that :(

Alesfatalis Over a year ago

@Rickshaw i don't know your querys but those are the two options you have ;)

Rickshaw Over a year ago

Here is the query, but it's pretty difficult putting those querys together since one is based $_POST while the other one is $_GET :(

Alesfatalis Over a year ago

@Rickshaw edited my answer. Hope this is the solution :)

|

Collectives™ on Stack Overflow

Calling two querys in mysql_fetch_array?

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related