Force MySQL to return something versus nothing?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Force MySQL to return something versus nothing?

Post by VladSun »

My suggestion seems to be not working ;) Jab - skip it.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Force MySQL to return something versus nothing?

Post by califdon »

JAB: I think it will help you understand the relationship between PHP and MySQL if you consider what these two very different kinds of software are intended to do. MySQL is (as you know, of course) a database management system and, as such, it has one and only one task: interact with the database. It does not attempt to format results for display (except in a crude way, from the command line), it does not care about you and your arrays, its purpose is very restricted: interact with the database--create tables, add rows of data, delete rows, update rows, select, sort and group data from tables, etc. etc. PHP has a much broader purpose, to perform all sorts of logical operations, primarily aimed at supplying data for the web server to send to a browser. So it should not be surprising that MySQL returns no rows when there is no data for those rows. It is the job of your PHP script to recognize when it needs to supply dummy "non-data" to conform to the particular requirements of your application.

Hope this helps.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Force MySQL to return something versus nothing?

Post by John Cartwright »

The answer your original question, however far fetched it may be ;)..

The only thing I can come up with is querying a row that does exist with a bunch of left joins (1 per left join per tag). The key here is always returning a single row with attached columns for each tag. Like others have mentioned, you cannot return something that does not exist.

Code: Select all

SELECT 
   'tag1' AS tag1name, 
   IF(tag1.id IS NULL, 'exists', 'does not exist') AS tag1exists
   'tag2' AS tag2name, 
   IF(tag2.id IS NULL, 'exists', 'does not exist') AS tag2exists
FROM foobar
LEFT JOIN tags AS tag1 USING (id)
LEFT JOIN tags AS tag2 USING (id)
HAVING tag1name = 'tag1' 
     AND tag2name = 'tag2'
This would obviously involve PHP to help prepare the query, dynamically adding column names, LEFT joins, and having operations.

I feel so dirty coming up with someone like this, and I'm not sure if it works. I would still recommend you follow the other suggestions before continuing down this dark path. On the other hand, you tend to enjoy walking to so called dark path.
Post Reply