scarhand

I build an array and then foreach array item I perform a large query. I need to write this into 1 query but don't know how. The problem is this page is taking a long time to load because it is performing 1 query, then its performing another query for each result of the first query via a foreach loop. Heres the code: $arr_websites = array(); $sql = mysql_query("select `website` from `leads` group by `website` asc"); while ($row = mysql_fetch_assoc($sql)) { if (!empty($row['website'])) $arr_websites[] = $row['website']; } foreach ($arr_websites as $key => $ws) { $sql = mysql_query("select l.website, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 else 0 end) AS c_day, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 1 day, '%Y-%m-%d') then 1 else 0 end) AS c_yesterday, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 2 day, '%Y-%m-%d') then 1 else 0 end) AS c_2_days, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 3 day, '%Y-%m-%d') then 1 else 0 end) AS c_3_days, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 4 day, '%Y-%m-%d') then 1 else 0 end) AS c_4_days, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 5 day, '%Y-%m-%d') then 1 else 0 end) AS c_5_days, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 6 day, '%Y-%m-%d') then 1 else 0 end) AS c_6_days, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 7 day, '%Y-%m-%d') then 1 else 0 end) AS c_7_days, sum(case when YEARWEEK(FROM_UNIXTIME(COALESCE(a.date_assigned, l.date_added))) = YEARWEEK(CURDATE()) then 1 else 0 end) AS c_week, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now(), '%Y-%m') then 1 else 0 end) AS c_month, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 1 month, '%Y-%m') then 1 else 0 end) AS c_last_month, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 2 month, '%Y-%m') then 1 else 0 end) AS c_2_months, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 3 month, '%Y-%m') then 1 else 0 end) AS c_3_months, sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y')= date_format(now(), '%Y') then 1 else 0 end) AS c_year from `leads` as l left join `assignments` as a on (a.id_lead = l.id) left join `dealerships` as d on (d.id = a.id_dealership) where a.is_reassign='no' and a.id_dealership!='65' and a.id_dealership!='77' and a.id_dealership!='89' and a.id_dealership!='138' and (a.website='$ws' or l.website='$ws') ") or die(mysql_error()); } I don't know how to get this to have the correct sums. The problem is that the "leads" table and then "assignments" table both have a "website" field, sometimes the website field may be empty in one or the other which is why you see the code and (a.website='$ws' or l.website='$ws'), where $ws is the result of the first query that is being looped in the foreach for the second query. The assignments have an id_lead field which links the assignments to a lead. Heres some things I've tried which makes the page faster, but does not produce the correct sum's: - i've tried grouping by l.website - i've tried a left join with the code left join assignments as a on (a.id_lead = l.id AND a.website=l.website) I hope this makes sense, any help would be greatly appreciated.

I'll show you what I'm trying to do in the following query, with this next query you can see I'm trying to get the c_year variable to be a count of the rows with the website for the current year, however the number is showing the same for all results: $sql = mysql_query("select `website`, (select count(*) from `assignments` where YEAR(FROM_UNIXTIME(`date_assigned`)) = YEAR(CURDATE())) as c_year, count(*) as c_all from `assignments` group by `website` order by `website` asc "); So it is showing "4433" in every row of the results for each website... However the "c_all" variable shows a perfect count.

is there a way to see a websites htaccess file that is not on your own server? i want to know this for educational purposes so i can see how some of the higher ranking websites do their SEO with their htaccess files if it is impossible, i understand

i need some help with this sql query heres my mysql table structure: DOMAINS - id - domain_name KEYWORDS - id - keyword - keyword_slug PAGES - id - id_domain - id_keyword what i need is my sql query to give me results in this format: - domain_name -- keyword in domain linked via the "pages" table -- keyword in domain linked via the "pages" table -- keyword in domain linked via the "pages" table - domain_name -- keyword in domain linked via the "pages" table -- keyword in domain linked via the "pages" table -- keyword in domain linked via the "pages" table so i was thinking something like this? not sure how to get the grouping, confusing me a little: $sql = mysql_query("select d.domain_name, k.keyword from domains as d, keywords as k, pages as p where d.id=p.id_domain and k.id=p.id_keyword group by p.id_domain"); isn't exactly working....any help would be much appreciated.

if you look on this page: http://www.lyricsinger.org/lyric/michel-sardou/c-est-ma-vie there are several black diamonds with question marks in them. these are french characters. in the database, the TEXT field they are stored in is set to utf8_general_ci in the header you can see i have made the utf-8 charset declaration it makes no sense why it isn't showing the characters properly.

