Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Feb 2006
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with auto_increment

    hi,i hav a problem that is regarding the auto_increment....

    first of all....i hav used --->
    $query = "Select * from tblpatient order by Patient_ID desc limit 1";
    $newPatientID = $row["Patient_ID"] + 1;
    i hav oso set it to auto_increment.

    it can display the the ID(last_ID + 1),for example, the last record is 10.after i deleted the last record(by using DELETE statement),the new ID should show 11,but it displays 10 again......

    after i click the ADD button,inside the record will show 11....

    that means what the new_ID shows is not match with the ID that i inserted...???

    why???thanks u very much

    actually i hav faced the same problem by using MS SQL last time,then i used IDEN_CURRENT to solve all these problems.....but i'm not familiar with mysql.....hope somebody to help me....thanks!!
    Last edited by tanhaha_how; 01-11-2007 at 04:41 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    don't worry about gaps in your auto incremented sequence, it shouldn't matter. if you are using the value to insert into another table then you should use last_insert_id() to fetch the value.

    See this article for clarification.

  • #3
    Regular Coder
    Join Date
    Feb 2006
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ooo....i hav the only 1 table....which is tblpatient....then at the new_patientRecord.php,has a textbox to display the new patient ID which is ReadOnly...but the value show inside the textbox is diff with the value stall inside the database....heres my code (new_patientRecord.php):

    <?php
    $query = "Select * from tblpatient order by Patient_ID desc limit 1";
    $result = mysql_query($query);
    $row = mysql_fetch_assoc($result);

    $newPatientID = $row["Patient_ID"] + 1;
    ?>
    <form name="form" action="insert_patientRecords.php" method="POST" onSubmit=" return validate(this)">

    <td width="108" align="left" valign="Middle">Patient ID:</td>

    <td width="173" align="Left" valign="Middle">
    <input type="text" readonly="yes" size="10" maxlength="5" name="Patient_ID" style="font-size:10px; font-family:Verdana; width:40px;" value="<?php echo $newPatientID; ?>"></td>
    (insert_patientRecord.php)

    <?php
    include 'connStr.php';

    $query ="";

    if ($_POST["actionMode"]=="Create"){

    $upperSex = strtoupper($_POST["Sex"]);

    $query = "Insert into tblpatient(Patient_ID, Treatment_Date, Name, Sex, ICNO, Age, DOB, Address, Postcode, Race, Language_Spoken, Telephone_house, Telephone_office, Handphone, Allergy, Email)values('".$_POST["Patient_ID"]."', '".$date1."', '".$_POST["nam"]."', '".$upperSex."', '".$_POST["ICNO"]."', '".$_POST["Age"]."', '".$date."', '".$_POST["Address"]."', '".$_POST["Postcode"]."', '".$_POST["Race"]."', '".$_POST["Language_Spoken"]."', '".$_POST["Telephone_house"]."', '".$_POST["Telephone_office"]."', '".$_POST["Handphone"]."', '".$_POST["Allergy"]."', '".$_POST["Email"]."');";

    $result = mysql_query($query);

    mysql_query("COMMIT");
    }

    echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">";

    echo "<meta http-equiv=\"REFRESH\" content=\"0; URL=patientRecords.php\">";
    ?>
    now,the only problem is the new patient ID show in textbox is not match with the value after i stall into the database.....somemore i dunno how to use last_insert_id()....can u pls show me inside my code????
    thanks for help.....
    Last edited by tanhaha_how; 01-12-2007 at 02:09 AM.

  • #4
    New Coder
    Join Date
    Nov 2006
    Location
    UK
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try:

    File: insert_patientRecord.php
    Code:
    include 'connStr.php';
    
    $query ="";
    
    if ($_POST["actionMode"]=="Create"){
    
    $upperSex = strtoupper($_POST["Sex"]);
    
    $query = "Insert into tblpatient('', Treatment_Date, Name, Sex, ICNO, Age, DOB, Address, Postcode, Race, Language_Spoken, Telephone_house, Telephone_office, Handphone, Allergy, Email)values('".$_POST["Patient_ID"]."', '".$date1."', '".$_POST["nam"]."', '".$upperSex."', '".$_POST["ICNO"]."', '".$_POST["Age"]."', '".$date."', '".$_POST["Address"]."', '".$_POST["Postcode"]."', '".$_POST["Race"]."', '".$_POST["Language_Spoken"]."', '".$_POST["Telephone_house"]."', '".$_POST["Telephone_office"]."', '".$_POST["Handphone"]."', '".$_POST["Allergy"]."', '".$_POST["Email"]."');";
    
    $result = mysql_query($query);
    
    $patientID = mysql_insert_id()
    
    mysql_query("COMMIT");
    }
    
    echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">";
    
    echo "<meta http-equiv=\"REFRESH\" content=\"0; URL=patientRecords.php\">";
    Basically you can just call mysql_insert_id() after a insert query.
    - Mark

  • #5
    Regular Coder
    Join Date
    Feb 2006
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ooo...okok...

    but then how to pass the value of $PatientID to new_patientRecords.php???
    can u pls show me the code?????

    thanks a lot!!!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •