Public snipts » robertbanh's snipts » db

showing 1-3 of 3 snipts for db

# remove the group by if you want 1 total row returned.
#
select *, sum(s1.score+s2.score) total
from search_score s1
inner join search_word w1 on s1.word_id = w1.id
inner join search_score s2 on s1.sp_id = s2.sp_id and s1.id != s2.id
inner join search_word w2 on s2.word_id = w2.id
where 
w1.word = 'zinc' and w2.word = 'soft'
group by s1.id
order by total desc
limit 50

# updated note: if this will span more then 2 and goes nth level, then it's
# faster to run each word search separately. unset vars as needed.

mysql

# remove the group by if you want 1 total row returned.
#
select *, sum(s1.score+s2.score) total
from search_score s1
inner join search_word w1 on s1.word_id = w1.id
inner join search_score s2 on s1.sp_id = s2.sp_id and s1.id != s2.id
inner join search_word w2 on s2.word_id = w2.id
where 
w1.word = 'zinc' and w2.word = 'soft'
group by s1.id
order by total desc
limit 50

# updated note: if this will span more then 2 and goes nth level, then it's
# faster to run each word search separately. unset vars as needed.

copy | embed

0 comments - tagged in

db,
mysq,
sum

posted by robertbanh on Feb 04, 2010 at 10:05 a.m. EST

∞ Count Monthly/Yearly Totals in MySQL

SELECT 
      MONTHNAME(createDt) AS month,
      YEAR(createDt) AS year,
      COUNT(*) AS total
FROM tracking
GROUP BY MONTH(createDt), YEAR(createDt)
ORDER BY id

sql

SELECT 
      MONTHNAME(createDt) AS month,
      YEAR(createDt) AS year,
      COUNT(*) AS total
FROM tracking
GROUP BY MONTH(createDt), YEAR(createDt)
ORDER BY id

copy | embed

0 comments - tagged in

posted by robertbanh on Jan 21, 2010 at 5:01 p.m. EST

∞ Use found_rows() in MySQL

// use found_rows() as a sql function
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

sql

// use found_rows() as a sql function
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

copy | embed

0 comments - tagged in

posted by robertbanh on Jan 21, 2010 at 4:56 p.m. EST

A good read

Web Database Applications with PHP & MySQL

Public snipts » robertbanh's snipts » db

A good read

robertbanh's tags