NYCPHP Meetup

[Phil Powell]

MessageSee below, Tim, I'm not mistaken, you're not getting the full scope of my issues.

Phil
  ----- Original Message ----- 
  From: Tim Gales 
  To: 'NYPHP Talk' 
  Sent: Monday, September 22, 2003 12:58 PM
  Subject: RE: [nycphp-talk] Error involving inner SELECT using mySQL - HELP


  You say you can't do sub-queries



  But I think if you changed 

  ' AND sv.nnet_produkt_varegruppe_id IN (

     SELECT nnet_produkt_varegruppe_id 

     FROM nnet_produkt_varegruppe

     WHERE nnet_produkt_varegruppe_code =  'FLU17' 

   )'

  To

   AND sv.nnet_produkt_varegruppe_id = (

     SELECT nnet_produkt_varegruppe_id 

     FROM nnet_produkt_varegruppe

     WHERE nnet_produkt_varegruppe_code =  'FLU17' 

   )



  You might have better luck.



  I'll remember that but right now they're talking about a complete RDM redesign so this one is a moot exercise until further notice.



  You say you can't do foreign key constraints -



  From the manual:

  "Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data. 

  The syntax of a foreign key constraint definition in InnoDB: 

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)                  REFERENCES table_name (index_col_name, ...)                  [ON DELETE {CASCADE | SET NULL | NO ACTION                              | RESTRICT}]                  [ON UPDATE {CASCADE | SET NULL | NO ACTION                              | RESTRICT}]Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan. "



  That's fine.  You didn't ask me what version mySQL this is.  They don't OFFER InnoDB as table type for the version of mySQL that is running on the remote server.  So I STILL can't do foreign key constraints.



  You say the now() function is not allowed to be input into  datetime columns

  While that is true you might want to read up on timestamp column types



  Timestamps can (under most circumstances) be set to NULL or now().



  That's fine, for timestamps, but not for datetime.  Again, I don't have this problem in Oracle, sorry to be a db snob.



  In short, I think you are mistaken. 



  You said you added an associative table to "flatten out" the query and that it was BAD.



  Well when you make a sub-query, the database makes the equivalent of a temp table underneath the covers, from which it can do the selection. This means you should only use sub-queries on ad hoc requests. At other times (for routine retrievals) it is better to set up your own table from which to do the selections, because you can create the type of table it is and specifically put indexes where they are needed.



  In short, I don't agree with you. 



  That's fine.  You don't have to.  But before you go into slamming my theories realize the fact that I am working with a remote server that has an older version of mySQL than what your solutions can accommodate, therefore, again, mySQL is inadequate in this case.



  Phil

  T. Gales & Associates 
  Helping People Connect with Technology 
  http://www.tgaconnect.com 

  -----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 2:17 PM
  To: NYPHP Talk
  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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20030922/52025b24/attachment.html>