Jump to content

shoz

Staff Alumni
  • Posts

    600
  • Joined

  • Last visited

About shoz

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

shoz's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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. 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. 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. Put a semicolon at the end of each statement SHOW PROCESSLIST; KILL .... ;
  5. 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. 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. 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
  6. I meant that the second line should have DATE_FORMAT(MAX(a.timeRecord), the MAX() is missing. My first reason for suggesting you not use the variables is 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.
  7. 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
  8. 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.
  9. shoz

    Query Help

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

    Query Help

    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"
  11. 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.
  12. shoz

    Query Help

    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.
  13. 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.
  14. shoz

    Query Help

    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();
  15. As for the WHERE clause error, change the mysql_query calls to the following and post the errors again. Note that I've made a post above in the event you missed it. $sql = 'query here'; $result = mysql_query($sql) or die($sql."<br />\n".mysql_error());
×
×
  • 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.