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:
In the version that works however, it produces (correctly) a unique id and quiz id which is stored:
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.
