Finding the next ID number (not PK)

Yii 1.1 General Discussion
1

Guys and Girls,

Im just starting to look into this,

I need to auto insert the next ‘Lead Number (id_lead)’ into an Update form.

This isnt the primary Key, and it doesnt auto increment - becasue leads can be deleted, but must never be reused…

So, I need to query the database, find the last (highest) id_lead value, increment 1, and present it in the Update View (probably not directly in a form field, as the user might not realise that it needs to be saved, so i’ll echo it out next to the form field, something like “[$id_lead_next]<- Suggested Lead Number” and the update view will require an id_lead …

Any help appreciated, im sure its really simple to most of you - but everything i do in Yii is a learning curve, but im surfing it ok!

p

2

ok, I’ve managed it, probably a hacky way of doing it, but at this stage, its fine by me

Looking at a post on here, I did the following




<div class="row">

  <?php echo $form->labelEx($model,'id_lead'); ?>

  <?php echo $form->textField($model,'id_lead'); ?>

  <?php

  /*********************************************************************************************/

  $id_lead_last = Yii::app()->db->createCommand("SELECT MAX(`id_lead`) as `max` FROM `leads` WHERE 1")->queryScalar();

  echo "(New Lead ID: " . $id_lead_new =  $id_lead_last + 1 . ")";

  /*********************************************************************************************/

  ?>

  <?php echo $form->error($model,'id_lead'); ?>

  </div>

I might output it directly into the form field at a later date.

3

by what i know

auto increment dont repat him self

for example if you have id with AI and the lad id is 5 and you delete it.

then add a new field the next will be 6 and not 5

4

Image this scenario… two users enter the same form… both of them gets the same ID as MAX(id) is always the same at that time… so when they get to save… both of them will save the same ID…

So if autoincrement is not working for this case… the only reasonable solution is to use transactions and to get the max(id) at the time of saving the record… in this case the ID is not shown on the form at all during the update.

5

Additional scenario: You mentioned you can’t reuse an ID. So what if you create a “lead” and MAX() returns 10. Right after that you delete it. When you create a new “lead” MAX will return 10 again…Transactions won’t help in such a case.

amiramir is right: auto increment usually doesn’t reuse any numbers unless you specifically tell the database to do so. Is there a reason you are not able to use that?

6

Hmm, i guess i should convert the ‘id_lead’ to be the PK/AI instead of ‘id’… I will see what breaks, as i think alot of it works by using the ‘id’ of each table…

Thank you all, I know what I need to do - i just need to drink a few coffees and just get on with it now!

paul

7

ok, I changed the PK, and it all seems to work ok, apart from the navigation, so had to change $id to $id_lead all over the place, and sadly, it seems that to get the buttons on the datagrid to work i need to add this hacky code:

Perhaps if i had set the PF to id_lead when I built the CRUD/Model/Controller etc it wouldnt need it, but im too new to Yii to understand it all yet. grr


/*

		array(

			'class'=>'CButtonColumn',

		),

		*/

		array

		(

			'class'=>'CButtonColumn',

			'template'=>'{view}{update}{delete}',

			'buttons'=>array

			(

				'view' => array

				(

					'url'=>'$this->grid->controller->createUrl("/leads/view", array("id_lead"=>$data->primaryKey))',

					//'url'=>'$this->grid->controller->createUrl("/leads/update", array("id_lead"=>$data->primaryKey,"asDialog"=>1,"gridId"=>$this->grid->id))',

					//'url'=>'"index.php?r=leads/update&id_lead="',

				),

				'update' => array

				(

					'url'=>'$this->grid->controller->createUrl("/leads/update", array("id_lead"=>$data->primaryKey))',

					//'url'=>'$this->grid->controller->createUrl("/leads/update", array("id_lead"=>$data->primaryKey,"asDialog"=>1,"gridId"=>$this->grid->id))',

					//'url'=>'"index.php?r=leads/update&id_lead="',

				),

				'delete' => array

				(

					'url'=>'$this->grid->controller->createUrl("/leads/delete", array("id_lead"=>$data->primaryKey))',

					//'url'=>'$this->grid->controller->createUrl("/leads/update", array("id_lead"=>$data->primaryKey,"asDialog"=>1,"gridId"=>$this->grid->id))',

					//'url'=>'"index.php?r=leads/update&id_lead="',

				),

			),

		),