Hi all,

Im trying to make a database table with student information in mysql. What i would like to have is a 7 digit random number stored as the student id, as a key field in the table. Is there any special syntax i can use in order to make this happen?

Thanks,

Ryan...

    http://www.mysql.com/doc/en/CREATE_TABLE.html
    Option AUTO_INCREMENT
    The next AUTO_INCREMENT value you want to set for your table (MyISAM only; to set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less, and delete the dummy row).

      Thank you for what you have offered so far, but i am still a little unsure about what to do exactly. What i was trying to do was use the rand() function in php to create the number, then check it against the 'id' field from the table to make sure it wasnt in there, and then insert it as a new student. Is there a rand function in mysql that does this a little easier?

        If you have an AUTO INCREMENT field, then you shouldn't be inserting MySQL RAND() or PHP rand() values into it.

        You've already stated the solution to your problem, go and script it! There is no easier way, and that really isn't that hard anyway.

          superwormy,

          I have made the autoincrement field, just plain varchar(7) labeled 'id'. I have tried selecting the id's from the table and checking the new random number with it, but it just doesnt seem to be working. Here is what i have:

          do
          {
          $fet = "SELECT id from contact_info";

          $compare = mysql_query($fet) or die("Could not find an Id: " . mysql_error());

          $is_found = mysql_fetch_array($compare, MYSQL_ASSOC);

          $id = rand(10,18);

          foreach ($is_found as $col_val)
          {
          if ($id == $col_val)
          {
          $same = 1;
          break;
          }
          else
          $same = 0;
          }
          }while($same == 1);
          //Once this loop exits, it should have a number not already in the
          //database stored as $id...and then it should do the insert.

          //Although it is doing the insert and there are still duplicate
          //numbers coming up...

          $insert = "INSERT INTO contact_info(id,
          namePre,
          nameFirst,
          nameMid,
          nameLast,
          .......

            the point being made is that there is no need for you get a random value created.
            when you have id as primary and auto-increment , it is always unique and it increments for every new id.
            unless you have a specific reason for creating a random 7 digit number for the id, just leave it as it is.

            reg
            kevin

              I do have a reason for having a random number. 7 is just a size i picked, but the real reason is because this is for an online survey for the Computer Science department students to take in their senior project courses. Parts of the survey are to be anonymous, therefore, if one person goes in and fills out the survey and their student id is 1, and their answer_id is also 1, professors will be able to know which student entered what answers.

              Does this help?

                Table contact_info:
                +---------------+--------------+------+-----+---------+-------+
                | Field | Type | Null | Key| Default| Extra |
                +---------------+--------------+------+-----+---------+-------+
                | id | int(7) | | PRI | 0 | |
                | namePre | varchar(5) | YES | | NULL | |
                | nameFirst | varchar(25) | YES | | NULL | |
                | nameMid | char(1) | YES | | NULL | |
                | nameLast | varchar(25) | YES | | NULL | |
                | streetAddress | varchar(50) | YES | | NULL | |
                | city | varchar(25) | YES | | NULL | |
                | state | char(2) | YES | | NULL | |
                | zip | varchar(12) | YES | | NULL | |
                | phone | varchar(11) | YES | | NULL | |
                | email | varchar(50) | YES | | NULL | |
                | otherInfo | varchar(255) | YES | | NULL | |
                | alumni1 | varchar(70) | YES | | NULL | |
                | alumni2 | varchar(30) | YES | | NULL | |
                | alumni3 | varchar(30) | YES | | NULL | |
                | alumni4 | varchar(60) | YES | | NULL | |
                | alumni5 | varchar(10) | YES | | NULL | |
                | companyName| varchar(50) | YES | | NULL | |
                | companyCity | varchar(25) | YES | | NULL | |
                | companyState | char(2) | YES | | NULL | |
                | workPhone | varchar(11) | YES | | NULL | |
                | workExt | varchar(10) | YES | | NULL | |
                | workEmail | varchar(50) | YES | | NULL | |
                +---------------+--------------+------+-----+---------+-------+

                Table answers:
                Fields: 1.) id int(7) < --- also would like random, as a key to
                enter back in to come back to finish out the survey
                2.) questionid int(4)
                3.) answer varchar(50)

                These are really the only two tables. The contact info has to be key fielded completely different than the answers so they cannot be compared. Also, the student is given the option to save and logout, during the middle of the survey. What we would like to do here is have a random number generated in with their contact info that they can use as a password to re-enter the survey and continue where they left off.

                So, add question_marker varchar(7) rand() to the contact info table and on save and quit, the last question_id they completed and thier question marker is saved in another table with just those two fields so that if they have been there before and they enter their question_marker, they can start back up the survery at what question they finished with...

                Does any of this help?

                Ryan...

                PS - Thank you all so much for trying to understand.

                  Write a Reply...