Simplified description of a query

[laanes]

Hello,

 

Could anyone explain what this query is doing? And why the use of the sprintf function?

 

$query = sprintf("SELECT B.*, T.option_name, T.option_type, M.value_name FROM %1\$s_options_bot AS B LEFT JOIN %1\$s_options_mid AS M ON B.value_id = M.value_id, %1\$s_options_top AS T WHERE B.option_id = T.option_id AND B.product = %2\$d ORDER BY T.option_name, M.value_name ASC", $glob['dbprefix'], $_GET['productId']);

 

Kind regards,

laanes

[Mr Hyde]

Can't tell you what the query is doing besides joining tables and selecting a couple columns, but sprintf is a native formating function for php. A lot of developers use it as a way to verify the input into a query.

 

Once you figure out what the tables contain, and what values the columns hold, the answer to your question should be more obvious. I however, have no idea what those tables contain.

 

I'll also give you another hint... when you're writing queries, or looking at them, I find it's MUCH easier to figure out what's going on if you format them a little so they're easier on the eyes like so:

$query = sprintf("
    SELECT 
        B.*, 
        T.option_name, 
        T.option_type, 
        M.value_name 
    FROM %1\$s_options_bot AS B 
    LEFT JOIN %1\$s_options_mid AS M 
    ON B.value_id = M.value_id, %1\$s_options_top AS T 
    WHERE 
        B.option_id = T.option_id AND 
        B.product = %2\$d 
    ORDER BY 
        T.option_name, 
        M.value_name ASC", 
        $glob['dbprefix'], 
        $_GET['productId']);

 

Using extra lines doesn't effect MySQL any, but it sure as hell makes it a lot easier to read for me.

[Mr Hyde]

Sorry, mispoke... it looks like it's joining ONE table to itself. If I were you I would print off the values of these variables:

$glob['dbprefix']
$_GET['productId']

 

and look at that sprintf function page to see how it inserts them into the output.

 

OR you could just print off $query and find out the same thing.

[ManiacDan]

Hyde, it's not joining any table to itself, it's joining 3 tables with the same prefix.

 

This is a cross-post by the same user from devshed, so I'm closing this topic.  Conversation can continue over there.

 

-Dan