14 Replies - 2560 Views - Last Post: 13 January 2013 - 03:26 AM

#1 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Auto Increment Sub Id

Posted 09 January 2013 - 03:38 PM

I need to create a tiles table that has a structure like this for http://www.myownmealplanner.com:

id user_id sub_tile_id
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	2	3
7	3	1
8	3	2
9	3	3

etc.
I can't just create new tables for new users because I'm using cakephp and that would require new models, views, and controllers for every new table. How do I get the sub_tile_id to auto_increment starting at every new user id?

Is This A Good Question/Topic? 0
  • +

Replies To: Auto Increment Sub Id

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8959
  • View blog
  • Posts: 33,585
  • Joined: 12-June 08

Re: Auto Increment Sub Id

Posted 09 January 2013 - 03:42 PM

You would take care of that on the server side code.. in the php.
Was This Post Helpful? 0
  • +
  • -

#3 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Re: Auto Increment Sub Id

Posted 10 January 2013 - 08:08 AM

After dropping the id field, I tried both of these and got syntax errors
ALTER TABLE `tiles` MODIFY COLUMN `sub_id` INT NOT NULL AUTO_INCREMENT WHERE `user_id` = 2;
UPDATE TABLE `tiles` MODIFY COLUMN `sub_id` INT NOT NULL AUTO_INCREMENT WHERE `user_id` =2;

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8959
  • View blog
  • Posts: 33,585
  • Joined: 12-June 08

Re: Auto Increment Sub Id

Posted 10 January 2013 - 08:14 AM

You got syntax errors? What sort of errors?
Was This Post Helpful? 0
  • +
  • -

#5 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Re: Auto Increment Sub Id

Posted 10 January 2013 - 08:31 AM

I'm fairly certain it's not possible to alter the table using a where clause. For other stunts I've tried I've gotten the following error: there can be only one auto column and it must be defined as a key
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8959
  • View blog
  • Posts: 33,585
  • Joined: 12-June 08

Re: Auto Increment Sub Id

Posted 10 January 2013 - 08:41 AM

I am not sure how to process that. Where clause? What stunts generated what message?!

https://dev.mysql.co...lter-table.html
Was This Post Helpful? 0
  • +
  • -

#7 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Auto Increment Sub Id

Posted 10 January 2013 - 09:12 AM

why do you use the where clause of a field?! aren't you modifying a column property? you can not modify column properties for a single row...
Was This Post Helpful? 0
  • +
  • -

#8 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Re: Auto Increment Sub Id

Posted 10 January 2013 - 09:13 AM

ALTER TABLE t2 AUTO_INCREMENT = value;
You cannot reset the counter to a value less than or equal to any that have already been used
Was This Post Helpful? 0
  • +
  • -

#9 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Auto Increment Sub Id

Posted 10 January 2013 - 09:33 AM

this is true
Was This Post Helpful? 0
  • +
  • -

#10 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Re: Auto Increment Sub Id

Posted 11 January 2013 - 06:59 AM

This didn't work
CREATE TABLE `tiles` (  
	`sub_tile` ENUM('1','2','3') NOT NULL,
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
	`user_id` INT(22)
 )

Was This Post Helpful? 0
  • +
  • -

#11 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Auto Increment Sub Id

Posted 11 January 2013 - 07:29 AM

Your code is correct! The only thing i can immagine is, that you have tried to create a table without having a database selected...

try to use "CREATE TABLE `yourDataBaseName`.`tiles` (............"
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8959
  • View blog
  • Posts: 33,585
  • Joined: 12-June 08

Re: Auto Increment Sub Id

Posted 11 January 2013 - 07:46 AM

@Anthonidas - My guess is s/he is trying to do this on top of an existing table.. instead of actually doing it right and changing the table structure (and subsequent views, models, etc).
Was This Post Helpful? 0
  • +
  • -

#13 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Auto Increment Sub Id

Posted 11 January 2013 - 08:04 AM

i thought s/he was recreating the tables, not being able to alter them...

oh god... this happens, when you yourself do knot know what you are doing and why you are doing it... :dots:/>

This post has been edited by Anthonidas: 11 January 2013 - 08:06 AM

Was This Post Helpful? 0
  • +
  • -

#14 makamo66  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 42
  • Joined: 04-February 09

Re: Auto Increment Sub Id

Posted 11 January 2013 - 12:33 PM

I was asked at another forum why I want a sub_tile_id and this is my explanation. I'd like to know if it is even really necessary after all.
The jquery at http://myownmealplan...m/mealplans/add contains the following code (see the view source):

for (var i=1;i<100;i++){
$( "#draggable" + i ).draggable();
}

Each draggable div uses the primary key of the tiles table to create its own name, for example draggable1, draggable2,..., and on up to draggable100. The tile id (primary key) gives the draggable div its name and I am looping through 100 of these. If I have five users who each have 20 meal tiles then I have already exhausted all of the names available at 100 (5 times 20 being 100). Of course I could just loop over 200 meal tiles instead, keep adding users and keep looping over ever more meal tiles but it seems like a bad idea. Wouldn't the jquery slow down quite a bit if I loop through for example 500 tiles? If instead each user has his own set of meal tiles then I would grab the user id and the sub_tile_id so it would never be more than maybe 10 or 20 to loop through. (I'm a she BTW)
Was This Post Helpful? 0
  • +
  • -

#15 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Auto Increment Sub Id

Posted 13 January 2013 - 03:26 AM

This can be done purely within the INSERT statement. Assuming you already have an auto-increment on the id column, and you can only insert the user_id and sub_tile_id columns, the query to INSERT a new row would be this:
INSERT INTO `tiles` (
	`user_id`
	`sub_tile_id`
) SELECT
	`user_id`,
	`sub_tile_id`
FROM (
	SELECT
		`user_id`,
		MAX(`sub_tile_id`) + 1 AS `sub_tile_id`,
		0 AS `display_order`
	FROM `tiles`
	WHERE `user_id` = $user_id
	UNION
	SELECT
		$user_id,
		0,
		1
) AS `next_value`
ORDER BY `next_value`.`display_order`
LIMIT 1


This is assuming that you are passing the variable $user_id as the user ID variable.

This post has been edited by e_i_pi: 13 January 2013 - 03:28 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1