mongoose00318

Okay I changed my query to use the asterisk but still it's not working right. For example if I remove the LIKE from the where clause here is part of the data I get back. 37 => array (size=14) 'WORK ORDER' => string '23601611 0001' (length=14) 0 => string '23601611 0001' (length=14) 'ENTERPRISE' => string 'BURGER' (length=6) 1 => string 'BURGER' (length=6) 'PART NUMBER' => string 'BK4016LD.SFG' (length=12) 2 => string 'BK4016LD.SFG' (length=12) 'ITEM_NUMBER_BOM' => string '007' (length=3) 3 => string '007' (length=3) 'CATALOGUE_NUMBER_BOM' => string 'FA4016BK.SFGTC' (length=14) 4 => string 'FA4016BK.SFGTC' (length=14) 'DESCRIPTION_BOM' => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40) 5 => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40) 'RELEASE_WO_BOM' => string '23601611 0001' (length=14) 6 => string '23601611 0001' (length=14) The 'CATALOGUE_NUMBER_BOM clearly starts with FA but if I run this query: SELECT [PROD SCHD].[WORK ORDER], [PROD SCHD].[ENTERPRISE], [PROD SCHD].[PART NUMBER], [PPBOMFCB].[ITEM_NUMBER_BOM], [PPBOMFCB].[CATALOGUE_NUMBER_BOM], [PPBOMFCB].[DESCRIPTION_BOM] FROM [PROD SCHD] LEFT JOIN [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] WHERE [PROD SCHD].[WORK ORDER] IS NOT NULL AND [PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA*' I don't get any results?

I have successfully connected to the access database and the queries seem to be surprisingly fast but I'm having some issues getting some queries to work. I can't figure out if it is a difference in syntax between MySQL and MSSQL? For examples: SELECT [PROD SCHD].[WORK ORDER], [PROD SCHD].[ENTERPRISE], [PROD SCHD].[PART NUMBER], [PPBOMFCB].[ITEM_NUMBER_BOM], [PPBOMFCB].[CATALOGUE_NUMBER_BOM], [PPBOMFCB].[DESCRIPTION_BOM] FROM [PROD SCHD] LEFT JOIN [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] WHERE [PROD SCHD].[WORK ORDER] IS NOT NULL This works fine. But when I add another condition to the where clause like so: SELECT [PROD SCHD].[WORK ORDER], [PROD SCHD].[ENTERPRISE], [PROD SCHD].[PART NUMBER], [PPBOMFCB].[ITEM_NUMBER_BOM], [PPBOMFCB].[CATALOGUE_NUMBER_BOM], [PPBOMFCB].[DESCRIPTION_BOM] FROM [PROD SCHD] LEFT JOIN [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] WHERE [PROD SCHD].[WORK ORDER] IS NOT NULL AND [PPBOMFCB].[PART NUMBER] LIKE '%FA%' It won't work at all? The query works fine in Access though.

Cool man. Ya that works. I was trying to see how much of it I could do using just SQL and PHPMyAdmin. Thanks!

Here is my query so far: SELECT DISTINCT (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 7 DAY AND user_id = 7) AS Monday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 6 DAY AND user_id = 7) AS Tuesday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 5 DAY AND user_id = 7) AS Wednesday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 4 DAY AND user_id = 7) AS Thursday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 3 DAY AND user_id = 7) AS Friday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 2 DAY AND user_id = 7) AS Saturday, (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 1 DAY AND user_id = 7) AS Sunday FROM :TABLE ps WHERE ps.user_id = :USER The results it is giving me are all wrong so I'm not sure what I am doing wrong. I'm new to using views but I created a few this morning to help me quickly analyze certain data. I want to use the graphing feature in PhpMyAdmin more. What I'd like to be able to do is have a view that shows how many records were entered into the production_status table by the user for each day of the last week; or even better each day of this month and then chart it on a bar graph.

