CODEWITHSUNDEEP
phpmysqlsqlfunctionpdo
jwknz
jwknz

Reputation: 6822

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

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;
    }
}

Upvotes: 0

Views: 66

Answers (4)

jwknz
jwknz

Reputation: 6822

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 :-)

Upvotes: 0

Kamil Karkus
Kamil Karkus

Reputation: 1283

$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

Upvotes: 0

Alexis Peters
Alexis Peters

Reputation: 1631

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'],

Upvotes: 0

Mureinik
Mureinik

Reputation: 311228

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;
    }
}

Upvotes: 3

Related Questions