php mysql insert report with unique id

Question

I am trying to insert a new entry to my database with an unique id. Somewhere in my code is a mistake, but i cannot find it. Could you please help?

Autoincrement is no option

    $newConversationID = uniqid();

    $checkID = $DBcon->query("SELECT * FROM tbl_conversations WHERE conversation_id=$newConversationID");
    $countID = $checkID->num_rows;


    while($countID) {

        if ($countID==0) {
            $DBcon->query("INSERT INTO tbl_conversations (conversation_id,user_id, date) VALUES('$newConversationID','$fromID',UNIX_TIMESTAMP())");break;
        } else {

            $newConversationID = uniqid();

            $checkID = $DBcon->query("SELECT * FROM tbl_conversations WHERE conversation_id=$newConversationID");
            $countID = $checkID->num_rows;

        }

    }

You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Jul 01 '17 at 13:56
@Qirel In this case as the data is being generated in the script it does not really make a difference. — RiggsFolly, Jul 01 '17 at 13:57
While that's true, it's a good habit to adopt. Prepare anything that's a variable. — Qirel, Jul 01 '17 at 13:59
Seems like we're in agreement before even commenting then! ;-) — Qirel, Jul 01 '17 at 14:02

RiggsFolly · Answer 1 · 2017-07-01T14:49:53.587

Yes the mistake is in the where($countId). Assuming you dont find a similar row using the key, you will get zero in $countId and zero equates to FALSE. So the where clause will never run.

So if the new uniqueid does not match any existing key in that table, the while loop will not run!

If the new uniqueid does match an existing row, the while loop will run, but ONLY the ELSE condition will execute which does not actually do anything useful.

I am assuming you want to loop until you create a valid new uniqid and then store that row.

$qfind = "SELECT COUNT(conversion_id) FROM tbl_conversations WHERE conversion_id = ?";
$search = $mysqli->prepare($qfind);
$qinsert = "INSERT INTO tbl_conversations (conversion_id) VALUES(?)";
$insert = $mysqli->prepare($qinsert);

//loop till we end up with a new unique id
while(true) {
    $id = uniqid();
    $search->bind_param('s', $id);
    $search->execute();
    $result = $search->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);

    if ($row[0] == 0) {
        // we have a new unique index so store the row
        $insert->bind_param('s', $id);
        $insert->execute();

        // we are all done here so break out of the while loop
        break;
    }
}

Yes, and while operators only run if the condition is TRUE and Zero is NOT TRUE — RiggsFolly, Jul 01 '17 at 13:52

php mysql insert report with unique id

1 Answers1