Simple SQL question

slindstr

what I need to do is insert a value into a table as long as it is not already being used in another table. An example is applying for a membership with a desired username that is not in use (i.e. it needs to check the usernames table and if it is not being used insert it into the applications table).

What I tried (with no success) was :

INSERT INTO `applications` VALUES('myUsername') 
WHERE (SELECT COUNT(name) FROM `usernames` WHERE name='myUsername') = 0

Do I just need to break this into 2 queries and if mysql_num_rows() == 0 then continue inserting into applications?

Thanks
Steve

NogDog

I cannot think of a way to do it with just one query.

Piranha

I think that the database is not properly designed. Can you please show how those two tables look like and describe why you need two tables then it is much easier to see if it is possible to do it another way or not.

My guess is that the tables are virtually the same and that the only big difference is that one is for members and the other for persons that have applied to become members. If that is right then it would be better to have only one table and add a column with status for the person. The status could for example be member, applied, accepted, refused, former member. Then you don't have to worry about two tables, it would all go in one table.

slindstr

Thanks for all the replies guys.

Piranha, the two tables aren't the same. the users table looks something like (let's see how good my formatting is):


Users
-------------------------------------------
| username      |        varchar(40)
| password       |        varchar(40)
| is_active        |        bool
| date_joined    |        DateTime
|------------------------------------------


Applications
-------------------------------------------------------------
| username       |             varchar(40)                   |
| ...                                                                      |
| and then lots of boring business information       |
|------------------------------------------------------------

I guess what I could do is just stick them into the users table and then the applications table using a transaction (and multiple queries), and if they are denied just delete them using another transaction. Basically I just need to get this done now since it is now Tuesday 🙂.

Piranha

That sounds like a good idea 🙂

Roger_Ramjet

You can do this sort of thing with with 1 query, several ways depending on your version


"INSERT INTO applications(username )
SELECT n
FROM (SELECT 'myUsername' AS n) AS t
LEFT JOIN users u ON ( t.n = u.username )
WHERE u.username IS NULL"

Just ran a test on a spare db and it works. Must be 4.1 or greater though .

Basically, if the 'myUsername' is NOT in the second table then you get something to insert, otherwise you get NULL to insert so provided your table column is set up to NOT NULL then it bounces.

slindstr

Thanks a bunch for your reply - I KNEW there had to be a way to do it with one query, but I'm still having trouble getting it to work. The query I've got (with test values in it) is:

INSERT INTO applications(  `username` ,  `email` ,  `contact_name` ,  `business_name` ,  `business_license` ,  `business_address1` ,  `business_address2` ,  `business_city` ,  `business_zip` ,  `business_description` ,  `business_phone` ,  `business_size` ,  `primary_makes` ,  `url` ,  `type` ,  `date` ) 
VALUES (
'thisisatest',  'test@gmail.com',  'Steve',  'Test Technologies',  '12345',  '123 fake street',  '#2',  'Orlando',  '99999',  'Test Technologies does cool stuff',  '555-555-5555',  'N/A',  'N/A',  'www.google.com',  'advertiser', NOW( )
)
SELECT n
FROM (
SELECT  'username' AS n
) AS t
LEFT JOIN  `users` u ON ( t.n = u.username ) 
WHERE u.username IS NULL

And Here's the structure of the two tables in question:

applications
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| username             | varchar(100) | NO   |     |         |       |
| email                | varchar(100) | NO   |     |         |       |
| contact_name         | varchar(255) | NO   |     |         |       |
| business_name        | varchar(255) | NO   |     |         |       |
| business_license     | varchar(255) | NO   | PRI |         |       |
| business_address1    | varchar(255) | NO   |     |         |       |
| business_address2    | varchar(255) | NO   |     |         |       |
| business_city        | varchar(255) | NO   |     |         |       |
| business_zip         | int(5)       | NO   |     |         |       |
| business_description | longtext     | NO   |     |         |       |
| business_phone       | varchar(50)  | NO   |     |         |       |
| business_size        | varchar(100) | NO   |     | N/A     |       |
| primary_makes        | varchar(100) | NO   |     | N/A     |       |
| url                  | varchar(255) | NO   |     |         |       |
| type                 | varchar(10)  | NO   |     |         |       |
| date                 | date         | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+


users
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(100)     | NO   |     |         |                |
| password | varchar(100)     | NO   |     |         |                |
| email    | varchar(255)     | NO   |     |         |                |
| active   | tinyint(1)       | NO   |     | 0       |                |
+----------+------------------+------+-----+---------+----------------+

Any Idea why it might not be working? Both tables are InnoDb tables and I am running mysql v 5.0.15

Thanks again!

slindstr

Almost forgot - the error mysql is giving me is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT n FROM (SELECT 'username' as n) as t LEFT JOIN `users` u ON (t.n = u.user' at line 1

Roger_Ramjet

No, instead of the Values clause you use the Select clause to return the insert data

"INSERT INTO applications( `username`,  `email`,  `contact_name`,
 `business_name`,  `business_license`,  `business_address1,
 `business_address2`,  `business_city`,  `business_zip`,
  `business_description`,  `business_phone`,  `business_size`, 
 `primary_makes`,  `url`,  `type`,  `date`) 
SELECT d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16
FROM (SELECT ('thisisatest' as d1, 'test@gmail.com' as d2, 'Steve' as d3,  
'Test Technologies' as d4, '12345' as d5, '123 fake street' as d6,  
'#2' as d7,  'Orlando' as d8,  '99999' as d9, 'Test Technologies does cool stuff' as d10,  '555-555-5555' as d11, 'N/A' as d12, 'N/A' as d13, 'www.google.com' as d14,  
'advertiser' as d15, NOW( ) as d16)) AS t
LEFT JOIN  `users` u ON ( t.d1 = u.username ) 
WHERE u.username IS NULL)"

Remember, some of the insert columns must be not null as the subquery will return null values when u.username exists.

slindstr

sweet! I didn't know you could do that! worked like a charm - thanks!

Roger_Ramjet

Basic INSERT ... SELECT, go read about it in the manual.
Glad to be of help - and it was an interesting problem to work out cos I've never actually done it myself. Don't mean to be snotty but I design my apps with better architecture so that this sort of thing just never comes up.

slindstr

how would you redesign it?

leatherback

slindstr wrote:
how would you redesign it?

You make sure you never store the same information twice.
So ALL users are always stored in table users. In applications you only store the ID of the relevant user.