SQL Help

  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

Hello folks i was wondering if anyone could explain something to me, i have this sql query:

Code: [ Select ]
SELECT p.*, p.creator AS creator_id, u.username AS creator_name, c.username AS clients_name, c.id AS client_id
FROM " . MODULE_PROJECTS_TABLE . " p
LEFT JOIN " . CORE_USERS_TABLE . " c ON p.client_id = c.id
LEFT JOIN " . CORE_USERS_TABLE . " u ON p.creator = u.id
ORDER BY created DESC
  1. SELECT p.*, p.creator AS creator_id, u.username AS creator_name, c.username AS clients_name, c.id AS client_id
  2. FROM " . MODULE_PROJECTS_TABLE . " p
  3. LEFT JOIN " . CORE_USERS_TABLE . " c ON p.client_id = c.id
  4. LEFT JOIN " . CORE_USERS_TABLE . " u ON p.creator = u.id
  5. ORDER BY created DESC


Assume that the variables are correct and that it returns some rows. It returns an array like this:

Code: [ Select ]
array(
['id'] => 59
['title'] => 'Test'
['details'] => 'test'
['image'] => '7c8fc4f2b6836727e6cb40a95116fe98.jpg'
['created'] => 1244150298
['creator'] => 6
['client_id'] => 35
['progress'] => 50
['status'] => 'Midway'
['creator_id'] => 6
['creator_name'] => 'Liam'
)
  1. array(
  2. ['id'] => 59
  3. ['title'] => 'Test'
  4. ['details'] => 'test'
  5. ['image'] => '7c8fc4f2b6836727e6cb40a95116fe98.jpg'
  6. ['created'] => 1244150298
  7. ['creator'] => 6
  8. ['client_id'] => 35
  9. ['progress'] => 50
  10. ['status'] => 'Midway'
  11. ['creator_id'] => 6
  12. ['creator_name'] => 'Liam'
  13. )


Can anyone tell me why the "clients_name" attribute is not there?

Thanks
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Could you show us the code around your query and output line that you pulled this array from? There's a few possibilities.
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

This is my entire function. It uses phpBB's dbal for the db wrapping.

Code: [ Select ]
    function get_projects($num_projects = 'all', $start_from = 0){
        global $fb, $db;

        $sql = "SELECT p.*, p.creator AS creator_id, u.username AS creator_name, c.username AS clients_name, c.id AS client_id
                FROM " . MODULE_PROJECTS_TABLE . " p
                LEFT JOIN " . CORE_USERS_TABLE . " c ON p.client_id = c.id
                LEFT JOIN " . CORE_USERS_TABLE . " u ON p.creator = u.id
                ORDER BY created DESC";
            
        if($num_projects == 'all'){
            $result = $db->sql_query($sql);
        }else{
            $result = $db->sql_query_limit($sql, $num_projects, $start_from);
        }
        
        $projects = $db->sql_fetchrowset($result);
                
        //garbage collection
        $db->sql_freeresult($result);
        
        return $projects;         
    }
  1.     function get_projects($num_projects = 'all', $start_from = 0){
  2.         global $fb, $db;
  3.         $sql = "SELECT p.*, p.creator AS creator_id, u.username AS creator_name, c.username AS clients_name, c.id AS client_id
  4.                 FROM " . MODULE_PROJECTS_TABLE . " p
  5.                 LEFT JOIN " . CORE_USERS_TABLE . " c ON p.client_id = c.id
  6.                 LEFT JOIN " . CORE_USERS_TABLE . " u ON p.creator = u.id
  7.                 ORDER BY created DESC";
  8.             
  9.         if($num_projects == 'all'){
  10.             $result = $db->sql_query($sql);
  11.         }else{
  12.             $result = $db->sql_query_limit($sql, $num_projects, $start_from);
  13.         }
  14.         
  15.         $projects = $db->sql_fetchrowset($result);
  16.                 
  17.         //garbage collection
  18.         $db->sql_freeresult($result);
  19.         
  20.         return $projects;         
  21.     }


The code that outputs the array is simply

Code: [ Select ]
$fb->log($project);


Im suing firephp to do my debuggin.

I dont think that will help you much tho :(
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Your log statement shows $project while your code shows $project[s], but I figure that's just a typo. I don't see anything immediately that would drop the extra column. Try swapping a var_dump in for your firephp log line and see what it shows:

Code: [ Select ]
var_dump($projects);


As good practice, you may also want to build in some SQL error checks/handling. At the least, I would run the query manually against the DB and see what comes out; just to make sure it's a code issue and not a table issue.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 65 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.