MySQL Multiple Select Question

[ackerley1]

Hello, I am trying to select multiple, specific, rows from a table but I am not having much luck.

The query I am using for 1 row is:

SELECT * FROM `12all_fieldsd` WHERE `eid` = 2750

the eid is the specific field that I want to find and I need to find about 70 rows. I have tried some AND statements and also multiple individual queries like the one above and other hack job attempts all with no success.

Urgent help on this would be greatly appreciated.

Thanks

Rob

[brandrocker]

the eid is the specific field that I want to find and I need to find about 70 rows. ( condition?)

...not clear.
Please re-frame your question with example data.

[computergenius]

You mean "eid=123 AND eid=124 AND eid=125...."?

If so, how do you decide what values of eid are to be returned?

[DaveSawers]

If the eid field is what you want to find, you should use something like:

SELECT eid FROM `12all_fieldsd` WHERE ...

Although as noted by others, your request is not very clear.

[ackerley1]

Okay... for example... I want to find the follow eid's 100, 103, 104, 117, 119, 122

It is a contact form database, with comments and addresses of each person attached to an eid number that coresponds with persons name and server info (time, IP, etc) logs in a different table.

Problem is I need specific ones from 24,000+ entries.

Thanks for your help.

[brandrocker]

Still not very clear, if I'm not mistaken.
Do you mean?

You have two tables - one for person ( containing individual details, eid is PK)) and the other for comments etc (eid is FK)

Now you want to select comments made by some persons (say comments made by eid 100, 103, 104, 117, 119, 122)

