select help needed

pelegk2

i tried to combine this 2 select(lines of code) 🙁on mysql)
$query = "SELECT DISTINCT main_group FROM products531 GROUP BY main_group";
$result = mysql_query($query) or die ($query);
while ($row = mysql_fetch_array($result))// 1
{
$query1 = "SELECT DISTINCT charact FROM products531 WHERE main_group LIKE '%".$row['main_group']."%' GROUP BY charact";

and i combined it to :

SELECT DISTINCT charact FROM products531 WHERE main_group in  ( SELECT DISTINCT main_group FROM products531 GROUP BY main_group ) GROUP BY charact

but it isnt working?i am getting error message why?

eoghain

The reason that this isn't working is that MySQL doesn't currently support SubQueries (having a query in the where clause of another query).

I'm looking at your 2 queries and I'm a bit confused as to the need for both of them. Your first query gets a list of distinct main_group values from the products531 table. And your second query gets a list of distinct charact values from that same table based on the main_group value.

So given this data:

main_group | charact
1 | a
1 | a
1 | b
1 | c
2 | a
2 | d
2 | d

Your looping structure would give you a,b,c for main_group 1 and a,d for main_group 2. Is that what you want or are you looking for just a,b,c,d cause that is what your subquery would return to you. And if you are looking for a,b,c,d then you just need this query:

SELECT DISTINCT charact FROM `products531`;

Danicek

As eoghain said, your query seems a little bit strange.
Can you give us closer description of what you want to get here?

pelegk2

first i ive u the code,it includes 2 select files that i tried to put in 1 select that i first poseted the selects are :

$query = "SELECT DISTINCT main_group FROM products531 GROUP BY main_group";
$result = mysql_query($query) or die ($query);
/// start level 1

while ($row = mysql_fetch_array($result))// 1
{
$query1 = "SELECT DISTINCT charact FROM products531 WHERE main_group LIKE '%".$row['main_group']."%' GROUP BY charact";
$result1 = mysql_query($query1) or die ($query1);

}

so from this 2 selects i tried to build 1 select,whcuh based on the distinct result of the first it looks for every 1 of the result in the first 1 all the matching for it in the secon select using the
$row['main_group'] value!
so how do i combine this 2 selects into 1?
thanks again
PEleg

ultraslacker

Read the mysql manual on JOINs.

eoghain

You aren't making any sense. If you put these two queries into a single query the results that you are going to get are going to be the same as just writing this query:

SELECT DISTINCT charact FROM `products531`;

That I posted before. If you can give us a sample of the data in the table and what data you expect to get back from your query then we can help you solve your problem.

pelegk2

i did in the end this quesry :

SELECT DISTINCT main_group,charact,sub_group FROM `products531` GROUP BY charact,sub_group order by main_group

and then i wanted to add to it :

"AND products531.main_group LIKE '".$row['main_group']."' "

where the $row['main_group'] is the value that i am getting when going over the record set value of the first query!
how can i combine this 2 quesrys into 1?
is it possilble?

eoghain

Again you haven't told us what data is in your table and what data you want to get back out of your table.

Let me try to explain this better since you don't seem to be understanding what I'm saying.

Your 1st query, the one that selects distinct main_groups, will return you a list of all unique main_group fields in your database.

Your 2nd query, the one that selects distinct characts based on the main_groups returned by your first query, will return all unique characts in your database for the given main_group.

This is all well and good. But if you try to create a single query out of these 2 queries then you will be returning all unique characts in your database. You won't get any duplicate characts like you would with the 2 queries. So if you want duplicates, so you can say something like main_group 1 has charact a and b and main_group 2 has charact a, then you need to do 2 queries.

pelegk2

thanks alot

pelegk2

if it helps?

eoghain

Yes post your PHP code, but also post what you want that code to do. Also post some sample data from the table that you are querying and finally post what you expect to retrieve from that table in both of your queries.

pelegk2

i am attching the 2 files with the code
how do i copy few lines fronthe db+the titles of each col to put here?

eoghain

Run "select * on tbl_name" from the mysql command line interpreter and then just cut and paste a few lines from what is returned.

pelegk2

is attached

eoghain

I can't read any data from your main_group or charact fields they appear as ????º??- ???????.

pelegk2

it again
but anyway i will not understand its not english its hebrew