Retrieve all data from columns apart from id when using MySQL

3 次查看（过去 30 天）
Gurvinder
Gurvinder 2013-7-31
Hello, I'm new to Matlab and Mysql and need a little help. I have a database which is storing the results of an analysis which repeated 150 times, thus one dataset has 150 results for the specifity etc.
I have managaged to automate it so the data goes into the columns i need, the problem i have is extracting it so it can be displayed in a GUI to later then be graphed.
I was using this to take data out of my database:
smp = exec(conn, 'select sample from smp_table') smp = fetch(smp); get(smp, 'Data')
I know i can switch te "sample" to a "*" to get everything from the table, which is what i want, but I DON't want the primary key and cannot seem to execute this.
This is the code i am using to access all the data i want via command line (terminal) for mysql:
SET @sql = CONCAT("SELECT ", (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'idTestTbl,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TestTbl' AND TABLE_SCHEMA = 'MatSch_2'), ' FROM TestTbl');
prepare stmt1 from @sql;
execute stmt1;
Now i have tried something like this in Matlab, but cannot get it to work:
sql = 'SET @sql = CONCAT("SELECT ", (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), "idTestTbl,", '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "TestTbl" AND TABLE_SCHEMA = "MatSch_2"), "FROM TestTbl"); prepare stml from @sql'
smp = exec(conn, sql) smp = fetch(smp); get(smp, 'Data')
Can anyone point me in the right direction? Ultimatly i just want to extract all the contents of my table excluding the primary key column...would like a small script for this as i have 150 columns
Thank you in advance
P.S on Matlab 7.6.0 R2008A

请先登录，再回答此问题。

采纳的回答

Sven
Sven 2013-7-31
编辑：Sven 2013-7-31
Hi Gurvinder,
Can I suggest that I think it would be easier for you to simply get all the fields, and then just discard the one you don't want. Here's some MATLAB code that does exactly that:
% Make a simple SQL query
yourTable = 'smp_table';
keyField = 'ID';
SQLstr = sprintf('SELECT * FROM %s',yourTable);
% Get the data
curs = exec(conn, sqlStr);
curs = fetch(curs,0);
myData = curs.Data;
% Get the fieldnames of the data
myFields = cellfun(@char, get(rsmd(resultset(curs)), 'ColumnName'),'Un',0);
% Drop the primary key from the data (and fields in case you need them)
keyFldMask = strcmpi(keyField, myFields);
myData(:,keyFldMask) = [];
myFields(keyFldMask) = [];
The code above is relatively simple compared to the mysql concatenations from the table schema, and achieves the exact same result.
Is that an acceptable solution? I always prefer to muck about with data in MATLAB when SQL gets too messy :)
  3 个评论
显示 1更早的评论
Sven
Sven 2013-8-1
Yes, note at the top where I wrote:
keyField = 'ID'
You need to change 'ID' to whatever the name of your actual primary key field is. If you do that, then the keyFldMask variable will have exactly one non-zero entry (matching the column you want to discard) and the next two lines will discard that column.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by