WEB Dynamic SQL Statement

Discussion in 'OT Technology' started by hurleyint1386, Oct 25, 2008.

  1. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    I'm looking to have some check boxes indicating what people want to view from the database. How can I write a sql statement dynamically so it only selects certain columns from the table? Is there a better way than selecting each checked box individually then concatenating them?


    edit: just to clarify, this is for MySQL
     
    Last edited: Oct 25, 2008
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    I guess I don't understand what you mean? This seems very simple.
     
  3. intrktevo

    intrktevo New Member

    Joined:
    Oct 18, 2004
    Messages:
    5,781
    Likes Received:
    0
    Location:
    UCF
    select column1name, column2name FROM tablename
     
  4. ge0

    ge0 New Member

    Joined:
    Oct 31, 2005
    Messages:
    8,398
    Likes Received:
    0
    Location:
    JERSEY
    Just have a javascript function construct the SQL string on the fly.. Which then calls a ajax function to reload and display the results from the mysql query
     
  5. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    Lets say you've got 12 check boxes. A user checks 8 of them. How do you just select those 8? If you do "SELECT * FROM person" then you get all 12 results. If you just want to pull those 8, then you need to construct a separate select statement for just those. Sorry if this is confusing at all. I can't think of a very simple way of doing it. I guess I could select all from the table, but use just the columns they asked for, but I was wondering if there was another way.

    Also, I'd rather not do a whole bunch of java script to get my result.
     
  6. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Store each value of the checkboxes as a string, concatenate the query string once you've compiled the values from the checkboxes, and query your database accordingly.
     
  7. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    So this is what I figured I would do, but it's a bit of a pain in the ass because I need to append a comma to the end of each string. But if the string is empty, then I that means it wasn't checked, which will cause additional commas that will cause an error in the sql statement. I could write If statements for every variable, but it seems like there should be a more efficient way.
     
  8. catpiss

    catpiss Guest

    Wouldn't sending SQL statements to the database using Javascript open the door for some big security flaws.

    I am guessing that others can find out your table names, figure out table structure, and send their own calls to your database adding rows, removing rows...
     
  9. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Easy to avoid. Write a foreach statement with a max counter of the total number of values. Append the comma at the end of each value and increment the counter. When the counter reaches the max num (total number of values), do not append a comma.
     
  10. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    He didn't say anything about it using JavaScript. And the same security threats would exist even after the document was posted (post vars, not get). There are ways to avoid security threats like this. One method would be generating a unique token and matching it against the server.
     
  11. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    post what you've done
     
  12. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    I knew there would be an easier way haha I don't have time now, but when I write it, I'll let you know how things go. Thanks a lot.
     
  13. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    i always just put the commas on with the for loop and then chop the last character off after the loop is done :o
     
  14. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    That's sloppy. :o
     
  15. catpiss

    catpiss Guest

    Someone did... See below

     
  16. catpiss

    catpiss Guest

    How is that sloppy? It is logical and easy to understand. What would be more efficient?

    a) Having a condition called for every item in the loop
    or
    b) Chopping off one character at the end of the string once the loop is completed

    Not that it really matters since both are very fast. I've used both methods. I do feel the conditional one you suggested is cleaner, but that is my opinion and it may not be more efficient.
     
  17. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Do you read? A condition would only be called once. You're concatenating the comma at the end of your string. Once the loop is completed, the function would have returned a properly formatted SQL string. It's sloppy because you're running your string through an additional function to remove the last character.

    And yes, my method would be more efficient.
     
  18. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    it IS more efficient
     
  19. catpiss

    catpiss Guest

    Again you come off as a pompous asshat with the "do you read" remark.

    How do you achieve the below without putting a conditional statement within your loop?
    When the counter reaches the max num (total number of values), do not append a comma.

    How would you know if the counter reached the max num without having a conditional check statement? The conditional check would be run for every item in the loop. If the loop has 50 items it would check to see if it was the last item 50 times.

    The only reason I feel putting the conditional statement in a loop is cleaner, is that it is slightly easier for another programmer to figure out what you are doing. It saves them a couple of seconds of thought.
     
    Last edited by a moderator: Oct 27, 2008
  20. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Ok. I care what you think of me on the internet.

    Where did I say you don't put a conditional statement in the loop? I've mentioned it several times. You'll need a conditional statement to accomplish this. This would be a required task.

    The condition is tested several times through the loop. The condition is called once.

    Look everybody, this is catpiss backpeddling. Your argument on making the statement more "efficient" (which is what we are talking about) has nothing to do with saving the programmer a few seconds of thought. There's no excuses for a programmer to do it the method you've previously claimed as "efficient" because this method is the CORRECT way to do it, it is more efficient, and it is good practice whether you're a newbie or advanced programmer.

    This is why you don't hire designers to code your site. Because they think testing a conditional statement multiple times through the loop is less efficient than running a string through a function that will store every character of your string into an array then loop through each character in the string to access the desired result.

    Whether I'm right or wrong doesn't matter. We can assume that that the rest of this thread will be full of wonderful Jamaal logic. Let's get this started.
     
  21. pkr

    pkr Guest

    ohsnap catpiss got :Owned:
     
  22. catpiss

    catpiss Guest

    How so?
     
  23. catpiss

    catpiss Guest

    Of course you care what people think of you, or you wouldnt act like a pompous asshat. Everytime I jumped on you was to defend others from your elitist know-it-all attitude.

    Exactly. It is doing a comparison 50 times on a loop with 50 iterations. Do you assume that making a comparison requires absolutely no resources? it seems that way.

    Did I say it was running the code within the condition that many times? Nope.

    Exactly. Does the testing require no resources? Did I ever say the code within the condition was being run? No, I stated that it had to process the condition as in test the condition.

    Backpeddling? That sounds like a dumbass term that an ignorant tool would use.

    Learn to read. I stated I didnt know which one was more efficient. Learn to read.

    I stated one looks cleaner than the other and it was my opinion. Looks cleaner != More efficient.
    I also stated it may or may not more efficient. Backpeddling? Try reading my posts, pompous asshat. I asked which one was more efficient because I didnt know and dont care to try to figure it out.

    I'll take your word for it since I dont know... It is my prefered method anyways.

    I still wouldnt call the other code sloppy. It may not be the best method, but it isn't sloppy IMO. There are a lot of sloppy programmers out there that write much worse code.

    I see that a moderator abused his power. Well, you and Tony Stark should be glad. I wont post here anymore since the mods have no integrity and sell/give away private user information.
     
    Last edited by a moderator: Oct 27, 2008
  24. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
  25. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    while it's a silly argument i couldn't help but be a nerd and test to see which method is more "efficient" in terms of processing speed :)rofl:)

    i had a hunch that using an rtrim to chop the last comma off after the loop was over would be a little faster than incrementing a variable and running a conditional statement for each loop, so after coding each method and running it on an array of 10 elements here are the final results.

    running each 200,000 times in a row, the winner by the slight margin is...

    *drum roll*

    rtrim!

    conditional: ~0.896805047989 seconds
    rtrim: ~0.824401140213 seconds



    thank you for your time
     
    Last edited: Oct 27, 2008

Share This Page