sql returning no results

Question

The following code is returning no results where I use the variable in the code of $dep if I manually put the value in of 1 it returns the expected result. I have tried it with no quotes single quotes and double quotes. I have looked though loads of examples and I cannot see what I am doing wrong

$dep = 1;
    if (!$names) {
        $sql = "SELECT topic_id, topic_pid, ispublic, isactive, topic, dept_id FROM '.TOPIC_TABLE
            . ' WHERE dept_id='$dep' ORDER BY `sort`";
        $res = db_query($sql);

it is part of the core of osticket core code and works perfectly fine, the only changes I have made is also pulling though the dept_id adding the where clause which works when it is dept_id=1 but not when it is laid out like the code above — tom bruton lincs, Feb 17 '17 at 16:23
Is TOPIC_TABLE a constant? Your syntax is completely wrong, since tables shouldn't be quoted like this, and you're never breaking out of the quotes to use an external variable. — aynber, Feb 17 '17 at 16:24

Michael Di Prisco · Accepted Answer · 2017-02-17T16:43:48.903

I'm pretty sure your error is related to wrong quotes used. In your code, you write

$sql = "SELECT topic_id, topic_pid, ispublic, isactive, topic, dept_id FROM '.TOPIC_TABLE
        . ' WHERE dept_id='$dep' ORDER BY `sort`";

After FROM, you are using single-quotes('), but your whole query has been enclosed into double-quotes("), so that creates the issue.

It should be:

$sql = "SELECT topic_id, topic_pid, ispublic, isactive, topic, dept_id FROM ".TOPIC_TABLE
        . " WHERE dept_id='$dep' ORDER BY `sort`";

EDIT: Forgot to point out you should seriously use PDO or any other SQL Injection prevention methods. If, under any circumstance, your $dep variable could be sent via a public form, you could end up by having your DB dumped in the best case.

score 0 · Answer 2 · answered Feb 17 '17 at 16:21

There's a syntax error in the second line of the query - if you want single-quotes in the query, then you need to enclose it all in double-quotes:

$sql = "SELECT topic_id, topic_pid, ispublic, isactive, topic, dept_id FROM ' .TOPIC_TABLE
            . " WHERE dept_id='$dep' ORDER BY `sort`";

By the way, building a query like this, using string concatenation, is a REALLY BAD IDEA and leaves you open to SQL injection attacks - you should use prepared statements and parameters instead.

You missed 1st line quotes error.. After FROM, there should be double-quotes as I pointed out in my answer. I agree about SQL Injections — Michael Di Prisco, Feb 17 '17 at 16:42

score 0 · Answer 3 · answered Feb 17 '17 at 16:24

0

First as Fred -ii says make sure the if statement is executing properly. Then if dept_id is an integer value then you should not need the single quotes as scaisEdge says. Otherrwise the SQL looks fine. Make sure that there are in deed records in the database for the dept_id that is being passed in.

answered Feb 17 '17 at 16:24

Billy Stalnaker

146
1
1
10

Reviewing your code @Michael Di Prisco is right in that your quotes don't line up correctly. With the quotes you have in your question the sql will end up looking like this: `SELECT topic_id, topic_pid, ispublic, isactive, topic, dept_id FROM '.TOPIC_TABLE . ' WHERE dept_id='1' ORDER BY sort` exactly. Not concatting the actual table name in correcty – Billy Stalnaker Feb 17 '17 at 16:32

sql returning no results

3 Answers3