How can i get random question from database ?

Question

Not really good at PHP/Mysql can someone can help ?

Comments

I have a similar question here:
http://www.question2answer.org/qa/8843/how-to-retrieve-a-single-question-from-the-database

EDIT:
Can't seem to get an answer :/, but I am managing to pull some information on a question like so:

function single(){
$content=$this->content;
foreach ($content as $key => $part) {
if (strpos($key, 'custom')===0)
$this->output_raw($part);

elseif (strpos($key, 'q_view')===0){
$this->output($part['url']);
$this->output(strip_tags($part['title']));;
$this->output(strlen(@$part['netvotes_view']['prefix']));
//answers most likly wrong, needs testing
$this->output(strlen(@$part['content']['prefix']));     
                 }
    }
}

To load a page with a single question from $content=$this->content; is not ideal, but the above method is a practical way to call information already loaded on to your template :)

NOTE: if you have phpMyAdmin you can browse the questions and answers and click on the SQL button to get the raw query output, as for the fastest call within Q2A I think using qa_db_single_select may be the fastest method.

---

Here's a plugin that shows a random question in the sidebar:

https://github.com/NoahY/qa-random-question

---

Oh nice but as most of your plugins i using, custom characters are converted to "?" (Strangely /badges page instead).

---

that is strange... must be a database thing - can you tell me what the collation is for your posts table?

---

MySQL connection collation: utf8_general_ci. This is global i think.

MySQL charset: UTF-8 Unicode (utf8) (from another place)

Answer 1

What a neat idea... would make for a good plugin :)  This works (tested):

            $random_question = qa_db_read_one_assoc(
                qa_db_query_sub(
                    'SELECT * FROM ^posts WHERE type=$ ORDER BY rand() LIMIT 1',
                    'Q'
                ),
                true
            );

It returns an array of one question.

Comments

ORDER BY rand() is notoriously slow in MySQL. You'd be better off passing in a random number generated in PHP. For example you could grab the highest postid in the table (is it in the options or stats tables?), create random int between 1 and the max, then do something like
SELECT * FROM ^posts WHERE type=$ AND postid<=# LIMIT 1

---

yeah, I thought of that, but then don't you have to make a query just to find the max, and then another for the actual request?  I have to admit, I'm not up on mysql speed and such... this works, it's easy... I guess if we're talking about a site like stack overflow, speed is a big deal, but for my site it's not much of an issue.

---

I tried:
            $rand = qa_db_read_one_value(
                qa_db_query_sub(
                    'SELECT MAX(postid) FROM ^posts'
                ),
                true
            );
            if(!$rand) return;
            $rand = rand()/getrandmax()*$rand;
            $random_question = qa_db_read_one_assoc(
                qa_db_query_sub(
                    'SELECT * FROM ^posts WHERE type=$ AND postid >= # LIMIT 1',
                    'Q',$rand
                ),
                true
            );

and it takes the same time as the above query, maybe a bit longer... I guess this way will be quicker with larger databases?

---

Thank you! I'm using your plugin! Can I do this: Place of picking different questions, change the Unanswered Questions or Problems with the Best answer!