Welcome to WebmasterWorld Guest from 3.80.6.254

Forum Moderators: open

MySQL, UNIQUE includes an INT field, but when it's 0?

     
7:59 am on Mar 22, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1203
votes: 119


I have a table that looks like:

username - var
ignore_user - var, default NULL
ignore_id - int unsigned, default NULL

I have a UNIQUE index on username + ignore_user, and another on username + ignore_id.

The query that I'm using is (in PHP):

$hide_query = sprintf(<<<EOF
INSERT IGNORE INTO ignorelist VALUES('%s', '%s', '%s')
EOF
,
mysqli_real_escape_string($dbh, $user),
mysqli_real_escape_string($dbh, $hide_username),
mysqli_real_escape_string($dbh, $hide_id));


In the script, I will either have a value for $hide_username OR $hide_id, but never both.

The problem I'm having is when there's a value for $hide_username and none for $hide_id, it sets a value of 0 instead of NULL. But then when I set another row with the same $user and a different value for $hide_username, it doesn't write because it's matching the username + ignore_id index.

Can you guys and gals suggest a way to modify either the table or the script so that the UNIQUE doesn't match like that? My only idea was to change it from INT to VAR, but is that the best way?
2:01 pm on Mar 22, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3511
votes: 84


i'd suggest modifying the script.
... you need to develop the script so that if hide_id has no value, then $hide_username = NULL so that you insert NULL into the table rather than zero.
2:21 am on Mar 23, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1203
votes: 119


It has literally taken ALL day, but I finally figured out the trick!

if ($hide_username) {
// this had to be in quotes
$hide_id = 'NULL';
}

// the 3rd %s has to NOT be in quotes!
$hide_query = sprintf(<<<EOF
INSERT IGNORE INTO ignorelist VALUES('%s', '%s', %s)
EOF
,
mysqli_real_escape_string($dbh, $user),
mysqli_real_escape_string($dbh, $hide_username),
mysqli_real_escape_string($dbh, $hide_id));


I bet I went through 40 variations before I finally stumbled on it! LOL But the key was that the %s for $hide_id needed to not be in quotes, otherwise it was trying to send a string of NULL instead of just NULL.

Hopefully this will help someone else in the future! I found hundreds of cases where people were struggling with this, but with no real answer.
6:47 am on Mar 23, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3511
votes: 84


well done! you cracked it!