Random number which is not in the column

Databases
1

I have myTable like the belowcode randomNumber
(1) 86
(2) 13
(3) 27
(4) 35
(5) 42
(6) 58
(7) 61
(8) 78
(9) 95
(10) 98
(11) 53
[/code]Now I have a new randomNumber “49” which is coming from “rand(11,99);” in php.
I like to insert a new record “(12) 49” into myTable.

Here I have a problem, i.e, the randomNumber “49” should not be doubled in the column.

SELECT count(*) FROM myTable WHERE randomNumber=49

Although I can check it is doubled or not with the SQL above, I have to check againg with a new randomNumber which is not “49”

I like to put a new randomNumber which is not doubled.
How can I do it with your help?

2

You’ll have to check if exists whether you like or not. Unless id is autonumeric and what needs to stay unique is the combination of id and randomNumber. But for your description, it is unlikely that this is the case

3 
ALTER TABLE myTable ADD UNIQUE ( randomnumber )

now you will never have to check whether it exists already

2 Likes
4

if I make myTable UNIQUE, I will never have to check whether it exists already.

However, I have to check whether the random number is inserted or not.

By the way, can I select a random number value which is not in the column “randomNumber”?

[code]would-be code

SELECT randomNumber(any_value not in the records) as newNumber
FROM myTable
[/code]

5

do you know how to check the mysql status code after a statement executes?

6

I am afraid I think that I don’t know it.

7

do you know how to research it?

8

[quote=“r937, post:7, topic:242218”]
do you know how to research it?
[/quote]I did googling with the keyword “mysql status code”.
As I read some of search result, I think I found this is not the correct keyword for googling.

Do you have any other suggesions for the keyword?

9 
INSERT INTO myTable (randonNumber)
    VALUES (49)
    WHERE NOT EXISTS (
       SELECT randomNumber 
       FROM myTable 
       WHERE randomNumber = 49
    );

In PHP most extensions for interacting with a database will have a function that will tell you how many rows were inserted. If the extension doesn’t have such a function then before the INSERT you’ll need to do a SELECT query to count the number of rows in the table. After the INSERT you’ll have to do another SELECT to get the current number of rows in the table. If there is now one more then the new row was inserted. If there isn’t any change in the row count then that number already existed.

How many records will get inserted into the table? When the records are being used, in what way are they being used? Depending on how they’re being used you might be better off having PHP randomize the order in which the records are displayed

10

  1. “you cannot insert into a table and select from the same table in a subquery”

  2. doing before and after SELECT COUNT statements suffers from the same probelm as doing a SELECT for a specific value before the INSERT

taking advantage of a core dbms characteristic (uniqueness in a column) is still the “best practice” solution here

11

how to check mysql status code using foobar

where “foobar” is the application language you are using, e.g. java, asp, php, whatever

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.