small php/mysql help needed

hcp · 04-16-2008, 05:59 AM

i need help on this, can someone please look at this,

let's say there are two tables on my database
1. category table having column id with values like 1 to 10
2. details table having column category with the values like 2, 4, 6, 8, 10

so i want to list 5 values from category table's id columns but only those values that are in details table category column,

how do i do it pls guide me.

Wildhoney · 04-16-2008, 01:50 PM

Something like this, perhaps?

sql Code:

SELECT
    category.id
FROM
    category
INNER JOIN
    details
ON
    details.category = category.id
LIMIT
    5

freenity · 04-16-2008, 08:19 PM

[SQL]
SELECT category.id
FROM category, details
WHERE category.id = details.category
LIMIT 5;
[/SQL]

or maybe this?

hcp · 04-17-2008, 01:05 AM

thank you very much both of this works but i am facing one problem

my details table has some same value in many rows like this

1
1
1
1
10
10
10
4
4
5
5
5

show how can i limit only the the numbers by descending orders instead of limiting the rows

freenity · 04-17-2008, 01:07 AM

[CODE=SQL]
SELECT category.id
FROM category, details
WHERE category.id = details.category
ORDER BY category.id DESC;
[/code]

just add order by `field` desc; or asc for ascending order. ;)

hcp · 04-17-2008, 01:26 AM

actually it's like this in my category table there is id column having rows with values like this
1
2
3
4
5
6
7
8
9
10

and in my details table there is category column having rows like this
2
2
2
2
6
6
6
6
4
4
4
8
8
8
10

and i did this

PHP Code:


			
$query = "SELECT category.id, category.title, details.category ".
 "FROM category, details".
        "WHERE category.id = details.category ORDER BY category.id DESC LIMIT 5";
     
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
    echo $row['title']. " - ". $row['category'];
    echo "<br />";
}

and the output is now like this

10
8
8
8
6

and that is what i wanted but it's continuing the same value if i don't do LIMIT 5 than it goes like this
10
8
8
8
6
6
6
6
4
4
4
2
2
2
2

all i want is to output like this without repeating any values like
10
8
6
4
2

please help

serversphere · 04-17-2008, 06:58 PM

Try

Code:

SELECT category.id, category.title, details.category
FROM category, details
WHERE category.id = details.category
GROUP BY category.id
ORDER BY category.id DESC