How to use wildcards in a mysql_query("DELETE...

Howard_L · #1 21-Jul-2008, 13:15

...specifically something like:

PHP Code:


mysql_query("DELETE FROM $t1 WHERE( $f1='$ival1' && $f2='*' && $f3='$ival3' ) ")
      or die("del poop!... ". mysql_error());

The idea would be that if the user supplies a matching $ival1 and $ival3 and skips input for $ival2. (where I have substituted the '*' ), the DELETE will work.
In the script I would replace any ival which is skipped by the user with that wildcard character.
...so the user would effectively 'select' the row('s) to be deleted based on the cell values that they have specified .

In my trials, if I use a real matching value, either right on the line or in an '$ival2', the statement works..
I have been trying things like: * , \* , [\*] , % , \% , [\%] and variants with NO sucess....
What the heck am I missing....

Incidentally the variables are:
$t1 = "books";
$f1 = "refnum";
$f2 = "pgcol";
$f3 = "catnum";
...and from the user through a form.html:
$ival1 = $_POST['ival1'];
$ival2 = $_POST['ival2'];
$ival3 = $_POST['ival3'];

TurboPT · #2 21-Jul-2008, 18:44

If $f2 is a 'don't care' value, then why even include it? As long as the $f1 and $f3 match, then theoretically, $f2 is wild.

Quote:

Originally Posted by Howard_L

I have been trying things like: * , \* , [\*] , % , \% , [\%] and variants with NO sucess....

Because you specified the = operator, those are [some incorrect, though] examples of trying to match ONE character that is literally a * or %. The incorrect part is with the back-slashes (see below).

However, the wildcard IS the % symbol, but it is used with the LIKE keyword, but not with the =.

Quote:

Originally Posted by mysql

Note

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. (Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape.)

That tidbit of info [plus more!] was found here.

But for a real example, that uses a wildcard for the 2nd column, I came up with this:

Code:

mysql> select * from blah;
+----+-------+------+
| c1 | c2    | c3   |
+----+-------+------+
|  1 | hello |    1 |
|  2 | there |    2 |
|  3 | where |    3 |
|  4 | art   |    4 |
|  5 | thy   |    5 |
|  6 | query |    6 |
|  7 | str1  |   71 |
|  8 | str2  |   72 |
|  9 | str3  |   73 |
| 10 | str4  |   74 |
| 11 | str5  |   75 |
| 12 | str6  |   76 |
+----+-------+------+
12 rows in set (0.00 sec)

mysql> select * from blah where c1 < 5 and c3 < 10 and c2 like '%ere%';
+----+-------+------+
| c1 | c2    | c3   |
+----+-------+------+
|  2 | there |    2 |
|  3 | where |    3 |
+----+-------+------+
2 rows in set (0.01 sec)

mysql> delete from blah where c1 < 5 and c3 < 10 and c2 like '%ere%';
Query OK, 2 rows affected (0.07 sec)

...ignoring the the second column:

Code:

mysql> select * from blah where c1 > 7 and c3 > 70;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  8 | str2 |   72 |
|  9 | str3 |   73 |
| 10 | str4 |   74 |
| 11 | str5 |   75 |
| 12 | str6 |   76 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> delete from blah where c1 > 7 and c3 > 70;
Query OK, 5 rows affected (0.08 sec)

...which ultimately leaves the 'blah' table looking like this:

Code:

mysql> select * from blah;
+----+-------+------+
| c1 | c2    | c3   |
+----+-------+------+
|  1 | hello |    1 |
|  4 | art   |    4 |
|  5 | thy   |    5 |
|  6 | query |    6 |
|  7 | str1  |   71 |
+----+-------+------+
5 rows in set (0.00 sec)

HTH.
Oh, in case you may not know, the underscore is used to match any single character. They have basic examples at the link.

Howard_L · #3 22-Jul-2008, 00:46

Very good! Thanks for taking the time. I'm not working on the command line but I can see how it can be handy for testing. Maybe I should think about going ahead and installing.

Quote:

If $f2 is a 'don't care' value, then why even include it? ...

...well it might be a don't care value in this test but in the finished product I thought it would be nifty for a user to be able to specify any one of (or combination of) the 7 fields of a table which looks like this:

Code:

Ref#    Cat.    Cat.   Type   Title    Price  Quantity
        pg/col   #                                    
  1      3-5   Z-1    book   A book    6.00   5
  4      1-2   X-3    book   A Title   9.40   6
  3      1-2   X-3    book   A Title   9.40   6

The selection form.html looks something like this:

Code:

 Enter the values necessary to accomplish the desired task: 
_____________________________________________________________________________
|  Ref#  |  Cat. pg/col  |  Cat. #  |  Type  |    Title    |  Price  | Quant |
|        |               |          |        |             |         |       |
|________|_______________|__________|________|_____________|_________|_______|

Anyhow, 'LIKE' ...who would have thought! With your examples and reference I was able to get going again.

PHP Code:


/* So the user fills in selection values and submits form to this script which changes empty values to the  '%' sign: */
if(!$ival1) $ival1 = '%' ; ...etc...

/* ...and then this runs: */
    mysql_query("DELETE FROM $t1 WHERE( $f1 LIKE '$ival1' &&
                   $f2 LIKE '$ival2' && $f3 LIKE '$ival3' &&
                   $f4 LIKE '$ival4' && $f5 LIKE '$ival5' &&
                   $f6 LIKE '$ival6' && $f7 LIKE '$ival7'   ) ")
      or die("del poop!... ". mysql_error());

...and so far it's working nicely.
Thanks Again!, Howard++;

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

How to use wildcards in a mysql_query("DELETE...

Re: How to use wildcards in a mysql_query("DELETE...

Re: How to use wildcards in a mysql_query("DELETE...