Is their a way to format lr.submit_time in every record that is returned in the $results array without doing like a for/foreach loop? Like array_walk() or something similar? $query = " SELECT lr.*, l.make, l.model, l.part_number, l.description, pd.job_number, pd.line_item, pd.enterprise, pd.description, pd.qty AS order_qty, log.name AS user_full_name FROM leds_requests lr LEFT JOIN leds l ON l.id = lr.product_id LEFT JOIN production_data pd ON pd.id = lr.order_id LEFT JOIN login log ON log.user_id = lr.user_id WHERE lr.id IN( SELECT MAX(id) FROM leds_requests WHERE status_id = 0 GROUP BY order_id, product_id ) AND lr.id NOT IN( SELECT id FROM leds_requests WHERE lr.id = id AND status_id IN(1, 2, 3, 4) ) GROUP BY order_id, product_id, job_number "; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(); echo json_encode($results);

@Barand There was additional code below the exit() that I thought was irrelevant to the post and I was trying to keep it short for everyone. Turns out the problem was in the code below the exit(); lol...it was there for debugging purposes. The comment is old; I originally was going pass all that to the function; but instead I just pass the order_id to it now. The rest of is provided elsewhere from other parts of the application. I should have the function buttoned up sometime today and I'll post it for review. It's a part of a big new feature I'm building into the application. I'm looking forward to getting it out to the end user.

@mac_gyver Lol just noticed your sig...I don't know...I may have damaged my eyes Friday.

@mac_gyver Man...okay the problem was elsewhere in my code. The query was working fine...I guess it I was looking at it too long Friday and couldn't see it. Sorry about that; it seems to be working now. But as to your question, I'm pretty sure I'm using the FETCH_ASSOC mode.

I've been having issues with this since Friday and still haven't resolved it. If I call that function with a $order_id set to 7601 and the SQL clause says "LIMIT 1" I get a blank array that comes back. If I change it to LIMIT 2; I get the result I expected...but not two records...just the one? There are no blank records in the DB from what I see and if I run that same query in PHPMyAdmin it returns the results I expect? //build LED request buttons function build_led_request_btn($order_id, $pdo) { //info to gather //$order_id, $job_number, $enterprise, $request_id, $request_status //get request status $query = "SELECT * FROM leds_requests WHERE order_id = :order_id ORDER BY id DESC LIMIT 1"; $statement = $pdo->prepare($query); $statement->execute(['order_id' => $order_id]); $count_records = $statement->rowCount(); return $statement->fetch()['status_id']; exit(); }

var template = $('#request_led_dialog .led-item:first').clone(); template.insertAfter('#request_led_dialog .led-item:last'); This seems to have fixed it.

It seems like a selector problem. It doesn't seem to matter if I use .first() or .last() or nothing at all; when I dump it to the console it is selecting all .led-item(s)

Kind of have it working now but it's doubling everything up each time instead of just adding one more element : $('.led-item').first().clone().insertAfter('.led-item');

I'm trying to create a simple form that allows the user to add another item when they click a button. Here is my HTML: <div class="row led-item"> <div class="form-group col-10"> <label for="exampleFormControlSelect1">Product</label> <select class="form-control" id="exampleFormControlSelect1"> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> </select> </div> <div class="form-group col-2"> <label for="exampleFormControlInput1">Quantity</label> <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com"> </div> </div> My JS: //add item to request leds forms $('#request-leds-add-item').click(function() { $('.led-item:first').clone().appendTo('.led-item'); }); But instead of it cloning the .led-item and inserting it after the first instance of it; it inserts it into the the first instance of .led-item like so: <div class="row led-item"> <div class="form-group col-10"> <label for="exampleFormControlSelect1">Product</label> <select class="form-control" id="exampleFormControlSelect1"> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> </select> </div> <div class="form-group col-2"> <label for="exampleFormControlInput1">Quantity</label> <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com"> </div> <div class="row led-item"> <div class="form-group col-10"> <label for="exampleFormControlSelect1">Product</label> <select class="form-control" id="exampleFormControlSelect1"> <option>1</option> <option>2</option> <option>3</option> <option>4</option> <option>5</option> </select> </div> <div class="form-group col-2"> <label for="exampleFormControlInput1">Quantity</label> <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com"> </div> </div> </div>

