PHP MYSQL function works, but breaks when extra parameters are added

Question

The code below returns nothing, but if I remove this line:

'desc'          => $row['DESC'],

from the function it works fine.

DESC Is a valid column in the database and when I run the full query in phpmyadmin, it returns the desired result.

I am not sure why this line

'desc'          => $row['DESC'],

breaks the return of the result.

=======

After more investigating I can see the JSON output has the same issue. Altering the column name (since DESC is a keyword) and reflecting the changes in the query has no effect.

========

function get_all_subjects($db1) {
    $stmt = $db1->query("SELECT DISTINCT NAME, DESC, CLASSCODE FROM tbl_subjects WHERE VISIBLE = 1 ORDER BY NAME ASC");
    $stmt->execute();
    $count = $stmt->rowCount();

    $column = array();

    if ($count >0)
    {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
        {
            $column[] = array(
                        'name'          => $row['NAME'],
                        'desc'          => $row['DESC'],
                        'cc'            => $row['CLASSCODE']
                        );
        }

        return json_encode(array('subjects' =>$column)); 
    }
    else
    {
        return $count;
    }
}

your query got syntax error, desc is reserved keyword of sql language, use quotes — Kamil Karkus
– Kamil Karkus, Commented Feb 11, 2015 at 7:29
The result (without the line in question) works so I know my jquery works — jwknz
– jwknz, Commented Feb 11, 2015 at 7:45

Mureinik · Accepted Answer · 2015-02-11 07:30:52Z

3

DESC is a reserved word in SQL. If you want to use it as a column, you should protect it with forward quotes:

function get_all_subjects($db1) {
    $stmt = $db1->query("SELECT DISTINCT NAME, `DESC` AS D, CLASSCODE FROM tbl_subjects WHERE VISIBLE = 1 ORDER BY NAME ASC");
    $stmt->execute();
    $count = $stmt->rowCount();

    $column = array();

    if ($count >0)
    {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
        {
            $column[] = array(
                        'name'          => $row['NAME'],
                        'desc'          => $row['D'], // Using the alias, just in case
                        'cc'            => $row['CLASSCODE']
                        );
        }

        return json_encode(array('subjects' =>$column)); 
    }
    else
    {
        return $count;
    }
}

answered Feb 11, 2015 at 7:30

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jwknz Over a year ago

Thanks for the answer and explanation, but I must be having an issue somewhere else. Still no data return. I have also changed the column name to double check.

jwknz Over a year ago

I marked you as the right answer, since your answer prompted me to look further. I have however posted what the solution to my problem was below.

Alexis Peters · Accepted Answer · 2015-02-11 07:31:11Z

0

DESC is a reserved Keyword do something like

SELECT DISTINCT NAME, DESC as yourVar, CLASSCODE FROM tbl_subjects WHERE VISIBLE = 1 ORDER BY NAME ASC

and then like that

'desc'          => $row['yourVar'],

answered Feb 11, 2015 at 7:31

Alexis Peters

1,6511 gold badge11 silver badges17 bronze badges

Comments

Kamil Karkus · Accepted Answer · 2015-02-11 07:31:55Z

0

$stmt = $db1->query("SELECT DISTINCT `NAME`, `DESC`, `CLASSCODE` FROM `tbl_subjects` WHERE `VISIBLE` = 1 ORDER BY `NAME` ASC");

there was syntax error because desc is reserved keyword, you have to use quotes, the best habbit is quote each column and each table name

answered Feb 11, 2015 at 7:31

Kamil Karkus

1,2831 gold badge11 silver badges29 bronze badges

Comments

jwknz · Accepted Answer · 2015-02-11 09:04:52Z

0

Thank you all for your answers - I found the problem and what broke the script was that my strings in my JSON values had several - in them.

It took me a while to find it :-)

answered Feb 11, 2015 at 9:04

jwknz

6,82417 gold badges77 silver badges117 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL function works, but breaks when extra parameters are added

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related