Problem in generating account numbers based on account types

[heshan]

Hi all,

 

Here i have a serious problem. I want to sought it out using PHP and MySQL only.

I have a form includes fields of customer id and Account type( stored in a jump menu and includes 6 different types of accounts)

 

<style type="text/css">
<!--
body,td,th {
font-size: 18px;
font-weight: bold;
}
-->
</style>
<p><img src="../images/mahapitiya 1.jpg" width="1024" height="139" /></p>
<form id="form1" name="form1" method="post" action="">
  <label>
    <input type="submit" name="button" id="button" value="Logout" />
  </label>
</form>
<p> </p>
<form action="" method="post" name="form2" id="form2" 
onsubmit="return Validate();">
  <fieldset>
    <legend class="cap">Create an Account</legend>
    <table width="75%" border="0" cellspacing="0" cellpadding="5" align="center">
      <tr>
        <td> </td>
        <td class="title02"> </td>
        <td> </td>
        <td> </td>
      </tr>
      <tr height="30">
        <td width="10%"> </td>
        <td width="25%" class="title02" align="left">Customer ID</td>
        <td width="55%" class="attribute1" align="left"><input type="text" name="customer_id" class="attribute1" /></td>
        <td width="10%"> </td>
      </tr>
      <tr height="30">
        <td> </td>
        <td width="25%" class="title02" align="left">Account Type</td>
        <td width="55%" align="left" bgcolor="#FFFFFF" class="attribute1"><select name="account_type" id="jumpMenu" >
            <option selected="selected"></option>
            <option>Savings Investment</option>
            <option>Shakthi</option>
            <option>Surathal</option>
            <option>Abhimani Plus</option>
            <option>Yasasa Certificates</option>
            <option>Fixed Deposits</option>
          </select> </td>
        
        <td width="10%"> </td>
      </tr>
    </table>
    <p align="center"> </p>
    <p align="center">
      <input type="submit" onclick="return Validate();" name="submit" value="Submit" class="attribute1" />
        
      <input type="reset" name="reset" value="Reset" class="attribute1" />
        
      <label>
        <input type="submit" name="button2" id="button2" value="Help" />
      </label>
    </p>
  </fieldset>
  </td>
  <td width="5%"> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td align="center"> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td><font color="red" size="1" ></font></td>
    <td> </td>
  </tr>
  </table>
</form>
<p> </p>

<script language = "Javascript">
  
function Validate()
{
    if (document.form2.customer_id.value == '') 
    {
        alert('Please enter the valid customer id!');
        return false;
}
else if ( document.form2.account_type.selectedIndex == '' )
    {
        alert ( "Please select an account type!." );
        return false;
    }
return true;
}
</script>

 

 

There are 6 different types of tables exist in my database representing 6 different types of accounts.Each and every table there is a field called "account number" which is auto incremented.When user clicks on submit button i want account number to be opened based on selected account type.

How this could be done?

 

Thanks,

Heshan.

[TOA]

What I hear you saying is something like this

 

$table = $_POST['account_type'];
$customer = $_POST['customer_id'];
$sql = "SELECT * FROM $table WHERE customer_id=$customer";

 

This will select all fields (don't use that wildcard...put in the field names you want) for customer_id "$customer" from account_type table "$table".

 

But if you need more than that, you'll need to post your db structure

 

Hope that helps

[abdfahim]

As your form heading says, "Create an Account", I presume you want to create a new account with the customer ID, that is insert a new record in the corresponding table. To do that

 

1) for each option (account type), put the name of the corresponding table as option value

<option value="saving_investment_table">Savings Investment</option>
<option value="Shakthi_table">Shakthi</option>
<option value="Surathal_table">Surathal</option>

etc....

 

2) Then in the php portion, write something like

if(isset($_POST['submit'])){
      $query="INSERT INTO ".$_POST['account_type']." (`customer_id_column_name`) VALUES('".$_POST['customer_id']."')";
      mysql_query($query) or die(mysql_error());
}

[TOA]

As your form heading says, "Create an Account"

 

Saw that, but it didn't really sound like that's what he was asking for, since he said he wanted the records to be "opened". Now he's covered on both fronts  Good call

[abdfahim]

As your form heading says, "Create an Account"

 

Saw that, but it didn't really sound like that's what he was asking for, since he said he wanted the records to be "opened". Now he's covered on both fronts  Good call

 

Yup, it took me several minutes also to decide what is exact case ... then decided to go for this one .. better that we thought in opposite way as both are covered now, like you said .. :)

