n3mesis125

I apologize for the title and I am using version 5.1.41 if that helps any.

Hey Folks, I have built a queueing type system for where I work and how my system works is I upload data into a Mysql Database using .csv files. I then try to distribute those records to employees to read the data, lock the record and begin looking at the information to complete there task. Now one issue I am concerned about is how to only send a unique record to each employee at once without it duplicating similar records to people. Is MySQL smart enough to not let two ppl read the same record at once? Is there some way that I can make it so that MySQL will let someone read a record, lock it and nobody else will see that record. I just don't want the same record to be sent to two people. At the moment I do a pull with a query similar to the below, I immediately after that update the record with the employees ID # and update a column called 'lock' to 1 from 0. The only thing I'm worried about is the system fast enough to do all this or is there a better way to do it then what I am doing. $sql = "SELECT *, CAST(cycle_priority AS SIGNED INTEGER) AS BCycle FROM bot_adhoc_upload WHERE pid='".$pid."' AND CAST(cycle_priority AS SIGNED INTEGER) >= '".date('j')."' AND `lock`='0' AND `hold`='0' AND `complete`='0' AND `syslock`='0' ORDER BY BCycle ASC LIMIT 1"; $res = mysql_query($sql) or die(mysql_error()); $adhoc = mysql_num_rows($res) ? mysql_fetch_array($res) : 0; if($adhoc){ $sql2 = "UPDATE `bot_adhoc_upload` SET `lock`='1', `lockedby`='".$user."' WHERE `id`='".$adhoc['id']."'"; $res2 = mysql_query($sql2) or die(mysql_error()); } else { header("Location: bot_adhoc_empty.php?q=".$func->name); }

Interesting enough, not sure why this wouldn't work, but in the sub-select query I did with LEFT JOIN the ecp_data table. I originally tried AS Avaya ON (Avaya.avaya_id = u.avaya_id AND t.date = Avaya.date), oddly enough, it wouldn't SUM the data correctly when I tried to match the table's date column with the tracker_data t.date column. So I had to add in a WHERE clause to match the $date_range variable.

I actually figured it out on my own by browsing around This is what I did to make it work, not sure if there is a more efficient way, but this got the job done. Variable $date_range being the range the end-user picks from some menu drop-downs. SELECT t.date, t.employee_num, t.username, t.site, COUNT(t.date) AS Tracked, u.avaya_id, Avaya.calls as calls, Avaya.ocalls AS outgoing_calls, Avaya.caht AS aht_blend, Avaya.cheld AS calls_held, Avaya.occup AS agent_occup, u.employee_num, Avaya.aux AS aux, Avaya.avail as avail, m.name AS Mngr FROM tracker_data as t LEFT JOIN users as u ON (t.employee_num = u.employee_num) LEFT JOIN ( SELECT date, avaya_id, SUM(calls) AS calls, SUM(outgoing_calls) AS ocalls, AVG(aht_blend) AS caht, SUM(calls_held) AS cheld, AVG(agent_occup) AS occup, SUM(aux) AS aux, SUM(avail) AS avail FROM ecp_data WHERE date".$date_range." GROUP BY avaya_id ) AS Avaya ON (Avaya.avaya_id = u.avaya_id) LEFT JOIN management as m ON (u.manager = m.tag) WHERE t.date".$date_range." AND t.cat_type='phone' GROUP BY t.username ORDER BY t.username Hope this helps someone else out that is stuck. You basically just need to use sub-queries to have cross-table sums that add up properly. At least thats what I've gathered. Tks, n3m.

Hey Folks, I'm sure people have asked this over and over, but I am at a lost and have tried to google this issue for a solution. I have the following query: SELECT t.date, t.username, t.site, COUNT(t.date) AS Tracked, u.avaya_id, SUM(p.calls) AS mycalls, SUM(p.outgoing_calls), AVG(p.aht_blend), SUM(p.calls_held), AVG(p.agent_occup), u.employee_num, SUM(p.aux), SUM(p.avail), m.name AS Mngr FROM tracker_data as t LEFT JOIN users as u ON (t.employee_num = u.employee_num) LEFT JOIN ecp_data as p ON (p.avaya_id = u.avaya_id AND p.date = t.date) LEFT JOIN management as m ON (u.manager = m.tag) WHERE t.date".$date_range." AND t.cat_type='phone' GROUP BY t.username ORDER BY t.username There are 4 tables: tracker_data - this table tells me how much ppl are tracking ecp_data - this table tells me exactly how much work ppl get users - this table holds all profile info management - this table holds all management info I'm trying to SUM() columns in ecp_data and COUNT() entries from the tracker_data table. But when there is a large range ie date BETWEEN '' AND '', then its not summing up the totals correctly. Can anyone figure out with my query above what I'm doing wrong? Tks, n3m.

