Mysql_Insert_Id Not Generating An Id

[dinita]

I've been trying to generate a database for a quiz game, where players can submit their questions to a database and send their friends the quiz that they have created. I have three problems i hope you can help:

 

1. I'm having trouble storing the id generated from my last query into a table after the page has changed using the header function, it always returns a 0.

2. I am also having trouble with the final page echoing both an error message about submission and a success message at the same time.

3. I also have a problem with the header link not being able to modify, although it does take you to that page.

 

 

When the final submit button is pressed I get this:

 

Please complete form before submission

Warning: Cannot modify header information - headers already sent by (output started at /Applications/MAMP/htdocs/Quiz/go.php:33) in /Applications/MAMP/htdocs/Quiz/go.php on line 52

 

Notice: Undefined variable: quizid in /Applications/MAMP/htdocs/Quiz/go.php on line 73

Quiz submitted sucessfully

 

my code is like this:

<?php
//error reporting
error_reporting(E_ALL);
//connect to mysql
$con = mysql_connect("localhost","dinita","*****");
if(!$con)
{
die('Could not connect: '.mysql_error());
}
else{
// SELECT DATABASE
mysql_select_db("quizCreation", $con);
//Which form is it?
if(isset($_POST['title']) &&!empty($_POST['title']))
{

//accept POST data
$name = $_POST['name'];
$desc =$_POST['desc'];
}

//check if POST data is complete (all required fields are filled in)
//if POST data is not complete return an error
if(empty($name))
{
echo "Please complete form before submission";
}
else
{
//insert quiz title
$sql=" INSERT INTO quizName (Name, Description)
VALUES ('$name','$desc')";

mysql_query($sql, $con);

//get ID for quiz title
$quizid = mysql_insert_id();
if (empty($quizid)){

die("No Quiz id");
}
}

header("Location: http://localhost:8888/Quiz/quiz.creator.html");
}


if (isset($_POST['creator'])&&!empty($_POST['creator']))
{
//accept POST data
$question = $_POST['question'];

}
//check if POST data is complete (all required fields are filled in)
//if POST data is not complete return an error
if(empty($question) && empty($ans1) && empty($ans2) && empty($ans3) && empty($ans4))
{
echo "Please complete form before submission";
}
else
{
//insert question
$sql= "INSERT INTO questions (text, quiz_ID)
VALUES	 ('$question', '$quizid')";
mysql_query($sql, $con);
//get id of inserted question
$id = mysql_insert_id();

//insert answers + question id
for($i=1; $i<=4; $i++)
{
$correct = 0;

if($_POST['radio'] == "radio".$i)
{
$correct = 1;
}

$answer = $_POST['answer'.$i];

$sql=" INSERT INTO answers (question_ID, answer, correct)
VALUES ('$id','$answer','$correct')";

mysql_query($sql, $con);
}

}

//show message to say insert has completed successfully
echo "Quiz submitted sucessfully";
//close connection to mysql
mysql_close($con);

 

Thanks in advance for any help you can offer.

Edited October 4, 2012 by dinita

[avshelestov]

Do you insert primary key in your table?

[dinita]

I have three tables each with a primary key

 

like this:

 

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 04, 2012 at 12:37 PM
-- Server version: 5.5.25
-- PHP Version: 5.4.4
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `quizCreation`
--
-- --------------------------------------------------------
--
-- Table structure for table `answers`
--
CREATE TABLE `answers` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `ANSWER` varchar(80) NOT NULL,
 `CORRECT` tinyint(4) NOT NULL DEFAULT '0',
 `question_ID` int(11) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `questions`
--
CREATE TABLE `questions` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `text` varchar(80) NOT NULL,
 `quiz_ID` int(11) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `quizName`
