Mysqli prepare query not working

Question

I'm fetching images from database. I wrote the php script given below, only the "echo" statement in while loop is working but the variables not showing values coming from database.In other words, only html is working.

There is no any error as such. I don't know wht's the issue.

Help me. Thanks !

$db_username = 'xxxxx';
$db_password = 'xxxx';
$db_name = 'xxxx';
$db_host = 'localhost';
$item_per_page = 9;

$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

$page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);

if(!is_numeric($page_number)){
    header('HTTP/1.1 500 Invalid page number!');
    exit();
}

$position = (($page_number-1) * $item_per_page);

$results = $mysqli->prepare("select * FROM inserting_discount_product ORDER BY id DESC LIMIT ?, ?");

$results->bind_param("dd", $position, $item_per_page); 

$results->execute(); //Execute prepared Query

$results->bind_result($pro_id, $pro_title, 
$pro_cat,$pro_desc,$pro_price,$pro_img,$pro_discount_price,$pro_discount_percent
age,$product_code); 


while($results->fetch()){ 
                echo "
                <div id='$pro_id' class='col-md-3 col-sm-4 col-xs-12'>

                <h3 style='color:black'; align='center'>$pro_title</h3> 
                <div>

                <img src='../admin_area/product_images/product_pics/$pro_img'  width='180' height='180' /> <br>
                </div>


                <p style='color:black;margin-right:12px'><b>Price: $  $pro_discount_price </b>

                <span style='color:black;text-decoration:line-through;margin-left:15px'>
                <b style='color:red'>Price: $ $pro_price</b>
                </span>

                </p>

                <a href='details.php?pro_id=$pro_id' style='margin-right:10px' class='detail_hover'><b>Details</b></a>
                <span style='font-weight:bolder'>$pro_discount_percentage %</span>
                <a href='index.php?add_cart=$pro_id&product_code=$product_code' style='color:orange;margin-left:10px' class='basket-logo'>
                <span  class='fa fa-shopping-cart fa-2x'></span></a> 


                    </div>

                ";

}

score 0 · Answer 1 · answered Mar 03 '17 at 16:10

It is good practice to put your prepare statement in an IF, since prepare can return a boolean if it fails.

    if($results = $mysqli->prepare("select * FROM inserting_discount_product ORDER BY id DESC LIMIT ?, ?"))
        {
            $results->bind_param("dd", $position, $item_per_page); 

            $results->execute(); //Execute prepared Query

            $results->bind_result($pro_id, $pro_title, 
            $pro_cat,$pro_desc,$pro_price,$pro_img,$pro_discount_price,$pro_discount_percentage,$product_code); 

            while($results->fetch())
            {
               // ... stuff
            }
            $results->close();
        }
        else { echo $mysqli->error;  }

You should probably also explicitly declare your return columns in the SELECT instead of using *. Down the line you might add one column to the table, and immediately all of your code will break if you are doing SELECT * because the query will return more columns than you have variables in bind_result.

after relacing syntax with your suggested one and by adding column names instead of '*' i got this error " Allowed memory size of 67108864 bytes exhausted (tried to allocate 4294967296 bytes)" — shani jani, Mar 03 '17 at 16:41
Okay I have seen this error before. The cause is the definition of your table, one of the columns you are trying to select is of a very large type. I got it when I was using type LONGTEXT instead of TEXT. — J. Fortman, Mar 03 '17 at 20:34

score -1 · Answer 2 · edited May 23 '17 at 12:25

See here:

What is the difference between single-quoted and double-quoted strings in PHP?

This is a problem with your quotation in your echo statement. You have a mix of double quotes and single quotes. In php single quotes will display things as is, therefore your variables are not going to be dispalyed. Thus your echo statement will need to be broken up into concatenations in order to properly display what you want. Note that php uses the dot operator to concatenate.

For example:

echo "<div id='".$pro_id."' class='col-md-3 col-sm-4 col-xs-12'>

The reason there are not any errors is because it assumes all of your variables that you are echoing out should be echoed as plain text so it is literally writing "$pro_id" to the html as the element's id and not what the variable is.

Alternatively, your markup is a lot of html. You could transfer your whole while loop to html and use php shortcode to get your variables in the loop as follows:

while($results->fetch()){ 
    //End php here, go into HTML markup.
    ?>

     <div id='<?=$pro_id?>' class='col-md-3 col-sm-4 col-xs-12'>

    ...

    <?php
//Don't forget to add the closing brace to the while loop back in <?php
}

This method uses php's shorthand notation seen here: This echos php variables to the page while in html markup. This reduces the amount of quotation marks and strings that you have to deal with and is much easier to work with as this is direct HTML. If a majority of your content is going to be in HTML (which it is) and you only need to add a few PHP variables I would definitely reccommend closing your php ?> and using shorthand.

Just for the record: no editing could salvage this answer, which is irrelevant to the question *and* wrong by itself — Your Common Sense, Mar 03 '17 at 18:29

Mysqli prepare query not working

2 Answers2