Hey Folks, I have something simliar to this in XML but i'm not sure how to access the attributes of each row. Is there an easy foreach() statement I can do to access the attributes? Basically I'm trying to loop through the below items and only spit out the solarSystemName data for each row set. <?xml version='1.0' encoding='UTF-8'?> <eveapi version="2"> <currentTime>2007-12-14 20:19:41</currentTime> <result> <rowset name="solarSystems" key="solarSystemID" columns="solarSystemID,allianceID,constellationSovereignty,sovereigntyLevel,factionID,solarSystemName"> <row solarSystemID="30023410" allianceID="0" constellationSovereignty="0" sovereigntyLevel="0" factionID="500002" solarSystemName="Embod" /> <row solarSystemID="30001597" allianceID="1028876240" constellationSovereignty="0" sovereigntyLevel="3" factionID="0" solarSystemName="M-NP5O" /> <row solarSystemID="30001815" allianceID="389924442" constellationSovereignty="0" sovereigntyLevel="3" factionID="0" solarSystemName="4AZV-W" /> <row solarSystemID="30001816" allianceID="0" constellationSovereignty="0" sovereigntyLevel="0" factionID="0" solarSystemName="UNV-3J" /> <row solarSystemID="30000479" allianceID="0" constellationSovereignty="824518128" sovereigntyLevel="0" factionID="0" solarSystemName="SLVP-D" /> <row solarSystemID="30000480" allianceID="824518128" constellationSovereignty="824518128" sovereigntyLevel="3" factionID="0" solarSystemName="0-G8NO" /> </rowset> <dataTime>2007-12-14 14:49:43</dataTime> </result> <cachedUntil>2007-12-15 02:19:41</cachedUntil> </eveapi>

Hey Folks, Is there an easier way to do this so I don't need to repeat code, the below statement takes a long time to execute: if ($arg_list[1] == "func" && $mytime <= $daystamp && $i['func'] == $arg_list[4]){ if ($timediff <= $calcG){ $out_g = $out_g + $i[$arg_list[2]]; } else if ($timediff > $calcG && $timediff <= $mySL){ $out_y = $out_y + $i[$arg_list[2]]; } else if ($timediff > $mySL){ $out_r = $out_r + $i[$arg_list[2]]; } } else if ($arg_list[1] == "lob" && $mytime <= $lobstamp){ $timediff = $lobstamp - $mytime; if ($timediff <= $calcG){ $out_g = $out_g + $i[$arg_list[2]]; } else if ($timediff > $calcG && $timediff <= $mySL){ $out_y = $out_y + $i[$arg_list[2]]; } else if ($timediff > $mySL){ $out_r = $out_r + $i[$arg_list[2]]; } } I hate having to repeat the code each time. n3m

Hey folks, I'm looking for away to put in line breaks at certain patterns in text. Not sure if this is possible, but I'll give an example string and then what I'd like to see come out of it. <?php $string = "3.2 Here is the infor for this one 4.5 This is a test 5.0 Here we go"; ?> What I'm trying to do is search for an integer pattern for the numbers and use a line break. So the only thing at the moment that I can see could throw it off is if there is one in the beginning. I'd like to see the above string put into an array of <?php $myArray = array( [0] => '3.2 Here is the info for this one', [1] => '4.5 This is a test', [2] => '5.0 Here we go'); ?> Is this possible? Thanks, n3m.

Hey, Just need some help with getting my column data to show as a string. I inserted a bunch of data using phpmyadmin LOAD INFILE, however one of the columns of data I inserted wasn't wrapped with "" to show it was a string. So at this point I'm not even sure what mysql is making this column appear to be. Is there some quick way of fixing a column so that it reads as a string. Right now when I bring the data into an array in php, I am using an if statement like if($a['name'] == "BREAK") however, this isn't working because the word BREAK that is stored in the 'reason' column isn't looking like text, I've tried putting (string) in front of the data i bring into the array but that isn't working. However, if I edit the row in phpmyadmin and then just hit go, it fixes the data, however, I have over 100,000 records and do not want to have to edit each row in phpmyadmin. Thanks, n3m.

Thanks for the help guys, removed SUM and it worked perfectly, this made my reporting job 100x easier n3m.

When I try the above I now get the below error: #1111 - Invalid use of group function

Hey Guys, I'm using the below query and for whatever reason it keeps throwing an error: UPDATE `csit_actioned` SET `green` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) <= 194400,1,0), SET `yellow` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) BETWEEN '194400' AND '259201',1,0), SET `red` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) > 259200,1,0) WHERE id='294739' The error i get is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET `yellow` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) BET' at line 3 PS: Using PHP5 and MYSQL 5.0.67

Hey FOlks, How can I fix an error so that if I'm using SUM() with two tables and lets say Table B doesn't return any values for the WHERE clause? IE: SELECT SUM(t1.salary) as t1total, SUM(t2.salary) as t2total FROM table1 as t1 LEFT JOIN table2 as t2 ON (t2.id = t1.id) WHERE t1.date BETWEEN '2008-10-01' AND '2008-10-20' AND t1.username = 'test.account' GROUP BY t1.date Table 2 may not return any data if the user we are looking for hasn't submitted anything. For some reason mysql doesn't return any data at all when it can't SUM() an empty resultset? Hope this makes sense, I tried using IFNULL(t2.salary,0) but it doesn't seem to work. Thanks, n3m.

