#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Post Embedded queries??


    Hi,
    I have 2 mysql db tables as below:
    1.
    CREATE TABLE `event_events` (
    `eventID` int(255) NOT NULL AUTO_INCREMENT,
    `eventName` varchar(255) DEFAULT '',
    `eventSummary` text,
    `venueId` int(11) NOT NULL DEFAULT '0',
    `eventWebsite` varchar(255) NOT NULL,
    `eventStartDateTime` datetime NOT NULL,
    `eventEndDateTime` datetime NOT NULL,
    `eventDisplayOrder` int(11) NOT NULL DEFAULT '10',
    `eventPrice` decimal(10,2) NOT NULL DEFAULT '0.00',
    `eventIsActive` enum('yes','no') NOT NULL DEFAULT 'yes',
    PRIMARY KEY (`eventID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

    2.
    CREATE TABLE `event_orders` (
    `orderId` int(10) NOT NULL AUTO_INCREMENT,
    `orderUserId` int(10) NOT NULL,
    `orderEventId` int(10) NOT NULL,
    `orderEventPrice` decimal(7,2) NOT NULL,
    `orderEventQty` int(3) NOT NULL,
    `orderTotalAmount` decimal(7,2) NOT NULL DEFAULT '0.00',
    `orderDate` datetime NOT NULL,
    `orderPaid` enum('yes','no') NOT NULL DEFAULT 'no',
    PRIMARY KEY (`orderId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    I am building an event booking system for a customer and I have been given a list of constraints by my manager.
    To populate the value of orderTotalAmount in the 'event_orders' table:
    1. I take the value of orderEventQty submitted from a form
    2. Using the eventId passed in the form I am to get the event price from the table event_events
    3. I am not to pass a value for this (eventPrice) in the form
    4. Using the orderEventQty and the eventPrice I need to multiply them to get the orderTotalAmount and put this value into the event_orders table.

    I am using coldfusion and mysql for this, and the code I am using is:

    <cfparam name="FORM.orderUserId" default="0" />
    <cfparam name="FORM.orderEventId" default="0" />
    <cfparam name="FORM.orderEventPrice" default="0.0" />
    <cfparam name="FORM.orderEventQty" default="0" />

    <cfquery name="InsertOrder" result="InsertOrder" datasource="#APPLICATION.db_datasource#" username="#APPLICATION.db_username#" password="#APPLICATION.db_password#">
    INSERT INTO event_orders (
    orderUserId,
    orderEventId,
    orderEventPrice,
    orderTotalAmount,
    orderEventQty,
    orderDate
    )
    VALUES (
    <cfqueryparam value="#FORM.orderUserId#" cfsqltype="numeric">,
    <cfqueryparam value="#FORM.orderEventId#" cfsqltype="numeric">,
    (
    SELECT eventPrice
    FROM event_events
    WHERE eventId = <cfqueryparam value="#FORM.orderEventId#" cfsqltype="numeric">
    ),
    (
    SELECT SUM (
    (
    SELECT eventPrice
    FROM event_events
    WHERE eventId = <cfqueryparam value="#FORM.orderEventId#" cfsqltype="numeric">
    ) * <cfqueryparam value="#FORM.orderEventQty#" cfsqltype="numeric"> )AS totalAmount
    ),
    <cfqueryparam value="#FORM.orderEventQty#" cfsqltype="numeric"/>,
    NOW()
    )
    </cfquery>

    So, my question is:
    What is the best way to do this and can somebody post an example please, thanks
    Conor
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Code:
    <cfparam name="FORM.orderUserId" default="0" />
    <cfparam name="FORM.orderEventId" default="0" />
    <cfparam name="FORM.orderEventPrice" default="0.0" />
    <cfparam name="FORM.orderEventQty" default="0" />
    
    <cfquery name="InsertOrder" 
             result="InsertOrder" 
             datasource="#APPLICATION.db_datasource#" 
             username="#APPLICATION.db_username#" 
             password="#APPLICATION.db_password#">
    INSERT 
      INTO event_orders 
         ( orderUserId
         , orderEventId
         , orderDate
         , orderEventPrice
         , orderEventQty
         , orderTotalAmount )
    SELECT <cfqueryparam value="#FORM.orderUserId#" cfsqltype="numeric">
         , <cfqueryparam value="#FORM.orderEventId#" cfsqltype="numeric">
         , NOW()
         , eventPrice
         , <cfqueryparam value="#FORM.orderEventQty#" cfsqltype="numeric">
         , eventPrice * 
           <cfqueryparam value="#FORM.orderEventQty#" cfsqltype="numeric">
      FROM event_events
     WHERE eventId = 
           <cfqueryparam value="#FORM.orderEventId#" cfsqltype="numeric">
    </cfquery>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo