PHP MYSQL - Many data in one column

Hi,

I need to store of 100-200 data in mysql, the data which would be separated by pipes..

any idea how to store it on mysql? should I use a single column or should I make many multiple columns? I don't know exactly how many data users will input.

I made a form, it halted at the part where multiple data needs to be stored.

Anyone know how to store multiple data in single column or is there any alternative way?

please help me..

thank you very much

From stackoverflow

• If you have a form where this data is coming from, store each input from your form into it's own separate column.

  Look for relationships in your data: sounds like you have a "has many" relationship which indicates you may want a linking table where you could do a simple join query...

  Storing multiple data in a single column will be a nightmare for queries and updates, unless you're storing XML, event then it would give me nightmares...

• Sounds like you need a join table. Have just the data you need in both tables, create a third table with the ID of both tables, then it doesn't matter if you need 100, 200, 300 or more.

• You should implement your table with an ID for the source of the data. This ID will be used to group all those pieces of similar data so you don't need to know how many you have beforehand.

  Your table columns and data could be set up like this:

  sourceID        data
  --------        ----
         1         100
         1         200
         1         300
         2         100
         3         100
         3         200
  

  When you query the database, you can just pull in all of the data with the same sourceID. With the data above, the following query would return two pieces of data.

  SELECT data
  FROM dataTable
  WHERE sourceID = 3
  

  If you have multiple tables, you'll need to associate them with each other using JOIN syntax. Say you have a main table with user data and you want to associate all of this input data with each user.

  userID    userName    otherData
  ------    --------    ---------
       1         Bob          xyz
       2         Jim          abc
       3         Sue        lmnop
  

  If you want to join data from this table (userTable) with data from the dataTable, use a query like this:

  SELECT userID, userName, data, otherData
  FROM userTable
  LEFT JOIN dataTable
  ON userTable.userID = dataTable.sourceID
  WHERE userTable.userID = 1
  

  This query will give you all of the data for the user with an ID of 1. This assumes that the sourceID in your data table is using the userID from the user table to keep track of who the extra data belongs to.

  Note that this is not the only JOIN syntax in SQL. You can learn about other types of joins here.

• Hello,

       |     | z | z1 | z2 | z3 | z4 |
   xxx | yyy | z5| z6 | z7 | z8 | z9 | kkk
       |     | ...                   |
  

  my output will have to look like this, where z-z9-... will be bunch of hunderds data stored in mysql. i tried google with join mysql, cant find much resources tho.

  I tried bill the lizard solution, i created second table and may i know how to get sourceid from first table to second one?

  thanks guys for your help. much appreciate it

  Bill the Lizard : See my edited answer for more information on SQL JOINS.

• Thanks Bill the Lizard.

  Bill the Lizard : You're welcome. I hope you got your problem solved.

Access Intranet via SSL using WebBrowser Winforms Control

I have a .Net 2.0 app that is used internally and we want to use the WebBrowser control to access some Web resources. We want to add encryption to these sites using SSL using self signed certificates.

My question is if there is way to disable all the warnings about the SSL keys coming from an untrusted source? I would like to avoid to have to install the keys in each of the workstations running the app.

Any other suggestions on how to do this are welcome.

From stackoverflow

• I do not believe there is a work around for this, you will always get the warning when accessing the above mentioned web resources via the WebBrowser control (or Internet Explorer for that matter) You could however distribute the root cert via Group Policy.

• I have a work around for this elventear. It is part of a Code Project article that I am writing that illustrates various 'tricks' you can perform when dealing with security and the WebBrowser Control. I will advise here when it is available.

• Ok, article is up - see http://www.codeproject.com/KB/shell/WebBrowserControlDialogs.aspx

  Hopefully this helps.

• You can do this by hooking dialogs (as someone above send the link) but then implementing SSL will be pointless. Because when an attack does the MITM attack you'll ignore the warnings and continue anyway. Better potion your installer might install the certificate in the first place.

Microsoft MIDL does not report an error if a typedef uses an unknown type, is it a bug?

I would like to know whether I am missing something:

//this is test.idl
typedef foo foo_t;
// end of test.idl

When I compile test.idl with the following command:

midl /W4 test.idl

I get this output

Microsoft (R) 32b/64b MIDL Compiler Version 6.00.0366
Copyright (c) Microsoft Corporation 1991-2002. All rights reserved.
Processing .\test.idl
test.idl

and I get a wrong test.h (at the bottom of this message) which has only

typedef foo_t;

where the unknown foo type was silently discarded.

I would have expected an error message stating "foo is an unknown type", am I wrong?

Do I need to pass any particular arguments to the MIDL command?

I got the same result with MIDL compiler version 7.00.0500

/* this ALWAYS GENERATED file contains the definitions for the
interfaces */

 /* File created by MIDL compiler version 6.00.0366 */
/* at Thu Nov 13 11:47:40 2008
 */
/* Compiler settings for test.idl:
    Oicf, W4, Zp8, env=Win32 (32b run)
    protocol : dce , ms_ext, c_ext, robust
    error checks: allocation ref bounds_check enum stub_data
    VC __declspec() decoration level:
         __declspec(uuid()), __declspec(selectany), __declspec
(novtable)
         DECLSPEC_UUID(), MIDL_INTERFACE()
*/
//@@MIDL_FILE_HEADING(  )

#pragma warning( disable: 4049 )  /* more than 64k source lines */

/* verify that the <rpcndr.h> version is high enough to compile this
file*/
#ifndef __REQUIRED_RPCNDR_H_VERSION__
#define __REQUIRED_RPCNDR_H_VERSION__ 475
#endif

#include "rpc.h"
#include "rpcndr.h"

#ifndef __RPCNDR_H_VERSION__
#error this stub requires an updated version of <rpcndr.h>
#endif // __RPCNDR_H_VERSION__

#ifndef __test_h__
#define __test_h__

#if defined(_MSC_VER) && (_MSC_VER >= 1020)
#pragma once
#endif

/* Forward Declarations */

#ifdef __cplusplus
extern "C"{
#endif

void * __RPC_USER MIDL_user_allocate(size_t);
void __RPC_USER MIDL_user_free( void * );

/* interface __MIDL_itf_test_0000 */
/* [local] */

typedef foo_t;

extern RPC_IF_HANDLE __MIDL_itf_test_0000_v0_0_c_ifspec;
extern RPC_IF_HANDLE __MIDL_itf_test_0000_v0_0_s_ifspec;

/* Additional Prototypes for ALL interfaces */

/* end of Additional Prototypes */

#ifdef __cplusplus
}

#endif

#endif

From stackoverflow

• I submitted the following bug to Microsoft:

  MIDL does not report an error if a typedef uses an unknown type

Stored Procedures MSSQL2005

If you have a lot of Stored Procedures and you change the name of a column of a table, is there a way to check which Stored Procedures won't work any longer?

---

Update: I've read some of the answers and it's clear to me that there's is no easy way to do this. Would it be easier to move away from Stored Procedures?

From stackoverflow

• I'm sure there are more elegant ways to address this, but if the database isn't too complex, here's a quick and dirty way:

  Select all the sprocs and script to a query window.

  Search for the old column name.

• There's a book-style answer to this, and a real-world answer.

  First, for the book answer, you can use sp_depends to see what other stored procs reference the table (not the individual column) and then examine those to see if they reference the table:

  http://msdn.microsoft.com/en-us/library/ms189487.aspx

  The real-world answer, though, is that it doesn't work in a lot of cases:

  • Dynamic SQL strings: if you're building strings dynamically, either in a stored proc or in your application code, and then executing that string, SQL Server has no way of knowing what your code is doing. You may have the column name hard-coded in your code, and that'll break.
  • Embedded T-SQL code: if you've got code in your application (not in SQL Server) then nothing in the SQL Server side will detect it.

  Another option is to use SQL Server Profiler to capture a trace of all activity on the server, then search through the captured queries for the field name you want. It's not a good idea on a production server, because the profile incurs some overhead, but it does work - most of the time. Where it will break is if your application does a "SELECT *", and then in your application, you're expecting a specific field name to come back as part of that result set.

  You're probably beginning to get the picture that there's no simple, straightforward way to do this.

• If you are only interested in finding the column usage in the stored procedure probably the best way will be do do a brute force search for the column name in the definition column sys.sql_modules table - which stores the definition for the stored procedures/functions.

• I'm a big fan of SysComments for this:

  SELECT DISTINCT Object_Name(ID) 
  FROM SysComments 
  WHERE text LIKE '%Table%'
  AND text LIKE '%Column%'
  

  Sören Kuklau : Well, looks like I've just learnt a new trick. Thanks!

  Mladen Prajdic : in 2005+ the recommended way is to use OBJECT_DEFINITION function since syscomments is deprecated

