Forum Moderators: open
A long time ago I got help putting together commands to accomplish a task. I just now tried the php code that was written for us and it doesn't seem to work. First, here is what we are trying to do:
We have a database called quiz. We are trying to copy questions from 2 practice exams and combine them into a new practice exam. Inside the DB, there is a table called questions with all of the fields you will see below in the code. We want to copy all of the questions that have a setid field of 62 and 64 into one with a setid of 63. Using the code that was written below, we would have done the 2 copies 1 at a time (62 then 64). One other requirement is that we need the id field to autoincrement for each question with a new unique id so that it doesn't overwrite any of our existing questions in the database.
<?php$dbHost = "localhost"; //Location Of Database usually its localhost
$dbUser = "username"; //Database User Name
$dbPass = "password"; //Database Password
$dbDatabase = "quiz"; //Database Name
$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
$sql = "SELECT * FROM `questions`
WHERE `setid` = '62'
";
if ( !$result = mysql_query($result) ) {
die('MySQL Error: ' . mysql_error());
}
while ( $row = mysql_fetch_assoc($result) ) {
$sql = "INSERT INTO `questions`
('id', 'setid', 'question', 'sol1', 'sol2', 'sol3', 'sol4', 'sol5', sol6', 'sol7', 'sol8', 'solution', 'solexp', 'ismcq', 'ismultiple', 'isoe', 'image', 'imageext', 'isimport', 'randfield')
VALUES
('', '63', '" . $row['question'] . "', '" . $row['sol1'] . "' . $row['sol2'] . "' . $row['sol3'] . "' . $row['sol4'] . "' . $row['sol5'] . "' . $row['sol6'] . "' . $row['sol7'] . "' . $row['sol8'] . "' . $row['solution'] . "' . $row['solexp'] . "' . $row['ismcq'] . "' . $row['ismultiple'] . "' . $row['isoe'] . "' . $row['image'] . "' . $row['imageext'] . "' . $row['isimport'] . "' . $row['randfield'] . "')
";
if ( !result2 = mysql_query($sql) ) {
die('MySQL INSERT Error: ' . mysql_error());
}
}
I saved this code as a php file, uploaded it and ran it in my browser. When I ran this (I did replace username and pass), I didn't get any errors, but nothing happened either. I don't need this to strictly be a PHP solution - straight sql will work too. Anything that will work. We are using Linux and MySQL.
Hope it makes sense and many thanks in advance!
If you can gain access to the interface where you can issue SQL statements against your quiz DB then something like the following should work:
INSERT INTO questions (setid, question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield)
SELECT 63,question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield
FROM question
WHERE setid=62
INSERT INTO questions (setid, question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield)
SELECT 63,question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield
FROM question
WHERE setid=64
Typically with most databases (not sure about MySQL) you never have to list autoincrement fields when inserting rows in the table where the field is defined as an autoincrement so the solution above has it omitted. Once you've created a question and gotten an autoincrement ID value, you WILL have to include the field in inserts into other tables which have a foreign key reference back to questions.id.
If you want the questions inserted in the same order as those that exist for setid=62 and setid=64 then you can add ORDER BY questionid to each of the SELECTs above.
If you can't gain access to the interface for issuing SQL statements directly against your DB, try omitting id from the list of fields to be inserted and omitting '', from the list of values to be inserted in your PHP and give it another try.
[edited by: ZydoSEO at 3:47 pm (utc) on Dec. 21, 2008]
#1146 - Table 'quiz.question' doesn't exist
Thanks again for the help..
INSERT INTO questions (setid, question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield)
SELECT 63,question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield
FROM questions
WHERE setid=62
INSERT INTO questions (setid, question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield)
SELECT 63,question, sol1, sol2, sol3, sol4, sol5, sol6, sol7, sol8,
solution, solexp, ismcq, ismultiple, isoe, image, imageext, isimport, randfield
FROM questions
WHERE setid=64
[edited by: ZydoSEO at 10:00 pm (utc) on Dec. 22, 2008]
Thanks VERY much!
