SQL query help

[louislsh]

EDITED: Trying a way to insert picture

 

[ctingyee]

I assume you use the MySQL and know where you upload the image to.
make the field data type as blob and use load_file when inserting. Your code should similar like this

CREATE TABLE table1(id INT, image BLOB);

INSERT INTO table1 (id, image) VALUES(1, LOAD_FILE('your_image_file_location.gif'));

 

[davidktw]

I assume you use the MySQL and know where you upload the image to.
make the field data type as blob and use load_file when inserting. Your code should similar like this

CREATE TABLE table1(id INT, image BLOB);

INSERT INTO table1 (id, image) VALUES(1, LOAD_FILE('your_image_file_location.gif'));

Generally it shouldn't be done this way, because there is often no assumption that the database server is in the same host as the application/web server.

Most database drivers will allow writing blob(or alternatively long text) into the database directly over network

Such mode of insertion is more appropriate without assuming the database can read the filesystem where the application write to for temporary files.

In strict system, the user process that is running the application server is not going to be the same as the process running the database server. Not even the same group, hence for the file to be accessible by both processes, that means the file must be readable by the world, which is not good security practice.

These advice is for general reading, since TS has removed the original post.

 

[louislsh]

Thank you all for the info, I am trying to code the following table which in a single column contain data of 2 stores.
I am trying to Split it into a report which Store ABC and XYZ have their own column of data.
I did search online and i'm guessing I should read up using case expression to do what I want?

 

[davidktw]

Thank you all for the info, I am trying to code the following table which in a single column contain data of 2 stores.
I am trying to Split it into a report which Store ABC and XYZ have their own column of data.
I did search online and i'm guessing I should read up using case expression to do what I want?

First of all, I don't think what you wanted is something that should be solely performed in SQL. You are using data to create DDL while not inherently wrong, but not necessarily need to be done in SQL only.

If this is an assignment that uses an external processing system, then perform the task of building up the DDL using the programming language instead.

That means you should retrieved a unique set of STORE values as such
SELECT distinct(STORE) from QTY;

For each returned value for the above SQL, create the following DDL
SQLEXECUTE("DROP TABLE IF EXISTS report;")
$NEWTABLESQL = "CREATE TABLE report ( Article VARCHAR(255),";
FOREACH $value
$NEWTABLESQL += ", $value INTEGER";
DONE
$NEWTABLESQL += ");";
SQLEXECUTE($NEWTABLESQL);

$NEWTABLESQL should have the value
"CREATE TABLE report (Article VARCHAR(255), ABC INTEGER, XYZ INTEGER);"

However for your case, I find such approach incorrect. First DDL has limitation in schema are syntax. Your data however can be very flexible. Your data may contain some values which are not suitable as column names. Also SQL DDL is case insensitive, as such "ABC" and "Abc" will be the same which it may be consider unique for your data.

Do not be confused between pivot tables in EXCEL and true breed RDBMS. The former are dealing with data all the way, while the latter you are constructing schema using data.

I would like to recommend that instead of such approach, solve your problem at the application layer instead of the database layer.

First of all, your qty table while not structurally wrong, is having very inefficient way of storing data. There is no keys at all. Neither of your Article, Store, or Qty can be used as a key. This will make accessing the table slow since you will need to perform full table scan, should you be accessing it randomly.

Using application approach to solve your problem instead. Your 2nd table has a couple of things you need to take care. Does every article need to be found in all stores ? If not you might have some empty article id that is found in one store but not in the other and hence you will need to place the QTY as 0(ZERO).