[heshan]

Thanks you for both of you all  for your ideas...

 

I want to further explain the case.

I use the word "opened" means a new account has been created.

 

The db structure of one account table looks like this...

 

CREATE TABLE IF NOT EXISTS `savings_investment` (

  `si_number` int(12) NOT NULL AUTO_INCREMENT,

  `si_balance` float NOT NULL,

  `si_interest` float NOT NULL,

  `account_type` varchar(15) NOT NULL,

  PRIMARY KEY (`si_number`)

 

Every table looks like this.

 

Customer id is created as a result of another operation which is done before that. Therefore just do not want to think much about that.

 

I want something like this to be done. If user select on savings investment account type and click on the submit button there should be a message comes like " A new savings investment account has been created". The account number 1 should be generated under this account type from the database. Hope you all get a better idea........

 

Thanks,

Heshan.

[abdfahim]

I want something like this to be done. If user select on savings investment account type and click on the submit button there should be a message comes like " A new savings investment account has been created". The account number 1 should be generated under this account type from the database. Hope you all get a better idea........

 

Sorry, not totally yet ..

- do you want to insert any new record in the corresponding table when user click submit? (if yes, then my past reply will work)

- What is your account number field in the table? si_number? You want to display the si_number for the just created account?

[heshan]

@abdbuet, you got the thing correct,

 

Yeah, i want new record to be added when user clicks on submit table.

The si_number is the account number which i refer earlier.

 

si_number is the savings investment account number....

Is it possible to create a message box saying " A new ......( name of the account) has been created successfully."??

 

 

[abdfahim]

yes, possible. After the insert code (in my first reply), echo the following

echo "A new account with number ".mysql_insert_id()." has been created successfully.";

[heshan]

I have tried this code. This generates an account number if user select savings investment. How this could be generalized to all accounts?? That is if user selects on shakthi account it updates data on shakthi table likewise......

 

if(isset($_POST['submit'])){
      $query="INSERT INTO ".$_POST['account_type']." (`si_number`) VALUES('".$_POST['account_type']."')";
  mysql_query($query) or die(mysql_error());
     
}
echo "A new account with number ".mysql_insert_id()." has been created successfully.";

 

Thanks,

Heshan.

[abdfahim]

Have you changed your SELECT portions of your form as I instructed in my first reply??

 

<option value="savings_investment">Savings Investment</option>
<option value="Shakthi_table_name">Shakthi</option>
<option value="Surathal_table_name">Surathal</option>

etc... etc..

 

Also, put the echo just after mysql_query, otherwise it'll keep showing some text even if nobody click on submit button. Also, why you are inserting account_type in si_number field?? si_number is an auto_number field, ryt?? Your query should look like

 

if(isset($_POST['submit'])){
      $query="INSERT INTO ".$_POST['account_type']." (`si_balance`,`si_interest`) VALUES('0','NA')";
      mysql_query($query) or die(mysql_error());
      echo "A new account with number ".mysql_insert_id()." has been created successfully.";     
}

[abdfahim]

to make this more clear, and some update of the last reply

 

Have you changed your SELECT portions of your form as I instructed in my first reply??

lets say, the db table name is Shakthi_table_name for Shakthi plan and so on for others. Then change like

 

<option value="savings_investment">Savings Investment</option>
<option value="Shakthi_table_name">Shakthi</option>
<option value="Surathal_table_name">Surathal</option>

etc... etc..

 

Also, put the echo just after mysql_query, otherwise it'll keep showing some text even if nobody click on submit button. And, why you are inserting account_type in si_number field?? si_number is an auto_number field, ryt??

Another thing is, you should have same name for the field as you already have distinguished table name. e.g. balance, interest (not si_balance, si_interest). It'll make your life easy.

 

I presume your initial balance and interest is zero for a new account, then you should have something like this

 

if(isset($_POST['submit'])){
      $query="INSERT INTO ".$_POST['account_type']." (`balance`,`interest`,`account_type`) VALUES('0','0','".$_POST['account_type']."')";
      mysql_query($query) or die(mysql_error());
      echo "A new account with number ".mysql_insert_id()." has been created successfully.";     
}

[heshan]

@abdbuet,

you help me a lot regarding this matter. Now this works perfectly....

Hope you will help me out in the future as well regarding issues in php....

 

 

Thanks,

Heshan.

[abdfahim]

you are welcome. we always try to help each other in this forum.

 

Please do mark this post as SOLVED. It is a good practice, and helpful for forum members also.

 

Thanks,