Write an MySQL Query and PHP page that displays the query results
I need a PHP page written that displays the results from a MySQL query. Currently, I have a PHP page where a person submits a form that includes a combobox. Based on the value submitted in the combobox, I would like the following query to run with the results displayed on the page. I need the following written properly and hopefully in a single MySQL statement:
1) Get each person who was charged with a crime
WHERE intConviction_crime_ID=SELECTED CRIME ID FROM COMBOBOX
2) For each defendant, loop through the results to get all of the crimes that each defendant has been accused of. For one defendant, this can be accomplished with the following query:
SELECT strCrime_name, strConviction_result
FROM tblcrimes, tblconvictions
WHERE intConviction_defendant_ID = EACH DEFENDANT FROM ABOVE
AND strConviction_result LIKE 'guilty'
Next, for each crime, I would like a percentage of how often each strCrime_name appears compared to all of the results in loop of queries in part 2.
The output from the query should be the strCrime_names with the percentage that each name appears.