I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there. There may be an SQL statement that could do all this in one
command, but I am no SQL expert. I was wondering if anyone had some
PHP oriented solutions. I can get the show_id, no problem. I seem to
be having a problem with the rest, as it involves arrays. After I go
into table2 and get all the attendee_ids, I get an array of
attendee_ids that I need to search table3 for, to create a new array
of attendee names. 5 1501
Jerim79 wrote:
I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there. There may be an SQL statement that could do all this in one
command, but I am no SQL expert.
select
table3.attendee _name
from
table1, table2, table3
where
table1.show_nam e = 'Mickey Mouse' and
table1.show_id = table2.show_id and
table2.attendee _id = table3.attendee _id
Note that the default String enclosing characters in MySQL are not
standard, i.e., not '', so you have to change that.
Jerim79 wrote:
I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there.
Hi,
So far it sounds like a nice denormalized database.
Please don't 'adjust' it. ;-)
I think your database is designed just fine (based on what you told).
There may be an SQL statement that could do all this in one
command, but I am no SQL expert.
Yes, that is easy done in one SQL statement that joins the 3 tables.
I was wondering if anyone had some
PHP oriented solutions. I can get the show_id, no problem. I seem to
be having a problem with the rest, as it involves arrays. After I go
into table2 and get all the attendee_ids, I get an array of
attendee_ids that I need to search table3 for, to create a new array
of attendee names.
Don't.
Write a single query that joins the 3 tables and let the database do the
hard work.
You might want to throw in the 3 relevant tables (how they are
structured) if you want us to make the join.
(Or better, repost that to a databasenewsgro up)
Good luck.
Regards,
Erwin Moller
Jerim79 wrote:
I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there.
Hi,
So far it sounds like a nice normalized database.
Please don't 'adjust' it. ;-)
I think your database is designed just fine (based on what you told).
There may be an SQL statement that could do all this in one
command, but I am no SQL expert.
Yes, that is easy done in one SQL statement that joins the 3 tables.
I was wondering if anyone had some
PHP oriented solutions. I can get the show_id, no problem. I seem to
be having a problem with the rest, as it involves arrays. After I go
into table2 and get all the attendee_ids, I get an array of
attendee_ids that I need to search table3 for, to create a new array
of attendee names.
Don't.
Write a single query that joins the 3 tables and let the database do the
hard work.
You might want to throw in the 3 relevant tables (how they are
structured) if you want us to make the join.
(Or better, repost that to a databasenewsgro up)
Good luck.
Regards,
Erwin Moller
On Aug 7, 10:08 am, Jerim79 <my...@hotmail. comwrote:
I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there. There may be an SQL statement that could do all this in one
command, but I am no SQL expert. I was wondering if anyone had some
PHP oriented solutions. I can get the show_id, no problem. I seem to
be having a problem with the rest, as it involves arrays. After I go
into table2 and get all the attendee_ids, I get an array of
attendee_ids that I need to search table3 for, to create a new array
of attendee names.
$row_rsIDAttend = mysql_fetch_ass oc($result_from _atendee_id_sql ); //
result from sql request of tbl2
do {
$qry_rsAttendee = "SELECT name FROM table3 WHERE id = " .
$row_rsAttendee['attendee_id']; //sql tbl3
$result = mysql_query($qr y_rsAttendee); // run query
$row_rsAttendee = mysql_fetch_ass oc($result); //fetch the first/next
array of field data
$array_attendee _names[] = $row_rsAttendee['name']; //fill your data
array of names
}while ($row_rsIDAtten d =
mysql_fetch_ass oc($result_from _atendee_id_sql ));
I wrote this out pretty quick, so check over my syntax, but this
should do what you want.
once the do loop is finished you will have an array
($array_attende e_names[]) with the number of elements in it that
represent your attendee list. Each individual element (attendee name)
can be accessed by refering to an element in the array
($array_attende e_names[1]). The elements start at 0. so if there are
5 attendees they will be numbered 0-4. If you want to show the 3rd
attendee in the list do this:
echo $array_attendee _names[2];
check out this for more on arrays: http://us2.php.net/manual/en/language.types.array.php - basic array
use info http://us2.php.net/manual/en/ref.array.php - array functions
GJ
On Aug 7, 9:26 am, Boris Stumm <st...@informat ik.uni-kl.dewrote:
Jerim79 wrote:
I am trying to create a search system for an existing database.
Because of the way the database is setup, I need to traverse three
different tables, gathering information:
1) Go into table1 and lookup show_id by show name. (For instance,
let's say I am looking for the Mickey Mouse show. I look it up and
find that it's show_id is 6)
2) Go into table2 and lookup all attendee_ids for show_id 6. (For
instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
are the attendee_id's)
3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
(For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
Alice, Tom, Peter and Greg.)
I am not free to adjust the tables, so I have to stick with what is
there. There may be an SQL statement that could do all this in one
command, but I am no SQL expert.
select
table3.attendee _name
from
table1, table2, table3
where
table1.show_nam e = 'Mickey Mouse' and
table1.show_id = table2.show_id and
table2.attendee _id = table3.attendee _id
Note that the default String enclosing characters in MySQL are not
standard, i.e., not '', so you have to change that.
That worked like a charm. Thank you very much. I took SQL back in
college, and use it from time to time, but never really have a need
for it 90% of the time. Your example immediately made sense. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ralph Guzman |
last post by:
I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative.
I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query:
SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity,...
|
by: michael calwell |
last post by:
Dear all,
I have googled until my eyed have fallen out, and still I cannot find
what I am looking for, and I would be grateful for any help at all. I
can redesign my tables, create new fields etc, if needs be.
I have 2 tables. One contains a foreign key to another table.
Table: Names
===========
|
by: Robert Oschler |
last post by:
I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x.
Here's my dilemma.
1) ---------
I have two tables that have records with a FULLTEXT index text field in each
of them. The problem is the relationship between the tables is a
|
by: calan |
last post by:
Relative SQL newbie here......this is probably easy, but....
Lets say I have a table (MainTable) that stores a list of input table names,
a primary key (PKey), and a field called "Configured" for each one. Each of
these input tables also contain a field called "Configured", which is set to
true or false in another process based on an OrderNumber. (So an order's
inputs are stored in several input tables, and the MainTable is a summary...
|
by: Katrina |
last post by:
I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:
Sub dbOpentableX()
Dim dbsAddSampling As Database
| |
by: David Wender |
last post by:
I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.
Dim objKeys(2) as Object
objKeys(0) = "CL"
objKeys(2) = 4000
Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
|
by: Geoff Jones |
last post by:
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.
|
by: john |
last post by:
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent records. Should I design a new
form for this or can I somehow make this work in the same form.
Thanks in advance,
john
|
by: qudra |
last post by:
Searching multiple records
--------------------------------------------------------------------------------
Hi guys. I need ur help.
In VB.NET we usually use this code to search the database through windows application:
CODE::::::
private sub btnSearch_click(--)
dim empno as byte
empno=inputbox("Enter emp no")
dim r as datarow
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |