473,557 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with SQL statement

Don
Hi folks

I wonder if someone could help me with a script I have been trying
unsuccessfully to write for a while now.

I have two tables - member_dest and member_det and have 3 search
criteria ie category, destination and gender. I have written the php
code to handle any combination of the 3 and produce $category, $dest
and $gender (some or all of which might be '%' ie wildcard. $category
and $gender can be found in member_det and $dest is from
member_destinat ion.

Now I need to select data from both tables by searching the 3 criteria
ie in english something along the lines of the following -

Select * from both tables where member_det.cate gory LIKE $category AND
member_det.gend er LIKE $gender AND member_dest.des tination LIKE $dest
Hope thats clear enough. Any help would be greatly appreciated.

Don

Jul 17 '05 #1
4 1266
On 5 Jan 2005 11:18:58 -0800, "Don" <in**@keyportmi lls.com> wrote:
I wonder if someone could help me with a script I have been trying
unsuccessful ly to write for a while now.

I have two tables - member_dest and member_det and have 3 search
criteria ie category, destination and gender. I have written the php
code to handle any combination of the 3 and produce $category, $dest
and $gender (some or all of which might be '%' ie wildcard. $category
and $gender can be found in member_det and $dest is from
member_destina tion.

Now I need to select data from both tables by searching the 3 criteria
ie in english something along the lines of the following -

Select * from both tables
What relates the two tables together?
where member_det.cate gory LIKE $category
Other than some quotes you're basically there, assuming you've escaped
$category appropriately.
AND
member_det.gen der LIKE $gender AND member_dest.des tination LIKE $dest


Similar to above.

--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #2
Don
Thanks Andy

member_id is the colum that relates both. I had the following query
working -

$query = 'SELECT member_hol_dest .member_id AS dest_id,
member_hol_deta ils.member_id AS det_id from member_hol_dest ,
member_hol_deta ils WHERE member_hol_dest .dest LIKE "'.$dest.'" AND
member_hol_deta ils.gender LIKE "'.$gender. '" AND
member_hol_deta ils.category LIKE "'.$category.'" ';
This worked but gave me an array that looked something like the
following:

=============== =============
member_id | Member_id |
=============== =============
1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |
| |
=============== ==============

What I really wanted was a list of member_id's of a those who satisfy
any or all of the search criteria.

Cheers

Jul 17 '05 #3
Don wrote:
Thanks Andy

member_id is the colum that relates both. I had the following query
working -

$query = 'SELECT member_hol_dest .member_id AS dest_id,
member_hol_deta ils.member_id AS det_id from member_hol_dest ,
member_hol_deta ils WHERE member_hol_dest .dest LIKE "'.$dest.'" AND
member_hol_deta ils.gender LIKE "'.$gender. '" AND
member_hol_deta ils.category LIKE "'.$category.'" ';
This worked but gave me an array that looked something like the
following:

=============== =============
member_id | Member_id |
=============== =============
1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |
| |
=============== ==============

What I really wanted was a list of member_id's of a those who satisfy
any or all of the search criteria.

Cheers


I would suggest using an explicit JOIN, that way you have a distinct
expression (the ON clause) that relates the two tables and another
distinct expression (the WHERE clause) for selection citeria. You
don't provide enough information to clearly understand the relationship
between the tables. I am assuming there is a many-to-one relationship
between member_hol_deta ils and member_hol_dest here. Then, using an
explicit (INNER) JOIN:

SELECT member_hol_dest .member_id AS dest_id,
member_hol_deta ils.member_id AS det_id
FROM member_hol_dest
INNER JOIN member_hol_deta ils ON
member_hol_deta ils.member_id=m ember_hol_dest. member_id
WHERE <condition 1>
AND/OR
<condition 2>
etc...

Hope this helps or at least doens't hurt...

NM

--
convert uppercase WORDS to single keystrokes to reply
Jul 17 '05 #4
Don
Excellent, I had an idea it was to do with joins but I dont have alot
of experience with them. I incorporated your script and it works
perfectly.

Thanks very much to both of you.

Don

Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

46
5111
by: Kingdom | last post by:
In my data base I have a list of componet types e.g. type A - I have 8 off - type B I have 12 off etc. I'm using Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM Parts_Table") to populate a drop down but would like to use several drop downs restricting the contents of each drop down to the records pertaining to one
6
391
by: Mark Reed | last post by:
Hi all, I am trying to learn a little about programming (I know next to nothing so far) and have found some code which hides the toolbars. However, this bit of code is a little too effective and hides all of them including hiding the database window, disabling menu changes. What I am after is the same effect as disabling all the check boxes...
8
5378
by: drose0927 | last post by:
Please help! I can't get my program to exit if the user hits the Escape button: When I tried exit(EXIT_SUCCESS), it wouldn't compile and gave me this error: Parse Error, expecting `'}'' 'else if (choice == 27) exit(0) } }' Here is my program (Simple loop to display currency equivalencies based
5
17254
by: Jesee | last post by:
I am reading Jeffrey Richter's book "Applied Microsoft .NET Framework programming",i came across "Exception handing". Page 405 says "If the stack overflow occurs within the CLR itself,your application code won't be able to catch the StackOverflowException exception and none of your finally blocks will excute.",I don't understand it. Following...
11
2181
by: Scott C. Reynolds | last post by:
In VB6 you could do a SELECT CASE that would evaluate each case for truth and execute those statements, such as: SELECT CASE True case x > y: dosomestuff() case x = 5: dosomestuff() case y > x: dosomestuff()
2
1845
by: Greg Corradini | last post by:
Hello All, A few weeks ago, I wrote two scripts using mx.ODBC on an Access DB. Among other things, both scripts create new tables, perform a query and then populate the tables with data in a dictionary that I've uploaded from elsewhere. These scripts have run hundreds of times in the last few weeks with no problems. But recently they...
6
2343
by: redashley40 | last post by:
This is my first attempt in SQL and PreparedStatement I have add the PreparedStatement and I'm not to sure if I'm doing it correctly. When I do a test run on Choose 1 ,or 2 I get this error. Error - com.mysql.jdbc.Statement here is my code below public class DBAssign { /**
2
2886
by: rookiejavadude | last post by:
I'm have most of my java script done but can not figure out how to add a few buttons. I need to add a delete and add buttong to my existing java program. Not sure were to add it on how. Can anyone help? my script is below. thank you import java.awt.*; //import all java.awt import java.awt.event.*; //import all java.awt.event import...
0
4117
by: RCapps | last post by:
When running the below SQL Query I keep getting the following error: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'. For some reason it is only returning the first 11 chars of the column name? Any help would be greatly appreciated... This...
2
8092
by: kya2 | last post by:
I am not able to create following store procedure. CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT ) RESULT SETS 1 LANGUAGE SQL BEGIN part1 DECLARE TOTAL_LEFT INT DEFAULT 0; SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S; WHILE (TOTAL_LEFT > 0) DO
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7914
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3578
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2045
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.