php - how to check if table or table row is empty?

Question

I have following function defined to check if the table row is empty or not

function check_table($table) {
    global $con;
    $result = mysqli_query($con, "SELECT * FROM $table");
    return (mysqli_num_rows($result) > 0) ? true : false;
}

and then call it to fill table

// check if payment table is empty, then insert into in, else update it
if(check_table('payment_details') == false) {
    insert_into_table('payment_details',$payment_detail);
} else {
    update_table($session_user_id,'payment_details',$payment_detail);
}

the insert_into_table() defined

function insert_into_table($table,$register_data) {
    global $con;
    array_walk($register_data, 'array_sanitize');

    $fields = '`' . implode('`, `', array_keys($register_data)) . '`';
    $data = '\'' . implode('\', \'', $register_data) . '\'';
    mysqli_query($con , "INSERT INTO $table ($fields) VALUES ($data)");
}

but its not returning as it desired , and the table is still empty after execute it. its not working.

so where I am wrong?

any help would be appreciate.

thanks

NEW UPDATED

This is all my code looks like, just ensure me am I going right ? if not please give me some improve suggestions.

init.php

$session_user_id = $_SESSION['userid'];
//users table
$user_data = user_data($session_user_id,'users', 'id','username','password','first_name','last_name','email','allow_email',
'password_recover','active','city','state','country','phone','custom','date','plan','duration','domain','amount',
'pp_txn_id', 'pp_item_no','pp_payment_status','pp_payer_email','pp_payment_date','pz_reference_no', 'pz_item_no',
'pz_payment_status','pz_payer_email','pz_payment_date','expiry','status','cpanel');

// for paypal payments
$pp_data = user_data($session_user_id,'pp_details','pp_txn_id','pp_item_no','pp_payment_status','pp_payer_email','pp_payment_date');
// for payza payments
$pz_data = user_data($session_user_id,'pz_details','pz_reference_no','pz_item_no','pz_payment_status','pz_payer_email','pz_payment_date');

member.php

require_once(get_template_directory().'/member/core/init.php');

$customField = $user_data['custom'];
//check if transaction is done
if ( $customField == $user_data['id'] && ($user_data['pz_payment_status'] == 'Success') || ($user_data['pp_payment_status'] == 'Completed') ) {
    //info for pp_details table
        $pp_detail = array(
            'id'                => $user_data['id'],
            'pp_txn_id'         => $user_data['pp_txn_id'],
            'pp_item_no'        => $user_data['pp_item_no'],
            'pp_payment_status' => $user_data['pp_payment_status'],
            'pp_payer_email'    => $user_data['pp_payer_email'],
            'pp_payment_date'   => $user_data['pp_payment_date'],
        );
        //info for pz_details table
        $pz_detail = array(
            'id'                => $user_data['id'],
            'pz_reference_no'   => $user_data['pz_reference_no'],
            'pz_item_no'        => $user_data['pz_item_no'],
            'pz_payment_status' => $user_data['pz_payment_status'],
            'pz_payer_email'    => $user_data['pz_payer_email'],
            'pz_payment_date'   => $user_data['pz_payment_date'],
        );
        //reset the users table if transaction is success
        $update_data = array(
            'status' => 'Active',
            'pp_txn_id' => NULL,
            'pp_item_no' => NULL,
            'pp_payment_status' => NULL,
            'pp_payer_email' => NULL,
            'pp_payment_date' => NULL,
            'pz_reference_no' => NULL,
            'pz_item_no' => NULL,
            'pz_payment_status' => NULL,
            'pz_payer_email' => NULL,
            'pz_payment_date' => NULL,
        );
    // check if payment table is empty, then insert into in
    if(check_table($session_user_id,'pp_details') == false) {
        if ($user_data['pp_payment_status'] == 'Completed') {
            insert_into_table('pp_details',$pp_detail);
            update_user($session_user_id, $update_data);
        }
    } elseif (check_table($session_user_id,'pz_details') == false) {
        if ($user_data['pz_payment_status'] == 'Success') {
            insert_into_table('pz_details',$pz_detail);
            update_user($session_user_id, $update_data);
        }
    }
    // make sure if user_data transaction id's not match with payment_data details, then activate the package
    if ( ($user_data['pp_txn_id']) != ($pp_data['pp_txn_id']) ) {
        // update the pp_details table
        update_table($session_user_id,'pp_details',$pp_detail);
        // reset the users table and activate package
        update_user($session_user_id, $update_data);

    } elseif ( ($user_data['pz_reference_no']) != ($pz_data['pz_reference_no']) ) {
        // update the pz_details table
        update_table($session_user_id,'pz_details',$pz_detail);
        // reset the users table and activate package
        update_user($session_user_id, $update_data);
    }
    // email credentials 
    if ( $user_data['pp_payment_status'] == 'Completed' ) {
        $transactionID  = $pp_data['pp_txn_id'];
        $itemNo         = $pp_data['pp_item_no'];
        $paymentStatus  = $pp_data['pp_payment_status'];
        $paymentDate    = $pp_data['pp_payment_date'];
        if ($pp_data['pp_payer_email'] !== $user_data['email']) {
            $customer_Email = array($pp_data['pp_payer_email'],$user_data['email']);
        } else {
            $customer_Email = array($user_data['email']);
        }
    } elseif ( $user_data['pz_payment_status'] == 'Success' ) {
        $transactionID  = $pz_data['pz_reference_no'];
        $itemNo         = $pz_data['pz_item_no'];
        $paymentStatus  = $pz_data['pz_payment_status'];
        $paymentDate    = $pz_data['pz_payment_date'];
        if ($pz_data['pz_payer_email'] !== $user_data['email']) {
            $customer_Email = array($pz_data['pz_payer_email'],$user_data['email']);
        } else {
            $customer_Email = array($user_data['email']);
        }
    }

    $message = "Thank You <b>".$user_data['first_name']."</b> for using our service.<br> Your Transaction details are below:<br><br>
            Transaction ID/Reference: $transactionID<br>
            Item No: $itemNo <br>
            Payment Status: $paymentStatus <br>
            Payment Date: $paymentDate <br><br>
            Kind Regards <br>
            - HostPLUS1 &copy; ".date('Y')."
    ";
    if ( isset($_GET['success']) && empty($_GET['success']) ) {
        //if sucess then refresh the page to remove the $_GET val
        refresh('3',$_SERVER['SCRIPT_URI']);
        if ($user_data['status'] == 'Active') {
            email($customer_Email,'Thank you, your payment has been completed',$message);

        }
    }
};

