title: index cardinality for quiz table field is '2', but should it be '0'
body: <p>I have a simple quiz system in which there is a quiz table and php code written to add a quiz to the database, including a unique ID for each quiz. In the database &amp; program that work, I notice the index cardinality is 0, and in the database that does not appear to work, I notice the index cardinality is 2. </p>
  
  <p>Is this causing the lack of generation of a unique quizid, or has it got something to do with the code?</p>
  
  <p>For example, I create a quiz called testquiz, it should generate an id of 1,2,3 etc.</p>
  
  <p>Instead it produces this:</p>
  
  <p><a href="https://i.stack.imgur.com/vObt2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vObt2.png" alt="enter image description here"></a></p>
  
  <p>In the version that works however, it produces (correctly) a unique id and quiz id which is stored:</p>
  
  <p><a href="https://i.stack.imgur.com/JydZF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JydZF.png" alt="enter image description here"></a>
  <strong>Not working</strong> Cardinality shown below:(this table has a cardinality of 2 and a unique quiz id is not generated when the code is run)</p>
  
  <pre><code>Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
  Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  2   A   No
  </code></pre>
  
  <p><strong>This however, works:</strong></p>
  
  <pre><code>Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
  Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  0   A   No  
  </code></pre>
  
  <p>MySQL generates cardinality automatically, so I assume I cannot change it? IF this is the problem, what can be done?</p>
  
  <p>Else, if the code is the problem, can someone point out what the issue is:</p>
  
  <p><strong>Code for Add New Quiz</strong></p>
  
  <pre><code>&lt;?php
  
      include('scripts/connect_db.php');
  
          if(isset($_POST['quizName']) &amp;&amp; $_POST['quizName'] != ""
          &amp;&amp; isset($_POST['quizTime']) &amp;&amp; $_POST['quizTime'] != ""
          &amp;&amp; isset($_POST['numQues']) &amp;&amp; $_POST['numQues'] != ""){
  
              $qName=mysqli_real_escape_string($con,$_POST['quizName']);
              $qTime=mysqli_real_escape_string($con,$_POST['quizTime']);
              $nQues=mysqli_real_escape_string($con,$_POST['numQues']);
  
              $qTime = preg_replace('/[^0-9]/', "", $qTime);
              $nQues = preg_replace('/[^0-9]/', "", $nQues);
  
              $fetch=mysqli_query($con,"SELECT id FROM quizes 
                                  WHERE quiz_name='$qName'")or die(mysqli_error());
              $count=mysqli_num_rows($fetch);
              if($count!="")
              {
                  $user_msg = 'Sorry, but \ '.$qName.' \ already exists!';
                  header('location: admin.php?msg='.$user_msg.'');
              }else{
                  mysqli_query($con,"INSERT INTO quizes (quiz_name, display_questions, time_allotted) 
                      VALUES ('$qName','$nQues','$qTime')")or die(mysqli_error());
  
                  $lastId = mysqli_insert_id();
                  mysqli_query($con,"UPDATE quizes SET quiz_id='$lastId' 
                                  WHERE id='$lastId' LIMIT 1")or die(mysqli_error());
  
                  $user_msg = 'Quiz, \ '.$qName.' \ has been created!';
                  header('location: admin.php?msg='.$user_msg.'');
              }
          }else{
              $user_msg = 'Sorry, but Something went wrong';
              header('location: admin.php?msg='.$user_msg.'');
          }
  ?&gt;
  </code></pre>
  
  <p>To summarise, the main problem is that when the code is run to add a new quiz, a unique quiz id is not being generated and/or not being added to the database, so the quiz id is always 0. </p>
  
  <p>In my version of the program that works, the quiz id is generated and added correctly, but that version included defunct MySQL functions. </p>
  
  <p><strong>Previous original Code working correctly (but using defunct MySQL):</strong></p>
  
  <pre><code>&lt;?php
  
      include('scripts/connect_db.php');
  
          if(isset($_POST['quizName']) &amp;&amp; $_POST['quizName'] != ""
          &amp;&amp; isset($_POST['quizTime']) &amp;&amp; $_POST['quizTime'] != ""
          &amp;&amp; isset($_POST['numQues']) &amp;&amp; $_POST['numQues'] != ""){
  
              $qName=mysql_real_escape_string($_POST['quizName']);
              $qTime=mysql_real_escape_string($_POST['quizTime']);
              $nQues=mysql_real_escape_string($_POST['numQues']);
  
              $qTime = preg_replace('/[^0-9]/', "", $qTime);
              $nQues = preg_replace('/[^0-9]/', "", $nQues);
  
              $fetch=mysql_query("SELECT id FROM quizes 
                                  WHERE quiz_name='$qName'")or die(mysql_error());
              $count=mysql_num_rows($fetch);
              if($count!="")
              {
                  $user_msg = 'Sorry, but \ '.$qName.' \ already exists!';
                  header('location: admin.php?msg='.$user_msg.'');
              }else{
                  mysql_query("INSERT INTO quizes (quiz_name, display_questions, time_allotted) 
                      VALUES ('$qName','$nQues','$qTime')")or die(mysql_error());
  
                  $lastId = mysql_insert_id();
                  mysql_query("UPDATE quizes SET quiz_id='$lastId' 
                                  WHERE id='$lastId' LIMIT 1")or die(mysql_error());
  
                  $user_msg = 'Quiz, \ '.$qName.' \ has been created!';
                  header('location: admin.php?msg='.$user_msg.'');
              }
          }else{
              $user_msg = 'Sorry, but Something went wrong';
              header('location: admin.php?msg='.$user_msg.'');
          }
  ?&gt;
  </code></pre>
  
  <p>I cannot spot the error, or tell if the error is in the php code, or in the database setup.</p>
  
  <p>Thoughts? Solutions? Thank you.</p>