SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Location
    California, US
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Syntax question.

    I am getting an error in my statement and I cannot figure out why.

    Here is my select statement:
    PHP Code:
    $today date('Y-m-d');
    $arrCompanies =  func_query(SELECT companies.company_namecompany_dates.day FROM companiescompany_dates WHERE companies.companyid IN (SELECT DISTINCT companyid FROM company_dates WHERE date >= $today)); 
    Here is my error:
    PHP Code:
    INVALID SQL1064 You have an error in your SQL syntaxCheck the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT companyid FROM company_dates WHERE date >
    SQL QUERY FAILURE: SELECT DISTINCT company_name, xcart_company_dates.day FROM xcart_companies, xcart_company_dates WHERE xcart_companies.companyid IN (SELECT DISTINCT companyid FROM company_dates WHERE date >= 2005-06-12) 
    Now if I query the IN part seperatly

    PHP Code:
    $arrTemp func_query(SELECT DISTINCT companyid FROM company_dates WHERE date >= $today)); 
    It returns:

    PHP Code:
    Array ( [0] => Array ( [companyid] => ) [1] => Array ( [companyid] => ) ) 
    I am assuming I am writing the IN part wrong. What I want to happen is for the first query to select those fields WHERE the companyid of that row is IN the array or select statement in the ()'s.

    So if I hard coded it would look like:
    PHP Code:
    $today date('Y-m-d');
    $arrCompanies =  func_query(SELECT companies.company_namecompany_dates.day FROM companiescompany_dates WHERE companies.companyid IN (1,9)); 
    Any ideas on how to get this result?

    If I type out the select statment is would be something like:

    SELECT company_name and date value FROM companies, company_date tables WHERE companyid value is in the result of (Select distinct companyid FROM company_dates WHERE date field is greater than or equal to today's date)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,242
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    source of error: use of a feature not supported by your version of mysql

    solution: upgrade, or rewrite the query

    my advice: write two queries in a row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Location
    California, US
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem ended up being I forgot my select statement was producing a multi dimensional array.

    Solution was to take the temp array and strip it to a single row array then check that against the companyid in the second array

    PHP Code:
    $today date('Y-m-d');
    # select all companyid's with dats greater then today
    $arrDates func_query("SELECT DISTINCT companyid FROM company_dates WHERE date > $today ORDER by date");

    # push id's into non multidimensional array
    $arrCompID = array();
    for(
    $i=0$i count($arrDates); $i++){
        
    $ID $arrDates[$i]["companyid"];
        
    array_push($arrCompID$ID);
    }
    $imploded implode(","$arrCompID);

    # select all company names and day names where companyID is in companyid array.
    $arrCompanies =  func_query("SELECT DISTINCT company_name, day FROM companies, company_dates WHERE companies.companyid IN ($imploded) AND company_dates.companyid = companies.companyid LIMIT 5"); 


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
  •