Query in mysql works but when assigned to a variable in php doesn't give the result

Question

I have the following query written in mysql. It works properly in mysql but when I assign it to the variable in php and try to run it ,it gives an error - "Parse error: syntax error, unexpected identifier "IPRANDPATENTS"". How am I supposed to run this query in php successfully?

    <table align="center">
        <tr>
            <th>RollNo</th>
            <th>IPRANDPATENTS</th>
            <th>COMPUTERNETWORKS</th>
            <th>DATAWAREHOUSINGANDMINING</th>
            <th>DESIGNANDANALYSISOFALGORITHMS</th>
            <th>SOFTWARETESTINGMETHODOLOGIES</th>
            <th>NETWORKPROGRAMMINGLAB</th>
            <th>DATAWAREHOUSINGANDMININGLAB</th>
            <th>SOFTWARETESTINGLAB</th>
            <th>INTERNETOFTHINGS</th>
        </tr>

    <?php
        
        $sql= "SELECT rollnum, 
max((case when subject='IPRANDPATENTS' then gradea end)) as `IPRANDPATENTS`,
max((case when subject='COMPUTERNETWORKS' then gradea end)) as `COMPUTERNETWORKS`, 
max((case when subject='DATAWAREHOUSINGANDMINING' then gradea end)) as `DATAWAREHOUSINGANDMINING`, 
max((case when subject='DESIGNANDANALYSISOFALGORITHMS' then gradea end)) as `DESIGNANDANALYSISOFALGORITHMS`,
max((case when subject='SOFTWARETESTINGMETHODOLOGIES' then gradea end)) as `SOFTWARETESTINGMETHODOLOGIES`,
max((case when subject='NETWORKPROGRAMMINGLAB' then gradea end)) as `NETWORKPROGRAMMINGLAB`,
max((case when subject='DATAWAREHOUSINGANDMININGLAB' then gradea end)) as `DATAWAREHOUSINGANDMININGLAB`,
max((case when subject='SOFTWARETESTINGLAB' then gradea end)) as `SOFTWARETESTINGLAB`,
max((case when subject='INTERNETOFTHINGS' then gradea end)) as `INTERNETOFTHINGS`
from ocse4year group by rollnum;
";
        $result = mysqli_query($conn, $sql);
    

        $resultCheck = mysqli_num_rows($result);

        if ($resultCheck > 0)
            {
                while($row = mysqli_fetch_assoc($result))
                {
                    echo "<tr><td>". $row["rollnum"] . 
                    "</td><td>" . $row["IPRANDPATENTS"] . 
                    "</td><td>" . $row["COMPUTERNETWORKS"] . 
                    "</td><td>" . $row["DATAWAREHOUSINGANDMINING"] . 
                    "</td><td>" . $row["DESIGNANDANALYSISOFALGORITHMS"] . 
                    "</td><td>" . $row["SOFTWARETESTINGMETHODOLOGIES"] . 
                    "</td><td>" . $row["NETWORKPROGRAMMINGLAB"] .
                    "</td><td>" . $row["DATAWAREHOUSINGANDMININGLAB"] .  
                    "</td><td>" . $row["SOFTWARETESTINGLAB"] . 
                    "</td><td>" . $row["INTERNETOFTHINGS"] . 
                    "</td></tr>";
                }
            echo "</table>";
            }

        else{
        echo "0 result";
    }
    
    $conn-> close();
    ?>

</table>

Double quote chars in MySQL are used for JSON paths/values, not for object names quoting. Replace with backticks. — Akina, Jun 21 '21 at 09:34
double quotes causing you problem, use backtick or single quotes or leave it without quotes. — Mohammad Masood Alam, Jun 21 '21 at 09:37
I replaced it with backticks but guess what, I got empty tables — Tonnt 7575, Jun 21 '21 at 09:37
To get errors out of PHP even in a LIVE environment add these 4 lines to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. — RiggsFolly, Jun 21 '21 at 09:51

score -2 · Answer 1 · answered Jun 21 '21 at 09:34

-2

Please replace the alias names with ' instead.

So "IPRANDPATENTS" would be 'IPRANDPATENTS' - the issue is that you are breaking out of the string by using " inside a string declared with ". PHP thinks that the string has ended, that's why it didn't expect IPRANDPATENTS.

answered Jun 21 '21 at 09:34

Neeko

121
1
8

Yes I did. Yet There seems to be some problem – Tonnt 7575 Jun 21 '21 at 09:38
1

Does it print _0 result_? Can you try printing something inside the while-loop. – Neeko Jun 21 '21 at 09:45
I tried. It prints the roll numbers but not the result – Tonnt 7575 Jun 21 '21 at 09:48
This would only be needed if the variables were interpolated in the string. It doesn't make a difference when concatenating. – Barmar Jun 23 '21 at 14:29

Query in mysql works but when assigned to a variable in php doesn't give the result

1 Answers1