Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+6 votes
5.0k views
in Q2A Core by
edited by
What is the best way for me to upload a lot of questions to the DB so that the community could answer it? I tried doing a submit directly in the DB, but then some things like the data, no related questions, etc were working when all I submitted was type "Q" and the title

Is there a plugin for this or a simple SQL command that we can use (if so, let us know what parameters are needed to make a complete question)

While at it - how about if we wanted to load the answer as well?

5 Answers

+7 votes
by
edited by
 
Best answer
Okay, so here's my php sample code (based on gidgreen's answer) to import a question then import an answer to that question while q2a maintains the database statistics correctly:

(Hopefully it'll save others some time)

ps - love the software gidgreen, thanks for creating & sharing it!


    // Set up files
    define('QA_BASE_DIR', dirname(empty($_SERVER['SCRIPT_FILENAME']) ? __FILE__ : $_SERVER['SCRIPT_FILENAME']).'/');
    define('QA_INCLUDE_DIR', QA_BASE_DIR.'qa-include/');
    error_reporting(E_ALL);
    require QA_INCLUDE_DIR.'qa-base.php';
    require_once QA_INCLUDE_DIR.'qa-app-post-create.php';
    require_once QA_INCLUDE_DIR.'qa-app-options.php';
   
    // Connect to database
    qa_base_db_connect(null);

    // START OUTER LOOP HERE if importing multiple q&a sets

    // Set up question content
    $questionuserid = null;
    $questiontext = "sample question";
    $questioncontent = "sample question content";
    $questiontags = "sample, tag";
       
    // insert question into db
    $postid = qa_question_create($qa_db, null, $questionuserid, null, $questiontext, $questioncontent, $questiontags, false, null);
   
    // Retrieve question row from db. Required to insert answer
    $result = mysql_query("SELECT * FROM qa_posts WHERE postid=" . $postid);   
    $question = mysql_fetch_assoc($result);   
   
    // START INNER LOOP HERE if importing multiple answers per question

    // Set up answer content
    $answeruserid = null;
    $sampleanswer = "sample answer content";

    // insert answer into db
    qa_answer_create($qa_db, $answeruserid, null, $sampleanswer, false, null, $question);

    // END INNER LOOP HERE if importing multiple answers per question

    // END OUTER LOOP HERE if importing multiple rows
by
Hi Kp, Perfect... Im using it... thank you for sharing.. :)
by
How do you run this code? I'm hosting my Q2A in Hostgator... and know nothing about PHP...
+3 votes
by
One way is to use the functions in qa-app-post-create.php to load content into the database - that will take care of all the indexing and related things.

You can call:

$db=qa_base_db_connect(null);

Then for each question, do this (assuming it is an anonymous question):

qa_question_create($db, null, null, null, $title, $content, $tagstring, false, null);

Where $title and $content contain the question text, and $tagstring is a comma-separated list of textual tags.

But it's a little more complicated to create answers using qa_answer_create(...), since you need to pass in the database row for the appropriate question (although if you look inside the function you'll see that not much of the row is actually needed).

So your other option is to insert content directly into the database as you were doing. Then once you're finished, click each of the 3 clean-up buttons in the 'Statistics' panel of the 'Admin' section. That will recalculate all the information in the database for related questions and other statistics.

When doing this for questions, you should at least set the columns: type, created, title. Others will default to NULL or 0 as appropriate. For answers you should set the columns: parentid, type, created, content - the parentid should match the postid of the question that is being answered.
by
edited by
When I used gidgreen's solution in my own import php file,  I got an error:

    define('QA_BASE_DIR', dirname(empty($_SERVER['SCRIPT_FILENAME']) ? __FILE__ : $_SERVER['SCRIPT_FILENAME']).'/');
    define('QA_INCLUDE_DIR', QA_BASE_DIR.'qa-include/');
    error_reporting(E_ALL);
    require_once QA_INCLUDE_DIR.'qa-base.php';
    require_once QA_INCLUDE_DIR.'qa-app-post-create.php';
        $db = qa_base_db_connect(null);
        qa_question_create($db, null, null, null, "sample question", "sample question", "sample, tag", false, null);

Produced an error: "Reading one value from invalid result" at the ultimate point where the query is sent to mysql.

Stepping through the code I saw that the db functions set the global db resource variable "$qa_db".

I changed the db code above (replaced the last two lines) to:

        qa_base_db_connect(null);
        qa_question_create($qa_db, null, null, null, "sample question", "sample question", "sample, tag", false, null);

and that worked fine. Of course perhaps I'm omitting setup code in my php file that causes gidgreens solution not to work but I hope this snippit helps someone use their own code from scratch.
by
Thanks kpq2a - you weren't doing anything wrong - my code was just wrong!
+1 vote
by

Here is the updated code for Q&A v1.3.1.

 // Set up files
    define('QA_BASE_DIR', dirname(empty($_SERVER['SCRIPT_FILENAME']) ? __FILE__ : $_SERVER['SCRIPT_FILENAME']).'/');    
    error_reporting(E_ALL);
    
    require_once 'qa-include/qa-base.php';
    require_once QA_INCLUDE_DIR.'qa-app-post-create.php';
   

// Connect to database
    qa_base_db_connect(null);

    // START OUTER LOOP HERE if importing multiple q&a sets

    // Set up question content
    $questionuserid = null;
    $questiontext = "sample question";
    $questioncontent = "sample question content";
    $questiontags = "sample, tag";
       
    // insert question into db                         
    $postid =qa_question_create(null, $questionuserid, null, null, $questiontext, $questioncontent, '', null,$questiontags, false, null);
   
    // Retrieve question row from db. Required to insert answer
    $result = mysql_query("SELECT * FROM qa_posts WHERE postid=" . $postid);   
    $question = mysql_fetch_assoc($result);
    
    // This field is required when inserting answer  
    $question['hidden'] = false;
   
    // START INNER LOOP HERE if importing multiple answers per question

    // Set up answer content
    $answeruserid = null;
    $sampleanswer = "sample answer content3";

    // insert answer into db                
    qa_answer_create($answeruserid, null, null, $sampleanswer,'',null, false, null, $question);

 

by
Hi there!

Thanks for the code. However, I'm quite a newbie with PHP and DB's so I have some questions:

Do I add this code to qa-app-post-create.php?
Where do I add it?
Do I submit the data into the DB first and then this code will sort out the points/related questions?

Thanks!
by
The above code is to add all question data (text/comment/user id) into the database and their answers. It is calling the proper code so all indexes and so are ok. You could take the questions from a CSV file. For my own code, I did a page using the page plugin, read a CSV file and create the questions using that code and looping through the CSV lines.  You may need someone near you to help you through this since you're not familiar with the technology involved!
by
Any way to set up this to upload questions from csv ?
+1 vote
by
it is not a esay thing if you have millions of questions, sqldump maybe your best choise.
+4 votes
by

Version 1.4 beta 1 contains a new set of easy-to-use functions for creating database content, defined in qa-app-posts.php.

...