0

I want to create a leaderboard for my site and I need to fetch data but I cannot do it while using multi_query.

My code looks like this:

$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'user';
$DATABASE_PASS = 'password';
$DATABASE_NAME = 'dbname';

$conn = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  } else {
    $sql = "SELECT SUM(tbl.points) AS Total FROM (SELECT points FROM Pickem WHERE id='1' UNION ALL SELECT points FROM Pickem2 WHERE id='1' UNION ALL SELECT points FROM Pickem3 WHERE id='1' UNION ALL SELECT points FROM Pickem4 WHERE id='1' UNION ALL SELECT points FROM Pickem5 WHERE id='1') tbl;
            SELECT SUM(tbl.points) AS Total2 FROM (SELECT points FROM Pickem WHERE id='2' UNION ALL SELECT points FROM Pickem2 WHERE id='2' UNION ALL SELECT points FROM Pickem3 WHERE id='2' UNION ALL SELECT points FROM Pickem4 WHERE id='2' UNION ALL SELECT points FROM Pickem5 WHERE id='2') tbl;
            SELECT SUM(tbl.points) AS Total3 FROM (SELECT points FROM Pickem WHERE id='3' UNION ALL SELECT points FROM Pickem2 WHERE id='3' UNION ALL SELECT points FROM Pickem3 WHERE id='3' UNION ALL SELECT points FROM Pickem4 WHERE id='3' UNION ALL SELECT points FROM Pickem5 WHERE id='3') tbl;
            SELECT SUM(tbl.points) AS Total4 FROM (SELECT points FROM Pickem WHERE id='4' UNION ALL SELECT points FROM Pickem2 WHERE id='4' UNION ALL SELECT points FROM Pickem3 WHERE id='4' UNION ALL SELECT points FROM Pickem4 WHERE id='4' UNION ALL SELECT points FROM Pickem5 WHERE id='4') tbl;
            SELECT SUM(tbl.points) AS Total5 FROM (SELECT points FROM Pickem WHERE id='5' UNION ALL SELECT points FROM Pickem2 WHERE id='5' UNION ALL SELECT points FROM Pickem3 WHERE id='5' UNION ALL SELECT points FROM Pickem4 WHERE id='5' UNION ALL SELECT points FROM Pickem5 WHERE id='5') tbl;
            SELECT SUM(tbl.points) AS Total6 FROM (SELECT points FROM Pickem WHERE id='6' UNION ALL SELECT points FROM Pickem2 WHERE id='6' UNION ALL SELECT points FROM Pickem3 WHERE id='6' UNION ALL SELECT points FROM Pickem4 WHERE id='6' UNION ALL SELECT points FROM Pickem5 WHERE id='6') tbl;
            SELECT SUM(tbl.points) AS Total7 FROM (SELECT points FROM Pickem WHERE id='7' UNION ALL SELECT points FROM Pickem2 WHERE id='7' UNION ALL SELECT points FROM Pickem3 WHERE id='7' UNION ALL SELECT points FROM Pickem4 WHERE id='7' UNION ALL SELECT points FROM Pickem5 WHERE id='7') tbl;
            SELECT SUM(tbl.points) AS Total8 FROM (SELECT points FROM Pickem WHERE id='8' UNION ALL SELECT points FROM Pickem2 WHERE id='8' UNION ALL SELECT points FROM Pickem3 WHERE id='8' UNION ALL SELECT points FROM Pickem4 WHERE id='8' UNION ALL SELECT points FROM Pickem5 WHERE id='8') tbl;
            SELECT SUM(tbl.points) AS Total9 FROM (SELECT points FROM Pickem WHERE id='9' UNION ALL SELECT points FROM Pickem2 WHERE id='9' UNION ALL SELECT points FROM Pickem3 WHERE id='9' UNION ALL SELECT points FROM Pickem4 WHERE id='9' UNION ALL SELECT points FROM Pickem5 WHERE id='9') tbl;";
    if ($result = mysqli_multi_query($conn, $sql)){
        $row = mysqli_fetch_array($result); 
        $sum = $row['Total'];
        $sum2 = $row['Total2'];
        $sum3 = $row['Total3'];
        $sum4 = $row['Total4'];
        $sum5 = $row['Total5'];
        $sum6 = $row['Total6'];
        $sum7 = $row['Total7'];
        $sum8 = $row['Total8'];
        $sum9 = $row['Total9'];
        echo $sum;
        $conn-> close();
    }
    }

Can you help me to make this code work beacause I'm a little bit stuck.

XRanshu
  • 1
  • 1
  • 1
    The very fact you've written queries with such a structure suggests that your database is probably badly denormalised. And also even despite that you could still write this as a single query. – ADyson Jun 09 '22 at 21:46
  • Meanwhile we don't really know what "cannot" actually means. What specific error/problem are you experiencing? And for what reason did you choose multi_query rather than just separate calls to mysqli_query? – ADyson Jun 09 '22 at 21:47
  • This is not how you use `mysqli_multi_query`. See https://www.php.net/manual/en/mysqli.multi-query.php - you need to call `mysqli_store_result` for each select statement to get `$result`. – dev-null-dweller Jun 09 '22 at 21:50
  • 1
    **NEVER USE `mysqli_multi_query()`** This function will cause you many issues and it doesn't solve any problems. Please consider using normal queries/prepared statements. – Dharman Jun 09 '22 at 22:18

0 Answers0