• While this will take the most work, the best way to ensure that everything works is to write integration tests.

  Integration tests are just like unit tests, except in this case they would integrate with the database. It would take some effort, but you could easily write tests that exercise each stored procedure to ensure it executes w/o error.

  In the simplest case it would just execute the sp and make sure there is no error and not be concerned about the actual results. If your tests just executed sp's w/o checking results you could write a lot of this genericly.

  To do this you would need a database to execute against. While you could setup the database and deploy your stored procs manually, the best way would be to use continuous integration to automatically get the latest code (database DDL, stored procs, tests) from your source control system, build your database, and execute your tests. This would happen every time you committed changes to source control.

  Yes it seems like a lot of work. It's a lot of work, but the payoff is also big. The ability to ensure that your changes don't break anything allows you to move your product forward faster with a better quality.

  Take a look at NUnit and NDbUnit

How to save and load different types of objects?

During coding I frequently encounter this situation:

1. I have several objects (ConcreteType1, ConcreteType2, ...) with the same base type AbstractType, which has abstract methods save and load . Each object can (and has to) save some specific kind of data, by overriding the save method.
2. I have a list of AbstractType objects which contains various ConcreteTypeX objects.
3. I walk the list and the save method for each object.

At this point I think it's a good OO design. (Or am I wrong?) The problems start when I want to reload the data:

Each object can load its own data, but I have to know the concrete type in advance, so I can instantiate the right ConcreteTypeX and call the load method. So the loading method has to know a great deal about the concrete types. I usually "solved" this problem by writing some kind of marker before calling save, which is used by the loader to determine the right ConcreteTypeX.

I always had/have a bad feeling about this. It feels like some kind of anti-pattern...

Are there better ways?

EDIT: I'm sorry for the confusion, I re-wrote some of the text. I'm aware of serialization and perhaps there is some next-to-perfect solution in Java/.NET/yourFavoriteLanguage, but I'm searching for a general solution, which might be better and more "OOP-ish" compared to my concept.

From stackoverflow

• Is this either .NET or Java? If so, why aren't you using serialisation?

  George Stocker : Serialization is correct as well. Damn brits. :-)

  Mitch Wheat : The irony is, I usually spell it with an 's', as I'm a brit!

  George Stocker : Ah ha! So we are rubbing off on you. When you start drinking coffee instead of tea, then we can claim victory!

  Bill the Lizard : LOL. I'm American, but I accidentally set my system to English without noticing that it was British English. :)

  DR : It's neither .NET nor Java :) Of course serialization would get the job done, but as does my solution. But I clarified my question.

  George Stocker : Well, the Abstract-Factory applies across all OOP languages. I'd recommend looking at the Gang of Four Design Patterns book. Also, the concept of developing with each layer doing a specific task applies to all OOP languages. It just so happens that Java and .NET are two major OOP Languages.

• If you can't simply use serialization, then I would still definitely pull the object loading logic out of the base class. Your instinct is correct, leading you to correctly identify a code smell. The base class shouldn't need to change when you change or add derived classes.

  The problem is, something has to load the data and instantiate those objects. This sounds like a job for the Abstract Factory pattern.

• There are better ways, but let's take a step back and look at it conceptually. What are all objects doing? Loading and Saving. When you get the object from memory, you really don't to have to care whether it gets its information from a file, a database, or the windows registry. You just want the object loaded. That's important to remember because later on, your maintanence programmer will look at the LoadFromFile() method and wonder, "Why is it called that since it really doesn't load anything from a file?"

  Secondly, you're running into the issue that we all run into, and it's based in dividing work. You want a level that handles getting data from a physical source; you want a level that manipulates this data, and you want a level that displays this data. This is the crux of N-Tier Development. I've linked to an article that discusses your problem in great detail, and details how to create a Data Access Layer to resolve your issue. There are also numerous code projects here and here.

  If it's Java you seek, simply substitute 'java' for .NET and search for 'Java N-Tier development'. However, besides syntactical differences, the design structure is the same.

  DR : The method names where meant as sketches to describe my problem. Of course I use better names in real applications :) But thanks for the article, I will read it!

Modifying Excel spreadsheet with .NET

Here's the problem:

I have to modify an existing Excel spreadsheet using .NET. The spreadsheet is hugely complex, and I just have to add data in some predefined areas.

I'm investigating my options, and Excel Automation/InterOp is out of the question, as I'm implementing an ASP.NET website, and Excel probably isn't installed on the server. From what I find online, InterOp is also a very expensive solution performancewise.

Creating a CSV file is also ruled out because of the complex nature of the original spreadsheet.

Currently I'm leaning towards an ADO.NET OleDb solution, but I find that mentioned very rarely (Google and Stackoverflow.com) so I'm kinda worried: What's the catch with OldDb for Excel? The only drawback I can find on MSDN so far, is that I can't create cells with formulas, but that's really not an issue in my case.

