Select count breaks in do-while loop

Question

Where I work we need to register computers into a database, before we just used pen an paper, but I was asked to create an easier method. So I made a small local homepage.

The computers are registered by a tag and number. The tag combined with the number must be unique, I made this loop that increment the entered number until it finds a free one.

                $checkUnique = openConnection("stationer")->prepare("SELECT COUNT(1) FROM `dator` WHERE `tag_id`=:id AND `tag_number`=:number");

                do{
                    $checkUnique -> bindParam(":number", $_POST['number']);
                    $checkUnique -> bindParam(":id", $_POST['tag']);
                    $checkUnique -> execute();
                    $checkUniqueResult = $checkUnique->fetchColumn();

                    if($checkUniqueResult != 0 && empty($searchTagNumber)){
                        $errors[] = "Non-unique tag and number"; break;
                    }

                    $_POST['number'] = $searchTagNumber == "+" ? $_POST['number']+1 : $_POST['number']-1;

                    if($_POST['number'] <= 0){
                        $errors[] = "The tag number can't be 0 or lower"; break;
                    } 
                }while($checkUniqueResult > 0);

But for some odd reason, it appear to randomly stop, even if the tag and number isn't unique, with no error messages, and I have no idea what causes it.

Add error_reporting(E_ALL) at the beginning to see if there really are errors — Phate01, Feb 05 '15 at 14:35
And usually primary keys can be auto-increment: you add a new row and db automatically increments primary key — Phate01, Feb 05 '15 at 14:36
@Phate01 But that would prevent the computers to have the same number but different tags, and I already have a ID field. and No errors even with `error_reporting(E_ALL)` — Yemto, Feb 05 '15 at 14:40
Your loop does many things, but it doesn't find the first free tag+id. — Sumurai8, Feb 05 '15 at 14:46
I am not sure why you don't get an error when there is a non-unique tag and number though. — Sumurai8, Feb 05 '15 at 14:50
@Sumurai8 I have no doubt it isn't the best solution to find a non-unique tag+id, but it's the only one I know how to do. — Yemto, Feb 05 '15 at 14:55
You break after "non-unique tag and number". That means you should get the error and DONT find the first unique tag+number. If you remove that break it should work correctly (with a lot of error arrays). If it doesn't, I would recommend to `var_dump(...)` variables in your loop. — Sumurai8, Feb 05 '15 at 14:59

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

You can use an unique key like this:

UNIQUE KEY `computer_key` (`tag`,`number`)

Or change engine MyISAM: mysql two column primary key with auto-increment

edited May 23 '17 at 12:12

Community

1
1

answered Feb 05 '15 at 15:19

Phate01

2,499
2
30
55

Thanks, I had no idea MyISAM could do that. – Yemto Feb 05 '15 at 16:12

Select count breaks in do-while loop

1 Answers1