1

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?

MitchCool1
  • 329
  • 3
  • 14
  • Did you refer http://stackoverflow.com/questions/3536670/mysql-selecting-rows-where-a-column-is-null ? – Pratik Joshi Jun 17 '15 at 16:45
  • 4
    you're using a reserved word in your query https://dev.mysql.com/doc/refman/5.5/en/keywords.html being `type`. probably why too and you're not seeing the error for it, because you're probably not checking for errors. – Funk Forty Niner Jun 17 '15 at 16:46
  • Please rename `type` to `item_type` – Pratik Joshi Jun 17 '15 at 16:47
  • 2
    add `$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened and you'll see the syntax error. – Funk Forty Niner Jun 17 '15 at 16:51
  • 1
    Ftr those ORs are nasty, and should force you to rethink your schema design. – Mike Purcell Jun 17 '15 at 16:51
  • 1
    someone's probably going to put in an answer, based on my comment too. *it bites me everytime*. so in other words, use ticks around that column `\`` or name it to something other than a reserved word – Funk Forty Niner Jun 17 '15 at 16:52
  • Ok Mitch... what's the score on this? are you reading these comments? where are we at with this? plus, you've got some answer below which is going to make my head spin pretty soon. Notice the upvotes on "my" comments? there's a reason for this. you want me to post my comments to an answer so we can close this up, properly? – Funk Forty Niner Jun 17 '15 at 16:59
  • I'm going to close this question in about 5-10 mins. based on the use of that reserved word. don't leave us in the dark. if you've any issue with that, you can flag it and vote to reopen. – Funk Forty Niner Jun 17 '15 at 17:06
  • Okay, sorry, I was trying to fix everything. I've updated 'type' to 'item_type' in my database and all the documents that used 'type'. I also already had the error mode on to check for errors. While I'm sure the 'type' problem was stopping this from working, there also must be something else. It still isn't working, I'm also not getting any more error messages. I'm scouring through my code to see what is wrong, I'm not sure if I can be much more help in what I think though. – MitchCool1 Jun 17 '15 at 17:07
  • then please update your question with the code you are now using and marking it as an edit underneath your original question. thank you. – Funk Forty Niner Jun 17 '15 at 17:09
  • @Fred-ii- Okay, done. – MitchCool1 Jun 17 '15 at 17:14
  • @Fred-ii- Just solved it and edited the question. Thanks for the help. – MitchCool1 Jun 17 '15 at 17:44
  • 1
    @MitchCool1 That's great Mitch, glad to hear it. You know; Stack lets you post your own answer. *Cheers* and thanks for the update. – Funk Forty Niner Jun 17 '15 at 17:46

2 Answers2

1

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;
MitchCool1
  • 329
  • 3
  • 14
  • those single quotes `'` should be ticks `\`` Mitch. The character to the left of the number 1 on the keyboard. – Funk Forty Niner Jun 17 '15 at 18:34
  • @Fred-ii- Oh, why is that? Just for proper formatting for the answer on this site? The quotes, nor ticks, were used in the actual code. – MitchCool1 Jun 18 '15 at 17:27
  • using regular quotes rather than ticks, would have sent out the wrong signal for visitors to the Q&A, stating that using quotes is valid syntax. Using that, would result in an error. It's best to show what the actual solution was. – Funk Forty Niner Jun 18 '15 at 17:29
  • 1
    Okay, that makes sense. – MitchCool1 Jun 18 '15 at 17:39
0

"....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.

Kirs Sudh
  • 373
  • 2
  • 15