--
CREATE TABLE `quizName` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` varchar(45) NOT NULL,
 `Description` varchar(45) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[Barand]

1. I'm having trouble storing the id generated from my last query into a table after the page has changed using the header function, it always returns a 0.

 

When the page changes you get a new connection.

 

mysql_insert_id() is valid only for the current connection

[avshelestov]

Use var_dump().

I think variable $_POST['title'] don't exist.

[dinita]

When the page changes you get a new connection.

 

mysql_insert_id() is valid only for the current connection

 

is there a way to store and id once you get a new connection? or even to change the page without starting a new connection?

 

Use var_dump().

I think variable $_POST['title'] don't exist.

 

 

$_POST['title'] is the name of a hidden form field in my html, i'm using it as an ID for the different forms.

[Barand]

Use a session variable

 

// insert query goes here

$_SESSION['quizid'] = mysql_insert_id();

[dinita]

thanks Barand that definately helped fix one of my problems I now only have two problems:

 

2. I am also having trouble with the final page echoing both an error message about submission and a success message at the same time.

 

I think the problem lies somewhere in this bit of the code:

if (isset($_POST['creator'])&&!empty($_POST['creator']))
{
//accept POST data
$question = $_POST['question'];
$quizid  =$_SESSION['quizid'];
}
//check if POST data is complete (all required fields are filled in)
//if POST data is not complete return an error
if(empty($question) && empty($ans1) && empty($ans2) && empty($ans3) && empty($ans4))
{
 echo "Please complete form before submission";
}
else
{
 //insert question
 $sql= "INSERT INTO questions (text, quiz_ID)
 VALUES	  ('$question', '$quizid')";
 mysql_query($sql, $con);
 //get id of inserted question
 $id = mysql_insert_id();

 //insert answers + question id
 for($i=1; $i<=4; $i++)
 {
 $correct = 0;

 if($_POST['radio'] == "radio".$i)
 {
 $correct = 1;
 }

 $answer = $_POST['answer'.$i];

  $sql=" INSERT INTO answers (question_ID, answer, correct)
   VALUES ('$id','$answer','$correct')";

   mysql_query($sql, $con);
 }

}

//show message to say insert has completed successfully
echo "Quiz submitted sucessfully";
//close connection to mysql
mysql_close($con);

 

3. I also have a problem with the header link not being able to modify,

 

i get the warning:

Cannot modify header information - headers already sent

[Barand]

The success message needs to be part of the else {...}

[Christian F.]

The header error is explained in the thread "HEADER ERRORS - READ HERE BEFORE POSTING THEM ", which is stickied at the top of this section.

 

As for why it's printing both messages, is because that's what you're asking the script to do. You seem to have indented the script properly, but hard to tell for sure since this forum is fond of messing with the indentation if you paste it "incorrectly".

However, if your code does indeed look like the above, then you really should be intending it properly. If you do that, and follow the flow of the logic while paying attention to the blocks, then you should be able to see the cause of the problem quite easily.

 

As a bonus: If this code is in an included file of its own, and the above is all of the code in the file, you can use return to your advantage.

 

PS: There is no need to manually close the database connection, PHP handles that for you quite nicely on its own.

[dinita]

Thanks you all for all your help!

 

In the end I created a few functions to fix the problems i encountered-

 

The first one inserts data into a database and returns the id like this:

function insert_data($table_name, $data)
{
$id = false;

if(!is_array($data))
{
die('You must supply an array of data to be inserted');
}
$con = mysql_connect("localhost", "dinita", "*********");

if(!$con)
{
die('could not connect: '.mysql_error());
}
else
{
$sql = "INSERT INTO ".$table_name." (".implode(',', array_keys($data)).") VALUES ('".implode("','", $data)."')";
echo $sql;
mysql_select_db("quizCreation", $con);
mysql_query($sql, $con);

$id = mysql_insert_id();

mysql_close($con);
}

return $id;
}

 

and another to redirect even when headers are sent using:

function redirect_page($url = 'http://localhost:8888/Quiz/quiz.creator.html')
{
if(!headers_sent())
{
header("Location: ".$url);
exit;
}
else	
{
echo '<script type="text/javascript">';
echo 'window.location.href="'.$url.'";';
echo '</script>';
echo '<noscript>';
echo '<meta http-equiv="refresh" content="0;url='.$url.'" />';
echo '</noscript>';
exit;
}
}

Edited October 9, 2012 by dinita

[PFMaBiSmAd]

You shouldn't be opening a mysql connection, executing one query, then closing the mysql connection inside your function.

 

Contacting the mysql server and making a connection is a relatively time consuming process. You should make a connection near the start of any script that needs it, use that connection for the duration of your script's execution, then either let php close the connection when the script ends or close it yourself in you have a reason to do so before your script ends.