Mysql Query not returning any output?

Question

I'am using codeigniter and I'm executing this query

SELECT c.id AS id, c.name AS name, c.img_add AS img, c.downloads as down, c.tag as tag, c.date as date,c.views as view FROM core c WHERE c.name = 'Ganesha (2)' ORDER BY c.downloads DESC

The query is used in the model, and does not returns any data, while the same query when executed in the Heidi SQL, returns me with the appropriate data.

I don't know weather it is a query problem, codeigniter problem or php's, so I'm posting for all.

This is a code which I wrote in my codeigniter model. This may help in answering.

public function getFolderDetail($folder) {

    $sql = "SELECT c.id AS `id`, c.name AS `name`, c.img_add AS `img`, c.downloads as `down`, c.tag as `tag`, c.date as `date`,c.views as `view` FROM core c
            WHERE c.name = ?
            ORDER BY c.downloads DESC";
    $params = array($folder);
    return $query = $this->db->query($sql,$params);
}

I just need a reason why this query works on Heidi sql, but gives no result while fetching from PHP — K Cloud
– K Cloud, Commented Nov 15, 2013 at 7:01

DRapp · Accepted Answer · 2013-11-15 15:41:04Z

1

My suspicion may be reserved words, and may need tick marks around them. I would start with your

 `name`, `date` and `view` columns...

the tick is the ` key left of the #1 on keyboard, not a single quote

each part that may be reserved would need the tick marks... such as

c.`name` as `name` (same with others)

it would be applicable in the where clause to

where c.`name` = 'something'

ARE YOU running into an invalid function?

By sending in the string you mention of "Alexa (2)", might it be interpreting that as a FUNCTION called "Alexa", and it is passing a parameter of 2, and thus Alexa is not a real function. Can you try by NOT having parens as the value?

Here is a list of MySQL reserved words

edited Nov 15, 2013 at 15:41

answered Nov 10, 2013 at 14:06

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

7 Comments

K Cloud Over a year ago

i have used the escape letter yet no luck, I also stated in my question that this query is working on Heidi Sql, a query browser for desktop, yet fetches me no result when called as a query from php.

DRapp Over a year ago

@KCloud, did you tick wrap each of the possible reserved words (name, date, view and maybe even tag)... such would be written as c.name as name (same with others) it would be applicable in the where clause too.

K Cloud Over a year ago

i tick wrap everything all the names, now for better explanation when i send '(' or ')' to the query it doesn't executes else it works fine...for eg. in the code above replace the ? with 'Alexa' the code goes fine, replace it with 'Alexa (2)' the code result with nothing found.

DRapp Over a year ago

@KCloud, see revision... it is falsely interpreting Alexa(2) as a FUNCTION CALL?

K Cloud Over a year ago

Thanks, for your help, but things got sorted out, I even posted a response of this question you can look below. Thank you again.

|

user1431775 · Accepted Answer · 2013-11-10 14:26:12Z

0

try to escape reserved word "date"

SELECT `c`.`date` as `date` etc...

http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html

answered Nov 10, 2013 at 14:26

user1431775

Comments

K Cloud · Accepted Answer · 2013-11-15 14:53:43Z

0

I got the problem, actualy the value in folder for Ganesh (2) was coming as Ganesha (2) which is a url format of the value, as I am sending this value as urls.

I did this

public function getFolderDetail($folder) {
$folder = str_replace('&#41;',')',$folder);
$folder = str_replace('&#40;','(',$folder);
    //echo $folder = str_replace('(','\(',$folder);

    $sql = "SELECT c.id AS `id`, c.name AS `name`, c.img_add AS `img`, c.downloads as `down`, c.tag as `tag`, c.date as `date`,c.views as `view` FROM core c
            WHERE c.name = ?
            ORDER BY c.downloads DESC";
    $params = array($folder);
    return $query = $this->db->query($sql,$params);
}

and now the code works fine.

answered Nov 15, 2013 at 14:53

K Cloud

2711 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mysql Query not returning any output?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related