If that be the case try 'OR' in your WHERE CLAUSE (provided you know your eid's already).

Otherwise, please let me know your table structures and exactly what data do you want to select.

[ackerley1]

hmmm... sorry for the late repsonse... was in the war path of Hurricane Dean when it made landfall in the Yucatan and Northern Belize...

Okay here is the structure:

Table 1: Name, email, IP, time and date stamps and the EID - which is unique.
Table 2: address, phone, comments, etc - each field has its own unique id, but also has the corresponding EID to the person who made the entry. So it could have up to 11 rows that correspond to 1 EID.

I know which EID's I want and and I really need nothing from Table 1 as I already have extracted that. I just need a way to get all the rows from Table two that correspond with specific EID's.

Thanks

[DaveSawers]

So use:

select * from 'Table 2' where EID=123

Which is pretty much what you came in with, so if it's not working, then:

SQL is case sensitive in table and field names so make sure that's right.

[Dubbya]

You might simply have too many apostrophes in the query. Try this.

SELECT * FROM Table2 WHERE eid='123'

If that's not the problem, then check your code to ensure that your problem isn't a typo or improper case in referencing a field, table or cell name.

W3Scools has some great tutes and examples you can use to build your SQL SELECT Statement.

Enjoy!

[ackerley1]

Yes, but is there a way to get more than one set of EID's?

SELECT * FROM Table2 WHERE eid='123' AND '124' AND '125'??

I would like to pull all of the ones I need in one query.

[imvain2]

I'm not sure if this is what you are looking for, but this combines table1 and table2's results based on matching the EID and it lets you provide multiple EIDs.

select * from table1,table2
where (table1.eid = 100
or table1.eid = 103
or table1.eid = 104
or table1.eid = 117
or table1.eid = 119
or table1.eid = 122)
and table1.eid = table2.eid

[marms767]

Yes, you use an IN statement for that

select * from Table2 where eid in ('123','124','125')

I think you mentioned an IP address could be the eid? If that is the case and these are the first 3 digits of each IP Address you are looking to match up you would have to only look at the first 3 characters of the "eid" and then compair it to the list given.

select * from Table2 where left(eid,3) in ('123','124','125')

[hewgriff]

select * from table1 where eid in ('100','101','102');

Would get you all the info from 100, 101 and 102 (and however many others you named specifically)

or

select * from table1 where eid like '10%';

Would get you all the info from 10, 100-109 and 1000 - 1099

Matt G.
Power tools, air tools, cordless power tools - ToolBarn.com

[scotthai]

hewgriff,
MySQL, SQL, ORACLE - they all run similar syntax, but it is actually not so hard to find the information you need.

//PHP REFERENCE
// find all ids like some name in category
// table bigrows has n_id, and cat_id
$sql = @mysql_query("SELECT * FROM 'bigrows' WHERE n_id = '2' AND cat_id = '1'");
This returns a results set from 'bigrows' (table name) where the lookup item, person id is 2 while also returning a second event qualifier of cat_id = 1. This is great when looking up loads of data, you could look for all daves from california like so.

$name = $_POST,$_GET,$_REQUEST,(ETC) ['name'] where the input is Dave, david, dave // example of one type of variable getter method --> POST,GET,REQUEST
$name = strtolower($name);
$sql = @mysql_query("SELECT * FROM users WHERE n_name LIKE '%$name%' AND state_id ='1' AND state_name = ' CALIFORNIA'");

There are also many different ways of collecting data across multiple datasets with a real well built database. So the challenge is first creating the database to function well within the scope of the project.

So onto specific names from the table,

$sql = @mysql_query("SELECT name,date,id,timestamp FROM bigtable WHERE eid = '123');
This returns the rows from the select statment,
if using PHP you can go further and create an associative array of the data.

$row = mysql_fetch_assoc($sql);
echo $row['name']; // prints out the name as a match to eid 123

You can also use the MATCH method of SQL (MySQL 4.1)
SELECT * FROM 'bigtable' WHERE MATCH (name,date,id,timestamp) AGAINST ('%word%');
SELECT * FROM 'bigtable' WHERE MATCH(eid) AGAINT (eid);

The later would get you an absolute match against the eid, however the drawback to this method of searching is that MySQL has in the default config that the match must be longer than 3 characters, so this method works better when doing a FULL INDEX search.

For what you are looking for the best bet is going to be a select against the rows you wish to fetch information from and then doing a WHERE clause as an exact match to the EID.

SELECT name,id,timestamp,date FROM bigtable WHERE EID = '123';

I hope this helps. If you want to email me the actual problem in a better description I could try and help out better.

Scott Haines
web design and development

[CarolineBogart3]

Originally Posted by ackerley1

hmmm... sorry for the late repsonse... was in the war path of Hurricane Dean when it made landfall in the Yucatan and Northern Belize...

Okay here is the structure:

Table 1: Name, email, IP, time and date stamps and the EID - which is unique.
Table 2: address, phone, comments, etc - each field has its own unique id, but also has the corresponding EID to the person who made the entry. So it could have up to 11 rows that correspond to 1 EID.

I know which EID's I want and and I really need nothing from Table 1 as I already have extracted that. I just need a way to get all the rows from Table two that correspond with specific EID's.

Thanks

So you don't say what the ID is that Table1 and Table2 have in common. Assuming it's customer_id for the sake of this example:

select table2.* from table1, table2 where table1.customer_id=table2.customer_id and x

where x is
table1.eid IN ('some','set','of','eid','values');
or
(table1.eid >= 'low value' and table1.eid <= 'high value');

[kylegetson]

Not sure if you are still having trouble, Im not clear on your question either, but try this:
SELECT[list all the fields you want to be listed]
FROM [the table your pulling from]
WHERE eid=whatever

hope that helps.

[malco-UK]

Among others I do not understand. Is it that you cannot fetch multiple results. ie You can get one but not more ? or are you unable to access the field at all.

If you are having problems with multiple results, use a loop to feed each query as a single query.

LMK if this works

[John Redfield]

It sounds like your problem is related to how you arrived at the criteria for the data set.

You mention eid 100 then eid 103... what is the criteria that excludes eid's 101 & 102?

Once you can clarify that, you can build that into the query, most likely with a join and where clause conditions. You use a join when you are querying more than one table. You can also create a dataset as a temporary table, and use the temporary table to make your join. It all depends though on the how you arriving at the criteria within the data set.

Can you share that with us?

John

[ackerley1]

Okay, this is getting wildly out of control and more complicated than I thought...

Firstly... forget table 1... it is not needed in this instance.

Secondly.... if I use the following query:
SELECT * FROM `table2` WHERE `eid` = 2750
I get the results I want and need for eid 2750. If i repeat that query and change the eid value I get another set of results I need. However I have over 60 of these to retrieve and I don't want to do them one at a time.

Is there a way to combine:
SELECT * FROM `table2` WHERE `eid` = 2750
SELECT * FROM `table2` WHERE `eid` = 2752
SELECT * FROM `table2` WHERE `eid` = 2757
SELECT * FROM `table2` WHERE `eid` = 2758
etc.

into 1 single query?

Thanks

[John Redfield]

Hi Rob,

Yes, you have two choices.

1. Build the criteria into the query. Why are choosing those particular eid's? Do they have a specific value (for example a date or date range) that you can query against? Whatever that value is that makes these eids different from the others must be added to the query.

2. Write out all the specific eids in your Where Clause using AND.

Example: SELECT * FROM `table2` WHERE `eid` = 2750
AND `eid` = 2752
AND`eid` = 2757
AND`eid` = 2758


Obvioulsy number one is the way to go if its doable.

Hope this helps