Question about creating a complex series of queries.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Question about creating a complex series of queries.

Post by lafflin »

Hello,
I first just want to start by thanking all of you who over the past few weeks have answered so many of my Newbie questions, with your help I have created my database and I'm currently creating my queries and dynamic webpages for them. Thank you all so much, you know who you are. This Forum is the best I've ever came across hands down. I promise when i become as proficient as the gurus on here to support this forum to the fullest.

My question:

I would like to create a query form, one which searches my database by most or all of the criteria that can be entered into the registration form.
For instance, lets say that the following fields are part of the registration (and they are, but there's plenty more)

First_name (full text searchable)
last_name (full text searchable)
Sex
DOB
active (enum Y or N)
Class (This field is on a seperate table)

It would be great if I could actually search by combination of fields, but I need to crawl before I can walk and I first need to understand how to search my DB by user input.
Where I'm at now in my learnig is how to create insert and update queries on my pages (php).

what I'm thinking is that I obviously need to create a loop with each condition being a query, but I don't know the syntax for using a variable in my query.

Any help given would be greatly appreciated.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: Question about creating a complex series of queries.

Post by feyd »

lafflin wrote:what I'm thinking is that I obviously need to create a loop with each condition being a query, but I don't know the syntax for using a variable in my query.
Actually, no. You need to generate a single query that combines each of the elements to be searched. This generally involves using a foreach() to iterate over the input generating a single string as a result. Through a little searching I'm sure you can find examples of string creation, if you need help in that regard.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

For really basic questions such as this, you would probably learn more by going through a few of the several good online tutorials. Instead of just getting a little tip here and a little tip there, you would learn how the basic operations are coded, without gaps in your understanding. You could start with http://w3schools.com/. I'm not trying to brush aside your questions, but you would really benefit by learning things in a logical sequence.
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post by lafflin »

I found it. Thanks. I'm a bit confused about how the search query works though. I just tested my code using two fields like this:

Code: Select all

$query = "SELECT * FROM student_info WHERE first_name LIKE '%".trim($_POST['fn'])."%' AND last_name LIKE '%".trim($_POST['ln'])."%'" ;
and I can leave either field on my form blank and the search still works. That's great cause that's what I want, but for the purpose of having a better understanding of what I'm doing I have to wonder, why will the query still return results with populated values of whichever field I've left blank in my search?

in other words, if i search for last_name and leave the first_name field on my form blank I will still get results that have a first_name. The reason this is confusing me, is because i have used "AND" in my query instead of "OR". If I leave a form field blank what value is that sernding to my query? Obviously not null, but it has to send something right? Maybe I should just leave well enough alone.

Anyway, thanks Feyd.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The LIKE '%%' is the key to it. The LIKE keyword tells the database you want to use similarity. The '%' are wild card characters when in this mode. i.e. They will match just about anything.
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post by lafflin »

I see, well thanks feyd, your contributions are as appreciated as they are valuable.

As much as that seemed like a very basic question, and it is, it's only basic if you know the answer. I actually did search google for the answer, but I just wan't finding it on the tutorial sites. I actually found it on some other post about nothing related at all while just reading lines of code.
But the truth is that I didn't just decide to learn PHP from the mods at devnet, although it might seem like that sometimes, I actually have been studying it for a few weeks now out of a book and several online tutes. But sometimes what seems to be simple I just haven't seen yet.

By all means, ignore me if you feel that by answering my question or pointing me towards an appropriate resource might be a waste of your time. I really do understand.
Post Reply