I have defined all my functions in a separate file called user_functions.php

Answer 1

Hsn, where are you specifying the conditions for checking the table? You're saying "SELECT * FROM $table", but you're not specifying conditions (or limits as Yegor pointed out). This means if there is even just ONE (1) transaction in your payment_details table, no new transactions will be processed. You should filter your results with conditions.

I believe you're writing this for a PayPal IPN, so I'll format my answer as such. Below all of the rewrites, you'll see information as to why I suggest what I do.

This is how I would recommend rewriting it:

/* Escape the variables right away */
// global $con; // uncomment this if you need to. I don't know where in your code it's defined
$clean_ppTxnId = ""; // we'll use this in check table.
foreach($payment_detail as $key => $value){
   $escapedKey = $con->escape_string($key);
   $escapedValue = $con->escape_string($value);
   if($key == 'pp_txn_id'){  $clean_ppTxnId = $escapedValue;  }
   unset($payment_detail[$key]);
   $payment_detail[$escapedKey] = $escapedValue;
}
// the payment details have now been looped, sanitized and replaced.

// check if payment table is empty, then insert into in, else update it
if(check_table('payment_details') == false) {
    /* new payment that we've never seen before */
    insert_into_table('payment_details',$payment_detail);
} else {
    /* if we go into the else statement, this exact payment has already
     * been processed once. */

    /* update_table($session_user_id,'payment_details',$payment_detail); */

    /*******
     * The line above is going to force you to either double process or
     * you'll overwrite PayPal transaction records.
     * Instead, this should be regarded as PayPal sending the transaction
     * to your IPN a second time (which does happen).
     ******/
}
/* If you're done with all database transactions at this point, you should call $con->close(); */

Now for the check_table function

function check_table($table, $clean_ppTxnId) {
    global $con;
    $result = $con->("SELECT * FROM `$table` WHERE (`pp_txn_id` = '$clean_ppTxnId') LIMIT 1");
    // I've added a where conditional to filter results and a LIMIT statement as Yegor suggested.
    $returnVal = ($result->num_rows > 0) ? true : false;
    $result->close();
    return returnVal;
}

Finally the insert_into function:

function insert_into_table($table,$register_data) {
    global $con;
    /* removed array_walk because my solution filters the data in before
     * calls this function.
     */

    $fields = '`' . implode('`, `', array_keys($register_data)) . '`';
    $data = '\'' . implode('\', \'', $register_data) . '\'';
    $result = $con->query("INSERT INTO `$table` ($fields) VALUES ($data);");
    // you can check the result for something if you want, but you shouldn't need to.
    $result->close();
}

Summary of edits:

• I added conditional checks so your IPN can process more than 1 total payment.
• I added protection against double-processing payments.
• I implemented MySQLi's real_escape_string (also called escape_string) function. This function uses the MySQL Database's settings to sanitize/escape input. Perhaps your array_sanitize() function was already doing this, but I decided to add this because you did not post the function.
• I have switched all MySQLi commands/queries to OOP style. That is partially out of preference, but I also find them easier to understand at a glance and quicker to write.
• Finally, I've added commands to close query results. This frees up database resources and server memory. These will automatically be closed when the script finishes running/exits, but if you close them early, the server & the database both get back resources sooner.

