May 11, 2012

Set variable from alias in SQL query

Question by Sherif

I have a simple JOIN query:

$lstCheck = $dbWHMCS->query('SELECT * FROM tblmonitorports mp 
                            INNER JOIN tblmonitorhosts h ON h.hst_id = port_mon 
                            INNER JOIN tblhosting ho ON ho.id = h.hst_serverid
                            INNER JOIN tblclients cl ON cl.id = ho.userid');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['ho.id'];
            $clientid = $data['cl.id'];
}

My problem is that I have an “id” column in both the tblhosting and tblclients tables, so my variables both have the same id. I tried to set it using an alias in the example above (ho. and cl.) but it doesn’t work. How can I do this in the example above?

Thank you!

Answer by Mirko

How about this? (a bit rusty on php details, but this should do it):


$lstCheck = $dbWHMCS->query('SELECT ho.id hid, cl.id cid FROM tblmonitorports mp 
                            INNER JOIN tblmonitorhosts h ON h.hst_id = port_mon 
                            INNER JOIN tblhosting ho ON ho.id = h.hst_serverid
                            INNER JOIN tblclients cl ON cl.id = ho.userid');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hid'];
            $clientid = $data['cid'];
}

Answer by Starx

You are selecting the records, with a wild card *, so you can’t select the fields like that.

As per your query h.hst_serverid & ho.userid have the exact same value as you want. SO simply do this

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hst_serverid'];
            $clientid = $data['userid'];
}

However, selecting specific rows might sound better too

$lstCheck = $dbWHMCS->query('SELECT ho.id hid, cl.id cid, ....');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hid'];
            $clientid = $data['cid'];
}
May 4, 2012

Zend 1.10 place websites in virtual subdirectories

Question by Julien

I have the following situation:
We have a webapp built with Zend Framework 1.10 that is available under www.domain.com/webapp

On the server filesystem, we really also have the webapp deployed in /srv/www/webapp

Now, for some reasons I can’t detail too much, the project manager has requested, now that the app is finished, that each client recieves his own url litteraly.
So we would have:
www.domain.com/webapp/client1
www.domain.com/webapp/client2

Normally, what start after the webapp/ would be the controllers, actions and so forth from zend.

Therefore the question: is there a quick way in apache to create these virtual subdirectories, as in the example, client1, client2 ?

I guess it should be possible with url rewriting ?

Thanks in advance

Answer by Starx

Rather than creating virtual directories, this can be solved by creating a specific route with Zend_Route. Assuming, controller as User and the action to pass the name would be view, then

$route = new Zend_Controller_Router_Route(
    'webapp/:username',
    array(
        'controller' => 'user',
        'action'     => 'view',
        'username'   => 'defaultuser'
    )
);
...

Please fill the form - I will response as fast as I can!