How do I construct this php/mysql query?

[Jeffro]

I'm hoping someone can help me solve my latest riddle.  I'm trying to select the following from mysql:

 

"Look for the first number in the string..  omit the forward slash, which will always precede the first number..  and return the 3 letters in front of that slash."

 

For example.. 

http://missouri.mydomain.com/abc/def/834838838338.php would return:  def

 

and

 

http://ohio.mydomain.com/xyz/33433344.php  would return:  xyz

 

I'm thinking it's going to start with a: select substr(now what?) from mytable

 

My urls will always take 1 of the 2 forms above.. and they will always have a different state name.

 

 

[mikesta707]

If you are gaurenteed that your URL's will always look like that, simple string manipulation would suffice. This is what I would do

$str = strtolower($url);//convert it to lower case
$str = $str_replace("http://");//remove the http:// part at the beginning
$pieces = explode("/", $str);

$size = count($pieces);//get count
$directory = $pieces($size-2);//get the directory right before the numbered file name

 

You could also do a regular expression search, but I don't see the point if you can use simple string functions

[Jeffro]

Thanks for the assistance.  However..  I'm looking to just do a select in mysql.  My goal is to count all urls with like codes and display those on my page. 

 

So..  In my example, I'm trying to count all the rows in mysql with codes:  def and xyz.  I'll ultimately display these on my page like:

 

def (30) 

xyz (28)

 

The solution will resemble the following..  which currently selects the first part of the url in mysql and works well:

SELECT SUBSTR(url,8,INSTR(url,'.')- as name, COUNT(*) as count FROM mytable GROUP BY SUBSTR(url,8,INSTR(url,'.')-

[mikesta707]

Oh I see. Sorry I misunderstood. This is the PHP baord. You may want to try posting this in mysql help

 

[xyph]

You don't want to group by regexp. The query required would be extremely slow, require a couple ugly IFs, etc.

 

You're better off using mikesta707's snippet and giving the top directory it's own column on your database.

 

That way it becomes much easier to count and group.

 

SELECT COUNT(*) as `count`, `name` GROUP BY `top_dir`[code]

BTW - The MySQL regex to perform this can be found here
http://www.cruzinthegalaxie.com/counting-items-with-mysql-and-regex-in-the-group-by-statement/

[code]SELECT
DATE_FORMAT(signup_date,’%m-%d-%Y’) as signup_date,
count(landing_page) as signup_count,
IF(landing_page REGEXP ‘^/(google|yahoo).*’,SUBSTRING(landing_page,1,7),landing_page) as lp
FROM signup
WHERE
1=1
AND signup_date >= ‘” . $istartdate . “‘
AND signup_date <= ‘” . $ienddate . “‘
GROUP BY DATE_FORMAT(signup_date,’%m-%d-%Y’), IF(landing_page REGEXP ‘^/(google|yahoo).*’,SUBSTRING(landing_page,1,7),landing_page)

 

[Jeffro]

Thanks guys.  I'll play a little more with what you both have provided and see if i can make a working solution between all the good information.