Asked  7 Months ago    Answers:  5   Viewed   55 times

I was writing a database handler class in PHP using the mysqli class and prepared statements. I was attempting to print out the result. It didn't work right off the bat so I decided to do some debugging. I tried to use the num_rows() method from the mysqli_statement class, but it kept returning 0. I decided to write a small portion of the test code to keep it simpler so I could see what was going wrong. I was then able to return the data I wanted, but the num_rows() method still returns 0 even when it is actually selecting and retrieving some data. Here is the code:

$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if(mysqli_connect_errno())
{
  die('connection failed');
}

$statement = $mysqli->stmt_init();

$query = "SELECT name FROM table WHERE id = '2000'";
if($statement->prepare($query))
{
    $statement->execute();
    $statement->bind_result($name);
    $statement->fetch();
    $statement->store_result();
    echo $statement->num_rows();
    echo $name; 
}
else
{
    echo 'prepare statement failed';
    exit();
}

The expected result is:

1name

And the actual result is:

0name

Can anyone tell me why this is?

 Answers

69

I wonder if num_rows() is reporting relative to the current resultset. Try capturing num_rows() prior to fetching the data. e.g.

if($statement->prepare($query))
{
    $statement->execute();
    $statement->store_result();
    echo $statement->num_rows();
    $statement->bind_result($name);
    $statement->fetch();
    echo $name; 
}

Does that have any effect?

Saturday, May 29, 2021
 
TheCarver
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 7 Months ago
51

Hey I've had a go at this I can't test it as I dont have the additional files and cart object but it should be close to error free

I've got a Session variable 'cart' if present we grab it unserialize and were done then can edit the values and save it back out so on

If not present i.e. first hit or cart was deleted we build a new cart from the database (This isnt ideal just for testing as presently your adding every item from the database to the cart?)

If the post or get value of adjQ is present we modify some of the values of the cart object and save it back out to the session variable

If the post or get value of showCart is present we output the current cart To make this work you might have to tweak your Shopping_Cart Object to support the variables being called and the getCount function and the getAllRows function

I've removed the additional storage of an array of the items from the cart (w) not sure what thats for since you have the data stored in the object dont need to replicate it

All the request variables should be sanitized to prevent injection attacks n so on

I've added a hidden field to trigger the showCart request

Anyway hope this helps

<?php
    session_start();
?>
<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Testing the Shopping Cart</title>
    </head>
    <body>
<?php # cart.php
// This script uses the ShoppingCart and Item classes.
//error_reporting(0);

    // Create the cart:
    require('ShoppingCart.php');
    require('userMenu.php');

    $rowCount = 0;

    if(isset($_SESSION['cart']))
    {
        echo "We have a stored cart in a Session variable, retrieving data ...";

        $cart = unserialize($_SESSION["cart"]);

        $rowCount = $cart->getCount();
    }
    else
    {
        $cart = new ShoppingCart();

        // Create some items:
        require('Item.php');
        require ('Connect.php');

        $conn=Connect::doConnect();

        $query = "SELECT product_id, product_name, product_price from product";
        $result = mysqli_query($conn, $query);

        $rowCount = $result->num_rows;

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $cart->addItem(new Item($row["product_id"], $row["product_name"],$row["product_price"]));
            }
        }
        $conn->close();
    }

    if(isset($_REQUEST['adjQ']))
    {
        echo "In stoc avem ".$rowCount." tipuri de produse";

        // Update some quantities:
        $cart_items_new = array_combine($_POST['item_adjust'], $_POST['quantity']);
        foreach ($cart_items_new as $product_id=>$quantity) {
            if($quantity > 0) {
                $cart->updateItem($product_id, $quantity);

                $conn=Connect::doConnect();

                $query1 = "SELECT product_id, product_name, product_price from product where product_id='$product_id'";
                $result1 = mysqli_query($conn, $query1);

                $row1 = mysqli_fetch_array($result1);
                echo $product_id." ".$quantity." + ".$row1["product_name"];
            }
            else {
                $cart->deleteItem($product_id);
            }
        }

        // Show the cart contents:
        echo '<h2>Continutul cosului de cumparaturi (' . $rowCount . ' tipuri de produse)</h2>
        The user is ' . $_SESSION["user"] . '.<br>
        User type is ' . $_SESSION["user_type"] . '.';

        if (!$cart->isEmpty()) {
            foreach ($cart as $arr) {
                // Get the item object:
                $item = $arr['item'];
                // Print the item:
                printf('<p><strong>%s</strong>: %d @ $%0.2f bucata.<p>', $arr['item']->getName(), $arr['item']->getQuantity(), $arr['item']->getPrice());
            } // End of foreach loop!

            echo "Saving card to Session variable";
            //New_cart is only set in adjQ request prehaps this code should be there?
            $_SESSION["cart"] = serialize($cart);
        } // End of IF.
    }

    if(isset($_REQUEST['showCart']))
    {
        if ($cart->getCount() > 0) {
            // output data of each row
            echo '<table border='."1".'><form action="cart.php" method="post">';
            echo '<tr><td><b>'."Id produs".'</td><td><b>'."Denumire".'</td><td><b>'."Pret".'</td><td>'."Numar de bucati solicitate".'</td></tr>';
            foreach ($cart->getAllRows() as $row) {
                echo '
                    <tr>
                        <td>'. $row->getProductId() . '</td>
                        <td>'. $row->getName() . '</td>
                        <td>'. $row->getPrice() . '</td>
                        <td><input type="input" value="0" name="quantity[]"><input type="hidden" value="' . $row->getProductId() . '" name="item_adjust[]"/><input type="hidden" value="showCart" name="showCart"/></td>
                    </tr>';
            }
            echo '<tr><td colspan="3"><input type="submit" value="Adauga in cosul de cumparaturi" name="adjQ"></td></tr></table>';
        } else {
            echo "Cart is empty";
        }

    }
?>
</body>
</html>
Saturday, May 29, 2021
 
Slinky
answered 7 Months ago
89

I have mainly come up against formatting and date issues when opening text files which have been saved with different regional settings. Two useful cell properties for dealing with this are:

  • .Text returns the cell value as it is displayed
  • .Value2 returns the unformatted cell value or date serial number.

As you say, standard date and number formats depend on windows regional settings and this may not be desired behavior as the same workbook can display differently in different regions. MS introduced the regional code prefixes in number formats (circa Excel 2000?) which enforce consistent display if needed but they need to be explicitly selected.

If you really want to see a date or number as the user entered it, you could extract the contents of the .xlsx file looking at the worksheet cell format and the shared strings xml definitions which list the number formats in the saved workbook. I don't really see a need to do this though as the underlying value is stored internally as a serial number and this will not change.

Saturday, July 31, 2021
 
rasmusx
answered 4 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share