调整SQL行以包括集合 [英] Adjusting SQL line to include a collection

问题描述

我有一个函数调用资源的一天，我已经复制并更改为编辑精选。该函数在DB中查找，并根据值和今天日期获取ramdom映像。

I have a function call resource of the day which I have duplicated and changed to be called Editors Picks. The function looks in the DB and grabs a ramdom image based on value and todays date.

这是SQL行：

sql_value(
    "select resource value 
     from resource_data 
     where resource > 5 and 
           resource_type_field=$rotd_field and 
           value like '" . date("Y-m-d") . "%' limit 1;"
   ,0);

我想尝试并调整这一行来拉一个集合，拉取收集图片：

I would like to try and adapt this line to pull in a collection instead, this is the SQL line that pulls in a collection image:

sql_query("select collection.ref, 
                  collection.home_page_publish, 
                  collection.home_page_text,
                  collection.home_page_image,
                  resource.thumb_height,
                  resource.thumb_width 
           from collection 
           left outer join resource on collection.home_page_image=resource.ref 
           where collection.public=1 and
                 collection.home_page_publish=1"
                 .$filterClause.
           " order by collection.ref desc");

有没有人知道如何调整顶部的SQL行来拉取集合信息，

Does anyone know how to adapt the top SQL line to pull in the collection information instead, eg can I change date function to so something else?

这是2页的代码：

首页。 php

<?php

function HookEditorsPickHomeReplaceslideshow ()
    {
    include_once dirname(__FILE__)."/../inc/rotd_functions.php";

    global $baseurl, $view_title_field;

    $rotd=get_editors_pick();
    if ($rotd===false) {return false;} # No ROTD, return false to disable hook and display standard slide show.

    # Get preview width
    $sizes = get_image_sizes($rotd, true);
    foreach ($sizes as $size)
        {
        if ($size["id"]=="pre")
            {
            $width = $size["width"];
            break;
            }
        }


    # Fetch title
    $title = sql_value("select value from resource_data where resource='$rotd' and resource_type_field=$view_title_field","");

    # Fetch caption
    $caption=sql_value("select value from resource_data where resource='$rotd' and resource_type_field=18","");

    # Show resource!
    $pre=get_resource_path($rotd,false,"pre",false,"jpg");
    ?>
    <div class="HomePicturePanel" style="width: <?php echo $width ?>px; background-color:#f1f1f1; height: 409px;">
    <a onClick="return CentralSpaceLoad(this,true);" href="<?php echo $baseurl?>/pages/view.php?ref=<?php echo $rotd ?>"><img class="ImageBorder" style="margin-bottom: 0px; margin-top: 0px; border:#CCC; solid: 0px;" src="<?php echo $pre ?>" /></a>
    <br />
    <div class="ResourceOfTheDayHead">Our Resource of the day</div>

    <div class="ResourceOfTheDayText"><?php echo i18n_get_translated(htmlspecialchars($title)) ?></div>
    <div class="ResourceOfTheDayCaption"><?php echo $caption ?></div>
</div>
    <?php

    return true;
    }


?>

这是：rotd.functions.php

And this is: rotd.functions.php

<?php

function get_editors_pick()
    {
    global $rotd_field;


    # Search for today's resource of the day.
    $rotd = sql_value("select resource value from resource_data where resource>5 and resource_type_field=$rotd_field and value like '" . date("Y-m-d") . "%' limit 1;",0);
    if ($rotd!=0) {return $rotd;} # A resource was found?


    # No resource of the day fields are set. Return to default slideshow functionality.
    return false;
    }

?>

推荐答案

我不熟悉php，代码可能有用。

I am not fluent with php, but this pseudo-code may be useful.

string whereClause = "WHERE ";
for each (item in collection)
{
  whereClause = whereClause + " " + item;
}

string sqlQuery = "SELECT stuff, otherStuff FROM myTable " + whereClause + " ORDER BY thing2";

如果where子句中的项目未进行清理，SQL注入可能会出现。在网上有很多例子，如何防止这一点，但最好的方法是使用参数，而不是像上面显示的动态SQL。

The SQL injection can come if the items in the where clause are not sanitized. There are many examples on the web of how to prevent this, but the best way is to use parameters instead of dynamic SQL like I am showing above.

这篇关于调整SQL行以包括集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！