Prevent some duplicate entries into database

[SalientAnimal]

Hi All,

 

I have a bit of a problem with my one form. Initially the form only had two fields to capture, a radio button selection (4 options) and then a free text field. The free text field needs to be captured as either a Cellular number or an identity number, and originally this worked fine. The ID Number / Cellular number are both captured in the same field and thus write to the same column in my MySQL database. The problem now though is, that allow I want to allow duplicate ID Numbers, I don't want to allow Duplicate Cellular numbers.

 

How to I allow the ID number to be a duplicate, but not the ID number?

 

The reason why I am allowing either or to be captured, is because if it is a brand new user, I will not have a Cellular number for them, but I will have an ID Number. If it is a past user, I will have a cellular number and therefore HAVE to use the Cellular number, but want to prevent duplication of this number in my database.

 

Any help here is appreciated.

 

Here is my form's code:

			<!-- DISPLAY THE DETAILS OF THE LOGGED IN USER -->
			<!--<label>User Logged In :
			<span class="small">You are logged in as</span>
			</label>-->
</div>
			
<div id='radio' class='form2'>	
<table class='radio' border ='0'><tbody>

<tr>
<td colspan='4'>
				<p class='survey_highlight'>
				<b>Transaction Details</b>				
				</p>
</td>
</tr>

<tr>
<td>
			<label>Logged In As :
			<span class="small">User who is logged in</span>
			</label>
</td>			
<td>		
			<input type="text" name="username" id="username" value="<?php echo htmlentities($_SESSION['username']);?>" readonly style="background-color: #C9C9C9">
			<input type="hidden" name="user_id" id="user_id" value="<?php echo htmlentities($_SESSION['user_id']);?>" readonly style="background-color: #C9C9C9">			
</td>
</tr>

<tr>
<td>
			<label>Tracker Reference Number :
			<span class="small">Random Reference Number on Tracker</span>
			</label> -->
</td>			
<td>		
			<input type="hidden" name="sales_reference" id="sales_reference" value="<?php echo $random;?>" readonly style="background-color: #C9C9C9">		
</td>
</tr>


<tr>
<td>
			<label>Customer MSISDN / ID:
			<span class="small">MSISDN / ID on which the transaction was performed</span>
			</label>
</td>			
<td>		
			<input type="text" name="msisdn" id="msisdn"/>
</td>
</tr>



<tr>
<td colspan='4'>
<p class='survey_highlight'><b>Please select a suitable transaction type from the options below</b></p>
</td>
</tr>



<td>
			<!-- FIELD FOR CAPUTRING THE INTERVIEWEE'S AGE GROUP -->
			<label>Type of Transaction :
			<span class="small">What kind of transaction</span>
			</label>
</td>

<p id="sale_type_info">		
<td>	
<label><input type="radio" name="sale_type" onChange="JavaScript:select_radio_button('sale_type_info');" value="0" id="rbg_sale_type_0">
<span class='survey_button'>New Sale</span></label>
</td>

<td>
<label><input type="radio" name="sale_type" onChange="JavaScript:select_radio_button('sale_type_info');" value="1" id="rbg_sale_type_1">
<span class='survey_button'>Save</span></label>
</td>

<td>
<label><input type="radio" name="sale_type" onChange="JavaScript:select_radio_button('sale_type_info');" value="2" id="rbg_sale_type_2">
<span class='survey_button'>Upgrade</span></label>
</td>

<td>
<label><input type="radio" name="sale_type" onChange="JavaScript:select_radio_button('sale_type_info');" value="3" id="rbg_sale_type_2">
<span class='survey_button'>Cancellation</span></label>
</td>
</tr>
<tr>


<td>
</td>



</tr>
</table>


<table border='0'>
<td align="right"> </td>
<td align='right'><input type="submit" name="btn_submit2" id="btn_submit2" value="Submit"></td>
<td align="right"> </td>
</table>

 
</tbody> 
</table>
</form>
</body>
</div>
</div>
<div id="container">
<div id="footer" style="margin-top:0px;">

[Csharp]

First, you should never use a column to store 2 types of data. Otherwise you'll quickly stop understanding what you're doing yourself.

 

> How to I allow the ID number to be a duplicate, but not the ID number?

Like this, this makes no sense. A user id should always be unique, that's what an ID is good for.

 

Now, you should use two different fields for the auto numeric ID and the Phone number. This way you can make both UNIQUE (depending on the database vendor) and can manage users with their ID rather than using an absurd field with silly data.

[SalientAnimal]

Hi there Csharp, 

 

Thanks for your reply. How ever the ID number is not a user ID Number as such but an Identity number, similar to that of a Green Card Number or a passport number. The reason I store the ID number and Cellular number in the same column is because if the person is a new subscriber they would not have a cellular number assigned to them, and they could possibly request more than one cellular number. 

 

However, in the instance of the user already having a cellular number I only want to be able to capture that cellular number once.

[maxxd]

Hang on, I'm confused (proving Csharp's point in the process).

 

So, you want to allow a single user to register multiple cell numbers? What does that have to do with green cards or passports? If you're attempting to associate multiple cell phones with a single user, break the cell phone number column out into a separate table and link the records via the auto-incremented user ID from your user table. This user ID won't be a green card number or passport number or even cell phone number, but an internal, database-generated identification number that the end user never sees - it's used specifically for linking records between tables (for instance, the user and cell phone tables), and ensuring that whatever action you're about to perform on a record in the table is being performed on the correct record.

 

You could even add another column to the user table that specifies what type of number the user is using for the public-facing identification (cell phone, green card, passport, library), which might make whatever logic you're using a bit more transparent.

[SalientAnimal]

I have gone and created confusion on this thread by referring to it as an ID Number. The ID Number is not the table column that you would use as an ID column, but an Identity Number, where I am from each person has a unique ID Number (Identity Number) this is the same thing as a green card number / passport number.