Jump to content

scarhand

Members
  • Posts

    382
  • Joined

  • Last visited

Posts posted by scarhand

  1. 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.

  2. 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.

  3. 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

  4. 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.

     

  5. 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?

  6. 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

  7. 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>';
    }
    
    ?>
    

  8. 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 (8) 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.

  9. 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?

  10. 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.

  11. 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.

  12. 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.

  13. `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.

  14. 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?

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.