NYCPHP Meetup

[Tim Gales]

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.

 

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. "

 

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().

 

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. 

 

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 <mailto:jsiegel1 at optonline.net>  

To: 'NYPHP Talk' <mailto:talk at lists.nyphp.org>  

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20030922/d0b194ff/attachment.html>