Jump to content

shoz

Staff Alumni
  • Posts

    600
  • Joined

  • Last visited

Posts posted by shoz

  1. <?php
    
      $path = $_SERVER['HTTP_HOST'].'/';
      require $path.'charts.php';
    
    ?>

     

    So what Caesar said earlier is best?

     

    Or use:

    <?php
    
      $path =  $_SERVER['DOCUMENT_ROOT'].'/';
      require $path.'charts.php';
    
    ?>
    

     

    EDIT: Sounds like they are both valid, but the second puts less stress on server?

     

    If you're using an absolute path use $_SERVER['DOCUMENT_ROOT']. It's more than just the stress on the server.

     

    For example

    page1.php

    <?php
    require($_SERVER['DOCUMENT_ROOT'].'/myvars.php');
    echo $var1
    ?>
    

     

    myvars.php

    $var1 = 'hello world';
    

     

    As it stands now, the output of the page1.php script will be 'hello world' but if the require line is changed to "require($_SERVER['HTTP_HOST'].'/myvars.php');" apache will parse myvars.php and the OUTPUT will be included in page1.php which in this case is nothing and page1.php will not have a $var1 variable set meaning there will be no output other than possibly errors referring to the fact that the $var1 variable does not exist.

  2. You're using a relative path. The path is always going to change, if you move the file.

     

    Unless...

     

    <?php
    
      $path = $_SERVER['HTTP_HOST'].'/';
      require $path.'charts.php';
    
    ?>

     

    How is that different from just using / to go to the root folder and then type the filename? I _WAS_ fairly sure that / went to the root, but now I'm doubting my knowledge.

     

    How is starting off with / a relative path? I've always been told that's an absolute path to the root.

     

    / can be thought of as the top directory (root) of a linux system. All paths start from "/". Apache has what's called the DOCUMENT ROOT. It's the top directory for files that apache will serve. In your case that directory could be "/home/charlieholder/www".

     

    require("/filename") looks for "filename" starting at "/" and not "/home/charlieholder/www", while a request over the web for "http://www.host.com/filename" causes apache to look for the file in "/home/charlieholder/www/". You can use the variable $_SERVER['DOCUMENT_ROOT'] to get the root you're looking for. It will contain (in this example) the value "/home/charlieholder/www"

     

    Btw, keep in mind that a require("http://www.host.com/filename") also requests the page over the web. Compared to require("/home/charlieholder/www/filename") which uses the filesystem path and is most likely what you'd like to do because a request over the web will make apache parse the file as a php script and the OUTPUT will be included in your script rather than the code itself.

     

     

  3. Another approach in light of the variable ORDER BY column may be to create specific queries based on which column is used in the ORDER BY. SELECTing and LIMITing the results in a subquery using the relevant table.

    I'll keep this in the back of my mind in case I do have to come back to it or face a similar situation elsewhere.

     

    After putting more thought into it I don't believe the suggestion is usable. Other ideas of JOINing the wv_user and activity table in a subquery may be better to keep in mind.

     

  4. I placed a multi-column index (role_wvid, login) on wv_user and the using temporary; using filesort has been eliminated.

    explain_multi_idx.jpg

     

    Would someone be willing to offer a brief explanation or link to an explanation of why the multi-column index made a difference in this case?  I'm still a bit of a noob when it comes to DB optimization.

     

    These are the relevant parts of the query that make the multi-column index useful.

     

    WHERE you.role_wvid=2
    ORDER BY you.login
    LIMIT 0,25
    

     

    The basic explanation is that without the multi-column index MYSQL had to retrieve ALL the rows from the disk that had a role_wvid with a value of 2 and then ORDER/sort and LIMIT the results using a temporary table.

     

    With an index which can be thought of as being similar to the following

     

    role_wvid    login
    -------------------------
    1             john
    2             bill
    2             jim
    2             suzan
    3             betty
    3             mark
    

     

    you'll notice that MYSQL will be able to lookup the rows with a lot_wvid value of 2 and retrieve the rows in the order of the "login" without much effort using only the index. This also allows MYSQL to retrieve only the first 25 rows in the table that match. The EXPLAIN thinks that it will have to look at 101 rows but when the actual query is run it doesn't(shouldn't) because of the LIMIT.

     

    Many times I've put a multi-column index on the ORDER BY column before the WHERE clause column to achieve similar results as well. Keep in mind that this is my "understanding" of what happens and shouldn't be thought of as an authoritative answer.

     

    Also, if the LIMIT is left off the query or if the second part of the limit extends the actual number of records (LIMIT 100,25 on a table with 102 records), I notice that using temporary; using filesort makes its way back into EXPLAIN.  What would be the cause of this?

     

    I don't know why that occurs. It could be a bug that doesn't allow MYSQL to recognize that it can use the same plan to execute the query when the LIMIT extends beyond the number of records in the table. If the EXPLAIN also shows that it doesn't use the multi-column index, then a FORCE INDEX will probably help.

     

    Would it be more efficient to precede this query with another that counts the number of records in wv_user and stops the LIMIT from extending beyond the actual number of records?

     

    Usually you'd be using the LIMIT to create some kind of pagination for the results. Generally you'd retrieve the COUNT before hand to determine whether or not a "next" button should be displayed. Even if that's not the case retrieving the COUNT of the records in the wv_user table with a role_wvid of the specified value should be relatively quick.

     

    In regard to your post about restructuring the query. Another approach in light of the variable ORDER BY column may be to create specific queries based on which column is used in the ORDER BY. SELECTing and LIMITing the results in a subquery using the relevant table. I haven't worked it out in my mind completely so it may or may not be practical in this instance.

     

    You should find the following site helpful btw Optimizing MYSQL Queries

  5. I'm not at work anymore so I'll have to try your suggestions tomorrow, but...

     

    MAX(a.TimeRecord) AS LastActiveStamp,
    IFNULL(DATE_FORMAT(a.TimeRecord, '%a. %b. %D, %Y'), '-') AS LastActiveDisp

     

    I can't be sure without the code in front of me, but I think the program itself needs the timestamp as well, which is why I'm returning two separate fields there.

     

    I meant that the second line should have DATE_FORMAT(MAX(a.timeRecord), the MAX() is missing.

     

    As for the variables, my original query wasn't using them and I had added them thinking it would probably be more optimal.  Is there any reason why that's not the case?

     

    My first reason for suggesting you not use the variables is

     

    The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed.

     

    I also believe that identical expressions are evaluated only once but I can't find the entry in the manual (assuming it exists) that corroborates that. Assume that I'm wrong for now.

  6. Try putting a multi-column index on (role_wvid, login) and try the query again. Use an EXPLAIN and post the results. If MYSQL does not use the new index, try forcing it with "SELECT .... FROM wv_user AS you FORCE INDEX(indexname) CROSS JOIN ....".

     

    If the index is still not used then you can SELECT and LIMIT the rows from the wv_user table first in a subquery and then do the JOIN

    SELECT 
    you.*, s.full_name .... etc etc
    FROM
    (
        SELECT
        sub_you.user_wvid AS UserID, 
        LOWER(sub_you.login) AS Login, 
        LOWER(sub_you.password) AS `Password`, 
        sub_you.subname AS SubName
        FROM
        wv_user AS sub_you
        WHERE
        sub_you.role_wvid = 2
        ORDER BY
        sub_you.login
        LIMIT 0,25
    ) AS you
    .......
    CROSS JOIN ...
    CROSS JOIN ....
    ...
    GROUP BY
    you.login
    ORDER BY
    you.login
    

     

    Note that there is no second WHERE clause. The ORDER BY is repeated because it shouldn't be assumed that the JOIN will leave the result in the order created by the subquery.

     

    Could you also post each EXPLAIN after each attempt, even if one of the solution works.

     

    Btw, the following line should be.

     

    IFNULL(DATE_FORMAT(MAX(a.TimeRecord), '%a. %b. %D, %Y'), '-') AS LastActiveDisp 
    

     

    I'd also recommend against using the variables and instead do.

     

    CASE 
        WHEN SUM(IF(t.onsite=1, 1, 0)) > 0 AND  SUM(IF(t.onsite=0, 1, 0)) > 0 THEN
        'both'
    etc
    

  7. If you're using MYSQL 5.0 you should also be able to use a Stored Procedure. The logic should be similar to what you'd do in PHP. If the performance of the two turned out to be similar, I'd use PHP.

     

    If you have concerns about the speed of doing this, keep in mind that you can limit how many entries you handle at a time by perhaps displaying per month statistics.

  8. I know I'm just jumping in here, but if closed is simply a flag, don't bother indexing it, MySQL will never use this index.

     

    If MYSQL decided not to use the index because it believes it would be faster not to, then you should be able to force the use of the index. It is a flag but only in a few rows will it be NULL or have a value of 1 so it should be faster to use the index.

     

    Actually, now that I've thought about it in the context of the relationship that a row with a "Closed" field with a 1 or a NULL has with the subquery, I don't believe there's any reason to use the subquery. A row with a Closed field of 1 is closed and a row where the field is NULL is open. Those should be the last entries and there should be no need to consider the date.

     

    SELECT
    *
    FROM
    requests AS r
    INNER JOIN
    routing AS ro
    ON
    r.requestID = ro.requestID
    WHERE
    ro.Closed IS NULL
    

     

    EDIT: Eventually you'll probably be using an ORDER BY and LIMIT using the date field to go through the entries based on how long each has been open. A multi-column index on (thedatefield, Closed) should be the most appropriate when you decide to do that.

  9. WHERE ro2.Closed!=1  // all open tickets

    now u r right that bascially i am saying here is IS NULL equals open tickets, and IS NOT NULL will be searches for closed tickets. Should I use that IS NULL? or index Closed field and use what i have currently?

     

    You should index the "Closed" field and use "WHERE ro2.Closed IS NULL". An index can't be used for inequality. ie can't be used for "WHERE ro2.Closed != 1". By using Closed != 1 you should also be eliminating all rows where the field "Closed" is NULL. I would have expected the query to give you an empty set because of that.

     

    EDIT: Btw, for closed/resolved tickets use "WHERE Closed = 1" not "WHERE Closed IS NOT NULL"

  10. For the "else" statement, I am not sure what to put between there.  Will it be echo $thisCatID =0; if query can't find catID?  ???

     

    The else block should do whatever you think appropriate when the catID is not present.

     

    <?php
    if (isset(....))
    {
        //look for subcategories
       SELECT * FROM table WHERE ...
        
        //if no subcategories are found display a message informing the user  
        if (!mysql_num_rows($result))
        {
            echo 'no subcategories found';
        }
        else
        {
            //display subcategories
        }
    }
    else
    {
        /**
         * redirect to the list of categories from which the user can choose to view its subcategories.
         * You only need to redirect if the page displaying the categories is a separate script.
         *
         * You can alternatively echo a message stating that no categories have been found that match 
         * the selection even though none was made. What I'm trying to bring across is that you can do 
         * whatever you decide is best. You can even restructure the code if what you'd like to do requires it.
         */
    }
    ?>
    

     

    If you have any trouble, post what you've tried and explain what you're attempting to do.

  11. wow awesome, yeh that helps out.

     

    Actually I didnt think of a ticket not being assigned to someone as when you enter a ticket, you automatically assign it to someone from the beginning.

    All i needed was...

     

        SELECT

        ro2.*

        FROM

        helpdesk.routing AS ro2

        JOIN

     

          (

          SELECT

          requestID

          , MAX(DateTimeEntered) AS DateTimeEntered

          FROM

          helpdesk.routing ro1

          GROUP BY requestID

          ) AS rmax

     

     

        ON

        ro2.requestID = rmax.requestID

        AND

        ro2.DateTimeEntered= rmax.DateTimeEntered

     

        LEFT JOIN helpdesk.request r ON ro2.requestID=r.requestID

     

        WHERE ro2.Closed!=1  // all open tickets

     

    i think this is basically the same, ran alot faster for some reason though, maybe cause 1 less subquery i think?

     

    The queries are not the same. The LEFT JOIN on requests retrieves rows in the routing table even if they don't exist in the requests table. This should not be a logically possible scenario although an error could cause that situation.

     

    As for the speed of the query, it should not have been noticeably slow unless it was modified. I'd have to see the EXPLAIN of the queries and the CREATE statements for each. It should be noted that what the LEFT JOIN is doing in my query does to some degree limit how it can be constructed.

     

    Note that "WHERE ro2.Closed != 1" doesn't allow MYSQL to use indexes to do the comparison, should make the query that much slower and shouldn't return any "Closed" fields with NULL values. It's my understanding that the latest entry in an open request should have NULL as its Closed entry? Instead you should use "WHERE ro2.Closed IS NULL" I assume the "Closed" field represents the "SolvedIt" field in your query? You didn't mention a Closed field in your first post.

     

    EDIT: A short comment on the LEFT JOIN. As you said, the fact that I'm doing 2 subqueries could also make the query I posted slower and I could have done the order of the JOINs similarly to how you've done it to avoid that.

  12. There are 2 posts in the FAQ/Code Snippet Repository that deal with retrieving info from MYSQL.

     

    Post1 Post2

     

    In your example you could do something similar to

     

    <?php
    $result = mysql_query("select balance FROM account where account2='$theDest'");
    $row = mysql_fetch_assoc($result);
    $bal_dest = $row['balance'];
    $credit=($bal_dest+$theAmmount);
    
    echo "bal dest: $bal_dest <br />\ncredit: $credit";
    ?>
    

     

    You should be shown some examples of how to display query errors in one of the Faq Posts. If you have additional PHP problems similar to this one they should be directed to the PHP-Help forum.

  13. Requires MYSQL >= 4.1.

     

    SELECT
    *
    FROM
    request AS re
    LEFT JOIN
    (
        SELECT
        r.*
        FROM
        routing AS r
        INNER JOIN
        (
            SELECT
            requestID, MAX(DateGotTheRequest) AS DateGotTheRequest
            FROM
            routing
            GROUP BY
            requestID
        ) AS rmax
        ON
        r.requestID = rmax.requestID
        AND
        r.DateGotTheRequest = rmax.DateGotTheRequest
    ) AS ro
    ON
    re.requestID = ro.requestID 
    

     

    The first thing to note is the LEFT JOIN. If any requests have not been assigned to anyone, then an entry will still show up but all "routing" fields will be NULL. I assume this is a possible case and would be considered an open request.

     

    Note also that there are no tests on the SolvedIt field as all returned rows should either be opened are closed if they are in the routing table is my understanding. Is there another state that a request can be in?

     

    If you're using an older version of MYSQL post the version number

     

    SELECT VERSION();
    

  14. You removed the mysql_close() line from "top_adm.html"?

     

    I've removed that link the link error still persist...How do i remove those tags errors? I know this is just a MySQL help community forum not a paid support so i can't expect much but really thanks for all the helps.

     

    As for the notice errors. In top_adm.html you have

     

    if($gb=="") $gb == "%";
    

     

    Instead you should first test for the variables existence or lack thereof and then do the comparison or the assignment. I believe the "==" is an error and should instead be one "="?

     

    if (!isset($gb) || $gb == '') $gb = "%";
    

     

     

  15. In top_adm.html you close the link

     

    mysql_close($DbConn);
    

     

    Remove the line and the link error should no longer be displayed. You should try to restructure your code in such a way that doesn't require you to make the mysql_connect() call in each script.

     

    Now that you have error_reporting(E_ALL) at the top of your script you should notice other errors that you were not shown before. Try to locate and remove all the errors you see as they usually lead to other more significant errors later on in your code.

     

    I'll try to handle the edits on the posts. PM me if I missed anything.

  16. You also haven't assigned a value to variable "$db". it should hold I assume, the return value from a successful call to mysql_connect. Remove each "@" entry, you should not be suppressing errors while developing and after going live you should disable the display of errors, log and handle errors in a different way.

     

    Change each <? entry to <?php. Short tags are not enabled on all servers. Also Put the following at the top of your script.

     

    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    

     

    You can check the manual for info on what each function does. If you're still having problems post all output and the code you're using in its current form.

     

    Note that these types of questions should be posted in the PHP-Help forum and if you encounter additional PHP problems after this, post the questions there.

  17. Very Tough Query

     

    Hey all I have a tough query here. Basically I am making a top 25 poll, and I am trying to use two tables. The first table is the votes table and is set up as follows:

     

    voterid | place | teamid

     

    The second table should contain the number of points for each team from the votes table as well as the number of first place votes. Points can be calculated by the equation 26-place. So a first place vote gets 25 points, 2nd 24...

     

    The second table is set up as follows

     

    teamid | points | first_place_votes

     

    The query should incorporate all of the votes from the first table into the second one.  If anyone can help I would greatly appreciate it. I could use php to rig this up with arrays and such, but I would rather have it done more efficiently.

     

    You can get this output dynamically. If there are performance issues because of how frequent the page is being accessed or because of how much data there is, then caching the page that shows the output should be a viable option.

     

    To answer your question

    INSERT INTO
    table2
    (teamid, points, first_place_votes)
    SELECT
    teamid, SUM(26 - place) AS points, SUM(place = 1) AS first_place_votes
    FROM
    table1
    GROUP BY
    teamid
    

  18. Warning: mysql_query(): 2 is not a valid MySQL-Link resource in /user/langlab/wwwhome/signup/signup_adm.html on line 28

     

    The error is referring to the second argument to mysql_query(), $DbConn.

     

    Change the top of your script to the following. Note the use of <?php instead of <?. Make similar changes throughout the script.

     

    <?php
    error_reporting(E_ALL);
    $DbConn = mysql_connect("localhost", "langlab", "aaaaaaa") or die (mysql_error());
    mysql_select_db("langlab", $DbConn);
    var_dump($DbConn);
    

     

    Also change the mysql_query line to the following

    var_dump($DbConn);
    $Result = mysql_query($Query, $DbConn) or die(mysql_error());
    

     

    You said you made the "or die() " change already so the only difference should be the var_dump() line.

     

    You'll notice that your first call to mysql_query in the script does not give the same problems. That's because it doesn't use the second argument and mysql_query() sends the query over the last opened link. It would be best to figure out what the problem is first instead of making the same changes to all the mysql_query calls however.

     

    If you still get errors. Post the error and all other output in addition to the code you're using in its current form.

×
×
  • 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.