Databases
StackOverflow2013
Postsdbo
POmysql query & the php - how to do this?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql query & the php - how to do this?
    text
    copied!<p>Okay, so I have a database which has the following rows, which contains what the user rating for the questions (its a rating script - this is all finished, q1 all the way to q20):</p> <pre><code>id sid q1 q2 q3 q4 q5 ... q19 q20 </code></pre> <p><strong>Image of table:</strong> <a href="http://i.stack.imgur.com/SM70j.jpg" rel="nofollow">http://i.stack.imgur.com/SM70j.jpg</a></p> <p>Here is example row A:</p> <pre><code>id 1 sid 2447 q1 1.5 q2 2.5 q3 0.5 q4 4.5 q5 3.5 ... q19 2.0 q20 2.0 </code></pre> <p>Here is example row B:</p> <pre><code>id 2 sid 2447 q1 2.5 q2 1.5 q3 1.5 q4 3.5 q5 4.5 ... q19 1.0 q20 1.0 </code></pre> <p>Basically, I want a query which does the following:</p> <p>It counts the data and groups it by <code>sid</code>, I want all of the q1 to q20 rows added up:</p> <p>So q1 will be 4, q2 will be 4, q3 will be 2 and so on. </p> <p>I then want to divide these by how many rows there are, so q1 will become 2, q2 will become 2, q3 will become 1 and so on. (To get the average number).</p> <p>We have a page that will display this information on, so for example:</p> <p>Avg rating for Accommodation = <code>row1['q1'] + row2['q1'] / amt of rows</code></p> <p>Avg rating for Scenery = <code>row1['q2'] + row2['q2'] / amt of rows</code></p> <p>Avg rating for Food = <code>row1['q3'] + row2['q3'] / amt of rows</code></p> <p>I have 20 questions which people rate them on, this is all coded, the data is all storing.</p> <p>This is to show the data on page. I currently have it set out like</p> <pre><code>Avg rating for Accommodation Avg rating for Scenery Avg rating for Food </code></pre> <p>I just need help doing the mySQL query to grab the <code>total of q1 where sid = X, divided by the amt of rows for the average rating</code>.</p> <p>I then want to pass these to variables, for example:</p> <pre><code>$r['question1'] = ...; </code></pre> <p>So that we can simply do </p> <pre><code>echo "Avg rating for Accommodation = " . $r['question1']; </code></pre> <p>Sorry if I explained it as if you were stupid, but I'm trying to be as clear as I can to how I want it so I can get help achieving this.</p> <p>Any help would be greatly appreciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload