Some quick help needed to turn a few lines of PHP/MySQL into MySQL only

[SarcasticDwarf]

I have this:

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bid = $line['bookid'];
$votes = $line['votes'];
$avgvote = $line['avgvote'];
$avgvotew = $line['avgvotew'];
$sql2 = "INSERT INTO rating_averages (bookid, rating_average, rating_weighted, votes, updated) VALUES ($bid, $avgvote, $avgvotew, $votes, NOW())";
$result2 = mysql_query($sql2);
}



I want to turn it into something I can execute via a remote database connection, not by PHP. Can anyone show me how to do this?

 

[WannaFly]

assuming $result was from a select statement, put the select statment into this query but only with bookid,rating_average, rating_weighted,votes:
INSERT INTO rating_averages (bookid, rating_average, rating_weighted, votes, updated) VALUES (<SELECT STATEMENT>, NOW())

 

[SarcasticDwarf]

Sorry, I forgot the other lines were needed. Lemme just paste the whole thing in: (the truncate statement I have no issue with)

$sql = "TRUNCATE rating_averages";
$result = mysql_query($sql); //

$sql = "SELECT bookid, SUM(vote) AS votes, ((SUM(vote) / (SUM(vote)+40)) * AVG(vote) + (40 / (SUM(vote)+40)) * 7.8421) AS avgvotew, AVG(vote) as avgvote FROM rating ".
"GROUP BY bookid HAVING avgvote IS NOT NULL ORDER BY avgvotew DESC";
$result = mysql_query($sql);

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bid = $line['bookid'];
$votes = $line['votes'];
$avgvote = $line['avgvote'];
$avgvotew = $line['avgvotew'];
$sql2 = "INSERT INTO rating_averages (bookid, rating_average, rating_weighted, votes, updated) VALUES ($bid, $avgvote, $avgvotew, $votes, NOW())";
$result2 = mysql_query($sql2);

 

[WannaFly]

TRUNCATE rating_averages;

INSERT INTO rating_averages (bookid, rating_average, rating_weighted, votes, updated) VALUES (
SELECT bookid, AVG(vote), ((SUM(vote) / (SUM(vote)+40)) * AVG(vote) + (40 / (SUM(vote)+40)) * 7.8421), SUM(vote),NOW() FROM rating GROUP BY bookid HAVING avgvote IS NOT NULL ORDER BY avgvotew DESC));

didnt test it bout that should do it for you.

 

[SarcasticDwarf]

Originally posted by: WannaFly
TRUNCATE rating_averages;

INSERT INTO rating_averages (bookid, rating_average, rating_weighted, votes, updated) VALUES (
SELECT bookid, AVG(vote), ((SUM(vote) / (SUM(vote)+40)) * AVG(vote) + (40 / (SUM(vote)+40)) * 7.8421), SUM(vote),NOW() FROM rating GROUP BY bookid HAVING avgvote IS NOT NULL ORDER BY avgvotew DESC));

didnt test it bout that should do it for you.

I'm getting "[Xanthotech] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT bookid, AVG(vote), ((SUM(vote) / (SUM(vote)+40)) * AVG(v" out of that.

 

[WannaFly]

Apparently older versions of MySQL cannot handle subqueries, so that won't work. Sorry.