NYCPHP Meetup

[Phil Powell]

MessageANSI SQL is not what I'm used to, but anyway I bagged the whole initial approach, wrote a new association table (nnet_produkt_storrelse_assoc) and flattened out the query to be

SELECT s.nnet_produkt_storrelse_navn
FROM nnet_produkt p, nnet_produkt_storrelse_assoc sv, nnet_produkt_storrelse s
WHERE p.nnet_produkt_id = sv.nnet_produkt_id
AND p.nnet_produkt_id = ' . $row['nnet_produkt_id'] . 
AND sv.nnet_produkt_storrelse_id = s.nnet_produkt_storrelse_id

And that worked perfectly!

Phil

  ----- Original Message ----- 
  From: Jim Hendricks 
  To: NYPHP Talk 
  Sent: Sunday, September 21, 2003 2:58 PM
  Subject: Re: [nycphp-talk] Error involving inner SELECT using mySQL - HELP


  Why can't your query be restated as:

  SELECT s.nnet_produkt_storrelse_navn 
  FROM nnet_produkt_storrelse s
       JOIN nnet_storrelse_varegruppe_assoc sv ON s.nnet_produkt_storrelse.id = sv.nnet_produkt_storrelse id 
       JOIN nnet_produkt_varegruppe v ON sv.nnet_produkt_varegruppe_id = v.nnet_produkt_varegruppe_id 
  WHERE 
        v.nnet_produkt_varegruppe_code =  'FLU17' 
        AND s.nnet_produkt_storrelse_id > 1
  ______________________________________________________________
  Jim Hendricks, President, Biz Computing, Inc
  Phone:  (201) 599-9380     Email: jim at bizcomputinginc.com
  Web: www.bizcomputinginc.com 
  Snail:  Jim Hendricks,  Biz Computing, Inc.,  255 McKinley Ave, New Milford, NJ 07646
  ______________________________________________________________

  ----- Original Message ----- 
    From: Phil Powell 
    To: NYPHP Talk 
    Sent: Sunday, September 21, 2003 2:17 PM
    Subject: Re: [nycphp-talk] Error involving inner SELECT using mySQL - HELP


    I did, it seems that mySQL is becoming more and more of a waste of database!

    Can't do subqueries, can't do foreign key constraints, even the datetime field column datatype doesn't allow for the now() function as an input parameter value in INSERT?.. so tell me what GOOD is mySQL anyway????

    I rewrote the entire database table schema relational structure to include another association table and used that, flatting out the query.  However, it's really BAD coding because I have this query generating for every single $row['nnet_produkt_id'] from the outer query results.  BAD BAD BAD.. that means a while loop INSIDE another while loop!

    Can you say performance murder!

    Phil


      ----- Original Message ----- 
      From: Jeff Siegel 
      To: 'NYPHP Talk' 
      Sent: Sunday, September 21, 2003 1:46 PM
      Subject: RE: [nycphp-talk] Error involving inner SELECT using mySQL - HELP


      You need at least ver. 4.1 of mySql. You may also be able to rewrite it.
      See: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

      Jeff Siegel
        -----Original Message-----
        From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On Behalf Of Phil Powell
        Sent: Sunday, September 21, 2003 11:54 AM
        To: NYPHP Talk
        Subject: [nycphp-talk] Error involving inner SELECT using mySQL - HELP


        Ok guys, here we go again!

        SELECT s.nnet_produkt_storrelse_navn 
        FROM nnet_produkt_storrelse s, nnet_produkt_varegruppe v, nnet_storrelse_varegruppe_assoc sv 
        WHERE s.nnet_produkt_storrelse.id = sv.nnet_produkt_storrelse id 
         AND sv.nnet_produkt_varegruppe_id = v.nnet_produkt_varegruppe_id 
         AND sv.nnet_produkt_varegruppe_id IN (
           SELECT nnet_produkt_varegruppe_id 
           FROM nnet_produkt_varegruppe
           WHERE nnet_produkt_varegruppe_code =  'FLU17' 
         )
         AND s.nnet_produkt_storrelse_id > 1

        This produces the following error:

        You have an error in your SQL syntax near 'id AND sv.nnet_produkt_varegruppe_id = v.nnet_produkt_varegruppe_id AND sv.nnet_' at line 1

        I don't see anything wrong with this query, it's standard inner SELECT stuff!  I need help... again!

        Phil


--------------------------------------------------------------------------


      _______________________________________________
      talk mailing list
      talk at lists.nyphp.org
      http://lists.nyphp.org/mailman/listinfo/talk



----------------------------------------------------------------------------


    _______________________________________________
    talk mailing list
    talk at lists.nyphp.org
    http://lists.nyphp.org/mailman/listinfo/talk



------------------------------------------------------------------------------


  _______________________________________________
  talk mailing list
  talk at lists.nyphp.org
  http://lists.nyphp.org/mailman/listinfo/talk
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20030921/9026b6ce/attachment.html>