0

I have a query I'm trying to run in my php code and it is only returning one result, but if i run the same query in phpmyadmin it works. Can anyone tell me where I'm going wrong?

 <?php
     $sql = "SELECT * FROM `product_packs` WHERE `name` IN('" . implode("', '", $_SESSION['cart_items']) . "')";
     $result = $conn->query($sql);

 if ($result->num_rows > 0){
      while($row = $result->fetch_assoc()){
             echo     "<div class='col-xs-6 col-sm-4 col-md-2 col-lg-2'>
                 <div class='products " . $row['brandName'] . " all " . $row['product_range'] . "' id='products'>
                     <div class='hovereffect'>
                         <img class='img-responsive productimg' src='" . $row['img'] . "' alt=''>
                         <div class='overlay1'>
                             <h2> " . $row['name'] . "</h2>
                             <p> 
                                 " . $row['title'] . "
                             <br>
                             <br>
                                 " . $row['price'] . "
                             <br>
                             <a href='remove_from_cart.php?name=" . $row['name'] . "&price=" . $row['price'] . "'>
                                 Remove From Cart 
                             </a> 
                             </p>
                         </div>  
                     </div>
                 </div>";
         }
?>

I have printed the query to make sure the result of the implode is correct and it seems to be as i can run the result in phpmyadmin and it works fine.

Any help would be appreciated.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
J.Stevenson
  • 9
  • 4

2 Answers2

0

Try the following (NOT tested as far as I have not sufficent data provided form you):

UPDATED:

<?php
$sql = "SELECT * FROM `product_packs` WHERE `name` IN('" . implode("', '", $_SESSION['cart_items']) . "')";

// dump the query send to the database  
$var_dump($sql);

$result = $conn->query($sql);

if ($result->num_rows > 0){

    $resultset_count = $result->num_rows;
    // dump the number of resultsets in the query
    var_dump($resultset_count);

    while($row = $result->fetch_assoc()){

    echo "<div class='col-xs-6 col-sm-4 col-md-2 col-lg-2'>
            <div class='products " . $row['brandName'] . " all " . $row['product_range'] . "' id='products'>
                <div class='hovereffect'>
                <img class='img-responsive productimg' src='" . $row['img'] . "' alt=''>
                <div class='overlay1'>
                    <h2> " . $row['name'] . "</h2>
                    <p> 
                    " . $row['title'] . "
                    <br>
                    <br>
                    " . $row['price'] . "
                    <br>
                    <a href='remove_from_cart.php?name=" . $row['name'] . "&price=" . $row['price'] . "'>Remove From Cart </a> 
                    </p>
                </div>  
                </div>
            </div>
            </div>";
    }

?>
Michael
  • 663
  • 1
  • 13
  • 28
  • If this does not work please dump(`var_dump($aRows;)`) `$aRows` (or `$row` if you use your old code instead of mine): – Michael Jun 06 '16 at 10:18
  • this has returned me with two errors. Notice: Undefined variable: aRow & Warning: Invalid argument supplied for foreach() – J.Stevenson Jun 06 '16 at 10:28
  • If you share the error message we might help you ;-) did your old code reutrned an error? if yes, which – Michael Jun 06 '16 at 10:29
  • Please provide the result you get by sending the sql query in phpmyadmin and the result of a var_dump($row) after adding this in the 3. line of your code: `$row = $result->fetch_assoc();var_dump($row);die;` – Michael Jun 06 '16 at 10:32
  • I just did a dump of $aRows and it is showing all the info for one product even though the query is for two products – J.Stevenson Jun 06 '16 at 10:33
  • original didnt get any errors it was just only displaying one result – J.Stevenson Jun 06 '16 at 10:35
  • please share the result of the dump – Michael Jun 06 '16 at 10:35
  • this is the query printed on the site with two products in it SELECT * FROM `product_packs` WHERE `name` IN('Rock It Texture', 'Color Obsessed') if i run it in phpmyadmin it works fine – J.Stevenson Jun 06 '16 at 10:37
  • the result of the dump is array(8) { ["ID"]=> string(1) "3" ["name"]=> string(14) "Color Obsessed" ["header"]=> string(18) "Keep Color Vibrant" ["title"]=> string(20) "Total Confidence To:" ["img"]=> string(39) "pics/products/matrix/color_obsessed.jpg" ["price"]=> string(3) "$75" ["brandName"]=> string(6) "Matrix" ["product_range"]=> string(15) "total_results_1" } – J.Stevenson Jun 06 '16 at 10:38
  • please dump the content of `$_SESSION['cart_items']`. Your code seems to be working correctly but query only gives you one result. Make a `var_dump($sql);` and compare if the query your code creates is equal to what you send in phpmyadmin – Michael Jun 06 '16 at 10:46
  • this is a dump of $sql with two products added to the `$_SESSION['cart_items']` `SELECT * FROM 'product_packs' WHERE 'name' IN('Rock It Texture', 'Color Obsessed')` – J.Stevenson Jun 06 '16 at 10:50
  • That query does bring up the two results in phpmyadmin – J.Stevenson Jun 06 '16 at 11:01
  • please copy my updated code above and post the result of the two var_dump(); which are included. The error must be somewhere arround the IN('...', '...'). But till now I can't figure out why phpmyadmin is working, and your code not. need some more debug data. – Michael Jun 06 '16 at 11:07
  • this is the result of the var_dump `string(91) "SELECT * FROM 'product_packs' WHERE 'name' IN('first', 'Rock It Texture', 'Color Obsessed')" int(2)` – J.Stevenson Jun 07 '16 at 05:54
  • You must change the used singel quotation marks used for product_packs and name from ' to `. ( SELECT * FROM \`product_packs\` WHERE \`name\` IN('first', 'Rock It Texture', 'Color Obsessed') ) – Michael Jun 07 '16 at 07:55
  • oh, sorry i am using ` it was just so it showed up as code text that i changed it to ' the resulting dump is ( string(91) "SELECT * FROM `product_packs` WHERE `name` IN('first', 'Rock It Texture', 'Color Obsessed')" int(2) ) – J.Stevenson Jun 07 '16 at 08:27
  • So all works as expected till this point... please add a `var_dump($row)` after the line `while($row = $result->fetch_assoc()){` and share the output – Michael Jun 07 '16 at 09:31
  • I cannot post the dump result as it is too long, but I can see from reading it through it has all the data about the two products in the query – J.Stevenson Jun 07 '16 at 09:51
  • Okay, if both data sets are available in your request the error must be located in your HTML code. Is this project online available somewhere? Do you hide something with your css? have you inspected your HTML code, maybe the elements are hidden? – Michael Jun 07 '16 at 11:57
  • thanks for all your help, I went through my HTML and found it was missing a `` tag, I feel so stupid for missing something like that... – J.Stevenson Jun 08 '16 at 07:39
0

Why don't you use FIND_IN_SET? It will work for you, I don't know your database structure but I have created an example query that could be of help for you

why don't use FIND_IN_SET? it will work for you i don't know your database structure still i have created query for you it might help you

SELECT * FROM product_packs
   WHERE (
      FIND_IN_SET(cart_items, (SELECT cart_items   TABLENAME
          WHERE cart_items = '$_SESSION['cart_items']')
      )
   )
ORDER BY `product_id` ASC
DrColossos
  • 12,656
  • 3
  • 46
  • 67
kiran gadhvi
  • 228
  • 2
  • 16
  • @DrColossos ive tried using `FIND_IN_SET` but it returns a fatal error `Fatal error: Call to undefined function FIND_IN_SET()` – J.Stevenson Jun 07 '16 at 06:09
  • @J.Stevenson check this http://stackoverflow.com/questions/25977164/how-to-explode-user-id-from-a-database-column/25977216 .it may help you. – kiran gadhvi Jun 07 '16 at 06:13
  • if my understanding after reading that is correct `FIND_IN_SET()` is a sql function but I still don't think i can run the function in php as it returned a fatal error – J.Stevenson Jun 07 '16 at 06:32