mysqli not been able to find existing records

Question

I've got a hard one on hand: mysqli can't find [some] existing records. Any help would be appreciated.

Case in point: I truncate the table (empty it);

On a first run, I fill it up with 2,495 records from a text file. Just to make sure things are correct (since I verified inconsistencies), I re-run the very same file input. 12 (always the same 12) records can't be located and are re-inserted instead of updated. I checked the file and re-entered them by hand on the text version.

The error persists: on the second run, there are 2,508 records and, of course, 12 duplicates.

Code:

$result = $DB->query("SELECT * FROM `inventory-temp` WHERE MFR='".$MFR."' AND SKU='".$SKU."'");

        $upd = false;
        if ($result->num_rows > 0) {
            if (($result->row['MFR'] == $MFR) && ($result->row['SKU'] == $SKU)) {
                $upd = true;
            }
            unset($result);
        }
        if ($upd) {
            $DB->query($update);
            echo $i, ' = Record updated! <br />';
        } else {
            $DB->query($insert);
            echo $i, ' = New record created! <br />';
        }

I believed the problem was the num_rows return; so I re-checked within the if statement. The problem continued, so I added the unset trying to see if it had anything to do with crumbs.

I also tried ->real_query. No changes. What could I, possibly, be missing?

UPDATE: this is really killing me!

On the 'invisible' character(s), it was a no go. I replaced the original records by hand (on the xls file); re-exported to CSV; and re-tried a few times. Still the same problem.

I failed to inform that I have an UPDATED field with CHAR of lenght of 1 character. Before I do the updates in bulk, I clear that FLAG on all records and update them with * as the record is inserted or updated.

I changed the check code to include that:

$result = $DB->query("SELECT * FROM `inventory-temp` WHERE MFR='".$MFR."' AND SKU='".$SKU."' AND UPDATED=' '");

It still fails to find the existing records.

What is even funnier (not really) is that when I check the table, visually, both the original and duplicate records DO have the UPDATED field with *.

Now is the kicker:

As the records are inserted or updated, I echo the record number (sequence number) and the MFR just for some visual aid.

On the second run, I get the 12 (always the same) Record inserted instead of Record updated.

When I go back to the XLS file for the corresponding line number, the record inserted IS NOT THE ONE listed; but a similar one for a different MFR with the same SKU.

Mind you that THIS MFR has a lenght of 4 characters. The duplicated ones have the length of 11 characters.

Very puzzling ....

These fields had, respectively, CHAR 15 and CHAR 20. For test I changed them to both to TinyText. The problem still happens. Very weird ....

UPDATE (2)

: I went ahead and, instead of a csv input, I made a plain text file delimited by TAB; a.k.a. chr(9). Re-ran everything multiple times.

===> The problem persists.

"When all fail, the obvious .."

Okay, I will, for test, remove ALL records for that MFR and re-run everything.

UPDATE 3:

Okay, trying to solve the puzzle, I went ahead and changed the length of those two columns (every listing) by adding or removing characters like aaaa to make them up to the column width (15 chars). Nothing worked. I did that for both of the culprits MFR 1 and 2. No solution.

The idea was to be able to update the inventory file in real time instead of after hours by updating just the modified records instead of the entire table when dumping the CSV file replacing the table contents.

Oh well, for now it will be after hours. If I run into a solution, I will posted it here.

I just wonder what else the 'i' in MySQL have changed.

Thank you for your trying ...

I don't think you need to check if the returned row values are the same as the variables. The database already does that for you as you have a `where` clause. — FirstOne, Dec 11 '17 at 13:26
Maybe an invisible character that is not being inserted? Or something like that? — FirstOne, Dec 11 '17 at 13:38
[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a class for](https://github.com/GrumpyCrouton/GrumpyPDO) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](https://phpdelusions.net/pdo/mysqli_comparison) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Dec 11 '17 at 13:51
FirstOne: you might, actually, be right about the 'invisible' character. I will re-enter those entire 12 records by hand in the original set. I have had many problems with 'invisible' characters so, it would not surprise me. Thank you! — , Dec 11 '17 at 14:47
Please do not vandalize your posts. Once you have submitted a post, you have licensed the content to the Stack Overflow community at large (under the CC-by-SA license). — Andrew T., Dec 14 '17 at 17:49

Nigel Ren · Answer 1 · 2017-12-11T13:34:58.773

0

Using query() returns a result object and your trying to fetch the actual data from this result.

if ($result->num_rows > 0) {
       $row = $result->fetch_assoc();
       if (($row['MFR'] == $MFR) && ($row['SKU'] == $SKU)) {
           $upd = true;
       }
}

Or - as pointed out that your query is already checking the data, you could simplify it to...

if ($result->num_rows > 0) {
   $upd = true;
}

Which could also make the code shorter by putting the check in the later if...

if ($result->num_rows > 0) {
    $DB->query($update);
    echo $i, ' = Record updated! <br />';
} else {
    $DB->query($insert);
    echo $i, ' = New record created! <br />';
}

edited Dec 11 '17 at 13:34

answered Dec 11 '17 at 13:28

Nigel Ren

56,122
11
43
55

I wouldn't bother checking the variables as the database should already be doing that. – FirstOne Dec 11 '17 at 13:30
Yes; that's correct! If the records exists, the $update statement is performed. If not, the $insert statement is performed. What's happening is that on the second run, 12 of the records fails to bee seen. I was wondering if there is a way around. I like to keep code succinct; coding only the needed stuff whenever possible. If it's not clear, I was wondering if there is an easier way to check for an existing record. Thanks! – Dec 11 '17 at 13:34
If these two values made up a key which didn't allow duplicates, you could use INSERT... ON DUPLICATE (https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) which always tries to insert the record and updates it if it already exists. This depends on how the keys are defined though. – Nigel Ren Dec 11 '17 at 13:37
the `->fetch_assoc()` part of the code is done in the CLASS. I did try that too but we are, actually, missing the point: why those 12 records are returning '0' (not found ---> does not even go in that check). – Dec 11 '17 at 13:37
If you are using non standard classes - which we don't have any knowledge of, it's impossible to guess why your code isn't working! – Nigel Ren Dec 11 '17 at 13:39
I'll look into that (key/ insert/on duplicate). I like the idea. As a matter of fact, I'll try it and come back here if not a solution. Thank you! – Dec 11 '17 at 13:40
The CLASS is standard (from OpenCart). Those two versions that you mentioned above, were my original ones. The one I show in the code is the modified trying to figure out the problem. FirstOne might actually be right: an 'invisible' character in the original data set. I will re-enter the entire 12 records and retry. – Dec 11 '17 at 14:46

score 0 · Answer 2 · edited Dec 15 '17 at 16:20

Problem solved

(I don't believe I should have gone to this):

Making MFR+SKU the table key, as suggested, did not work the way I wanted. A fatal error would be thrown (maybe I was not handling it correctly).

I created a new field named INDEX at the beginning of the table and populated it, in the first pass, with the conjunction of trimmed MFR+SKU.

In the second pass, I checked for the existing INDEX with empty UPDATED field.

It, finally, worked as expected!!!!!!!

mysqli not been able to find existing records

2 Answers2