search for string in database records

bigfatwallet

Hi there,

I am having difficulty with the following and would appreciate any help you can give me.

CREATE TABLE ptpfullpageapproved (
 domain varchar(250) NOT NULL
) TYPE=MyISAM;

CREATE TABLE ptpfullpagehistory (
 username varchar(50) NOT NULL default '',
 referringip varchar(15) NOT NULL default '',
 referringsite varchar(250) NOT NULL default '',
 referringdate date NOT NULL default '0000-00-00',
 valid char(3) NOT NULL default ''
) TYPE=MyISAM;

I would like the query to loop through ptpfullpageapproved and for each value loop through ptpfullpagehistory and count the number of occurences the string $domain is found within $referringsite.

I will also need the query to include these additional where statements:
username='$id'
valid='Yes'

Can you please help me with the code to output this on a php file.

Many thanks in advance for your help.

wilku

You should use query similar to

SELECT referringsite,COUNT(*)
        FROM ptpfullpagehistory,ptpfullpageapproved
        WHERE referringsite=domain AND username='$id' AND valid='Yes'
        GROUP BY referringsite;

bigfatwallet

Hi wilku,

The query you suggested will always return 0.

I am not looking for when they are the same as that will not happen.

I would like the query to loop through ptpfullpageapproved and for each value loop through ptpfullpagehistory and count the number of occurences the string $domain is found within $referringsite.

For example:

ptpfullpageapproved might have two records:
phpbuilder.com
hotmail.co.uk

ptpfullpagehistory table might have three records:
username1 - ip - http://www.phpbuilder.com/etc - todays date - yes
username1 - ip - http://www.hotmail.com/etc - todays date - yes
username1 - ip - http://www.phpbuilder.com/etc - todays date - null

What I am trying to do is loop through ptpfullpageapproved and for each record loop though ptpfullpage history and count the number of times the string $domain is found within the referring url.

My results would look something like this for username1

Domain | Total Hits

phpbuilder.com | 2
hotmail.com | 0

Thanks in advance for your help if you an help me.

wilku

would changing

WHERE referringsite=domain AND username='$id' AND valid='Yes'

WHERE LOCATE(domain,referringsite) > 0 AND username='$id' AND valid='Yes'

help?
Or try to look into string comparison operators (LIKE) in mysql documentation