Ad:

PHP Discussion Thread
Unsolved
Views: 451

Oct 22nd, 2009

Need help in inserting data to two tables

Expand Post »

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 Syntax (Toggle Plain Text)
<?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();
?>
<?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();
?>

Similar Threads

Insert data into two tables using php and mysql in MySQL
The operation has timed out error while inserting data through web service in IT Professionals' Lounge
Join Two data tables in C#
Copy data from 2 tables in 2 databases in MS SQL
Adding dynamic rows and inserting the data in database in JSP

wayz1229

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Oct 2009

Permalink

Oct 22nd, 2009

Re: Need help in inserting data to two tables

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

Reputation Points: 26

Solved Threads: 31

Posting Whiz

Offline

354 posts
since May 2008

Permalink

Oct 22nd, 2009

Re: Need help in inserting data to two tables

samarudge

Reputation Points: 26

Solved Threads: 31

Posting Whiz

Offline

354 posts
since May 2008

Permalink

Oct 22nd, 2009

Re: Need help in inserting data to two tables

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

network18

Reputation Points: 29

Solved Threads: 76

Practically a Master Poster

Offline

616 posts
since Sep 2009

Permalink

Oct 25th, 2009

Re: Need help in inserting data to two tables

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

wayz1229

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Oct 2009

Permalink

Oct 28th, 2009

Re: Need help in inserting data to two tables

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 Syntax (Toggle Plain Text)
<?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();
?>
<?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();
?>

Graphix

Reputation Points: 82

Solved Threads: 74

Posting Pro in Training

Offline

401 posts
since Aug 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Open Source PHP Financial Modules

Next Thread in PHP Forum Timeline: My head is going to explode if I can't fix this. PLEASE HELP

DaniWeb Message

Cancel Changes

User Name
Password