I came to a stand-still here with this script. An example is at the bottom. As you can see, I'm building a script for a multiple search front end page for a mysql database. If you look at my code, you'll see what I'm trying to do. The problem is that the first $build segment that is used and added to $SQL cannot start with an "and", but the rest have to. How do I get rid of it for only the first one? They all have to to have "and" in it to work, the query could start with ANY of them. Any ideas on how to make the first one without the "and" but remain listed as having it? I started a "$count" type thing, but that won't work for only the first one.

that's not a perl solution, but more a little trick i use, change the select clause to be: select select * from members where 1 with a trailling space, so you can keep your "and" everywhere since 1 is true (now it will list everybody if there's no parameter filled, so i added the "and 0;" for that case), so here's my try at it:

Btw, think to escape the content of $$_, some malicious user could fill in an SQL request as an argument, like ';select username, password from members;(the name of the tables and the fields are generally guessed user,users, username, usernames, etc etc and member(s), customer(s), etc etc) it would result to a query like: select * from members where 1 and member_id = '';select username, password from members;'asking for the pass when you dont expect them...

I suppose you know a better name for %big_hash (like %cgi_data), but I was lazy about picking a name.

This will look in the hash for the 23 known columns and add the correct '=', 'like', or 'like %%' line to your query if needed and will join them together with 'and'. By doing the join you avoid the extra 'and' before the first item. And by looking at only the 23 accepted choices you don't have to worry about someone giving a strange column name to break things.

Also I dropped the ' ne "" ' from the if statements as they are not needed. If something is not the empy string, then it has some value, and as long as that value is not the number 0 everything is fine. You might need to add that back if a valid value will be the number 0.

You can do it without the hash, but building the individual parts of the where become harder to do inside a loop.

-- Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?

which means that the for will loop thru each data in the array @checks and put its content into the default input variable $_ (perl sometimes assumes you use it with certain command when you dont provide any argument). The first time it runs, $_ will be "member_id", then "last_name" and so on... You can also consider it as:

for ($i = 0; $i++; $i < @checks) { $_ = $checks[$i];

------------------------------

The next line 09 (that will then be looped thru all the elements of @checks) is a little trickier, let's have a look at it:

$params .= "and $_ = '$$_' " if ($$_ ne "");

You can read it this way if you wish:

if ($$_ ne "") { $params .= "and $_ = '$$_' "; }

So, what the hell are those strange $_ and $$_, i will explain it with the following example:

Rather than have 23 separate variables I have one hash (%big_hash) that has all 23 values in it. For testing I just put 4 values in the hash, but I expect that this would be reading from a form and you can fill the hash at least as easily as you were filling the 23 separate variables.

You have three types of where clauses. Most of them are the simple kind with an equal sign and the names fo those columns are in the @usees_equals array. The others are in the other two arrays. By using qw we don't have to put quotes around each word so the list is much easier to make.

In my first version I used map to get the where clauses for each group, making one long list to feed to join. This time around I have removed the maps and replaced them with three separate foreach loops to make it easier to see what is going on. I also added $item rather than $_ for readability. So this time we get the @wheres array with each where clause that should be included and then send it on to join.

I have also switched from the

Code

(some conditional here) ? do the true thing : do the false thing

statements to the more straight forward

Code

do the true thing if (some conditional here)

And finally we build $SQL by starting with the stuff up to 'where' and then add each individual where clause which we have placed in the @wheres array. The individual wheres are joined together so that an 'and' will be between each, and not in front of the first one. The extra new lines are there to make the output easier for me to read.

So this version should be easier to understand, but it does introduce four arrays and $item that were not in the original.

-- Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?

heh, i finally understand what the use i can have with join, that's very smart i would have never thought of that before.

btw, i really prefer your second solution, i didnt even dared to look the first one in detail yet, it looked so scaring. But i will do right now, i bet i have more to learn there... (and in many other things )

freddo

;--- Real programmers´butcher dont understand when they just ask for 3735928559.

Well, freddo's code worked, but it won't pick up any of the wilcard (%) searches. If it uses all the straight (=) searches, it works perfectly. For example: searching for dob of "%%%%-06-%%" will not work.

So, I guess I need to use the other code with all the hashes and stuff??

Well, I typed it in and it wouldn't pick up anything at all. All the searches I tried came up blank, even single ones. The only thing that I might have screwed up was the "%big_hash" part. The rest you had typed out as an example for me, but the big-hash one you had input in there so there was no template for me to go by. So, I just typed it how I thought it would go. Did I screw it up?? I checked for syntax 2 or 3 times, but I can't figure out why it picks up nothing now. Here's the new code for you to see what I did wrong. :)

here's my try at your last question, i bet there will be some typos or bugs, but it´s 4:30am here, and i dont have apache, nor mysql around. So if it cores too much, tell me and i´ll get a look at it at home this w-e.

So notice that you set all the odd items as keys and the following even items as their values, except for the last item ('paid') that has no value. Remember hashes are not quite arrays and they need an even number of things so they get key => value pairs. The '=>' sybmol is just a fancy comma. By using it we don't have to put quotes around thing just before it, in this case the key, so it is easier to type (and in my editor the keys are in a different color from the strings used for the values).

On the topic of creating the HTML to show your answers you can take the 92 lines with two very large repetitive here documents and reduce it to this...

I changed your header row to use TH tags because that does bolding automatically. And then for each $ref returned by the database we print an opening TR, then loop through the keys and for each one print its value inside TD tags, and finally print the closing TR. You could feed that last map a combination of @uses_equals, @uses_like, and @almost_like but unfortunately they would not be in the right order.

This will print a table with all 23 columns. If you want to leave some of them out you just need to remove them from the list fed to the two maps in the printing.

-- Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?