Need MySQL query help

General discussion about netjukebox
Post Reply
User avatar
wbartels
netjukebox developer
Posts: 872
Joined: Thu Nov 04, 2004 3:12 pm
Location: Netherlands
Contact:

Need MySQL query help

Post by wbartels »

Code: Select all

$query = mysql_query('SELECT
            configuration_httpq.httpq_host,
            configuration_httpq.httpq_port,
            configuration_httpq.httpq_pass,
            configuration_httpq.media_share,
            configuration_httpq.httpq_id
            FROM configuration_httpq, configuration_session
            WHERE configuration_session.session_id = "' . $cfg['session_id'] . '"
            AND configuration_httpq.httpq_id = configuration_session.httpq_id
            ORDER BY configuration_httpq.name');
How can I change this query so that I also get the first result (ORDER BY configuration_httpq.name) when configuration_session.httpq_id is not equel to any configuration_session.httpq_id?
BigBossMan
User
Posts: 33
Joined: Wed Mar 30, 2005 9:27 am
Location: SLC, Utah, USA

Post by BigBossMan »

Code: Select all

SELECT  
   configuration_httpq.httpq_host,  
   configuration_httpq.httpq_port,  
   configuration_httpq.httpq_pass,  
   configuration_httpq.media_share,  
   configuration_httpq.httpq_id  
   FROM configuration_httpq LEFT OUTER JOIN configuration_session 
      ON configuration_httpq.httpq_id = configuration_session.httpq_id 
   WHERE configuration_session.session_id = "' . $cfg['session_id'] . '"  
   ORDER BY configuration_httpq.name
Maybe an outer join?
Not sure how you can order by something that is not in the select.
User avatar
wbartels
netjukebox developer
Posts: 872
Joined: Thu Nov 04, 2004 3:12 pm
Location: Netherlands
Contact:

Post by wbartels »

BigBossMan, Thanks for the tip.
Your example didn't work, but it could be a good starting point.
Now it is time to get my MySQL book back :wink:

I found an ugly workaround, for the time being:

Code: Select all

$query = mysql_query('SELECT
                configuration_httpq.httpq_host,
                configuration_httpq.httpq_port,
                configuration_httpq.httpq_pass,
                configuration_httpq.media_share,
                configuration_httpq.httpq_id
                FROM configuration_httpq, configuration_session
                WHERE configuration_session.session_id = "' . $cfg['session_id'] . '"
                AND configuration_httpq.httpq_id = configuration_session.httpq_id');
$configuration_httpq = mysql_fetch_array($query);
if (!isset($configuration_httpq['httpq_id']))
    {
    $query = mysql_query('SELECT
                    httpq_host,
                    httpq_port,
                    httpq_pass,
                    media_share,
                    httpq_id
                    FROM configuration_httpq
                    ORDER BY name');
    $configuration_httpq = mysql_fetch_array($query);
    }
BigBossMan
User
Posts: 33
Joined: Wed Mar 30, 2005 9:27 am
Location: SLC, Utah, USA

Post by BigBossMan »

Sorry - SQL has never been my strong point.

This query, should you be able to execute it at the CL against MySQL or is this requiring stuff from inside php ?

BBM
User avatar
wbartels
netjukebox developer
Posts: 872
Joined: Thu Nov 04, 2004 3:12 pm
Location: Netherlands
Contact:

Post by wbartels »

BigBossMan wrote:Sorry - SQL has never been my strong point.

This query, should you be able to execute it at the CL against MySQL or is this requiring stuff from inside php ?

BBM
I can run it from inside PHP.
So it is working.
Still, one query would be nicer.
Post Reply