mysql if condition in query can't get tablename from mysql_field_table

Question

This query works:

SELECT Article.id, 
       Article.post_time, 
       Article.post_locked, 
       Article.comments_locked, Article.title,  
       IF(CHAR_LENGTH(Article.content)>2000, 
          RPAD(LEFT(Article.content,2000),2003,'.'), 
          Article.content) as content, 
       Article.tags, Category.*, 
       User.id, User.user_name, 
       Comment.comment_count 
  FROM `articles` as `Article` 
LEFT JOIN `categories` as `Category` ON `Article`.`category_id` = `Category`.`id` 
LEFT JOIN `users` as `User` ON `Article`.`user_id` = `User`.`id` 
LEFT OUTER JOIN (SELECT article_id, count(*) comment_count FROM `comments`) as `Comment` ON `Article`.id = `Comment`.article_id 
    WHERE '1'='1' 
 ORDER BY `Article`.`id` DESC

But when I loop through the resultset to assign the table name along with the field using 'mysql_field_table', the 'content' returns a table name of nothing, while all others have their correct table:

Array ( 
    [0] => Article 
    [1] => Article 
    [2] => Article 
    [3] => Article 
    [4] => Article 
    [5] => 
    [6] => Article 
    [7] => Category 
    [8] => Category 
    [9] => User 
    [10] => User 
    [11] => Comment )

using

for ($i = 0; $i < $numOfFields; ++$i) {
   array_push($table,mysql_field_table($this->_result, $i));
   array_push($field,mysql_field_name($this->_result, $i));
}

Anyone ever try to do this? Have a solution? I want to return less data from my DB in my query. Or is it less intensive (on mysql, memory, cpu) to simply select all content and truncate the content via PHP? I thought returning less from DB would be better.

Thanks a bunch!!

Peace.

EDIT

to clear up, this is the result, you will see why it isnt what I want:

Array ( 
    [0] => Array ( 
        [Article] => Array ( 
            [id] => 8 
            [post_time] => 1278606312 
            [post_locked] => 0 
            [comments_locked] => 0 
            [title] => Article 8
            [tags] => test ) 
        [] => Array ( 
            [content] => my content for Article  ) 
        [Category] => Array ( 
            [id] => 2 
            [name] => cat2 ) 
        [User] => Array ( 
            [id] => 3 
            [user_name] => user3 ) 
        [Comment] => Array ( 
            [comment_count] => 1 ) 
    ) 
   [1] => Array ( 
        [Article] => Array ( 
            [id] => 7 
etc...

Answer 1

no you cant use a as [tablename].[columnname]-like format for custom column names.

It would be weird anyway if it would work, because how can content be defined as 'Article.content' if it's not really part of the Article table dataset.

Just select the columns you need and join where needed.

But what's WHERE '1' = '1' doing in there? that will just evaluate to true as it is a boolean expression, but it won't affect your resultset.

Answer 2

In order to use characters beyond the English alphabet and spaces in a column alias, the standard SQL means requires using double quotes (though MySQL supports using backticks IE: "`" too):

...,
IF(CHAR_LENGTH(Article.content)>2000, 
   RPAD(LEFT(Article.content,2000),2003,'.'), 
   Article.content) AS "Article.content", 
...

Answer 3

But when I loop through the resultset to assign the table name along with the field using 'mysql_field_table', the 'content' returns a table name of nothing, while all others have their correct table

Once you've done that magic on Article.content, to create the content field, it no longer belongs to the Article table. Rather, it belongs to the result set of that query. I believe that's the explanation for having no table associated with that field.

Imagine a GROUP BY query, with something like COUNT(*) as number. 'number' doesn't belong to any table.

Answer 4

If you really need the ability to know that the column had a particular source, could you have a view on top of Article which does this manipulation to content? Then the source would appear to be the view? Unfortunately, MySQL doesn't support declared computed columns in tables, that might also be useful to you in this case.

Answer 5

while ($row = mysql_fetch_row($this->_result)) {
    $prev_table;
    for ($i = 0;$i < $numOfFields; ++$i) {
        if ($table[$i] == "") {
            $tempResults[$prev_table][$field[$i]] = $row[$i];
        }else {
            $tempResults[$table[$i]][$field[$i]] = $row[$i];    
        }
        $prev_table = $table[$i];
    }
}

Oh well, mysql couldnt do what I wanted. I added the prev_table to take the one before ;)

Thanks to everyone for the help.