0

So basically I want to check a MySQL database table to see if a certain number or string is present in a certain column (using PHP).

The reason for this is that each “member” added to my table is given a unique, random, 9-digit number between 100000000 and 999999999. However, I need to make sure that the PHP code doesn’t generate an ID that has already been given to some other member in the past. If it finds that the generated ID already exists, it will re-generate the number until it gets one that has not been previously used.

Now I’ve done some research and I’ve found questions similar to this, but none quite seem to answer this specific problem using PHP. Some have said to use INSTR (see second solution: MySQL query String contains), but I’m not quite sure how I could implement that into what I’m trying to do (it returns all rows (supposedly) where ‘string’ occurs in a column, but that doesn’t help. If it were how many times it occurs, then we would be on the right track). If this isn’t possible or very difficult to do, then I could also just read all the data in my specific column (see: How get all values in a column using PHP?) and then perform a regular PHP function somehow to search for my generated ID…and if it returns false then we don’t have to re-generate.

So the question is: How can I perform a function of sorts which returns true or false in regards as to whether or not ‘string’ (or number) is present in a certain MySQL table column (using PHP)? I’m also working with MySQLi, Procedural syntax…and I’m a relative beginner when it comes to MySQL and PHP.

Thanks in advance!

Community
  • 1
  • 1
William F
  • 87
  • 1
  • 8

2 Answers2

1

I wouldn't do that, imagine (just imagine) you have 99999999 customers, your function will be repeated up to 999999999 times. This is going to kill your application. Instead, create a 

autoincrement int(10) unique

field in your database, let it start at 100000000 and it will automatically increment one to the number. This way, mysql assures that you will have this number unique all the time no matter what happens and you don't need a function that is potentially dangerous for your app.

baao
  • 71,625
  • 17
  • 143
  • 203
  • Thanks for your response! Unfortunately, the ID is supposed to be a password, and therefore an autoincrement would make it too easy for (potential) hackers to find out the pattern. As for the limit that comes with `rand()`, I can use a CAPTCHA to eliminate spam and just extend the random number generator’s search field. I really need a simple way to just make sure the `rand()` didn’t generate a number that already exists in the database (under the corresponding column). – William F Aug 02 '15 at 12:15
  • Ok, if it's a password, every plain text field has to be considered insecure and should be avoided. You will get nearly allways unique values for password_hash, so this might be the better alternative to above. @WilliamF – baao Aug 02 '15 at 13:07
  • Ah yes, indeed. I didn’t know `password_hash()` existed, but after some research and testing, it looks like I’ll be going to the traditional “user puts their password in” route, and then secure it with `password_hash()`. Thank you very much for the help…it wasn’t what I came here looking for, but it’s what I’m going to use in the end. As far as unique IDs go, I’d think the `autoincrement` is also the best route. @Michael – William F Aug 03 '15 at 13:16
1

"How can I perform a function of sorts which returns true or false in regards as to whether or not ‘string’ (or number) is present in a certain MySQL table column":

$query = "SELECT count(*) AS num_found 
FROM myTable 
WHERE columnName = ?"
$stmt= $conn->prepare($query);
$stmt->execute(array($value));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['num_found'];

It really would be easier to use an autoincrement.

Edit: It's not super clear if by "if a certain number or string is present in a certain column" you mean IS the entire value of that column or if it just needs to be part of the text of the column. if the latter use LIKE with wildcards %

Jessica
  • 7,075
  • 28
  • 39