[SOLVED] : DB query returns nothing

Hi everybody,

I’m currently facing a very strange issue with the db api. When I write some quite complex query (involving inner joins and where clauses with several conditions) and try to execute them through $modx->db->query( $query ), nothing gets returned but, the same query executed within phpMyAdmin does effectively return the desired results.

Has anybody some idea to solve this problem ?

Thanks in advance,

rmic.

Definitely not without more information about the query.

Okay, so here’s my query :

SELECT film.id_film, id_modxdoc, titre_film, annee
FROM films_en_rapport, film, fc_to_modx
WHERE ( (films_en_rapport.id_film_origine = \"".$idFilmRapp."\")
AND (films_en_rapport.id_film_rapport = film.id_film)
AND (fc_to_modx.id_film = films_en_rapport.id_film_rapport)
);


$idFilmRapp is initialized correctly.

$filmsEnRapport = $modx->db->query($query);
$modx->db->getRecordCount($filmsEnRapport) returns 0 and the following while loop is never executed (as no result is returned from the query).

If I execute "echo $query" and copy / paste the result in phpMyAdmin, everything goes fine and I get the expected result, so I guess the SQL code is correct.

What really confuses me is that other queries (i.e. simpler) work fine.

I’ve had similar problems the last 2 days. Relatively simple queries* worked fine in phpmyadmin or even in a standalone php script, but did absolutely nothing using the modx dbapi. I finally gave up on it, cause I can’t lose any more time... If there’s any limitations with the dbapi, I’d like to know about them. I didn’t even get an error msg, so bug-hunting is a bit tedious :|

* e.g. one was using CONCAT(), another one simple compare <> operators
Using TVs with @SELECT binding, tried various formats

Hmmm, I’ve never had these problems with the DBAPI that you mention; the functions are relatively straightforward wrappers for the standard mysql_ functions in PHP, though the select() and other command specific functions have some restrictions based on the way it builds the query from the arguments. But using $modx->db->query() doesn’t present any problems for me regardless of the complexity of my queries...

I suggest you try those queries with mysql_ functions (or PDO¹ if available) directly, and see if you get the same problems. Unfortunately, I have no way of testing those queries against your specific tables...


¹ - BTW, I now prefer to use PDO (wrapped by my new O/RM project xPDO) to access external databases. The DBAPI will be deprecated in 0.9.7, since the new modX class in the new core has direct access to PDO functions. If you know of the benefits of PDO, I’d suggest giving it a try; you can use it stand-alone with your existing MODx quite easily, and it even provides PDO emulation for environments that don’t have it built-in to PHP.

This becomes stranger and stranger, I gave it a try with the standard mysql_ functions and it didn’t work either, so I decided to simplify my query by splitting it, and now, even a very simple query like SELECT field FROM table where ( condition ); doesn’t work (even without any function like CONCAT() or something ...) .

The problem is maybe somewhere else.


I didn’t have time to try PDO/xPDO but I’ll find time asap to have a look at it.

Is your table in the modx db?
If so, go even simpler.... Exclude the where.

Try the following:


That didn’t work? How about:

Okay,

After a few days of wondering what could have happened, I had an interesting idea : write the resulting query in a text file instead of displaying it on the document. And that’s what gave me the solution : I was using a placeholder of my template as parameter for my query, but when the query is sent to the mysql server, the placeholder was still not replaced, but it was well when I displayed it on the web page.

I’m not sure everything is clear, so this could maybe help to understand :

- I made a plugin who fills some placeholders in my web pages.
- In my template, I called my snippet with something like [!mySnippet? &param=`[+placeholder+]`!]
- The query sent to mysql (and written in my text file but not on the web page) was "SELECT ..... where idFilm="[+placeholder+]" ....

Thanks to everybody which had a look at my problem.
Hope this could help someone else


R.

When I’m debugging snippets/plugins/modules/etc. I like to use die() to be absolutely sure that the MODx parser doesn’t gobble up my debug output.

Additionally, you could probably use htmlspecialchars(), or this clever function that someone I know created:

Nice idea sirlancelot, I’ll give it a try in the future

Thanks