i have created an index on an ID column, however i need to create some type of index or find a solution for when i am searching a TEXT column for a LIKE its taking forever to execute the LIKE query on this text column because there are hundreds of thousands of rows. i dont think i can just create an index on it, can i? what is the proper way to optimize this?

I figured it out. It was missing a ; after each ascii code.

Heres my code: $string = '&#73&#116'; html_entity_decode($string); echo $string; It should be echoing "It", but its just echoing the ASCII codes. Am I using the wrong function? I also tried htmlspecialchars_decode and it changes nothing.

i want this page: search.php?q=test&x=15&y=13 to go to: search/test/ but dont know how to do this. the search form uses "GET" and uses an input image to submit.

got it <?php $poem = preg_replace("/([a-z]){1}([A-Z])/", "$1\n$2", $poem); ?>

I have some information in a database, now unfortunately it is all on 1 line i am trying to add line breaks whenever a small letter is followed by a big letter, since this is properly formatted example: The blue birdSang all nightAnd cried all day I need it to output: The blue bird Sang all night And cried all day Heres my code: <?php $poem = preg_replace("$[a-z]{1}[A-Z]$", "$1$2\n", $poem); ?> Its working, except its removing the 2 characters from the start and end of each outputted line. example of the output: The blue bir ang all nigh nd cried all da

i got it <?php if ($pagi_count > 1) { echo '<ul class="pagination">'; if ($pagi_num > 1) echo "<li><a href=\"$url/page/$get_letter\">«</a></li>"; $pagi_start = 1; $pagi_end = $pagi_count; if ($pagi_count > 15) { $pagi_start = $pagi_num - 7; $pagi_end = $pagi_num + 7; if ($pagi_start < 1) { $pagi_end -= $pagi_start; $pagi_start = 1; } if ($pagi_end > $pagi_count) { $pagi_start += ($pagi_count - $pagi_end); $pagi_end = $pagi_count; } } for ($p = $pagi_start; $p <= $pagi_end; $p++) { $link = "$url/page/$get_letter"; if ($p > 1) $link .= "/$p"; if ($p == $pagi_num) $class = ' class="current"'; else $class = ''; echo '<li'.$class.'><a href="'.$link.'">'.$p.'</a></li>'; } if ($pagi_num < $pagi_count) echo "<li><a href=\"$url/page/$get_letter/$pagi_count\">»</a></li>"; echo '</ul>'; } ?>

heres my code: <?php if ($pagi_count > 1) { echo '<ul class="pagination">'; echo '<li><a href="#">«</a></li>'; $pagi_start = 1; $pagi_end = $pagi_count; for ($p = $pagi_start; $p <= $pagi_end; $p++) { $link = "$url/page/".$get_letter; if ($p > 1) $link .= "/$p"; if ($p == $pagi_num) $class = ' class="current"'; else $class = ''; echo '<li'.$class.'><a href="'.$link.'">'.$p.'</a></li>'; } echo '<li><a href="#">»</a></li>'; echo '</ul>'; } ?> now this works fine, however i want it to only show 20 page numbers, and if there are more than 20 total pages, i want the current page to be in the middle of the list (unless of course the current page is 1 or 2 then it would be more to the left of the list........ example: with 13 total pages, on page 8: << 1 2 3 4 5 6 7 ( 9 10 11 12 13 >> with 55 total pages, on page 2: << 1 (2) 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 >> with 65 total pages, on page 12: << 2 3 4 5 6 7 8 9 10 11 (12) 13 14 15 16 17 18 19 20 21 >> im really stumped on the math for this....any help would be much appreciated.

i have a script that wrote which updates fields in a mysql database with millions of rows. it just uses a while loop for each row. now the problem is that i forgot to add something to the script, but i already visited the .php file in my browser. i closed the browser and when i refresh the database it still seems to be running even though i closed the browser the script was running on.... is this because the script is still cached in the memory or what? how can i stop it?

ok so this works: "select * from `colors` where `color` REGEXP '^[^[:alpha:]]'"; however i need to group them and do a count, and this doesn't work: select color, color_slug, count(*) as count from `colors` where `color` REGEXP '^[^[:alpha:]]' group by `color` order by `color` asc all it does is seem to group all of the rows together if they match the REGEXP.