Because not all of your code was posted, you'll have to decide if any of these edits should be added to your code; however, if this is for a PayPal IPN like I think it is, then these edits would likely be useful. Regardless, you'll have to make that decision.

---

UPDATE: Okay, after your update I have some questions about your code and its logic.

This is your code:

// make sure if user_data transaction id's not match with payment_data details, then activate the package
if (!empty($payment_data['pp_txn_id']) === !empty($user_data['pp_txn_id']) || !empty($payment_data['pz_reference_no']) === !empty($user_data['pz_reference_no'])) {
    $update_data = array(
        'status' => 'Active',
    );
    update_user($session_user_id, $update_data);
}

The comment says make sure if user_data transaction id's not match with payment_data details, then activate the package, but this if statement does not do this. In fact this if statement might do something you do not want it to do.

The first part says if(!empty($array['index']) === !empty($another_array['index'])) Let's break that down.

• empty() returns a boolean indicating whether or not a variable does not have content (calling empty on a string will be true if the string is '' or ""). So, let $array['index'] = "foo";, then empty($array['index']) becomes empty("foo"). And we know that will return false because that string is not empty.
• Next you're saying !empty(...). So in an expanded form, $boolean_returned_by_not_empty = (empty(...) ? false : true);. You're switching the boolean to the opposite. This is fine and understandable.
• Then you're saying !empty(...) === !empty($another_array['index']).

Here's where the logic of the first part fails. You say in your comment that you want to compare whether or not they match, but all you're comparing is whether or not they're either both empty or both contain content. This means if you assign $payment_details['pp_txn_id'] = "foo"; and $user_data['pp_txn_id'] = "bar"; the if statement will run because neither one is empty despite the fact that they do not match. Likewise, if they do match ($payment_details['pp_txn_id'] = "foobar"; and $user_data['pp_txn_id'] = "foobar";), your code is going to run the if statement to update the table.

In the second portion of the if statement, you're doing the same thing and then just saying if either pair contains two strings with some data (this data doesn't even have to match), then go ahead and update the table.

In my opinion, that does not make logical sense when you want to know if they do not match. I believe a better check for this would be:

if( ($payment_details['pp_txn_id'] !== $user_data['pp_txn_id']) ||
    ($payment_data['pz_reference_no'] !== !$user_data['pz_reference_no']) ){

    $update_data = array(
        'status' => 'Active',
    );
    update_user($session_user_id, $update_data);

}

Also, after seeing the code you added, I would now recommend doing array_walk($payment_detail, 'array_sanitize'); Where I put the foreach loop. Then just assign $clean_ppTxnId after the array_walk. I can now see that your array_sanitize is safe.

---

That all said, there are still questions that remain about this script because it lacks detail and content. The basic idea should be:

1. User pays
2. PayPal contacts your IPN
3. You check in your database to see if you've already processed this transaction id. Disregard it if you have.
4. If you have not processed it, check the cost of the item (settle_amount; It is possible for a malicious purchaser to change the HTML amount attribute), check that every other necessary variable fulfills the expected criteria. If it does not, the checkout was tampered with and should not be processed.
5. If the checkout was not tampered with and everything seems fine, you should proceed. Go ahead and sanitize the variables.
6. Now log the transaction details to the transaction table of the database.
7. If users register before purchasing, you should attempt to grab the row from the user table which is relevant to the user and then proceed to step #8. If they are registered when they purchase, add a row by doing INSERT INTO <Table name for the user table goes here> (<column name 1>, <column name 2>, ...) VALUES (<value 1>, <value 2>, ...);. If you just registered the user doing this, then go to step #9.
8. If you're on #8, then you're updating a user rather than creating a new one. At this step, you should check if your query to get the user from the database succeeded. If it did succeed and the user exists on the system, then go ahead and update the user's row and set the status to Active. Otherwise log an error in a warning table of the database so that you can review it later to see if you need to refund or correct your code.
9. (Optional) If you so choose, you may dispatch an email (I won't go into detail, but you can use PHP's mail() function) to the payer_email to notify the user that the transaction has been processed.

Answer 2

Your function is bad:

function check_table($table) {
    global $con;
    $result = mysqli_query($con, "SELECT * FROM $table");
    return (mysqli_num_rows($result) > 0) ? true : false;
}

because it selects all rows only for getting bool result, I think you can improve it:

function check_table($table) {
    global $con;
    $result = mysqli_query($con, "SELECT * FROM $table LIMIT 1");
    return (mysqli_num_rows($result) > 0) ? true : false;
}

Answer 3

well I found the my mistake and fix it

there was typo mistake in my array 'pz_payment_data' => $user_data['pz_payment_data'],

$payment_detail = array(
    'id' => $user_data['id'],
    'pp_txn_id' => $user_data['pp_txn_id'],
    'pp_payment_date' => $user_data['pp_payment_date'],
    'pz_reference_no' => $user_data['pz_reference_no'],
    'pz_payment_date' => $user_data['pz_payment_date'],
);

I fix it and it works now :)