@kicken How would I active a shell script through PHP if I wanted to trigger the backup based on certain events that happen? @benanamen Audit Logging looks really interesting. I'd probably need something that parses the logs and makes it easier to search them, etc...something like Monyog? I'm not too familiar with a lot of these more advanced performance and logging features of MySQL. Any additional advice would be welcome!

@benanamen Okay, I was only asking because somethings there are events that happen in the software where I would want a DB backup triggered before any alteration of the data is allowed. I would want regularly timed backups as well; as you suggested above.

What is the best way to perform automated backups of an entire MySQL database using PDO?

@Barand Hmm...okay thanks for the heads up. I'll keep that in mind next time I have to do something similar.

Maybe I didn't need the join?

I tried this and I think it gave me what I wanted? SELECT *, MAX(insert_time) FROM production_data_archive GROUP BY order_id

I'm trying to select all of the most recent records for each 'order_id' with the following table structure: So with that example above I would want record 6840 because it is the newest record for order 5157. SELECT pda.* FROM production_data_archive pda INNER JOIN ( SELECT MAX(insert_time) as MAX_insert_time FROM production_status_archive pda2 GROUP BY order_id ) ON pda.order_id = pda2.order_id I'm not sure what I'm doing wrong with my query but I'm getting an error.

I have some Bootstrap popovers I'm trying to use and they work but you have to over over the element twice? Here is my JS: //initiate popover $('body').on('mouseover', '.btn_change_order_status_dialog', function() { if($(this).text() !== 'Not Started') { var btn = $(this); //perform query to determine most recent order status $.ajax({ url: 'scripts/order_status.php', method: 'GET', data: {action: 'get_order_status_history_for_dept', order_id: $(this).attr('data-order-id'), dept_code: $(this).attr('data-dept-code')}, success: function(data) { btn.attr('data-content', data); } }); } $(this).popover(); }); Here is my PHP that generates the HTML for the buttons: //build change order status buttons function build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status) { // define output button array $btn = []; // produce the buttons foreach ( $status as $dept => $stat ) { //check if status ID is an array holding additonal information $status_id = is_array($stat) ? $stat['status_id'] : $stat; //retrieve expected complete time if ( isset( $stat[ 'expected_complete_time' ] ) ) { $today = new DateTime( "now" ); $expected_complete_time = new DateTime( $stat[ 'expected_complete_time' ] ); $eta = $today->diff( $expected_complete_time ); $str_eta = $eta->format( '%r%d' ); if ( $expected_complete_time->format( 'd/m/Y' ) === $today->format( 'd/m/Y' ) ) { $eta = 'Today'; } else { $eta = ( $str_eta + 1 == 1 ) ? $str_eta + 1 . ' day': $str_eta + 1 . ' days'; } } else { $eta = ''; } // enabled if user dept is 1,2,3 or the current dept matches the user's dept $enabled = in_array( $user_dept_code, [ 1, 2, 3 ] ) || in_array( $dept, explode( ',', $user_dept_code ) ) ? '' : ' disabled'; // populate the dynamic values. you should actually use a template here with replaceable tags, then only produce the button that matches the current status value $buttons[ 1 ] = "<button type='button' class='btn btn-warning btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>ETA: $eta</button>"; $buttons[ 2 ] = "<button type='button' class='btn btn-danger btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>Delayed</button>"; $buttons[ 3 ] = "<button type='button' class='btn-primary btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>Finished</button>"; $buttons[ 0 ] = "<button type='button' class='btn btn-secondary btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Not Started</button>"; // get the button that matches the current status value for each dept $btn[ $dept ] = $buttons[ $status_id ]; } // examine the results return $btn; } Also, I've attached a video demonstrating the problem... Untitled Project.mp4

