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.

heres a better example 234 soap #@$ orange red turtles i want the query to select the first 2 items based on the fact that the first character in the string is not an alpha (letter a-z).

heres a better of what i'm trying to do using your example pikachu "select colors, count(*) as count from `table` where substring(`colors`, 1, 1) NOT REGEXP '[:alpha:]' group by `colors` order by `colors` asc"; though this is not working, you can see what im trying to do

lets say i have a table with the following in the "colors" field: 3 reds red orange 4 blues $%$ blues i need a query that will select the 3 rows where the first character of "colors" is not an actual letter, so the results returned should be: 3 reds 4 blues $%$ blues i have no idea how to do this. i wrote a query that selects by the first character if it is a letter.....but im stumped with this.

My current code (which works): <?php $body = explode("\n", $body); $body = array_slice($body, 3); $body = implode("\n", $body); ?> Is there a better way of doing this, so I dont have to explode and implode an array just to accomplish this?

The problem is the included IPN file has to be on another server, which is why I'm referencing it as such. Yes - that is the GET data I'm trying to pass to the remote IPN file.

I have this code: <?php $item_name = urlencode('product name'); $payment_amount = urlencode('20.00'); $payer_email = urlencode('me@email.com'); $getvars = "?item_name=$item_name&payment_amount=$payment_amount&payer_email=$payer_email"; include "http://www.mysite.com/ipn.php$getvars"; ?> It only seems to be passing $item_name, the other 2 variables come up as being set, but are empty.

you rock

`the_date` is an integer whose value is inserted via the php time() function this code doesn't seem to work, so I imagine I am missing a function: $sql = 'SELECT SUM(`amount`) as donate_total from `donations` where MONTH(`the_date`) = MONTH(CURDATE())'; any help would be great.

the problem is i want to have it so that: mysite.com/subpage would point to: myscript.com/?domain=mysite.com&page=subpage

how would i go about doing this? lets say i want to use different domains i.e.: mysite.com mycoolsite.com but i want them to both use the same php script i.e.: myscript.com/site.php?domain=mysite.com each site will show different content depending on the domain $_GET variable... can anyone help me with this?