dsux90368
dsux90368
2018-01-15 11:40

测验表字段的索引基数是'2',但它应该是'0'

已采纳

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 & 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.

Is this causing the lack of generation of a unique quizid, or has it got something to do with the code?

For example, I create a quiz called testquiz, it should generate an id of 1,2,3 etc.

Instead it produces this:

enter image description here

In the version that works however, it produces (correctly) a unique id and quiz id which is stored:

enter image description here Not working Cardinality shown below:(this table has a cardinality of 2 and a unique quiz id is not generated when the code is run)

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  2   A   No

This however, works:

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  0   A   No  

MySQL generates cardinality automatically, so I assume I cannot change it? IF this is the problem, what can be done?

Else, if the code is the problem, can someone point out what the issue is:

Code for Add New Quiz

<?php

    include('scripts/connect_db.php');

        if(isset($_POST['quizName']) && $_POST['quizName'] != ""
        && isset($_POST['quizTime']) && $_POST['quizTime'] != ""
        && isset($_POST['numQues']) && $_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.'');
        }
?>

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.

In my version of the program that works, the quiz id is generated and added correctly, but that version included defunct MySQL functions.

Previous original Code working correctly (but using defunct MySQL):

<?php

    include('scripts/connect_db.php');

        if(isset($_POST['quizName']) && $_POST['quizName'] != ""
        && isset($_POST['quizTime']) && $_POST['quizTime'] != ""
        && isset($_POST['numQues']) && $_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.'');
        }
?>

I cannot spot the error, or tell if the error is in the php code, or in the database setup.

Thoughts? Solutions? Thank you.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongwen1871 dongwen1871 3年前

    Found the answer. The code had an error, and a missing parameter:

    $lastId = mysqli_insert_id($con);
    

    Using the above, fixed it.

    点赞 评论 复制链接分享
  • douzhaxian1267 douzhaxian1267 3年前

    You have to active "Auto-increment" option When you activate it, will generate a uniqueId everytime you insert a new row.

    点赞 评论 复制链接分享