Jump to content

mongoose00318

Members
 View Profile  See their activity

  • Posts

    253

  • Joined

  • Last visited

 Content Type 

Posts posted by mongoose00318

    Using Aliases for Other Directories

    in Apache HTTP Server

    Posted

    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?

    Using Aliases for Other Directories

    in Apache HTTP Server

    Posted

    @kicken 

    1 hour ago, kicken said:

    $_SERVER['SERVER_ADDR"] represents apache's local address.  If you want the address of the client connecting to the server you need $_SERVER['REMOTE_ADDR'].  With regard to VPN's, that remote address will be the VPN exit server's public IP.  There's no way to get the clients local pre-vpn IP (which is part of the reason people use VPNs these days).

    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?

    Using Aliases for Other Directories

    in Apache HTTP Server

    Posted

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

    Statement with multiple joins and field with CSV in it

    in MySQL Help

    Posted

    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: image.png.2dc0048e25c9cc66de75e48b190e6222.png

    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?

    Looking for a string within some text and wrapping it with a link

    in PHP Coding Help

    Posted

    @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')
)

     

    Looking for a string within some text and wrapping it with a link

    in PHP Coding Help

    Posted · Edited by mongoose00318

    Here is sample data from 'drawings_pef_files"

    image.png.12bde581d9f8775fe871ab38666b40cc.png

    Sample data from 'production_data'

    image.thumb.png.61fbd930371c968aecd4506600e1b790.png

    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.

     

    Looking for a string within some text and wrapping it with a link

    in PHP Coding Help

    Posted

    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?

    Looking for a string within some text and wrapping it with a link

    in PHP Coding Help

    Posted

    @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)

    Looking for a string within some text and wrapping it with a link

    in PHP Coding Help

    Posted

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

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted

    No it's relevant. The dept_code of 13 represents the shipping dept. 

    So here is an example:

    image.png.086b55eebd96b8711c4099b89d8b3203.png

    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? 

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted

    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.

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted

    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.

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted 

    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?

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted 

    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 😕

    MySQL Join with Count Embedded?

    in MySQL Help

    Posted

    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.

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