What is the wisdom behind the non-response to prayers for the sick and others, and is it conceivable that all these people are praying for that which is not in their best interest?

Allah has promised to answer prayers, as stated in the Qur’an and Sunnah. Reasons why prayers might not be answered could be due to impediments such as consuming ill-gotten gains (haram), lack of certainty in the answer, heedlessness during supplication, praying for something sinful or for the severance of kinship ties, or impatience.

Indeed, supplication is an effective remedy, but a heart heedless of Allah and the consumption of ill-gotten gains weaken its power. If the conditions for supplication are met and the impediments are absent, then the answer to the prayer will not fail to materialize, except for a greater good, such as warding off an affliction or being stored for the Hereafter.