INSERT MySQL query not executing properly using PHP

Question

I'm currently making a graduation website for my school where grads are supposed to submit a comment as well as submit votes for a poll.

The database has 4 tables, one for the student information, one for questions, one for comments which has a foreign key referencing snum from students and one for the poll(called survey) which has 2 foreign keys, one referencing snum again and one referencing the question id.

This is the code my seniors left for me. What it's supposed to do is create blank rows in the comments and survey tables to be updated later. However what it actually does is send everything in the comment table twice (so if there were 300 students, I would end up with 600 rows in the comments table and 0 in the survey table)

I'm still quite new to MySQL and PHP and only learned it about a month ago. If anyone can help or suggest a better way of approaching this, it would be much appreciated.

$sql_query = "SELECT snum FROM students;";
$result = mysqli_query($link,$sql_query);
while ($list = mysqli_fetch_array($result)) {
    $snum[] = $list['snum'];
}

$sql_query = "SELECT qid FROM questions WHERE want = 1;";
$result = mysqli_query($link,$sql_query);
while ($question_list = mysqli_fetch_array($result)) {
    $qid[] = $list['qid'];
}

for ($i = 0; $i < count($snum); $i++)
{
    $sql_query = "INSERT INTO comments (snum, comment) VALUES ('{$snum[$i]}' , NULL);";
    $result = mysqli_query($link,$sql_query);
    for ($a = 0; $a < count($qid); $a++) {
        $sql_query = "INSERT INTO survey (snum, qid, male, female) VALUES ('{$snum[$i]}', {$qid[$a]}, NULL, NULL);";
        $result = mysqli_query($link,$sql_query);
    }
}

UPDATE 1:I think I found what the problem is. When I try to output $qid[$a], I get a null value. In the table, qid is a smallint unsigned, not null, auto_increment and is the primary key.

Instead of For loop, try with Foreach loop. – Naveed Ramzan Jan 01 '16 at 08:16 — Naveed Ramzan, Jan 01 '16 at 08:16
Didn't make a difference, but thank you for the suggestion – MrMiaoMiao Jan 01 '16 at 18:04 — MrMiaoMiao, Jan 01 '16 at 18:04

score 0 · Answer 1 · answered Jan 01 '16 at 10:56

0

Try this code

<?php
    $sql_query = "SELECT snum FROM students";
    $result = mysql_query($link,$sql_query);
    while ($list = mysql_fetch_array($result)) {
        $snum[] = $list['snum'];
    }

    $sql_query = "SELECT qid FROM questions WHERE want = '1' ";
    $result = mysql_query($link,$sql_query);
    while ($question_list = mysql_fetch_array($result)) {
        $qid[] = $list['qid'];
    }



    for ($i = 0; $i < count($snum); $i++){

        $sql_query = "INSERT INTO comments (snum, comment) VALUES ('".$snum[$i]."' , '');";
        $result = mysql_query($link,$sql_query);

        for ($a = 0; $a < count($qid); $a++) {
            $sql_query = "INSERT INTO survey (snum, qid, male, female) VALUES ('".$snum[$i]."','".$qid[$a]."', '', '');";
            $result = mysql_query($link,$sql_query);
        }

    }
 ?>

answered Jan 01 '16 at 10:56

Divyesh

329
3
17

Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Jan 01 '16 at 13:15
1

**Please [stop suggesting `mysql_*` functions**](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jan 01 '16 at 13:15

score 0 · Answer 2 · answered Jan 02 '16 at 00:24

This was solved by using mysqli_free_result($result) between the two select queries

$sql_query = "SELECT snum FROM students;";
$result = mysqli_query($link,$sql_query);
while ($list = mysqli_fetch_assoc($result))
{
   $snum[] = $list['snum']; 
}

mysqli_free_result($result);

$sql_query = "SELECT qid FROM questions WHERE want = 1;";
$result = mysqli_query($link,$sql_query);
while ($question_list = mysqli_fetch_assoc($result))
{
    $qid[] = $list['qid'];
}

INSERT MySQL query not executing properly using PHP

2 Answers2