Well..I figured it out...here is the solution for others that may have this issue... You need to add blank rows between the groups you want..otherwise it just sees it as one continuous group...

I've tried adjusting the setOutlineLevel but it is just grouping all of the rows together as one group right now. From my understanding the setOutlineLevel is for nesting groups anyhow...not sure what is going wrong because this code works fine: for ($row = 2; $row <= 10; ++$row) { $spreadsheet->getActiveSheet() ->getRowDimension($row) ->setOutlineLevel(1) ->setVisible(true) ->setCollapsed(false); } for ($row = 12; $row <= 20; ++$row) { $spreadsheet->getActiveSheet() ->getRowDimension($row) ->setOutlineLevel(1) ->setVisible(true) ->setCollapsed(false); } I can't spot the difference between what I'm doing with: //setup row groupings foreach ( $groupings as $g ) { $end = isset($g['end'] ) ? $g['end'] : $g['start']; for ( $row = $g['start']; $row <= $end; ++$row ) { $spreadsheet->getActiveSheet() ->getRowDimension($row) ->setOutlineLevel(1) ->setVisible(true) ->setCollapsed(false); } } and the code above it...

Well..I've tried this...but it isn't quite working... $worksheet = $spreadsheet->getActiveSheet(); // Get the highest row number and column letter referenced in the worksheet $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' // Increment the highest column letter $highestColumn++; $job_num_chk = ''; $groupings = []; //format the inserted records for ($row = 2; $row <= $highestRow; ++$row) { //$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53); for ($col = 'A'; $col != $highestColumn; ++$col) { $spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP); $spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT); if ($col == 'B') { $cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number //check if job number is different if ( $job_num_chk !== $cur_job_num ) { /* $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row //set dividing row to black $spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill() ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) ->getStartColor()->setARGB('000000'); */ $job_num_chk = $cur_job_num; //update job number } $row_m = $row-1; $row_a = $row+1; $above = $spreadsheet->getActiveSheet()->getCell($col . $row_m)->getValue(); $below = $spreadsheet->getActiveSheet()->getCell($col . $row_a)->getValue(); if ( $cur_job_num !== $above ) { $groupings[$cur_job_num]['start'] = $row; } elseif ( $cur_job_num !== $below ) { $groupings[$cur_job_num]['end'] = $row; } } } } //setup row groupings foreach ( $groupings as $g ) { $end = isset($g['end'] ) ? $g['end'] : $g['start']; for ( $row = $g['start']; $row <= $end; ++$row ) { $spreadsheet->getActiveSheet() ->getRowDimension($row) ->setOutlineLevel(1) ->setVisible(true) ->setCollapsed(false); } } It seems to have made two large groups: But I've looked at my array and it seems to be correct: Array ( [22005572] => Array ( [start] => 2 [end] => 3 ) [22039882] => Array ( [start] => 4 ) [23521431] => Array ( [start] => 5 [end] => 6 ) [23616901] => Array ( [start] => 7 ) [23617551] => Array ( [start] => 8 ) [23617561] => Array ( [start] => 9 ) [23617851] => Array ( [start] => 10 [end] => 14 ) [23618511] => Array ( [start] => 15 ) [23618761] => Array ( [start] => 16 ) [22016952] => Array ( [start] => 17 [end] => 18 ) [22017812] => Array ( [start] => 19 [end] => 20 ) [22023852] => Array ( [start] => 21 [end] => 25 ) [22023892] => Array ( [start] => 26 [end] => 28 ) [22027752] => Array ( [start] => 29 [end] => 30 ) [22031712] => Array ( [start] => 31 [end] => 32 ) [22032642] => Array ( [start] => 33 [end] => 34 ) [22036842] => Array ( [start] => 35 ) [22040842] => Array ( [start] => 36 ) [22961571] => Array ( [start] => 37 ) [22978751] => Array ( [start] => 38 ) [22978831] => Array ( [start] => 39 [end] => 40 ) [22992301] => Array ( [start] => 41 ) [22992321] => Array ( [start] => 42 ) [22992331] => Array ( [start] => 43 ) [23585641] => Array ( [start] => 44 ) [22010852] => Array ( [start] => 45 [end] => 46 ) [22007412] => Array ( [start] => 47 [end] => 48 ) [22031192] => Array ( [start] => 49 [end] => 54 ) [22039462] => Array ( [start] => 55 ) [22042092] => Array ( [start] => 56 ) [23584291] => Array ( [start] => 57 [end] => 58 ) [23615841] => Array ( [start] => 59 ) [23615921] => Array ( [start] => 60 ) [23616201] => Array ( [start] => 61 ) [23616221] => Array ( [start] => 62 ) [60266410] => Array ( [start] => 63 ) [29100510] => Array ( [start] => 139 [end] => 68 ) [25294050] => Array ( [start] => 69 [end] => 74 ) [25294060] => Array ( [start] => 75 [end] => 77 ) [25294070] => Array ( [start] => 78 [end] => 83 ) [25294080] => Array ( [start] => 84 [end] => 89 ) [25294680] => Array ( [start] => 90 [end] => 93 ) [25294690] => Array ( [start] => 94 [end] => 99 ) [23616371] => Array ( [start] => 100 ) [23616381] => Array ( [start] => 101 ) [23616391] => Array ( [start] => 102 ) [23616491] => Array ( [start] => 103 [end] => 104 ) [23616731] => Array ( [start] => 105 [end] => 106 ) [23616741] => Array ( [start] => 107 ) [23551851] => Array ( [start] => 108 ) [22003322] => Array ( [start] => 109 ) [22004302] => Array ( [start] => 110 [end] => 111 ) [22011072] => Array ( [start] => 112 ) [22015832] => Array ( [start] => 113 [end] => 117 ) [22024612] => Array ( [start] => 118 ) [22026012] => Array ( [start] => 119 ) [22027702] => Array ( [start] => 120 [end] => 121 ) [22028302] => Array ( [start] => 122 ) [22028382] => Array ( [start] => 123 ) [22031532] => Array ( [start] => 124 [end] => 126 ) [22973641] => Array ( [start] => 127 ) [22979331] => Array ( [start] => 128 ) [29089920] => Array ( [start] => 129 [end] => 133 ) [23587361] => Array ( [start] => 134 [end] => 136 ) [23607111] => Array ( [start] => 137 ) [23615581] => Array ( [start] => 138 ) [23616961] => Array ( [start] => 140 [end] => 141 ) [22012662] => Array ( [start] => 142 ) [22018892] => Array ( [start] => 143 [end] => 146 ) [22022802] => Array ( [start] => 147 ) [22027922] => Array ( [start] => 148 ) [22033592] => Array ( [start] => 149 ) )

I'm trying to dynamically group a range of rows using PHPSpreadsheet and having a lot of trouble thinking through the logic. My code so far looks like this: $worksheet = $spreadsheet->getActiveSheet(); // Get the highest row number and column letter referenced in the worksheet $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' // Increment the highest column letter $highestColumn++; $job_num_chk = ''; $grp_start_row = ''; //format the inserted records for ($row = 2; $row <= $highestRow; ++$row) { //$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53); for ($col = 'A'; $col != $highestColumn; ++$col) { $spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP); $spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT); if ($col == 'B') { $cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number //check if job number is different if ( $job_num_chk !== $cur_job_num ) { $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row //set dividing row to black $spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill() ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) ->getStartColor()->setARGB('000000'); $job_num_chk = $cur_job_num; //update job number } } } } This line: $grp_start_row = ''; Is where I'm hitting a mental wall...here's what the spreadsheet looks like so far: I want to be able to group the rows 101-105 in that example like below: I need to be able to determine where the start row is and the end row is in order to use the grouping feature (https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#groupoutline-a-row) Thanks everyone.