Jump to content

Populating My Database With Information From A Html Form

dinita

By dinita
in MySQL Help

 Share

Recommended Posts

dinita Member

dinita

Posted
Posted (edited)

Hello

 

I'm new to PHP and mySQL, I am trying to populate a database created using MySQL Workbench using information submitted via a form in my website. I have followed as many tutorials as i can and there appears to be an error in the php file, but i'm not sure what it is.

 

my form is coded like this:

 

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<form id="quizCreator" name="quizCreator" method="post" action="insert.php">
<p>
<label>Quiz Title </label>
<br />
<input type="text" name="quizTitle" id="quizTitle" />
<p>
<label>Describe your Quiz<br />
 <textarea name="desc" id="desc" cols="45" rows="5"></textarea>
 <br />
</label>
<p>
<label>Question
 <br />
 <input type="text" name="question" id="question" />
</label>
<p>
<label>Answers
 <br />
 1.
 <input type="text" name="answer1" id="answer1" />
</label>
<label>
 <input type="radio" name="radio" id="radio1" value="1" />
</label>
<p>2.
<label>
 <input type="text" name="answer2" id="answer2" />
</label>
<label>
 <input type="radio" name="radio" id="radio3" value="1" />
</label>
<p>3.
<label>
 <input type="text" name="answer3" id="answer3" />
</label>
<label>
 <input type="radio" name="radio" id="radio3" value="1" />
</label>
<p>4.
<label>
 <input type="text" name="answer4" id="answer4" />
</label>
<label>
 <input name="radio" type="radio" id="radio4" value="1" />
</label>
<br />		
<p>
<input type="Submit" name="nextPage" id="nextPage" value="Submit" />
</form>
</body>
</html>

 

and my php file is like this:

<?php
// CONNECT TO MYSQL SERVER
$con = mysql_connect("localhost","dinita","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// SELECT DATABASE
mysql_select_db("quizCreator", $con);

// CLEAN THE POST VALUES FROM THE FORM
$title = clean($_POST['quizTitle']);
$desc = clean($_POST['desc']);
$question = $_POST['question'];
$ans1 = clean($_POST['answer1']);
$ans2 = clean($_POST['answer2']);
$ans3 = clean($_POST['answer3']);
$ans4 = clean($_POST['answer4']);
$correct = clean($_POST['radio']);

// CREATE SQL QUERY
$sql="INSERT INTO quiz (quiz_name, quiz_desc)
VALUES
('$title','$desc')";
$sql="INSERT INTO question (question_body)
VALUES
('$question')";
$sql="INSERT INTO answer1 (answer)
VALUES
('$ans1')";
$sql="INSERT INTO answer2 (answer)
VALUES
('$ans2')";
$sql="INSERT INTO answer3 (answer)
VALUES
('$ans3')";
$sql="INSERT INTO answer4 (answer)
VALUES
('$ans4')";
$sql="INSERT INTO correct (correct_ans)
VALUES
('$correct')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Quiz submitted sucessfully";
mysql_close($con);
?>

 

I'm sorry if I havent explained my self well, Thanks for any help you can offer

 

Dinita

Edited by dinita
Jessica Advanced Member

Jessica

Posted
Posted

What is the error?

 

Your data structure is WAY off. You should not have 4 different answer tables. You also need to store the foreign keys like the quiz_id, question_id, etc in the answers and questions.

Stefany93 Regular Member

Stefany93

Posted
Posted

Create a new table named answers and create 4 different columns like answer1, answer, answer3, answer4, as much as you need.

 

Then make your query like this:

 

mysql_query("INSERT INTO table_name(answer1,answer2,answer3,answer4) VALUES('value1','value2','value3','value4')");

Only make new tables when there is no way around it, because MySQL queries tend to be a lot faster when they are inserting or fetching data only from a single table.

 

Also make sure to escape your variables with mysql_real_escape_string function before sending them into the database.

DavidAM Member

DavidAM

Posted
Posted

Create a new table named answers and create 4 different columns like answer1, answer, answer3, answer4, as much as you need.

 

NO!!! You should (almost) NEVER have multiple columns in the same table that carry the same data. The best-practice for this would be 2 tables:

 

-- Questions Table
ID  UNSIGNED INTEGER  AUTO_INCREMENT  PRIMARY KEY
Question  VARCHAR(###)

-- Answers Table
ID  UNSIGNED INTEGER AUTO_INCREMENT PRIMARY KEY
QuestionID UNSIGNED INTEGER  FOREIGN KEY TO Questions TABLE
Answer  VARCHAR(###)
IsCorrect BOOLEAN DEFAULT 0

 

If you want to control the sequence, you can add a sequence number to either or both tables.

 

Depending on the total number of questions and total number of answers, I might use SMALLINT rather than INTEGER, but it makes little difference.

 

Only make new tables when there is no way around it, because MySQL queries tend to be a lot faster when they are inserting or fetching data only from a single table.

 

Relational databases are optimized for handling multiple-table relationships. As long as you design your tables well, and build your queries well, MYSQL will handle this type of data very well.

jazzman1 Member

jazzman1

Posted
Posted

As David said, the best-practice for this would be 2 tables.

Take a look at this structure

 

1.Answers Table

+-------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| question_id | int(10) unsigned | NO | | NULL | |

| answer | varchar(255) | NO | | NULL | |

| answerer | varchar(64) | NO | | NULL | |

| created_at | datetime | NO | | NULL | |

| modified_at | datetime | NO | | NULL | |

+-------------+------------------+------+-----+---------+----------------+

 

2.Questions Table

+------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| question | varchar(255) | NO | | NULL | |

| questioner | varchar(64) | NO | | NULL | |

| created_at | datetime | NO | | NULL | |

| modified_at | datetime | NO | | NULL | |

+------------+------------------+------+-----+---------+----------------+

 

dinita Member

dinita

Posted
  • Author
Posted

Thanks for all your help before I recieevd a blank page with no error message now after taking your advice atleast I get an error it shows: "Error: Cannot add or update a child row: a foreign key constraint fails (`quizCreator`.`answers`, CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `questions` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE)" ... I think I have made a mistake in setting up the Foreign key but i'm not sure what it is.

 

 

Thanks Again, I really appreciate all your advice!

Christian F. Member

Christian F.

Posted
Posted

The blank page with no source is almost always caused by as parse error, with error reporting turned off. In that case you'll either need to turn on error reporting in php.ini or check the error logs.

 

As for the current problem, it's most likely an invalid value in the foreign key row (ID) or incompatible fields/table engines.

You'll have to look at the table definitions (SHOW CREATE TABLE), and the data in the two fields, to see if you spot any inconsistencies.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.