need optimized sql query, to represent dynamic table

[jimmy_sk8a]

(i'm using MySQL 5.0.18)

hello,

first of all, sorry for my english, i'm not native.

an application of our company uses 3 tables (relative linked) to represent "dynamic tables". more exactly there is a table to hold TableName, one to hold attribute names, and one to hold effective data.

an example follows:


table tblname:
-------------------------
¦ IDtblname ¦ Name ¦
¦=======================¦
¦ 1 ¦ Guestbook ¦
¦-----------------------¦
¦ 2 ¦ Gallery ¦
-------------------------

table tblattr:
---------------------------------------
¦ IDtblattr ¦ IDtblname ¦ Name ¦
¦=====================================¦
¦ 1 ¦ 1 ¦ Nickname ¦
¦-------------------------------------¦
¦ 2 ¦ 1 ¦ E-Mail ¦
¦-------------------------------------¦
¦ 3 ¦ 1 ¦ Message ¦
¦-------------------------------------¦
¦ 4 ¦ 1 ¦ Timestamp ¦
¦-------------------------------------¦
¦ 5 ¦ 2 ¦ ImgSrcSmall ¦
¦-------------------------------------¦
¦ 6 ¦ 2 ¦ ImgSrcBig ¦
¦-------------------------------------¦
¦ 7 ¦ 2 ¦ Comment ¦
---------------------------------------

table tbldata:
----------------------------------------------------------------
¦ IDtbldata ¦ IDtblattr ¦ rowid ¦ data ¦
¦==============================================================¦
¦ 1 ¦ 1 ¦ 1 ¦ Peter ¦
¦--------------------------------------------------------------¦
¦ 2 ¦ 2 ¦ 1 ¦ peter@lala.net ¦
¦--------------------------------------------------------------¦
¦ 3 ¦ 3 ¦ 1 ¦ Hallo, meine Nachricht ... ¦
¦--------------------------------------------------------------¦
¦ 4 ¦ 4 ¦ 1 ¦ 1125518784 ¦
¦--------------------------------------------------------------¦
¦ 5 ¦ 1 ¦ 2 ¦ Jimmy ¦
¦--------------------------------------------------------------¦
¦ 6 ¦ 2 ¦ 2 ¦ jimmy@your.com ¦
¦--------------------------------------------------------------¦
¦ 7 ¦ 3 ¦ 2 ¦ Testnachricht, blabla ... ¦
¦--------------------------------------------------------------¦
¦ 8 ¦ 4 ¦ 2 ¦ 1125910044 ¦
----------------------------------------------------------------


the resultant table from tblName = "Guestbook" could than be:


-----------------------------------------------------------------------------------
¦ rowid ¦ Nickname ¦ E-Mail ¦ Message ¦ Timestamp ¦
¦=================================================================================¦
¦ 1 ¦ Peter ¦ peter@lala.net ¦ Hallo, meine Nachricht ... ¦ 1125518784 ¦
¦-----------------------------------------------------------------¦---------------¦
¦ 2 ¦ Jimmy ¦ jimmy@your.com ¦ Testnachricht, blabla ... ¦ 1125910044 ¦
-----------------------------------------------------------------------------------


now, the user should have the possibility to set up queries to the resultant table, as if it would be real. he doesn't know about a relative representation of his table in the real db.

a function in the application interprets the WHERE clause of the User Query, and generates a correct query fitting the relative representation of the table.

up to now, this works as long as there are only ORs in the WHERE clause, eg.:

... WHERE (Name = 'Jimmy') OR (Message LIKE '%Hallo%')
will get..
... WHERE ((IDtblattr = 1) AND (data = 'Jimmy')) OR ((IDtblattr = 3) AND (data LIKE '%Hallo%'))

but as soon as there's an AND in the WHERE Statement, i can't make a selection, because there are more than one resulting row affected.
eg:

... WHERE (Name = 'Peter') AND (Message LIKE '%Hallo%')

i've found a very ugly, stupid and low solution, takes up 12 seconds to resolve:


SELECT
DISTINCT tbldata.rowid AS IDrow,
(SELECT data FROM tbldata WHERE (IDtblattr = 1) AND (rowid = IDrow) LIMIT 1) AS `Name`,
(SELECT data FROM tbldata WHERE (IDtblattr = 2) AND (rowid = IDrow) LIMIT 1) AS `E-Mail`,
(SELECT data FROM tbldata WHERE (IDtblattr = 3) AND (rowid = IDrow) LIMIT 1) AS `Message`,
(SELECT data FROM tbldata WHERE (IDtblattr = 4) AND (rowid = IDrow) LIMIT 1) AS `Timestamp`
FROM
`tbldata`
WHERE
rowid IN
(
SELECT rowid FROM tbldata WHERE (IDtblattr=1) AND
(data = 'Peter') AND
(rowid IN
(
SELECT rowid FROM tbldata WHERE (IDtblattr = 3) AND
(data LIKE '%Hallo%')
)
)
)


as you certainly know, the WHERE clause must be like that, because i can't access the subselected attributes.

i know, this is very ugly, and i'm searching for a better solution. please help me to find one! perhaps is there a solution with GROUP BY, or JOIN? i'm not very familiar with this kind of statements.

(i can't use views, i think, because i'm using the query later for SELECTing, UPDATEing and DELETEing entries)

many many thanks in advance
Jimmy

[chazzy]

yes you can use views.

the first thing i would tell you is to create as much of this in views as possible.

your updates/inserts have nothing to do with the views in that sense. you can write a stored procedure to handle your insert/update.

[jimmy_sk8a]

yes you can use views.

the first thing i would tell you is to create as much of this in views as possible.

your updates/inserts have nothing to do with the views in that sense. you can write a stored procedure to handle your insert/update.

I tried following view:
CREATE VIEW Guestbook AS
SELECT
DISTINCT tbldata.rowid AS IDrow,
(SELECT data FROM tbldata WHERE (IDtblattr = 1) AND (rowid = IDrow) LIMIT 1) AS `Name`,
(SELECT data FROM tbldata WHERE (IDtblattr = 2) AND (rowid = IDrow) LIMIT 1) AS `E-Mail`,
(SELECT data FROM tbldata WHERE (IDtblattr = 3) AND (rowid = IDrow) LIMIT 1) AS `Message`,
(SELECT data FROM tbldata WHERE (IDtblattr = 4) AND (rowid = IDrow) LIMIT 1) AS `Timestamp`
FROM
`tbldata`

But the VIEW gave me strange results, when i do a SELECT * FROM Guestbook. Only the first row is shown, but then repeated as many times as it haves datarows in tbldata. If i only extract the SELECT Statement, the Results are correct; so there must be a difference how does VIEWS (mysql) and a normal SELECT handles this Query....

... and another problem is, the "dynamic tables" are edited by the user, so this approach (above) is only for one specific table. But what happens if there's a new dynamic table created by a user (an insert in tblname)? --> i think, this results in many VIEWS, one for each "dynamic table", is that correct?

so, is there any other approach?

thx in advance

[chazzy]

you only get one row because you're only allowing 1 row based on your query.

What happens when you use select distinct tbldata.rowid from tabldata ?


... and another problem is, the "dynamic tables" are edited by the user, so this approach (above) is only for one specific table. But what happens if there's a new dynamic table created by a user (an insert in tblname)? --> i think, this results in many VIEWS, one for each "dynamic table", is that correct?

Use a stored procedure to create the table, then create the view, then when you request the data in the stored procedure return the data in the view.

[jimmy_sk8a]

yes, only selecting distinct tbldata.rowid works.

am i wrong? distinct only reduces same multiple values to one value, this shouldn't give me only one row, because only SELECTing works great, just the VIEW thing went wrong.


Use a stored procedure to create the table, then create the view, then when you request the data in the stored procedure return the data in the view.


... yes, but than i rather use real tables,...

[chazzy]

yes, only selecting distinct tbldata.rowid works.

am i wrong? distinct only reduces same multiple values to one value, this shouldn't give me only one row, because only SELECTing works great, just the VIEW thing went wrong.



you're right, but then you use limit 1 to limit it again. you need to somehow force a restriction of primary key over the multiple columns and drop your LIMIT 1 keyword.




... yes, but than i rather use real tables,...

I don't get this.