PHP MySQL query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ThEeNd
Forum Newbie
Posts: 3
Joined: Tue Nov 15, 2005 10:19 am
Contact:

PHP MySQL query

Post by ThEeNd »

I'm having a problem with a search and results page that i'm trying to put together.

I have a database with four fields among which PlanWidth and PlanDepth.

My search page has two fields: WidthSearch and WidthDepth.

If i put numbers in both fields say W:35 and D:37 i get back the records that have the width 35 and depth 37

How do i get it to work if one of the fields is left empty?
Say I want to search only by width or only by depth?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you post the code you have so far we can help a bit better :)

Mac
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

a simple if/else

Code: Select all

if($_POST['width'] && $_POST['depth']
{
   // query to find width AND depth
} ELSE
IF($_POST['width'] && !$_POST['depth'])
{
   // query to just find width
} ELSE
{
   // query to just find depth
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
ThEeNd
Forum Newbie
Posts: 3
Joined: Tue Nov 15, 2005 10:19 am
Contact:

Post by ThEeNd »

I see where you're going.

Create a separate query depending on what fields the user fills in. This may work in this situation. What do i do when i will need to have a whole lot more fields to search by?

Right now I am indeed trying to get it work with two fields. But in the end, having a floorplan database, i want to create a search page with about 20 search fields. (Depth, Width, Stories, 1st floor sq ft, 2nd floor sq ft, total sq ft, bathrooms, bedrooms, Features, Notes, etc)

I can't write a query for all possible combinations.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You simply need to write code that generates a query for you ;)

Code: Select all

<?php
$accepted_fields = array('department', 'floor', 'depth');

$clean_fields = array();
foreach($accepted_fields as $field) {
  if (isset($_POST[$field])) {
    $clean_fields[$field] = mysql_real_escape_string($_POST[$field]);
  }
}

$sql = "SELECT ... FROM ... WHERE ";
foreach($clean_fields as $key => $val) {
  $sql .= "`$key`='$val' AND ";
}
$sql = rtrim($sql, " AND ");

?>
I've found that sometimes you might want to use a LIKE instead of AND if the input contains a % or ? but that is up to you to implement ;)
Last edited by timvw on Tue Nov 15, 2005 2:56 pm, edited 1 time in total.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

SQL generation, I suppose.

Code: Select all

$first = true
foreach($params as $key => $value) {
  if ($first) {
    $first = false;
  } else {
    $sql .= " AND "
  }
  $sql .= " $key = '$value' ";
}
Nevertheless, if you put all your SQL in one place(like a mapper) and choose a less than optimal choice, you can always refactor.

Edit - Ah, timvw beat me to it. And with a more complete example too.
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

This is one of the examples where I would build a class, if you might use the procedure in more than one place .. ..

building a simple search object in OOP and simply pass the values as either "with" a value, or as null will "switch" the query within the object and then you only need to do something like

Code: Select all

//pseudocode
$result = $search_query->find($width,$depth);
and if either is null, it will get just width OR depth, or if =both have values, it gets width AND depth, and you dont have a whole pile of procedural code to switch around and change in multiple places ..

However, if it's a one-shot, single page deal, procedural would be a better choice ..

I built just such an object class some time ago, because the same "basic" query was being used in different places on many areas of my application, but to return different values based on what area of the application it was run in, and I put it into an object/class in order to simply pass a set of values and type of query, so I would only have to change the *core* sql query in one place, if I made changes later on in the application or database structure..

took longer to build the class and unit test it, but definitely worthwhile in time savings when changes need to be made :)

I cant share the code here- it's proprietary... sorry :)
ThEeNd
Forum Newbie
Posts: 3
Joined: Tue Nov 15, 2005 10:19 am
Contact:

Post by ThEeNd »

Thanks for all the ideas. Just one last question:

I'm just learning php and all but I find it wierd that i can't get to use % as a wildcard.

When i'm testing a query in SQL this works fine:

Code: Select all

SELECT * FROM floorplans
WHERE floorplans.PlanWidth Like '%' AND floorplans.PlanDepth Like '37'
It returns all records with the depth 37

if i run:

Code: Select all

SELECT * FROM floorplans
WHERE floorplans.PlanWidth Like '%' AND floorplans.PlanDepth Like '%'
it correctly returns all the records in the database.

So then if i have 20 search fields, why can't I get all the fields that are left blank to appear in the SQL statement as filled with %.

When i have a search page with search fields such as Width, Depth, TotalSqFt, Bathrooms, Bedrooms, Stories, etc. and when a user puts data only in two of those 20 fields why can't the rest 18 fields assume a default value of % which would return all records that match only the two filled fields?

I actually got my search/results application to work if for example i put in the width field 35 and in the depth field %. I just don't want to end up putting % in 20 fields when i run a search.
Post Reply