필터 지우기
필터 지우기

Retrieve all data from columns apart from id when using MySQL

조회 수: 6 (최근 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
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.
Gurvinder
Gurvinder 2013년 8월 1일
Fantastic Thank you

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Database Toolbox에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by