Jump to content

mongoose00318

Members
  • Posts

    250
  • Joined

  • Last visited

Everything posted by mongoose00318

  1. @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.
  2. 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?
  3. @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.
  4. @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') )
  5. 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.
  6. 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.
  7. 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?
  8. @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)
  9. 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...
  10. 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
  11. 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?
  12. 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.
  13. 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.
  14. 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?
  15. 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.
  16. 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 😕
  17. 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.
  18. 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.
  19. 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 ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3 WHERE chk.order_id = NULL Like this? Yea I was thinking about checking if a column as NULL! Just couldn't figure it out.
  20. Like this? Though the results it's giving me are still not as expected. SELECT pd.*, ps.* FROM `production_data`AS pd LEFT JOIN production_status AS ps ON ps.order_id = pd.id LEFT JOIN production_status ON ps.dept_code = 13 AND ps.status_id = 3 WHERE NOT EXISTS (SELECT * FROM production_status WHERE ps.dept_code = 13 AND ps.status_id = 3) Also, what do you mean when you said: I'd like to mention, once I get the query working, I won't be selecting all fields. I remember the advice you all have provided with that. I only do it like this to keep the query shorter for the forum post.
  21. I'm trying to exclude any rows from production_data that have any 13/3 rows in production_status.
  22. Ah yes okay I tried that with a subquery but I'm still not getting the right results: SELECT pd.*, ps.* FROM `production_data`AS pd LEFT JOIN production_status AS ps ON ps.order_id = pd.id WHERE NOT EXISTS (SELECT * FROM production_status WHERE ps.dept_code = 13 AND ps.status_id = 3) Is there a way to do a record count of the sub query and then tell it to only show the records with 0 records? It returns all of the other records in production_status except those with a dept_code = 13 and status_id = 3. I just need to know if the order has a record in production_status of dept_code = 13 and status_id = 3. I guess I could just remove all records from the production_data table which are shipped and store them in a separate table.
  23. I need to do the opposite of this: SELECT pd.*, ps.* FROM `production_data`AS pd LEFT JOIN production_status AS ps ON ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3 Basically, if there are no records in production_status where dept_code = 13 and status_id = 3 then include the record in the query...
×
×
  • 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.