Which query should I use to show random questions from particular tag ?

Question

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

Answer 1

Tags are stored in the database by linking posts (table qa_posts) to words (table qa_words) with an associative table qa_posttags. To restrict your query to a particular tag you need to join the posts table to the words table via that associative table.

SELECT q.postid
FROM qa_posts q
  INNER JOIN qa_posttags t ON q.postid=t.postid
  INNER JOIN qa_words w ON t.wordid=w.wordid
WHERE w.word='tagname';

So your statement should probably look like this:

qa_db_read_one_assoc(qa_db_query_sub('SELECT * FROM ^posts q INNER JOIN ^posttags t ON q.postid=t.postid INNER JOIN ^words w ON t.wordid=w.wordid WHERE q.type="Q" AND w.word=$ ORDER BY rand() LIMIT 1;', $tagname));

Comments

which part should replace with tag ?
I mean I want to replace with like "electrical" tag ?

---

qa_db_read_one_assoc( qa_db_query_sub('SELECT * FROM ^posts q INNER JOIN ^posttags t ON q.postid=t.postid INNER JOIN ^words w ON t.wordid=w.wordid WHERE q.type="Q" AND w.word=$ ORDER BY rand() LIMIT 1;', electrical);

---

let me try...............

---

There was a closing parenthesis missing in the last code snippet in my answer (fixed now). Also, string literals must be in quotes ("electrical", not just electrical).

---

$random_question = qa_db_read_one_assoc( qa_db_query_sub('SELECT * FROM ^posts q INNER JOIN ^posttags t ON q.postid=t.postid INNER JOIN ^words w ON t.wordid=w.wordid WHERE q.type="Q" AND w.word=$ ORDER BY rand() LIMIT 1', "motivational-quotes" ));

It's  working thank you so much man.....