I've also considered SSIS, but that's only based on my assumption that you can use existing Excel files when you generate spreadsheets. I don't know if that possible or not.

Then there's OpenXml. It seems overly complicated compared to OldDb, plus it's still undetermined which of the older Excel versions I have to support.

Am I missing something? Are there more alternatives?

From stackoverflow

• You have still few solutions. 1) Third party component for excel files. ( i think this is most pain-les & reliable solution ) 2) Using html-table.

  ---

  1) Simply look at internet :) 2) The excel works pretty fine with html-tables. If you generate this html:

  <html><body>
  <table>
  <tr><td><b>Column A</b></td><td><b>Column B</b></td></tr>
  <tr><td><font color="red">-154</b></td><td><font size="5">hello world</font></td></tr>
  </table>
  </body></html>
  

  the excel will show it as worksheet as you formated it. I think, this is most universal way, how to create "rich" excel worksheet in internet enviroment without having excel instaled on server.

• Check out MyXls a .NET solution for reading and writing binary XLS files.

• Have a look at Excel Web Access, which is part of Excel Services in MOSS, a server-side version of Excel that lets you calculate and display your Excel information through a Web browser. The MOSS overview page describes the sharepoint framework that can provide Excel functionality on the server. You can use the VSTO API to programmatically access spreadsheets.

• I've used Aspose.Cells for this sort of thing with good success. The documentation can be a little obtuse, but the product seems to work. It won't require you to have Excel on the server and it works with the latest versions of Excel (and previous versions).

• not sure if this is exactly what you need, but certainly could be: 4 Editing In Excel Tutorials (under .NET #5-8)

• The problem with using Excel as an ISAM database using any driver (ODBC, OLEDB., etc.) is the datatype of the column is based on the first 8 rows in the column - this is pure evil; for example: if a column is something like a part# and the first 8 parts happen to be only numeric then you get get NULL for any cell in that column where the part# isn't numeric. (110000 vs 111000-1 or 111000A). This is reguardless of how you format the column in excel (even if you set the column as text).

  I'll try and find a doc on this and update. Here's one:

  http://support.microsoft.com/kb/194124

  http://msdn.microsoft.com/en-us/library/bb177610.aspx

• You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

  They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs, otherwise the paid version starts at $425 for a 1 Developer License.

  There are additional answers that may apply to your question at StackOverflow: Create Excel (.XLS and .XLSX) file from C#.

• In that situation, I would normally favour not messing with the complex spreadsheet, but setting up a separate data file, to which I would link the data cells in the complex spreadsheet. This is much cleaner and safer than changing a complex spreadsheet from outside.

  If this is not easier than the alternatives you are currently facing,it would surprise me. But then I haven't seen your spreadsheet.

Wrapping Visual C++ in C#

I need to do some process injection using C++ but I would prefer to use C# for everything other than the low level stuff. I have heard about "function wrapping" and "marshaling" and have done quite a bit of google searching and have found bits of information here and there but I am still really lacking.

Things I have read in order of usefulness;
http://msdn.microsoft.com/en-us/library/ms235281(VS.80).aspx
http://www.drdobbs.com/cpp/184401742
http://geeklit.blogspot.com/2006/08/calling-c-lib-from-c.html

How can I wrap all the lower level stuff (native C++) in C# so I can easily command those functions in a language I am more comfortable with, C#?

Any information on the topic is much appreciated.

From stackoverflow

• You want to use P/Invoke, see MSDN Magazine.

  Marcus Cicero : The only thing is that I am not trying to call any windows API function. I want to inject into a 3rd party process, and call certain functions in that process using asm/etc via native C functions that I can call from say C#. Maybe I am misunderstanding the article however. Thanks.

• If Pinvoking isn't what you want to do, then create a managed C++ application. Use native C++ to do the process injection stuff. Use managed c++ to create a .NET friendly interface to this native behaviour. This can then be called from C#.

  Marcus Cicero : Any guides or examples you could point me to? Thanks!

• I think P/Invoke is really the most straightforward approach:

  • Create a DLL in unmanaged C++, containing all the functionality you need to do the injection.
  • In your C# code, declare some static extern methods, and use the DllImport attribute to point them to your C++ dll. For more details, see the link provided by arul in his answer.

  Note that P/Invoke isn't limited to "windows API functions" — you can call functions exposed by any native DLL.

  A potential downside of P/Invoke is that you will have to provide the signatures for your C++ functions, possibly specifying some less-than-obvious marshalling. In that case, you could consider constructing a COM server instead of a "plain" DLL, and using COM interop to call your code from C#.

  Marcus Cicero : Thank you for the clarification.