SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Auckland, New Zealand
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    issue with showing a number

    hey guys
    i have the following, im trying to add a field called stageid whihc is in the schedule table. but every time i try normal ways it errors does anyone know how i can add it to the below

    PHP Code:
    $query "(SELECT itemtime,name,tourneyid,0 AS itemtime_priv FROM tournaments WHERE itemtime>NOW() AND (UNIX_TIMESTAMP(itemtime)-UNIX_TIMESTAMP())<3600) UNION (SELECT itemtime,headline,0,itemtime_priv FROM schedule WHERE itemtime>NOW() AND itemtime_priv<=".current_security_level()." AND (UNIX_TIMESTAMP(itemtime)-UNIX_TIMESTAMP())<3600)".$holder." ORDER BY itemtime LIMIT ".$limit.";";
    $data $dbc->database_query($query); 

  2. #2
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    im trying to add a field called stageid whihc is in the schedule table
    You want to insert stagid into database but you are showing us here select query. Show us your insert query so probably we can help you.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you need to add a column to both SELECTs in the UNION --
    Code:
    SELECT itemtime
         , name
         , tourneyid
         , 0 AS itemtime_priv 
         , NULL AS stageid
      FROM tournaments 
     WHERE itemtime > CURRENT_TIMESTAMP
       AND itemtime < CURRENT_TIMESTAMP + 1 HOUR
    UNION (
    SELECT itemtime
         , headline
         , 0
         , itemtime_priv 
         , stageid
      FROM schedule 
     WHERE itemtime > CURRENT_TIMESTAMP
       AND itemtime < CURRENT_TIMESTAMP + 1 HOUR
       AND itemtime_priv <= ".current_security_level()." 
    ORDER 
        BY itemtime LIMIT ".$limit."
    notice how i optimized your itemtime WHERE conditions -- the way you were doing it was inefficient (by applying a function to a column, you prevent the optimizer from using an index on that column)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Auckland, New Zealand
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey
    thanks r937
    i tried that but sadly didnt work
    it says
    Error: Error in SQL Query
    1222 : The used SELECT statements have a different number of columns
    also i think the reason the itemtime bit was put how it was as its not a timestamp in the db im pretty sure that converts it to a timestamp? correct me if im wrong

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ShaneLangley View Post
    1222 : The used SELECT statements have a different number of columns
    count 'em

    the way i wrote it, each SELECT has 5 columns

    what did you write?

    EDIT: pls remove the parenthesis after UNION, that was a typo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Auckland, New Zealand
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "(SELECT itemtime,name,tourneyid,0 AS itemtime_priv, NULL AS stageid FROM tournaments WHERE itemtime>NOW() AND (UNIX_TIMESTAMP(itemtime)-UNIX_TIMESTAMP())<3600) UNION (SELECT itemtime,headline,0,itemtime_priv,stageid FROM schedule WHERE itemtime>NOW() AND itemtime_priv<=".current_security_level()." AND (UNIX_TIMESTAMP(itemtime)-UNIX_TIMESTAMP())<3600)".$holder." ORDER BY itemtime LIMIT ".$limit.";"
    mines the same from what i can tell

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's weird, you should definitely not get the "different number of columns" error for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Auckland, New Zealand
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats what i thought, im very confused lol

  9. #9
    SitePoint Addict X-Cart's Avatar
    Join Date
    May 2009
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shane, if you could specify tables structure here and also define what action you try to do (you have mentioned insert in your first message) that would be helpful.
    X-Cart - tens thousands live online shops worldwide
    Follow us on Twitter


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •