CODEWITHSUNDEEP
phpmysqldatabasepdo
MitchCool1
MitchCool1

Reputation: 329

SQL checking for NULL fields

SOLVED: I knew there were fields that were empty that should have caught the check, but they were empty rather than NULL. So, when I was checking for NULL fields, it didn't find any.

I'm trying to check whether elements in a row with a specific name have any blank fields. I use it to find if any values are null, if there is, update and rows that were null. It isn't working properly though. I believe I have all the column names correct, I can't see what the problem is though. Here is my query: 

    //this goes through each row 1 by 1 and checks if an element is null
            $stmt = $dbh->prepare("SELECT count(*) from csgo_item_list WHERE Item_Name =:itemname AND (Image_Link IS NULL OR Quantity IS NULL OR new_price IS NULL OR market_hash_name IS NULL OR last_update IS NULL OR is_tradable IS NULL OR old_price IS NULL OR item_type IS NULL OR skin_quality IS NULL OR skin_color IS NULL)");
//"$mydata->market_name" returns a valid name in the table
            $stmt->bindValue(':itemname', $mydata->market_name);
            $stmt->execute();
            $count = $stmt->fetchColumn();
            echo $count;

When I do this, some of the rows do have some null fields, yet when I echo $count it returns only 0's. This means I can't update my rows, because after the check I use the same line for an UPDATE:

if($count != 0){
$sql = $dbh->prepare("UPDATE csgo_item_list SET Quantity=:quantity, new_price=:newprice, Image_Link=:image_link, market_hash_name=:markethashname, last_update='1000-01-01 00:00:00', is_tradable='no', old_price=:oldprice, item_type=:type, , skin_quality=:skinquality, skin_color=:skincolor WHERE Item_Name=:itemname AND (Image_Link IS NULL OR Quantity IS NULL OR new_price IS NULL OR market_hash_name IS NULL OR last_update IS NULL OR is_tradable IS NULL OR old_price IS NULL OR item_type IS NULL OR skin_quality IS NULL OR skin_color IS NULL)");

I'll post an image of my database table, from what I've checked the names all match though: http://gyazo.com/5ab3f2676c44eb696b02a38a64d9742a

Can anyone see why this isn't working?

Upvotes: 1

Views: 148

Answers (2)

MitchCool1
MitchCool1

Reputation: 329

I knew there were fields that were empty that should have caught the check, but they were empty rather than NULL. So, when I was checking for NULL fields, it didn't find any.

What I had to do was set all the empty columns to NULL:

UPDATE `table` SET `column` = NULL;

Upvotes: 1

Kirs Sudh
Kirs Sudh

Reputation: 298

"....yet when I echo $count it returns only 0's. This mea...."

if($count != 0){ ....

if it is returning all '0's then why you are comparing "not equal" to 0 ?

it Should be if ($count == O){ I think.

Upvotes: 0

Related Questions