Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

SQL Statement not working

SELECT itemid, CONCAT(  "insert into tblmembers (ID,", GROUP_CONCAT( b.fieldname ) ,  ") values (", itemid,  ",", GROUP_CONCAT(  "'", data_txt,  "'" ) ,  ");" )
FROM jos_sobi2_fields_data AS a
JOIN tablefields AS b ON (
a.`fieldid` = b.`fieldid`
GROUP BY itemid
ORDER BY itemid
LIMIT 0 , 30

Can anyone tell me why this statement doesn't create records in the table 'tblmembers'
tblmembers & jos_sobi2_fields_data & tablesfields all exist and are structured properly.

1 Solution
Bill BachPresidentCommented:
The second field is attempting to create a SQL INSERT statement, but the INSERT statement is never run. The out of this is the series of INSERTs, which you should then copy into a script file -- then run the script.

If you really want the INSERTs to fire, then this should look more like INSERT INTO table SELECT ... syntax.
Mark WillsTopic AdvisorCommented:
It looks like you are trying to display a built up insert query / command - like dynamic SQL.

While it will not insert, what does it produce ?

Chances are you need to copy the result of that query and then run that.

It probably produces an output like :

[itemid]   [unknown]
101         insert into tblmembers (id, field1, field2, field3) values (101, 'data 1', 'data 2', 'data 3')
102         insert into tblmembers (id, field1, field2, field5) values (102, 'data 1', 'data 2', 'data 5')

Open in new window

Which is the good bit about a group_concat() function - for items that have columns listed vertically, it can kind of "pivot" the columns into a comma delimited list - ideal for creating dynamic sql. Have a look at : http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

But If the column names for tblmembers are already known, then you can achieve the same results by first doing an insert, then update the columns accordingly.

INSERT tblmembers (id)
SELECT distinct a.itemid
FROM jos_sobi2_fields_data AS a 
JOIN tablefields AS b ON a.fieldid = b.fieldid

Open in new window

then simply update the columns...

update tblmembers set field1 = data1
FROM jos_sobi2_fields_data AS a 
JOIN tablefields AS b ON a.fieldid = b.fieldid
WHERE tblmembers.ID = a.itemid
AND b.fieldname = 'field1'

Open in new window

of course you would need to repeat for every field.

If however you are using a programming language, then you can use the group_concat() to return the string needed to do the insert cleanly...

Kevin CrossChief Technology OfficerCommented:
I agree with above on the reason. You will need to use a MySQL prepared statement to run the dynamic sql created - http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

What exactly are you wanting to do? Just note that UPDATE WITH JOIN in MySQL is in the UPDATE, i.e.:
SET a.column = b.column
WHERE a.key = b.key

Open in new window

UPDATE a JOIN b ON a.key = b.key
SET a.column = b.column

Open in new window

Otherwise, the syntax shown by Mark should work.
joomlaAuthor Commented:
Thanks, helps me a great deal.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now