how to do a specific SQL query

how to do a specific SQL query

javismilesjavismiles Posts: 205Questions: 38Answers: 3

in one of the columns to be returned by datatables, i need to do an SQL that is a bit more complex than a select:
$stmt = $mysqli->prepare("SELECT AVG(points), COUNT(points) FROM easyvotes where idvoted=".$row["resultid"]." and type=2");

how do i do this in combination with the typical datatables php read procedure? thanks a lot

This question has an accepted answers - jump to answer


  • Bindrid2Bindrid2 Posts: 72Questions: 0Answers: 12

    Easiest way is to just create a view (assuming you are not doing updates)

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    yes I just managed to do it with a view, after some tries, it works, just 2 concerns:

    a) will performance suffer for big tables, the view is like a temporary pre-query that brings out all the distinct averages and then datatables selects the right one from those based on an extra where condition, but will performance suffer because of that

    b) Im also wondering about memcached and datatables, I use it all the time outside of datatables, anybody combining datatables and memcached for quick cache of db queries?

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    for reference my view is like this:
    SELECT AVG(a.points) as p, COUNT(a.points) as c, a.idvoted FROM easyvotes a where a.type=2 group by a.idvoted

    and then on the datatables php I use:
    $editor->leftJoin( 'avg2', 'mywork.workid', '=', 'idvoted' );

    and I select avg2.p

    and it all works, just wondering about performance of views and also possible use of memcached which helps me a lot, a lot, a lot with speed on my db work outside of datatables

  • allanallan Posts: 49,623Questions: 1Answers: 7,317 Site admin
    Answer ✓

    Closing in favour of your other thread on this topic.

This discussion has been closed.