mohammed mohammed - 4 months ago 7
PHP Question

avoid duplicate data in php mysql in update?

i have this database which contains tables one for questions and the other for answers and i need to use php to update the question and answers but when i do query to select the question and answers i get four questions and four answers or one question and one answer here's my tables structure

------------------------------------------------------- ---------------------
| question | | answers |
------------------------------------------------------- ---------------------

PHP server scripts are surrounded by delimiters, which?| | <&>...</&>
| | <?php>...</?>
| | <script>..</script>
| | <?php...?>


here's my complete code

<?php include ("connection.php"); ?>
<?php
$msg = "";
if (isset($_POST['update'])) {
if (is_numeric($_POST['question_id'])) {

$question_id = mysqli_real_escape_string($link, htmlspecialchars($_POST['question_id']));
$question_text = mysqli_real_escape_string($link, htmlspecialchars($_POST['question_text']));
$test_id = mysqli_real_escape_string($link, htmlspecialchars($_POST['test_id']));

if ($question_text == "" || $test_id == "" ) {
$msg = "يرجى تعبيئة كافة الحقول";
} else {
$sql = mysqli_query($link, " UPDATE `question` SET `question_text`='".$question_text."',`test_id`= '".$test_id."'
WHERE `question_id` = '".$question_id."' ")or die(mysqli_error($link));

/* UPDATE `question` SET `question_text`='".."',`test_id`= '".."' WHERE `question_id` = */
}
} else {
echo "Error !";
}
}else {
if (isset($_GET['question_id']) && is_numeric($_GET['question_id']) && $_GET['question_id'] > 0) {

$question_id = $_GET['question_id'];

$query = mysqli_query($link, "SELECT * FROM `question` WHERE `question_id` = '".$question_id."' ")
or die(mysqli_error($link));
while ($row = mysqli_fetch_assoc($query)) {
$question_id = $row ['question_id'];
$question_text = $row ['question_text'];
$test_id = $row['test_id'];


$query2 = mysqli_query($link,"SELECT * FROM `answers` WHERE answers.question_id = '".$question_id."' ") or die(mysqli_error($link));
while($row2 = mysqli_fetch_assoc($query2)){
$answer_text = $row2 ['answer_text'];
$correct = $row2 ['correct'];

}




/*
SELECT question.question_id,question.question_text,question.test_id,test.test_name
FROM question,test
WHERE question.test_id = test.test_id
AND question.question_id= '".$question_id."'

*/
/* SELECT `answer_text`, `correct` FROM `answers` WHERE answers.question_id = 6 AND answers.correct = 1 */
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" dir="RTL" lang="Ar">

<head>


<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

<link href="css/style.css" media="screen" rel="stylesheet" type="text/css" />

<meta name="viewport" content="width=device-width, initial-scale=1.0">


<meta http-equiv="refresh" content="30">



</head>

<body>

<form method= "POST" action = "" >



<p>
<label><p>الحقل المخصص للسؤال</p></label>
<textarea name = "question_text"><?php echo $question_text ; ?> </textarea>

</p>

<p>
<label>رقم الاختبار</label>
<td><input type="text" name="test_id" value="<?php echo $test_id ; ?>" /></td>
</p>

<p>
<label>الخيار الاول</label>
<input type="text" name="" value="<?php echo $answer_text ; ?>" />
</p>
<p>
<label>الخيار الثانى</label>
<td><input type="text" name="" value="<?php echo $answer_text ; ?>" /></td>
</p>
<p>
<label>الخيار الثالث</label>
<td><input type="text" name="" value="<?php echo $answer_text ; ?>" /></td>
</p>
<p>
<label>الخيار الرابع</label>
<td><input type="text" name="" value="<?php echo $answer_text ; ?>" /></td>
</p>


<p>

<input type="submit" name="update" value="تحديث البيانات" class="button save" />
</p>

<p>

<input type="hidden" name = "question_id" value="<?php echo $_GET['question_id']; ?>" />
</p>


</form>
</body>
</html>
<?php
}
}
}


?>

Answer

Well the problem is in your while cycles. The code is not very clean, but you are printing for every question 4 times the same answer (last found in your mysql database) and not all the answers. Can you try this, I am not sure if there is not a typo because I can not run the code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" dir="RTL" lang="Ar">

    <head>


        <title></title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

        <link href="css/style.css" media="screen" rel="stylesheet" type="text/css" />

        <meta name="viewport" content="width=device-width, initial-scale=1.0">


        <meta http-equiv="refresh" content="30">



</head>

<body>

    <form method= "POST" action = "" >

<?php include ("connection.php"); ?>
<?php
$msg = "";
if (isset($_POST['update'])) {
    if (is_numeric($_POST['question_id'])) {

        $question_id = mysqli_real_escape_string($link, htmlspecialchars($_POST['question_id']));
        $question_text = mysqli_real_escape_string($link, htmlspecialchars($_POST['question_text']));
        $test_id = mysqli_real_escape_string($link, htmlspecialchars($_POST['test_id']));

        if ($question_text == "" || $test_id == "" ) {
            $msg = "يرجى تعبيئة كافة الحقول";
        } else {
            $sql = mysqli_query($link, " UPDATE `question` SET `question_text`='".$question_text."',`test_id`= '".$test_id."' 
            WHERE  `question_id` = '".$question_id."'  ")or die(mysqli_error($link));

          /*   UPDATE `question` SET `question_text`='".."',`test_id`= '".."' WHERE  `question_id` =          */ 
        }
    } else {
        echo "Error !";
    }
}else {
    if (isset($_GET['question_id']) && is_numeric($_GET['question_id']) && $_GET['question_id'] > 0) {

        $question_id = $_GET['question_id'];

        $query = mysqli_query($link, "SELECT * FROM `question` WHERE `question_id` = '".$question_id."' ")
                or die(mysqli_error($link));
        $isFirst = TRUE;
        while ($row = mysqli_fetch_assoc($query)) {
            $question_id = $row ['question_id']; 
            $question_text = $row ['question_text'];
            $test_id = $row['test_id'];
            if ($isFirst) {
?>
            <p>
                <label>رقم الاختبار</label>
                <td><input type="text" name="test_id" value="<?php echo  $test_id ; ?>" /></td>
            </p>
            <?php
                $isFirst = FALSE;
            }
            ?>
            <p>
                <label><p>الحقل المخصص للسؤال</p></label>
               <textarea name = "question_text"><?php echo $question_text ; ?> </textarea>

            </p>
<?php
            $query2 = mysqli_query($link,"SELECT * FROM `answers` WHERE answers.question_id = '".$question_id."' ")    or die(mysqli_error($link));
            while($row2 = mysqli_fetch_assoc($query2)){
                $answer_text = $row2 ['answer_text'];   
                $correct = $row2 ['correct'];



/*
SELECT question.question_id,question.question_text,question.test_id,test.test_name
FROM question,test
WHERE question.test_id = test.test_id
AND question.question_id= '".$question_id."'

*/
/*  SELECT  `answer_text`, `correct` FROM `answers` WHERE answers.question_id = 6 AND answers.correct = 1 */
?>
            <p>
                <label>الخيار الاول</label>
          <input type="text" name="" value="<?php echo  $answer_text ; ?>" />
            </p>
<?php
            }
            ?>
            <p>
                <input type="submit" name="update" value="تحديث البيانات"  class="button save" />
            </p>

            <p>
                <input type="hidden" name = "question_id" value="<?php echo $_GET['question_id']; ?>" />
            </p>
            <?php
        }
    }
}
?>


    </form>
</body>
</html>