#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Select * where something = "all"


    I am working on a GUI that will be used to query a database. The table I am working on has 34 columns and the user will have the option to enter a value for any of the columns or leave them empty. I want to only use one sprintf statement to query the database so is there a way that if they didnt enter a value I can set the query to understand I want all values for that particular column.

    Example Code:

    snprintf(sqlEntry,sizeof(sqlEntry),"select * from main.myTable where length = 100 AND width = ALL");
    results = PQexec(sqlEntry);

    *I know that I can just do where length = 100 and not put width in at all but then I would need 3 different snprintf statements depending on if they user inserted a length or width or both. 3 different snprintf's for 2 variables is doable but I am working with 34 columns so the amount of coding for that is too much. Any help is much appreciated
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    Construct the query dynamically. I don't know the language you're using, but I would make a list of the 34 columns, loop through it and whenever the value for this column is set, you add " AND (the column name) = (the value)" to the query string.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    Construct the query dynamically. I don't know the language you're using, but I would make a list of the 34 columns, loop through it and whenever the value for this column is set, you add " AND (the column name) = (the value)" to the query string.
    Jacques1,

    That is a very good idea I wasn't thinking of being creative in my c/obj-c. I just know that you can do "select *" in postgres and it is basically select all so I was hoping there was something similar for individual columns. However, your solution will basically do exactly what I want so thank you.

IMN logo majestic logo threadwatch logo seochat tools logo