[Resolved] MySQL Wildcards

jonki

My problem is this - I want to use a wildcard that will take all values from a specific column if that is what the user wants.

For example - the user has 3 pull down menus he can select but he may only want to use 2 of them. So I want to have the default of each pull down menu as a wildcard that selects anything out of that column.

Here is my code;

//In my html doc:
<select name="marca">
<option value="[.*]">I don't care</option>
<option value="astonmartin">Aston Martin </option>
<option value="audi">Audi </option>
<option value="bmw">BMW </option> etc.....

<select name="color"> etc ....

<select name ="precio"> etc ....

//In my php form:

mysql_query("select * from db where marca='$marca' and color = '$color' and precio = '$precio' ")

Now when the user doesn't care about the "marca" and he is only interested in the color and the price then the php will show him every car that is the color and price he is looking for, regardless of what model it is.

I have tried "." "[a-z]" "%" "%." and every combination of these until my fingers have gone blue.

I have seen people asking the same questions but they never seem to get a simple answer - the obvious one of just leaving it off the "where" part doesn't work because the form is fixed and all fields are always there.

If you can help me on this one you will end a 4 hour long search and I will be very grateful - and if you could give me an idea about how to do this in a textbox rather than a pull down menu I will kiss your feet.

Thanks.

Jon

Pig

You need to build that logic into how you assemble the query. Build the string piece by piece and then run the query. Do not try to get the query to handle it on its own. For instance, if you wanted to an optional field called 'size', and the value in the drop down list to indicate the person didn't care had the value of 'nothing', then you could do:

if( !empty($POST['size']) && $POST['size'] != 'nothing' )
$sql .= " AND some_field LIKE '%" . $_POST['size'] . "%' ";

jonki

I'm sorry but I'm afraid you've lost me. I only started learning php a couple of days ago and there are just too many things in your statement that are new to me.

Could you tell me where exactly to put this? I am not sure of how to incorporate it into my code.

Also - is this for a text box or a pull down menu?

Thanks a lot for your help - I really appreciate it.

Jon

Pig

Sorry, let me explain that further.

So it sounds like you have a form. Let's say the field names are 'a', 'b', and 'c'.

You have a query to process their request, that uses the field names in the WHERE statement.

Rather than run a query that just plugs in the values, you want to build the query as a string, and then run the string. Instead of something like this:

mysql_query("
     SELECT 
          * 
     FROM 
          table 
     WHERE 
          table_a LIKE '%" . $_POST['a'] . "%' AND 
          table_b LIKE '%" . $_POST['b'] . "%' AND 
          table_c LIKE '%" . $_POST['c'] . "%'
");

You want to build the logic in how you create the query itself. So, let's say that a, b and c have a default value in the list set to 'nothing'.
eg:
<select name=a>
<option value=nothing>no preference</option>
</select>

On the process page, you would then build the query like this:

$sql = "
     SELECT 
          * 
     FROM 
          table 
";
if( !emtpy($_POST['a']) || !emtpy($_POST['b']) || !emtpy($_POST['c']) ) $sql .= " WHERE ";
if( !emtpy($_POST['a']) && $_POST['a'] != 'nothing' ) $sql .= " table_a LIKE '%" . $_POST['a'] . "%' ";
if( !emtpy($_POST['b']) && $_POST['b'] != 'nothing' ) $sql .= " table_b LIKE '%" . $_POST['b'] . "%' ";
if( !emtpy($_POST['c']) && $_POST['c'] != 'nothing' ) $sql .= " table_c LIKE '%" . $_POST['c'] . "%' ";

This will only include a, b and c if they have a value selected. Then you just run the query:
mysql_query($sql);

jonki

Wow - thanks a lot man - I get what the code is trying to do but somehow it doesn't work.

The column in my table is "tercio_tf"

Here is my "select" coding;

<p><b>Donde?
<select name="tercio_tf">
<option value=nothing>Da igual</option>
<option value="s">Tenerife Sur</option>
<option value="n">Tenerife Norte</option>
<option value="st">Santa Cruz /La Laguna</option>
</select>

And here is my php script;

$sql = "
SELECT
*
FROM
table
";
if( !emtpy($POST['tercio_tf']) ) $sql .= " WHERE ";
if( !emtpy($POST['tercio_tf']) && $POST['tercio_tf'] != 'nothing' ) $sql .= " tercio_tf LIKE '%" . $POST['tercio_tf'] . "%' ";

$result = mysql_query($sql);

I cut it down to one line just to check the errors as I couldn't get it to work - but unfortunately I still can't get it to work.

I tried "" around the nothing in the select option section, and I tried putting $ infront of the variable inside the &_POST[] bit but it doesn't work. I even put curly brackets around the $result=mysql_query bit but nothing helps.

I get the error message;

Fatal error: Call to undefined function: emtpy() in /home/wingates/wingateschool-www/projects/tenercoche/buscartest.php on line 23

Here is my original code which works fine as long as you select specific values;

$result = mysql_query("select * from tenercoche_privado where marca='$marca' and
modelo LIKE '{$modelo}%' and
tercio_tf='$tercio_tf' and
combustible = '$combustible' and
techo = '$techo' and
caja_cambios = '$cambios' and
edad <= '$edadmax' and
kms <= '$kmmax' and
potencia >= '$cvmin' and
'$precio_min' < precio < '$precio_max' order by precio ")
or die (mysql_error() );

Any ideas?

I really appreciate your help - this would take me years otherwise!!

Thanks.

Jon

Pig

Hehhehe... sorry. I typoed in my script, and when I cut and pasted it, it just got repeated. It should be empty(), not emtpy(). Sorry.

Give it another shot and see what it does.

jonki

... and I didn't even notice ....... Thanks - I'll give it a try.

jonki

Hi - I'm sorry about this but I can't get it to work - would it be possible for you to quickly check my variables above - especially in the $_POST brackets and the .... $sql .= " tercio_tf LIKE ...... bit?

You wrote your variables as "a" and "table_a" but I am using the same variables because the names of my options html (in this case atleast) are the same as the column names.

Thanks a lot.

Jon

Pig

Your vars look fine. Is your form using the POST method? Try pasting all of your code so we can see where else the problem may lie.

BTW, after re-reading my code I saw that I didn't account for the 'AND's that have occur between parameters. Those can be handled a couple different ways. One is to just put " AND" in the string you are adding, and then use substr() to chop off the last "AND".