Question

SQL query to delete all the unanswered questions ?

Answer 1

Deleting posts directly via SQL is not recommended, because that may mess up cached data in Q2A. Use the SQL query for identifying the post IDs you want to delete, and then delete them with the proper Q2A function (qa_post_delete()).

The SQL query for getting the IDs of posts with no answers looks somewhat like this:

SELECT q.postid
FROM qa_posts q
  LEFT OUTER JOIN qa_posts a ON a.parentid=q.postid
WHERE q.type='Q' AND a.parentid IS NULL;

Wrap it in PHP code like this:

$qry = "...";
$unanswered = qa_db_read_all_assoc(qa_db_query_sub($qry));
foreach($unanswered as $q) {
  try {
    qa_post_delete($q['postid']);
  } catch (Exception $e) {
    echo $e->getMessage() . "\n";
  }
}

Comments

Error deleting unanswered questions: Cannot delete or update a parent row: a foreign key constraint fails (`dbname`.`qa_posts`, CONSTRAINT `qa_posts_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `qa_posts` (`postid`))

---

That should not be possible, b/c the query matches parentid to postid and only selects questions that don't have a matching row. Please run "SELECT q.postid,q.type,q.title,a.parentid,a.type FROM qa_posts q LEFT OUTER JOIN qa_posts a ON a.parentid=q.postid WHERE q.type='Q' AND a.parentid IS NULL;" and inspect the results. Also check "SELECT * FROM qa_posts WHERE parentid='...';" with a post ID returned by the other query.

---

<?php
// Replace these variables with your Q2A database credentials
$databaseHost = 'your_database_host';
$databaseUser = 'your_database_username';
$databasePassword = 'your_database_password';
$databaseName = 'your_database_name';

// Establish a connection to the Q2A database
$connection = mysqli_connect($databaseHost, $databaseUser, $databasePassword, $databaseName);
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get unanswered question IDs
$query = "
    SELECT q.postid
    FROM qa_posts q
    LEFT OUTER JOIN qa_posts a ON a.parentid = q.postid
    WHERE q.type = 'Q' AND a.parentid IS NULL;
";

$result = mysqli_query($connection, $query);

// Delete unanswered questions and their associated answers
while ($row = mysqli_fetch_assoc($result)) {
    $postID = $row['postid'];
    try {
        // Delete associated answers (child posts) first
        mysqli_query($connection, "DELETE FROM qa_posts WHERE parentid = $postID AND type = 'A';");

        // Delete unanswered question (parent post)
        mysqli_query($connection, "DELETE FROM qa_posts WHERE postid = $postID;");
    } catch (Exception $e) {
        // Handle any exceptions that may occur during the deletion process
        echo $e->getMessage() . "\n";
    }
}

// Close the database connection
mysqli_close($connection);
?>

Answer 2

DELETE FROM qa_posts

WHERE type = 'Q' AND postid NOT IN (

    SELECT parentid

    FROM qa_posts

    WHERE type = 'A'

)

This code is giving error