Retrieve all data from columns apart from id when using MySQL

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

Answer 1

[Sven]

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 :)

[Gurvinder]

Hello Sven,

Thank you for your response. I tried your above code out however it still returns all the columns including the PK field and not discarding it.

this line of code: myFields = cellfun(@char, get(rsmd(resultset(curs)), 'ColumnName'),'Un',0);

returns all the fields as expected

keyFldMask then returns a 0 for the total number of columns within the table

myData then returns everything as does myFields

is there something i should be replacing in the code you have given above?

Also just for building my own knowledge where you have curs = fetch(curs,0); what does the 0 represent?

[Sven]

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]

Fantastic Thank you