DaniWeb Forum Index > Web Development > PHP

Need help in inserting data to two tables

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2009

Posts: 11

Reputation: wayz1229 is an unknown quantity at this point

Solved Threads: 0

wayz1229

Offline

Newbie Poster

Need help in inserting data to two tables

Oct 22nd, 2009

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.

 Help with Code Tags 
 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();
?>

•

Join Date: May 2008

Posts: 254

Reputation: samarudge is an unknown quantity at this point

Solved Threads: 20

samarudge

Offline

Posting Whiz in Training

Oct 22nd, 2009

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)

My Blog, Life and everything that matters to me - SamRudge.co.uk

2x Macbook Pro's, 1x Mac Pro, 1x iMac, 2x Macbook's running Fedora linux - In conclusion, I hate windows =)

•

Join Date: May 2008

Posts: 254

Reputation: samarudge is an unknown quantity at this point

Solved Threads: 20

samarudge

Offline

Posting Whiz in Training

Oct 22nd, 2009

My Blog, Life and everything that matters to me - SamRudge.co.uk

2x Macbook Pro's, 1x Mac Pro, 1x iMac, 2x Macbook's running Fedora linux - In conclusion, I hate windows =)

•

Join Date: Sep 2009

Posts: 551

Reputation: network18 is an unknown quantity at this point

Solved Threads: 64

network18

Offline

Posting Pro

Oct 22nd, 2009

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

•

Join Date: Oct 2009

Posts: 11

Reputation: wayz1229 is an unknown quantity at this point

Solved Threads: 0

wayz1229

Offline

Newbie Poster

Oct 25th, 2009

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

•

Join Date: Aug 2009

Posts: 81

Reputation: Graphix is an unknown quantity at this point

Solved Threads: 18

Graphix

Offline

Junior Poster in Training

Oct 28th, 2009

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:

 Help with Code Tags 
 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();
?>