Error occurred while tring to modifying data

heshanm · July 21, 2011, 4:41pm

Hi all,

I want to modify data if needed. For that i created a page which retrieves data from 2 pages. But this gives following error message.

Column ‘account_number’ in where clause is ambiguous

[php]

Customer details <?php //initilize variables $full_name = ''; $name_with_initials = ''; $phone_number = ''; $address = ''; $gender = ''; $date_of_birth = ''; $account_type = ''; $fd_period = ''; $sql =sprintf("SELECT account_details. full_name,phone_number,address,gender,date_of_birth,account.name_with_initials,account_type,fd_period"." FROM account_details,account"." WHERE `account_number`='%s'", mysql_real_escape_string($_POST['account_number']) ); $result=mysql_query($sql) or die( mysql_error() ); if( mysql_num_rows($result)<1 ) { echo "

No records found.

"; } $query ="SELECT account_details. full_name,phone_number,address,gender,date_of_birth,account.name_with_initials,account_type,fd_period"." FROM account_details,account"." WHERE account_details.account_number=account.account_number"; $result = mysql_query($query) or die(mysql_error()); while ($row=mysql_fetch_array($result)){ // replace blank variables with variables from the database $full_name = $row['full_name']; $name_with_initials = $row['name_with_initials']; $phone_number = $row['name_with_initials']; $address = $row['address']; $gender = $row['gender']; $date_of_birth = $row['date_of_birth']; $account_type = $row['account_type']; $fd_period = $row['fd_period']; } ?>

                    <td width="25%" class="title02" align="left">Full name</td>
                <td width="55%" class="attribute1" align="left"><label>
          <input type="text" name="full_name" value="<?php echo $row['full_name']; ?>" class="attribute1" />
          </label></td>
              <td width="10%">&nbsp;</td>
             </tr>
             <tr height="30">
              <td>&nbsp;</td>
              <td class="title02" align="left">Name with initials</td>
              <td class="attribute1" align="left"><input type="text" name="name_with_initials" value="<?php echo $row['name_with_initials']; ?>    " class="attribute1" /></td>
             </tr>
               <tr height="30">
              <td width="10%">&nbsp;</td>


              <td width="25%" class="title02" align="left">Phone Number</td>
              <td width="55%" class="attribute1" align="left"><label>
              <input type="text" name="phone_number" value="<?php echo $row['phone_number']; ?>" class="attribute1" />
              </label></td>
              <td width="10%">&nbsp;</td>
             </tr>
                   <tr height="30">
              <td width="10%">&nbsp;</td>


              <td width="25%" class="title02" align="left">Address</td>
              <td width="55%" class="attribute1" align="left"><label>
                <textarea name="address" id="textarea" <?php echo $row['address']; ?> cols="45" rows="5"></textarea>
              </label></td>
              <td width="10%">&nbsp;</td> 
<tr height="30">
                    
              <td>&nbsp;</td>
              <td class="title02" align="left">Gender</td>
              <td class="attribute1" align="left"><label>
                <select name="gender" id="select">
                       <option selected="selected"><?php echo $row['gender']; ?></option>
                  <option value="male">Male</option>
                        <option value="female">Female</option>
              </select>
              </label></td>
<tr height="30">
              <td>&nbsp;</td>
              <td class="title02" align="left">Date of birth</td>
              <td class="attribute1" align="left"><input type="Text" id="demo3" name="date_of_birth" value="<?php echo $row['date_of_birth'];                    ?>" maxlength="25" size="25"/><img src="../images/cal.gif" onClick= "javascript:NewCssCal('demo3','yyyyMMdd')" style="cursor:pointer"/>&nbsp;</td>
</tr>
<tr height="30">
    <td width="10%" height="41">&nbsp;</td>
    <td width="25%" class="title02" align="left">Account Type</td>
    <td width="65%" align="left" bgcolor="#FFFFFF" class="attribute1">
    <select name="account_type" onChange="fd_show(this.value)">
        <option selected="selected"><?php echo $row['account_type']; ?></option>
        <option value="savings_investment">Savings Investment</option>
        <option value="shakthi" >Shakthi</option>
        <option value="surathal">Surathal</option>
        <option value="abhimani_plus">Abhimani Plus</option>
        <option value="yasasa">Yasasa Certificates</option>
        <option value="fd">Fixed Deposits</option>
      </select>&nbsp;</td>
<tr height="30">
  <td colspan="4">
      <div id="fd_box" style="visibility: hidden;">
      <table width="100%" border="0" cellspacing="0" cellpadding="5" align="center">
        <tr height="30"> </tr>
        <tr height="30">
          <td width="10%"></td>
          <td width="25%" class="title02" align="left">FD period</td>
          <td width="55%" class="attribute1" align="left"><select name="fd_period">
              <option selected="selected"><?php echo $row['fd_period']; ?></option>
              <option value="< 1">less than 1 year</option>
              <option value="1-3 years" >1-3 years</option>
              <option value="> 3">more than 3 years</option>
              <option value="il">immediate loan</option>
          </select></td>
          <td width="10%"></td>
        </tr>
        <tr height="1"></tr>

  <tr height="30">
    <td>&nbsp;</td>
    <td width="25%" class="title02" align="left">&nbsp;</td>
    <td width="55%" align="left" bgcolor="#FFFFFF" class="attribute1">&nbsp;</td>

  &nbsp;&nbsp;&nbsp;&nbsp;
<label>
    <input type="submit" name="button2" id="button2" value="Help" />
  </label>
</p>

[/php]

smokeys_13 · July 21, 2011, 5:59pm

Hi there,

I would assume that you have probably got a column account_numberin both tables (account_details,account). If so, prepend account_number with “account.” or similar

heshanm · July 22, 2011, 6:35am

Yes. Both tables there is a column called “account_number”.

As suggested by you, how can i prepend that?

smokeys_13 · July 22, 2011, 6:17pm

It depends which table you want to reference, so either:

account_details.`account_number`

or:

account.`account_number`

heshanm · July 23, 2011, 7:11pm

Hi,

I changed that line. Now it retrieves as an empty record set…Fields were there but no records were there…

$sql =sprintf("SELECT account_details. full_name,phone_number,address,gender,date_of_birth,account.name_with_initials,account_type,fd_period"." FROM account_details,account"." WHERE account_details.`account_number`='%s'", mysql_real_escape_string($_POST['account_number']) );