时间戳

首页 > 解决方案 > Coldfusion 将数据库中的结果与商店信息和商店营业时间合并，我知道我错过了

coldfusion - Coldfusion 将数据库中的结果与商店信息和商店营业时间合并，我知道我错过了

问题描述

至少可以说，我对 QofQ 的经验是有限的。我真的可以使用一些帮助，而不是给任何借口。先感谢您。为了让事情更清楚，我摆脱了许多简单的搜索要求，但不知不觉 20 并不是选择企业的唯一标准。

我有两个数据库，一个用于存储商店及其信息，另一个用于存储营业时间。理想情况下，我想将商店的结果与商店营业时间合并。注意：并非所有商店都有营业时间，而且商店营业时的日期或营业时间也不相同，因此当我查找营业时间表时，我会从列表中查找与“businessID”和“dayWeek”匹配的结果“营业时间”数据库。

数据库：

  1. “businesses”包含 id [Key]、bus_name、bus_address、bus_city、bus_phone。我将结果限制为每页 20 个（显然 20 被变量替换）

数据示例：

  1. “hoursopen”包含 id [Key]、“businessID”匹配数据库“businesses”id [Key]、timeZone（调整时区）、dayWeek（仅选择今天的小时数）、openTime 和 closeTime 数据示例：

代码：

<cfquery name="businesses" datasource="businesses"> 
         SELECT id, bus_name, bus_address, bus_city, bus_phone
         FROM businesses
         WHERE id < 20
</cfquery>
            
<!-- 1st Query businesses (id) that were found to search in the hoursopen database, 
note that the two results are not in the same order-->
            <cfscript>
                businessResults=ValueList(businesses.id)
            </cfscript>  
            
<cfquery name="hoursopen" datasource="hoursopen"> 
    SELECT *
    FROM hoursopen
    WHERE businessID IN ( 
                        <cfqueryparam 
                            value="#businessResults#" 
                            cfsqltype="CF_SQL_INTEGER"
                            list="yes" 
                            />
                            ) AND dayWeek = dayOfWeek(Now())      
</cfquery>

<!-- In the results I bolded the BusinessID to Illustrate the relationship.-->

<p>------ BUSINESS LOCATION & PHONE ------ </p>
 <cfoutput query="businesses">
            <strong>Business ID: #id#</strong><br/>
            Business Name: #bus_name#<br/>
            Address: #bus_address#<br/>
            City: #bus_city#<br/>
            Phone: #bus_phone#<br/>
            <hr/>    
            </cfoutput>
    
<p>------ BUSINESS HOURS ------ </p>
<cfoutput query = "hoursopen">
     <p>ID: #id# | <strong>Business ID: #businessID# </strong>| Time Zone: #timeZone# | 
        Day of the Week: #dayWeek# | Store Opens: #openTime# | Store Closes: #closeTime#</p>
     <hr/>
</cfoutput>```

标签： coldfusioncoldfusion-2016

解决方案

如果我按照您的描述进行操作，我可能会按照以下方式进行操作。基本上我会运行业务查询，将这些结果放入一个结构数组中。当我这样做时，我会得到每项业务的时间，并将其放入一系列结构中。代码如下所示。请知道我根本没有测试过，但这是我做这种工作的典型方式：

    <cffunction name="getBusinessHours" returntype="array">
        <cfargument name="_id" type="string" required="yes">
        <cfargument name="_dayWeek" type="string" required="yes">

        <cfquery name="_qHours">
            select  * from  hoursopen
            where   businessId = <cfqueryparam value="#_id#" cfsqltype="integer"> and
                    dayWeek = <cfqueryparam value="#_dayWeek#" cfsqltype="varchar">
            order by 
                    dayWeek
        </cfquery>

        <cfset _hours = arrayNew(1)>
        <cfloop query="_qHours">
            <cfset _hour = structNew()>
            <cfset _hour['id'] = id>
            <cfset _hour['timeZone'] = timeZone>
            <cfset _hour['dayWeek'] = dayWeek>
            <cfset _hour['openTime'] = openTime>
            <cfset _hour['closeTime'] = closeTime>

            <cfset arrayAppend(_hours, _hour)>
        </cfloop>

        <cfreturn _hours>
    </cffunction>


    <cfquery name="_qBusinesses">
        select * from businesses   //limit query as needed
    </cfquery>


    <cfset _businesses = ArrayNew(1)>

    <cfloop query="_qBusinesses">
        <cfset _business = structNew()>
        <cfset _business['id'] = id>
        <cfset _business['name'] = bus_name>
        <cfset _business['address'] = bus_address>
        <cfset _business['city'] = bus_city>
        <cfset _business['phone'] = bus_phone>

        <cfset _business['hours'] = getBusinessHours(id, dayOfWeek(Now())>

        <cfset arrayAppend(_businesses,_business)>

    </cfloop>

    <cfoutput>
        <cfloop array="_businesses" index="_b">
            <strong>Business ID: #_b.id#</strong><br/>
            Business Name: #_b.name#<br/>
            Address: #_b.address#<br/>
            City: #_b.city#<br/>
            Phone: #_b.phone#<br/>
            <hr/>    
                
            
            <p>------ BUSINESS HOURS ------ </p>
            <cfloop array="_b.hours" index="_h" >
                <p>ID: #_h.id# | <strong>Business ID: #_b.id# </strong>| Time Zone: #_h.timeZone# | 
                    Day of the Week: #_h.dayWeek# | Store Opens: #_h.openTime# | Store Closes: #_h.closeTime#</p>
                <hr/>
            </cfloop>
        </cfloop>    
    </cfoutput>

推荐阅读