Need help in inserting data to two tables

[wayz1229]

I'm having problem with the script below.
The atitle getting the qid from the last question inserted in the table. I need to have atitle with the corresponding qid.

for example:
qtitle (qid=1)
- atitle (qid=1)
- atitle (qid=1)

qtitle (qid=2)
- atitle (qid=2)
- atitle (qid=2)

can anyone please help.. thanks in advance.

<?php
	require_once('auth.php');
	require_once('config.php');
	
	$date=date("F j, Y, g:i a");
	$stitle = $_POST['stitle'];
	$stitle = ucwords($stitle);
	$name = $_SESSION['SESS_NAME'];
	$member_id = $_SESSION['SESS_MEMBER_ID'];

    
$query = "INSERT INTO survey (stitle, sdate, name, member_id) VALUES ('$stitle', '$date', '$name', '$member_id')";
$result = mysql_query($query) or die("ERROR: $query.".mysql_error());

    $sid = mysql_insert_id();

    // reset variables
    unset($query);
    unset ($result);
	
    foreach ($_POST['questions'] as $q) {
        if (trim($q) != '') {
            $qtitles[] = $q;
        }
    }

foreach ($qtitles as $qtitle) {
 $query = "INSERT INTO questions (sid, qtitle) VALUES ('$sid', '$qtitle')";
   $result = mysql_query($query) or die("ERROR: $query. ".mysql_error());
    }
	
    $qid = mysql_insert_id();

    // reset variables
    unset($query);
    unset ($result);
	
     foreach ($_POST['options'] as $o) {
        if (trim($o) != '') {
            $atitles[] = $o;
        }
    }

foreach ($atitles as $atitle) {
        $query = "INSERT INTO answers (qid, sid, atitle, acount) VALUES ('$qid', '$sid', '$atitle', '0')";
        $result = mysql_query($query) or die("ERROR: $query. ".mysql_error());
    }

    //Check whether the query was successful or not
	if($result) {
		header("location: surveypreview.php");
		exit();
	}else {
		echo '<script>alert("Query Failed. Please try again !!!");</script>';
		echo '<script>history.back(1);</script>';
	}

    mysql_close();
?>

[samarudge]

Not 100% sure what you are trying to do but mysql(i)_insert_id() can get the primary key from the last insert statement
http://php.net/mysql_insert_id
http://php.net/mysqli_insert_id (If using MYSQLI link)

[samarudge]

Not 100% sure what you are trying to do but mysql(i)_insert_id() can get the primary key from the last insert statement
http://php.net/mysql_insert_id
http://php.net/mysqli_insert_id (If using MYSQLI link)

[network18]

no its right, mysql_insert_id() is the function if you looking for the id of last insert.
Is that you looking?

[wayz1229]

the above script insert the data to database. let say i hav 2 qtitle, and each qtitle has 2 atitle..
so in table1, there are total of 2 qtitle
and in table2, there are total of 4 atitle.

but the problem with the script is that, all the atitle in table2 is having the qid of last inserted qtitle.. thats the error in above script.. atitle should have the qid based on the qtitle..

[Graphix]

Why would you use 2 tables for the exactly same purpose? Why don't you just make the following two table:

table "surveyresults":
qa_id INT(9) AUTO_INCREMENT NOT NULL,
question CHAR(255),
answer LONGTEXT,
sid INT(9) NOT NULL,

And use the following code:

<?php
	require_once('auth.php');
	require_once('config.php');
	//
        // Retrieving which survey it is and who filled it in
        //
	$date=date("F j, Y, g:i a");
	$stitle = $_POST['stitle'];
	$stitle = ucwords($stitle);
	$name = $_SESSION['SESS_NAME'];
	$member_id = $_SESSION['SESS_MEMBER_ID'];

//
// Saving in the database who filled in the survey, what survey and when
//

$query = "INSERT INTO survey (stitle, sdate, name, member_id) VALUES ('$stitle', '$date', '$name', '$member_id')";
$result = mysql_query($query) or die("ERROR: $query.".mysql_error());

    $sid = mysql_insert_id();

    // reset variables
    unset($query);
    unset ($result);
	
// 
// Retrieving the answers
//
foreach ($_POST['options'] as $o) {
        if (trim($o) != '') {
            $answers[] = $o;
        }
}
//
// Retrieving the questions
//
    foreach ($_POST['questions'] as $q) {
        if (trim($q) != '') {
            $questions[] = $q;
        }
}

//
// Counting the amount of questions and answers
//
$question_amount = count($questions);
$answer_amount = count($answers);

//
// Inserting the answers and questions
//

for ($i=0; $i < $question_amount; $i++) {
 $answer = $answers[$i];
 $question = $questions[$i];
 $query = "INSERT INTO surveyresults (sid, question, answer) VALUES ('$sid', '$question','$answer')";
   $result = mysql_query($query) or die("ERROR: $query. ".mysql_error());
    }

    //Check whether the query was successful or not
	if($result) {
		header("location: surveypreview.php");
		exit();
	}else {
		echo '<script>alert("Query Failed. Please try again !!!");</script>';
		echo '<script>history.back(1);</script>';
	}

    mysql_close();
?>