mongoose00318

Okay cool...I have an Alias working...I think I was doing something with virtual hosts...maybe I got confused about what was what... Now, I need to get some kind of Apache log analyzer for usage statistics. I really want to use GoAccess but I can't seem to get it working (having to use Cygwin on a WAMP setup)...it looks much better than something like AWStats... Any suggestions when it comes to a log analyzer like this?

@kicken Ah yes that would make sense...and yes I meant to say REMOTE_ADDR...sorry about that. So if I make an Alias of /want and the root of path to the server is http://computerName; then I would access /want via http://computerName/want?

Good Morning, I have my live Apache server setup to be accessible internally over the network but I only really have one directory setup to be accessible which is accessed via the computer name (at least I think?)...so http://computerName takes you to the folder I have it pointed at. I would like to be able to setup other directories for other uses (for example AWStats) which would only be accessible to me but I've tried using Aliases and gotten nowhere...I always mess something up in the config file and have to revert back to how I had it. Also, I've tried setting up Aliases using the menu provided in WAMP and I still get nowhere. I think this is the only <Directory> config I have in it right now. <Directory /> AllowOverride none Require all granted </Directory> What do I need to do to be more specific about directories and allowing access to them? One other question, I log the IP of the users when they login, and when users are using VPN obviously their network IP is different. The VPN software has two IPs, it's main one and the one it issues to it's clients. I when I grab it using $_SERVER['SERVER_ADDR'] in PHP I always get the main VPN IP...is there a way of getting the one the VPN issues to the client? I only ask so I can tell Apache my internal IP when on the network and my IP using the VPN so I had access AWStats remotely as well...other than having to RDP into the server...

@Barand @requinix Awesome. I'll give it a try. Thank you!

Whew..okay trying to make sense of it. Just looked at the MySQL ref. manual on VALUES..as you suggested. That is interesting...but FIND_IN_SET only takes 1 needle to look for in the haystack, right? So how would I iterate over a list of values within the SQL statement itself? Hopefully I asked all that right.

@requinix What do you mean by a fake joined table?

@requinix 😕 I thought you may say that...I was thinking the same thing. Is there a temporary way to accomplish this until I can change the db structure as you said? There are other parts of the application I'll have to change to match that.

