[SOLVED] Optional Form Fields

[r00tk1LL]

I have a form that users can use to search with multiple fields but sometimes they wont search in all the provided fields. What is the best way to have php query the DB with only what has been submitted instead of sending blank html fields??

 

Thanks

[PC Nerd]

um, do validation.... like if(empty($field1)) {}

 

then once you know what is empty, you can query etc from there

 

thats the only way

 

[pocobueno1388]

You can do something like this:

 

<?php

$query = "SELECT * FROM table WHERE 1";

if (isset($_POST['optional_field'])) $query .= " AND optional_field = '{$_POST['optional_field']}";
if (isset($_POST['other_field'])) $query .= " AND other_field = '{$_POST['other_field']}";

$result = mysql_query($query)or die(mysql_error());

?> 

 

Check if they submitted that field, and if they did just add to the query.

[r00tk1LL]

OK great I'll try that now one more thing...Could I incorporate "LIKE" into that to use wildcards???

 

Heres the url if you want to see what all this is about

http://www.mynorthtexas.com/mypages/final/

[pocobueno1388]

Yes, you can use LIKE

[r00tk1LL]

I guess the question is can LIKE be used more than 2 times in a query statement?

Would this be the correct syntax:

$query .= " AND other_field LIKE '%{$_POST['other_field']}%' AND other_field LIKE '%{$_POST['other_field']}%'

Could you help me clean this up???

Thanks

[pocobueno1388]

Yes, you can use LIKE two times in one query.

 

Is that syntax not working for you? It looks okay to me, even though I'm not super familiar with using LIKE in my queries.

[r00tk1LL]

Hey thanks for helping me, I'm trying this without the LIKE to simplify things. This is not working, is there anything wrong....

//some previous code
$link = mysql_connect ( $hostname, $username, $password);
//user input variables
$query="SELECT uid, image, uname FROM mypages WHERE";
if (isset($_POST['name'])) $query .= " name = '{$_POST['name']}";
if (isset($_POST['age'])) $query .= " AND age = '{$_POST['age']}";
if (isset($_POST['location'])) $query .= " AND location = '{$_POST['location']}";
if (isset($_POST['marital'])) $query .= " AND marital = '{$_POST['marital']}";
if (isset($_POST['hobbies'])) $query .= " AND hobbies = '{$_POST['hobbies']}";
if (isset($_POST['interests'])) $query .= " AND interests = '{$_POST['interests']}";
if (isset($_POST['sex'])) $query .= " AND sex = '{$_POST['sex']}";
//rest of code

 

heres the page so you can see what I'm talking about:

http://www.mynorthtexas.com/mypages/final

[r00tk1LL]

Ok this doesnt work

if (isset($_POST['name'])) $query .= " AND name = '{$_POST['name']}'";

 

But this does

if (isset($_POST['name'])) $query .= " name = '{$_POST['name']}'";

???

[pocobueno1388]

Oops, in your initial query, I think you need to set at least one condition.

 

$query="SELECT uid, image, uname FROM mypages WHERE";

 

So you need to add something after the WHERE. Do you have any field that isn't optional? If you do, then you should put it there.

[r00tk1LL]

I see where this is going, could I reference a field that would always be true? For example anyone that has information in the database has a user id 'uid' field...

I.E.

$query="SELECT uid, image, uname FROM mypages WHERE uid = true";

 

Or just some kind of trick to allow the user to enter information in any field keeping them all optional??

[r00tk1LL]

OK I'm playing with this idea. I found that I can use this:

$query="SELECT uid, image, uname FROM mypages WHERE id > 0";
if (isset($_POST['age'])) 
{
$query .= " AND age = '{$_POST['age']}'";
}

And it will work fine no matter what variable I use

But if I use more than one:

$query="SELECT uid, image, uname FROM mypages WHERE id > 0";
if (isset($_POST['age'])) 
{
$query .= " AND age = '{$_POST['age']}'";
}
if (isset($_POST['name'])) 
{
$query .= " AND name = '{$_POST['name']}'";
}

The search fails every time no matter what

 

Please someone tell me what this is all about, I've been on this for days.

Thanks

[trq]

Try echo'ing $query prior to execution to see if there are any errors.

[r00tk1LL]

here is what it said:

SELECT uid, image, uname FROM mypages WHERE id > 0 AND age = '18' AND name = ''

It looks like it is grabbing that empty if statement??

[trq]

if (isset($_POST['name']) && (!empty($_POST['name']))

[r00tk1LL]

Hey what would be better (more efficient):

if (isset($_POST['name']) && (!empty($_POST['name']))

 

or

if ( $_POST['age'] != NULL) 
{
$query .= " AND age = '{$_POST['age']}'";
}

 

Because I found the last one worked just now?

[pocobueno1388]

I would use thorpe's suggestion:

 

if (isset($_POST['name']) && (!empty($_POST['name']))

 

Are you saying that when you use the above suggestion it doesn't work, or are you just saying that they both work but you think the second one is a better way?

[r00tk1LL]

They both work, I would just like to use the best one?

And then this thread will be solved

[r00tk1LL]

;D ;D

Ok Guys this is what I was looking for, I guess I'll stick with this for now since it is working.

 

$query="SELECT uid, image, uname FROM mypages WHERE id > 0";

if ($_POST['name'] != NULL) $query .= " AND name LIKE '%{$_POST['name']}%' || uname LIKE '%{$_POST['name']}%'";
if ($_POST['age'] != NULL) $query .= " AND age = '{$_POST['age']}'";
if ($_POST['location'] != NULL) $query .= " AND location LIKE '%{$_POST['location']}%'";
if ($_POST['marital'] != NULL) $query .= " AND marital = '{$_POST['marital']}'";
if ($_POST['hobbies'] !=NULL) $query .= " AND hobbies LIKE '%{$_POST['hobbies']}%'";
if ($_POST['interests']!=NULL) $query .= " AND interests LIKE '%{$_POST['interests']}%'";
if ($_POST['sex'] !=NULL) $query .= " AND sex = '{$_POST['sex']}'";

 

Thank you guys for your help!!!

;D ;D ;D ;D ;D

[pocobueno1388]

Awesome =] If it works for you, then that is just fine. There isn't a better way out of the two, it's just personal preference.