I'm on a windows OS, XP. WOuldn't setting session.gc_probability = 0 make it not run?

Hey Folks, Is there a way I can turn off the garbage collector so that it never deletes sessions, I will be deleting them manually each day as it is a small intranet site. I have access to the php.ini file. At the moment the [session] part of the php.ini file looks like: [session] ; Handler used to store/retrieve data. session.save_handler = files ; Argument passed to save_handler. In the case of files, this is the path ; where data files are stored. Note: Windows users have to change this ; variable in order to use PHP's session functions. ; ; As of PHP 4.0.1, you can define the path as: ; ; session.save_path = "N;/path" ; ; where N is an integer. Instead of storing all the session files in ; /path, what this will do is use subdirectories N-levels deep, and ; store the session data in those directories. This is useful if you ; or your OS have problems with lots of files in one directory, and is ; a more efficient layout for servers that handle lots of sessions. ; ; NOTE 1: PHP will not create this directory structure automatically. ; You can use the script in the ext/session dir for that purpose. ; NOTE 2: See the section on garbage collection below if you choose to ; use subdirectories for session storage ; ; The file storage module creates files using mode 600 by default. ; You can change that by using ; ; session.save_path = "N;MODE;/path" ; ; where MODE is the octal representation of the mode. Note that this ; does not overwrite the process's umask. session.save_path = "C:\sites\botserver\sessions" ; Whether to use cookies. session.use_cookies = 1 ; This option enables administrators to make their users invulnerable to ; attacks which involve passing session ids in URLs; defaults to 0. ; session.use_only_cookies = 1 ; Name of the session (used as cookie name). session.name = PHPSESSID ; Initialize session on request startup. session.auto_start = 0 ; Lifetime in seconds of cookie or, if 0, until browser is restarted. session.cookie_lifetime = 0 ; The path for which the cookie is valid. session.cookie_path = / ; The domain for which the cookie is valid. session.cookie_domain = ; Handler used to serialize data. php is the standard serializer of PHP. session.serialize_handler = php ; Define the probability that the 'garbage collection' process is started ; on every session initialization. ; The probability is calculated by using gc_probability/gc_divisor, ; e.g. 1/100 means there is a 1% chance that the GC process starts ; on each request. session.gc_probability = 1 session.gc_divisor = 100 ; After this number of seconds, stored data will be seen as 'garbage' and ; cleaned up by the garbage collection process. session.gc_maxlifetime = 1440 ; NOTE: If you are using the subdirectory option for storing session files ; (see session.save_path above), then garbage collection does *not* ; happen automatically. You will need to do your own garbage ; collection through a shell script, cron entry, or some other method. ; For example, the following script would is the equivalent of ; setting session.gc_maxlifetime to 1440 (1440 seconds = 24 minutes): ; cd /path/to/sessions; find -cmin +24 | xargs rm ; PHP 4.2 and less have an undocumented feature/bug that allows you to ; to initialize a session variable in the global scope, albeit register_globals ; is disabled. PHP 4.3 and later will warn you, if this feature is used. ; You can disable the feature and the warning separately. At this time, ; the warning is only displayed, if bug_compat_42 is enabled. session.bug_compat_42 = 1 session.bug_compat_warn = 1 ; Check HTTP Referer to invalidate externally stored URLs containing ids. ; HTTP_REFERER has to contain this substring for the session to be ; considered as valid. session.referer_check = ; How many bytes to read from the file. session.entropy_length = 0 ; Specified here to create the session id. session.entropy_file = ;session.entropy_length = 16 ;session.entropy_file = /dev/urandom ; Set to {nocache,private,public,} to determine HTTP caching aspects ; or leave this empty to avoid sending anti-caching headers. session.cache_limiter = nocache ; Document expires after n minutes. session.cache_expire = 180 ; trans sid support is disabled by default. ; Use of trans sid may risk your users security. ; Use this option with caution. ; - User may send URL contains active session ID ; to other person via. email/irc/etc. ; - URL that contains active session ID may be stored ; in publically accessible computer. ; - User may access your site with the same session ID ; always using URL stored in browser's history or bookmarks. session.use_trans_sid = 0 ; Select a hash function ; 0: MD5 (128 bits) ; 1: SHA-1 (160 bits) session.hash_function = 0 ; Define how many bits are stored in each character when converting ; the binary hash data to something readable. ; ; 4 bits: 0-9, a-f ; 5 bits: 0-9, a-v ; 6 bits: 0-9, a-z, A-Z, "-", "," session.hash_bits_per_character = 4 ; The URL rewriter will look for URLs in a defined set of HTML tags. ; form/fieldset are special; if you include them here, the rewriter will ; add a hidden <input> field with the info which is otherwise appended ; to URLs. If you want XHTML conformity, remove the form entry. ; Note that all valid entries require a "=", even if no value follows. url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset=" Thanks, n3m.