Theo Moorfield Theo Moorfield - 2 months ago 5
PHP Question

MySQL assign a unique value from another table

I have a problem that I can't figure out, I'm not experienced enough (or it can't be done!) I've trawled Google for the answer with no luck.

I have a system where I need to assign an ID to each row, with the ID from another table. The catch is that the ID must be unique for each row created in this batch.

Basically I'm selling links on my Tumblr accounts, I need to assign a Tumblr account to each link that a customer purchases but I want to assign all possible Tumblr accounts so that duplicates are kept to the minimum possible.

The URLs - each link that a customer buys is stored in this table (urls_anchors):

+----------+--------------------+------------+-----------+------+
| clientID | URL | Anchor | tumblrID  | paid |
+----------+--------------------+------------+-----------+------+
| 1234 | http://example.com | Click here | 67 | Yes |
| 1234 | http://example.com | Click here | 66 | Yes |
| 1234 | http://example.com | Click here | 65 | Yes |
| 1234 | http://example.com | Click here | 64 | Yes |
+----------+--------------------+------------+-----------+------+


All of the Tumblr accounts available for allocation are stored in this table (tumblrs):

+----------+-------------------+------------+
| tumblrID | tumblrURL | spacesLeft |
+----------+-------------------+------------+
| 64 | http://tumblr.com | 9 |
| 65 | http://tumblr.com | 9 |
| 66 | http://tumblr.com | 9 |
| 67 | http://tumblr.com | 9 |
+----------+-------------------+------------+


My best attempt at this has been the following query:

INSERT INTO `urls_anchors` (`clientID`, `URL`,`Anchor`, `tumblrID`, `paid`) VALUES ('$clientID','$url','$line', (SELECT @rank:=@rank+1 AS tumblrID FROM tumblrs WHERE @rank < 68 LIMIT 1), 'No')


Which works but keeps adding incrementally indefinitely, when there are only X number of Tumblrs to assign. I need the query to loop back around when it reaches the last row of Tumblrs and run through the list again.

Also i'm using this in a PHP script, I'm not sure if that's of any significance.

Any help would be MASSIVELY appreciated!

Thanks for looking :)

Answer

You can use a SELECT query as the source of data to insert.

INSERT INTO urls_anchors (`clientID`, `URL`,`Anchor`, `tumblrID`, `paid`) 
SELECT '$clientID','$url','$line', tumblrID, 'No'
FROM tumblrs
LIMIT $number_of_rows

DEMO

This will assign $number_of_rows different tumblrID values to the rows.

If you need to assign more tumbler IDs than are available, you'll need to do this in a loop, subtracting the number of rows inserted from $number_of_rows each time. You can use mysqli_affected_rows() to find out how many rows were inserted each time.