mysql - silly question

Cani Select from a previous selected results?

[ikbenhet1]

is it possible to select the results from another select? example:

$query1 = Select * from all where all like '%thisone%'
$query2= Select * from $query1 where all like "%b%" LIMIT 0, 2

Why do i want to do this?
I have a mysql-result filled with urls, i'm trying to reduce the number of results per domain to 2 urls, preferrebly through a mysql-select.

[dmorison]

Hi, have a look at:

[mysql.com...]

for details of mySQL support for nested queries (which is what you're getting at)

[ikbenhet1]

Thanks for the link, i'm off to reading it.

[daisho]

If you have problems rewriting the query using joins (since sometimes subselects are just required) this is a cheep hack:

// mySQL workaround for sub selects. Will return a list for use with IN
function subselect ($sql,$conn) {
$result=mysql_query($sql,$conn);

$type=mysql_field_type($result,0);

$retstr="";
$sep="(";

while( $row=mysql_fetch_array($result) ) {
$retstr.=$sep;
$sep=",";

if( $type=="int" ) {
$retstr.="$row[0]";
} else {
$retstr.="'".mysql_escape_string($row[0])."'";
}
}

if( strlen($retstr)>0 ) {
$retstr.=")";
} else {
if( $type=="int" ) {
$retstr="(-1)";
} else {
$retstr="('null value')";
}
}

mysql_free_result($result);

return $retstr;
}

To use you can just do:

$query2="Select * from all where all like "%b%" and all IN (".subselect("Select key from all where all like '%thisone%' ",$conn).") LIMIT 0, 2";

I hope that helps and makes sence.

Also you could use a temp table and to a select into in your first statement then your second statement selects from the temp table.

daisho.

[ikbenhet1]

Daisho, youre way ahead of me.

I'm still trying to extract the root from a url, in mysql and compare it agains the result-array, and see if it has 2 result for that domain already.

Not making much progress, found instr and substr for mysql to use to extract the root-domain of a url, but no idea how to implement it.
Currently i am using this query, without the 2 urls per domain check.

SELECT COUNT( url ) AS repetitions, `linkout`
FROM `link_database`
WHERE `anchor`
LIKE "%nl%"
GROUP BY linkout
ORDER BY repetitions DESC
LIMIT 0 , 10

[daisho]

Hey ikbenhet1,

I'm not getting a clear picture of what's going on here. Can you give me a couple example rows from the database just so I can see the data that's being stored. That will help me to wrap my head around it.

daisho.

[ikbenhet1]

If i past from the database, my post will be filled with urls, so i made an example with fake urls.

link_database contains:
id ¦ url ¦ linkout ¦ anchor ¦ link_value

1 ¦www/example.com ¦ www.test1.com/a ¦ a ¦ 1
2 ¦www/example.com ¦ www.test1.com/b ¦ b ¦ 1
3 ¦www/example.com ¦ www.test1.com/c ¦ c ¦ 1
4 ¦www/example.com ¦ www.test1.com/c ¦ c ¦ 1
5 ¦www/example.com ¦ www.test2.com/ ¦ test2 ¦ 1
6 ¦www/example.com ¦ www.test3.com ¦ test3 ¦ 1

Sample output:
Repetitions ¦ url

2 ¦ h*tp://www.test1.com/c
1 ¦ h*tp://www.test1.com/b
1 ¦ h*tp://www.test1.com/a
1 ¦ h*tp://www.test2.com
1 ¦ h*tp://www.test3.com

The first 3 of the output are all from domain test1.com, i want to limit any domain to 2 results if possible.

[ikbenhet1]

I searched and found no answers. I did find the same question on another forum, where the last post mentions:

select distinct hostname,..... from <tablename> WHERE url LIKE '%example.com%'

That's for 1 result per domain i think? not what i need, i needed 2 results.

So, I guess there is no easy answer for this one. Thanks anyway.

[daisho]

Ok I am still not understanding what you are trying to get. In your sql examples you have like '%nl%'. Are you looking for all links with the top level domain in Holland?

What are you trying to do with this? Your example SQL looks like your trying to count anchor text but I don't think that's what you are trying to do.

daisho.

[ikbenhet1]

Yes, i am counting anchor text.

It's a search (simple) engine i use %.nl% because the database contains mostly sites from holland.

I've have inserted besides achortext the title's content and headers and url as a seperate rows into the anchor.

So it gets 1 point extra(repettition) if it's in the title, i point extra if it is in the content i point extra if it's in the url and 1 point extra for each anchor text from (other) sites.

All i want now is to reduce the number of urls per host to 2 in the select(results).

[edited by: ikbenhet1 at 7:54 pm (utc) on June 28, 2003]

[jatar_k]

WHERE url LIKE '%example.com%'

that will select every row where example.com appears in the url column and can be preceded or followed by any amount of data.

That exact search can be limited to 2 as you did in your first post and will return 2 rows where example.com appears in the column url.

[ikbenhet1]

I don't want to limit to 1 domain, i want all domains to limit to 2 like so:

retition ¦ url
6 ¦ www.example.com/free
5 ¦ www.example.com/funny
4 ¦ www.example.com/weird
3 ¦ www.another-example.com/free-things
2 ¦ www.another-example.com/special-offer
1 ¦ www.another-example.com/free-stuff

End result:

1) www.example.com/free
2) www.example.com/funny [more from example.com]
3) www.another-example.com/free-things
4) www.another-example.com/special-offer [more from another-example.com]

I've read a lot's about postgress mysql, is it better for such tasks?

[jatar_k]

for postgres sql you would still have to work out your queries and methodologies. I can't see any massive advantage to switching.

[ikbenhet1]

sorry to post again,
The query below works, and counts how many urls a domain has got in the database.

Is it possible to change that instead of the database it searches through the results of this same query? (the bold 'url' parts)?

SELECT * , count( SUBSTRING( url, 1, LOCATE( '.nl', url ) + 3 ) ) AS root_domainnl
FROM link_database
WHERE url
LIKE SUBSTRING( url, 1, LOCATE( '.nl', url ) + 3 )
GROUP BY linkout
ORDER BY root_domainnl DESC

[ikbenhet1]

I've still not solved this problem.
My final thought about this was, i can simple count the domains in a variable with @ .

SELECT * , SUBSTRING( url, 1, LOCATE( '.nl', url ) + 3 ) AS root_domainnl
where @root_domainnl <> ""
SET @root_domainnl=@root_domainnl+1
FROM link_database;

My question basicly is: is this a good idea and can this be done this way?