Okay I thought this was working pretty well but upon modifying the data in the DB a bit to put it through it's paces I ran into some issues. I get this warning in MySQL now "Warning: #1292 Truncated incorrect DOUBLE value: '8,11'" and also the dataset it returns isn't what is expected so I am doing something wrong lol. I'm still learning to make my SQL more complex 😕 $depts_to_check = [1,2,3,5,6,7,8,10,11,12,13]; $str_depts_to_check = implode(',', $depts_to_check); $query = " SELECT DISTINCT l.user_id, l.name, (SELECT COUNT(*) FROM chat_to_users WHERE to_user_id = l.user_id AND status = 0) AS total_unread FROM login l LEFT JOIN chat_to_users ctu INNER JOIN chat_message cm ON ctu.message_id = cm.chat_message_id ON l.user_id = ctu.to_user_id AND ctu.status = 0 WHERE l.dept_code IN (". $str_depts_to_check .") AND (SELECT COUNT(*) FROM chat_to_users WHERE to_user_id = l.user_id AND status = 0) > 0 AND cm.timestamp < DATE_SUB(NOW(), INTERVAL 24 HOUR) ORDER BY total_unread DESC "; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(); Here's the table structure of the 3 tables. CREATE TABLE `chat_message` ( `chat_message_id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `chat_message` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time message was sent', PRIMARY KEY (`chat_message_id`) ) ENGINE=InnoDB AUTO_INCREMENT=483 DEFAULT CHARSET=latin1 CREATE TABLE `chat_to_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `order_id` int(11) DEFAULT NULL, `to_user_id` int(11) NOT NULL DEFAULT 0, `from_user_id` int(11) NOT NULL, `read_timestamp` timestamp NULL DEFAULT NULL COMMENT 'time user read message', `status` int(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=924 DEFAULT CHARSET=latin1 CREATE TABLE `login` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `dept_code` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(255) NOT NULL, `password_force_reset` tinyint(1) NOT NULL, PRIMARY KEY (`user_id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1 Also, I know where that warning originated from: That column (dept_code) in the login table can sometimes have a csv representation of the depts the user can belong to. Most users only have one dept_code but there are a few exceptions that have it like that. I'm not sure if I can work the query to work under this scenario?

@Barand Sorry I haven't been back to this task yet but I will be back to it hopefully next week sometime and I will provide an update then.

@Barand Hmm weird..it's doing something. But when I dump the $names array it only has 76 values. It should have over 115,000? Array ( [0] => ('(1)') [1] => ('1 ART CHECK LIST.CH201192') [2] => ('AS180551') [3] => ('Art (jacksonville-dc) (Z) - Shortcut (2)') [4] => ('Art (jacksonville-dc) (Z) - Shortcut (3)') [5] => ('Art (jacksonville-dc) (Z) - Shortcut') [6] => ('As173218') [7] => ('BK1144SF_LDQs Model (1)') [8] => ('CIT00028') [9] => ('CP061036_6248_NI') [10] => ('CP064096_6948SP1_SBLED') [11] => ('CT174631.E') [12] => ('CT2065FM_2LD') [13] => ('Co0771939') [14] => ('Customer Status System') [15] => ('Di202434') [16] => ('EN3038SF_Qs') [17] => ('Ex135513') [18] => ('Ex181015') [19] => ('Ex183450') [20] => ('FMD6SLR-80000_Rev_B') [21] => ('FileHandler') [22] => ('GR176558') [23] => ('HiFashion') [24] => ('MA9018CL_BKT') [25] => ('MFG (jacksonville-dc) (W) - Shortcut (3)') [26] => ('MFG (jacksonville-dc) (W) - Shortcut') [27] => ('MFG (jacksonville-dc) (Y) - Shortcut') [28] => ('MV-14 4-Product Pricer') [29] => ('M_TB1110CS_0IDMB') [30] => ('OR1618CP_FXGEHWSP') [31] => ('OR163231.E') [32] => ('PS151090.E') [33] => ('Plantation Market-MaryvilleTN') [34] => ('Ps185117') [35] => ('RVI_E Order Form - Dispenser ..') [36] => ('SFederal He18021413451') [37] => ('SG0068CP_ID3w211EMCSP_SBLED') [38] => ('SG0068RF_2L2VSP') [39] => ('SG0107RF_3w411EX_typA') [40] => ('SG133474_e') [41] => ('SG160531_e') [42] => ('SG167928_e') [43] => ('SG200030_e') [44] => ('SG2055TP_3w111SP') [45] => ('SG2056TP_3w211SP_SBLED Model (1)') [46] => ('SG2077FM_3w211SP_TYP_A') [47] => ('SG2310RF_1LDSF') [48] => ('SG3015TP_TX_SBLED') [49] => ('SH002119') [50] => ('SH103201 - Shortcut') [51] => ('SH141792') [52] => ('SHELL 609 ALLISON BONNET DR BESSEMER AL 85125') [53] => ('SPECIALT') [54] => ('Sd201942_DIE') [55] => ('Shortcut to Art & Engineering Request.mdb') [56] => ('Shortcut to Enterprise') [57] => ('Specialty Contracting Agreement - Rosedale Hwy') [58] => ('Synergy Survey Calculator - Eleventh Draft2') [59] => ('Thumbs') [60] => ('XX174610XX') [61] => ('Xx161880.E') [62] => ('Xx163854') [63] => ('Xx185178.DP') [64] => ('Xx4024NI_0IDGM') [65] => ('ci164947') [66] => ('ci167010') [67] => ('ck145891_E') [68] => ('export_1') [69] => ('mfg (Jacksonville-dc) (V) - Shortcut (2)') [70] => ('mfg (Jacksonville-dc) (V) - Shortcut (3)') [71] => ('mfg (Jacksonville-dc) (V) - Shortcut (4)') [72] => ('mfg (Jacksonville-dc) (V) - Shortcut (5)') [73] => ('mfg (Jacksonville-dc) (V) - Shortcut') [74] => ('mfg (Jacksonville-dc) (W) - Shortcut (2)') [75] => ('su2410tp') [76] => ('test') )

I get this error: Fatal error: Uncaught Error: Call to a member function exec() on string in All I did was change the glob('files/*.*'); to the correct path.

Yes

Here is sample data from 'drawings_pef_files" Sample data from 'production_data' Table structure -- -- Table structure for table `production_data` -- DROP TABLE IF EXISTS `production_data`; CREATE TABLE IF NOT EXISTS `production_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `job_number` int(8) NOT NULL, `enterprise` tinytext NOT NULL, `part_number` text NOT NULL, `description` text NOT NULL, `psm` tinytext NOT NULL, `qty` int(11) NOT NULL, `line_item` varchar(11) NOT NULL, `as400_ship_date` date DEFAULT NULL, `hold_reason` text NOT NULL DEFAULT '0', `hold_date` date DEFAULT NULL, `insert_time` datetime NOT NULL COMMENT 'time order was inserted or last updated', PRIMARY KEY (`id`), KEY `job_line` (`job_number`,`line_item`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; COMMIT; In the description field often times there is no DWG: prefixing the drawing number.

Okay so here my code...it takes like 20 minutes to finish executing though. It would be great if I could make it faster. <?php //start timer $rustart = getrusage(); //allow more memory and execution time for script ini_set('memory_limit', '4096M'); ini_set('max_execution_time', '60000'); //include dbconfig include('scripts/dbconfig.php'); //run functions in sequence delete_drawings(); get_drawing_list($pdo); cleanup_drawing_list($pdo); get_drawings($pdo); function get_drawing_list($pdo) { $path = '\\\directoryTo\PDF_Files'; $dirs = array(); // directory handle $dir = dir($path); $query = "TRUNCATE TABLE drawings_pdf_files"; $statement = $pdo->prepare($query); $statement->execute(); while (false !== ($entry = $dir->read())) { if ($entry != '.' && $entry != '..') { if (is_dir($path . '/' .$entry)) { $query = "INSERT INTO drawings_pdf_files(fileName) VALUES(?)"; $statement = $pdo->prepare($query); $statement->execute([ $entry, ]); } } } } function cleanup_drawing_list($pdo) { $query = "SELECT * FROM drawings_pdf_files"; $statement = $pdo->prepare($query); $statement->execute(); $drawings = $statement->fetchAll(); $query = "SELECT description FROM production_data"; $statement = $pdo->prepare($query); $statement->execute(); $descriptions = $statement->fetchAll(); foreach ($drawings as $drw) { //count matches $count = 0; //compare for a match foreach ($descriptions as $desc) { if (strpos($desc['description'], $drw['fileName'])) { $count++; } } //remove the record if it has no matches if ( $count == 0 ) { $query = "DELETE FROM drawings_pdf_files WHERE id = :id"; $statement = $pdo->prepare($query); $statement->execute([ 'id' => $drw['id'], ]); } } } function get_drawings($pdo) { $query = "SELECT * FROM drawings_pdf_files"; $statement = $pdo->prepare($query); $statement->execute(); $drawings = $statement->fetchAll(); foreach($drawings as $drw) { $dir_pdf = "\\\directoryTo\PDF_Files\\"; $dir_art = $drw['fileName']; $dest_pdf = "drawings/"; //create new directory for the files mkdir($dest_pdf.$dir_art); //copy each file in that folder $list_files = array_diff(scandir($dir_pdf.$dir_art), array('..', '.')); foreach($list_files as $f) { copy( $dir_pdf . $drw['fileName'] . '\\' . $f, $dest_pdf . $drw['fileName'] . '/' . $f); } } } function delete_drawings() { $dir = "drawings/"; $di = new RecursiveDirectoryIterator($dir, FilesystemIterator::SKIP_DOTS); $ri = new RecursiveIteratorIterator($di, RecursiveIteratorIterator::CHILD_FIRST); foreach ( $ri as $file ) { $file->isDir() ? rmdir($file) : unlink($file); } return true; } //output usage statistics //$ru = getrusage(); //echo "This process used " . rutime($ru, $rustart, "utime") . " ms for its computations\nIt spent " . rutime($ru, $rustart, "stime") . " ms in system calls\n"; ?> The way that the drawing gets put into the description varies: CONOCO 1'10x8 VC DF TP SGN||PRINCIPAL ILLUMINATION||ENG: CO3028TP_0VPR||DWG: CO200428||TO BE: DYED DIESEL (SPEC)|| So I can't look for "DWG:" Sometimes they don't put anything in the description telling you its the drawing. People just figure it out from experience. So what I've done is pull in all of the files from the drawing folder, put it into a table, then compare each drawing with each description, remove any drawings that dont have matches, and then copy the drawing files that do have matches. @BarandWhat do you think?

@Barand Yes! I'd love to do that...but...sadly when it comes to the data entry side there are no standards that are followed. I'm always having to do things backwards and goofy due to how data is stored. I've got a solution I'm working on which I'd like to hear your feedback on...just need some time to finish it. Probably will have it finished some time tomorrow. I actually got by using the strpos and str_replace functions...I look forward to your feedback. When I update I'll provide some more details as to the goofy problems I've had to work around to accomplish this (from bad data to network issues)

I am trying to wrap a string with an anchor tag if it finds a match with a block of text. Here is an example text block: CONOCO 1'10x8 VC DF TP SGN||PRINCIPAL ILLUMINATION||ENG: CO3028TP_0VPR||DWG: CO200428||TO BE: DYED DIESEL (SPEC)|| The string I would want to wrap with a link would be "CO200428". The next problem is the drawings (what I'm searching the text for) has over 115,000 possibilities. The text blocks to search are over 1300. I have the drawing names stored in a simple mysql table...but doing a foreach takes forever...I imagine it will take even longer when looking in the text blocks... Is there a way to easily to do the anchor wrap? I don't know regex very well...

Shipping is the last department a product goes through. If orders show as shipped (like row 4678 above) I don't want them to be included in the results from the query

No it's relevant. The dept_code of 13 represents the shipping dept. So here is an example: Order 1794 had two status updates on 6/8/2020. The first one with a status_id of 1 means it was started. The next submission @ 07:22:50 with a status_id of 3 means the order has shipped. So, since that record (TBL: production_status; id: 4678; status_id: 3) exists, I'm unconcerned with any of the other records for that order. In this case record 4677. Does that make sense?

Okay, it's working with it like this: SELECT pd.*, ps.* FROM production_data AS pd JOIN production_status AS ps ON ps.order_id = pd.id LEFT JOIN production_status AS chk ON chk.id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 WHERE chk.id IS NULL But, I've realized some other problems. For example, the field status_id has 4 possible values: 0 - Not started 1 - In progress 2 - Delayed 3 - Finished So I'm getting records back with status_ids of 0, 1, & 2...which makes sense to me now. I guess I didn't think through the exclusion criteria thoroughly enough...if the order has a status_id of 3 I don't want it to return any other records from the production_status table. I hope I've provided enough data for that to be helpful. Nonetheless, the query is now working and I think I understand what it is doing...mostly.

I need it to give the exact opposite results of what this does: SELECT pd.*, ps.* FROM production_data AS pd JOIN production_status AS ps ON ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3 I've been tinkering with the 2nd JOIN but I just keep getting the same results...impossible where and dept_codes being returned which shouldn't be.

SELECT pd.*, ps.* FROM production_data AS pd JOIN production_status AS ps ON ps.order_id = pd.id JOIN production_status AS chk ON chk.order_id = pd.id AND chk.dept_code = 13 AND chk.status_id = 3 Okay I think I found something wrong with the conditions. I was doing ps.id. But, still I think I'm doing something wrong: https://imgur.com/dUblOQX Why would I be getting any other dept_codes returning other than 13 right now? Without the WHERE shouldn't everything have a dept_code of 13?

I wish there was some way to better visualize this. When I have it do the "explain SQL" it says I have an impossible where. I think all the columns are right. My head hurts though lol.

SELECT pd.*, ps.* FROM `production_data` AS pd JOIN production_status AS ps ON ps.order_id = pd.id JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 WHERE chk.id IS NULL Changed to that and still get 0 matches 😕

Wait! I got something right! I realized I was referencing the first joins alias... SELECT pd.*, ps.* FROM `production_data` AS pd JOIN production_status AS ps ON ps.order_id = pd.id JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 This is returning 415 matches! But when I do... SELECT pd.*, ps.* FROM `production_data` AS pd JOIN production_status AS ps ON ps.order_id = pd.id JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3 WHERE chk.id = NULL I get zero matches.

Hmm...must have screwed something up...Showing rows 0 - 24 (1958329 total, Query took 0.0064 seconds.) That's a bit off... Joins are super confusing.