PHP if num_rows is not empty insert data else update the information

Question

I got this if statement that executes this function

if table is not empty update the required information else execute the insert query. but the problem is i cannot get into the insert part instead I'm being returned to the update query even though the table is empty.

Below is my query which checks if the information is already in the table.

$checkquery = "SELECT user_type_id , user_school_verification_id , resource_no ,resource_title , SUM(quantity) as Total_Quantity FROM tbl_borrow_issuance WHERE user_type_id = ? AND user_school_verification_id = ? AND resource_no = ?";
    $stmt1 = $mysqlconnection->prepare($checkquery);
    $stmt1->bind_param("sss",$getusertype, $getstudentno , $getresourceno );
    $stmt1->execute();
    $stmt1->store_result();
    $stmt1->bind_result($user_type_id, $user_school_verification_id, $resource_no, $resource_title, $Total_Quantity );

And Here is my if statement

if($stmt1->num_rows > 0){

    while ($stmt2->fetch())
    {
        $user_type_id = $user_type_id;
        $borrow_count = $borrow_count;  
        $Total_Quantity = $Total_Quantity;
        $resource_no = $resource_no;
        // $gettotalfinal = $getresourcequantity + 

        if ($getusertype == $user_type_id && $getresourcequantity <= $borrow_count)
        {
            if ($getusertype == $user_type_id && $Total_Quantity == $borrow_count){

                echo "ERRORMAX";

            }
            else
            {
                $add_quantity = "UPDATE tbl_borrow_issuance SET quantity = quantity + ? WHERE resource_no = ? ";
                $stmt3 = $mysqlconnection->prepare($add_quantity);
                $stmt3->bind_param("ss", $getresourcequantity, $resource_no);
                if ($stmt3->execute()) {
                    echo "SUCCESS";
                }else
                {
                echo "ERROR";
                }
            }
        }
        else
        {
        echo "ERRORUSER";
        }
    }   
}
else
{
        $query = "INSERT INTO tbl_borrow_issuance (user_type_id , user_school_verification_id, resource_no , resource_title , quantity ) VALUES (? , ? , ? ,? , ?)";
        $stmt = $mysqlconnection->prepare($query);
        $stmt->bind_param("sssss",$getusertype, $getstudentno, $getresourceno , $getresourcetitle ,$getresourcequantity);
        if ($stmt->execute()) 
        {

            echo "SUCCESS1";
        }
        else
        {

            echo "ERROR";
        }
}

Have you tested the query directly on the database, with the same parameters that are going to the script? It is not returning nothing? — Dionei Miodutzki, Mar 20 '18 at 15:20
Have you tried debugging your code? Where is it stuck? Even with some simple `var_dump`s, you might be able to figure that out — Nico Haase, Mar 20 '18 at 15:22
Yup i have tested the query and it returns data @DioneiMiodutzki — Corvus, Mar 20 '18 at 15:24
@NicoHaase it echoes the "SUCCESS" in inspect tool in chrome — Corvus, Mar 20 '18 at 15:26
So, you've tried debugging yet? What does `$stmt1->num_rows` contain? — Nico Haase, Mar 20 '18 at 15:28
@NicoHaase the table is empty but it returns the "SUCCESS" and not the insert query — Corvus, Mar 20 '18 at 15:30
Please answer the questions if you seek help. If "SUCCESS" is printed, then the update query fired. If your table was empty before, updating does.... nothing. — Nico Haase, Mar 20 '18 at 15:35
Yes , i tried inserting dummy data to my table and execute the code the update query works , the only problem is the insert part. — Corvus, Mar 20 '18 at 15:41

score 0 · Answer 1 · answered Mar 20 '18 at 15:23

You are doing a aggreation query when you use SUM on your query. Long story short, this query will always return 1 row, no mather what the table have.

You want to check the Total Quantity, not the row number.

So changing the if to:

if($Total_Quantity > 0){

Should do the trick.

PHP if num_rows is not empty insert data else update the information

1 Answers1