mac_gyver

when data insertion fails occasionally, it's usually because of sql special characters in a value breaking the sql query syntax, data containing duplicate values that violate a unique index or other constraint, or data that is out of range for a column definition. these conditions would produce query errors, so, the question becomes does the php code have error handling for all the database statements that can fail - connection, query, prepare, and execute that would be logging the errors to the php error log or is the mysql general query log enabled (it's not by default)?

here's a big problem with the existing table per category. the ids are repeated, so, having an id doesn't uniquely tell you what the item is, nor does it let you find (sub)items related to the item. when someone selects an item, you cannot just store the item id and the quantity. you must also store the category_id, requiring more data storage and more code for every operation. are you actually reading and getting the information in the replies? there's a post above stating how simple you can build the category and item tables from the table data you have shown us. the code to do so would look like (untested) - <?php // get or build a list of table information $tables = []; // table - is the existing table name // name - is the item name, which unfortunately repeats part of the table name, making everything harder than necessary // category - is the desired category name $tables[] = ['table'=>'rifles','name'=>'rifleName','category'=>'rifle']; $tables[] = ['table'=>'shotguns','name'=>'shotgunName','category'=>'shotgun']; // add entries for the rest of the tables here... // query to populate category table $sql = "INSERT category (name) VALUE (?)"; $cat_stmt = $pdo->prepare($sql); foreach($tables as $table) { // insert the category data $cat_stmt->execute($table['category']); $cat_id = $pdo->lastInsertId(); // query to populate item table $sql = "INSERT item (category_id, name, em, gm) SELECT $cat_id, `{$table['name']}`, em, gm FROM `{$table['table']}`"; $stmt = $pdo->query($sql); }

if you look again, the seconds are not used. the php date format string would be - Y-m-d\TH:i

one of the points was properly organizing the data in the database - which brings up the programming motto - a good database design supports good code design. you can build the category and item tables from your existing tables by using a dynamically built INSERT ... SELECT query and some code to get and loop over all the table names.

php installation was probably updated and turned off the output_buffering php.ini setting, resulting in previously working bad code to stop working.

the code i posted is a data-driven design. it takes the input data that is supplied to it and dynamically produces as many select/option menus as there are categories in the data. the javascript is also general-purpose and will work as is regardless of how many different select/option menus there are. there's no need to touch any of the actual code, unless there's a mistake that needs to be fixed or an actual change is desired. if you comment out the database specific code, and un-comment the fake data code, you can try this by itself to see how it works.

see the following example, using the above points (tested with fake data) - <?php // initialization $servername = "localhost"; $dbport = '3306'; $dbname = 'ehr_tracker'; $dbuser = 'root'; $dbpass = ''; // when you make the connection, also set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement // there's no good reason for you to catch a connection error, just let php catch it. // also, don't output the raw database errors, as these only help hackers. // likewise, a connection error is a fatal problem and code should stop executing in order to prevent follow-on errors. $pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8;'", $dbuser, $dbpass, array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); // get method business logic - get/produce data needed to display the page // the following assumes you want all category data, // if not add a WHERE clause to get just the categories you want $sql = "SELECT c.name category_name, i.id, i.name, i.em, i.gm FROM items i JOIN category c ON i.category_id = c.id ORDER BY c.name, i.name"; $stmt = $pdo->query($sql); // fetch the data, indexing/pivoting it by the first column selected - category_name $item_data = $stmt->fetchAll(PDO::FETCH_GROUP); /* // fake some data $data = []; $data[] = ['id'=>'1', 'name'=>'r1', 'em'=>'10', 'gm'=>'12', 'category_name'=>'rifle']; $data[] = ['id'=>'2', 'name'=>'b1', 'em'=>'20', 'gm'=>'22', 'category_name'=>'bow']; $data[] = ['id'=>'3', 'name'=>'b2', 'em'=>'25', 'gm'=>'27', 'category_name'=>'bow']; $data[] = ['id'=>'4', 'name'=>'s1', 'em'=>'30', 'gm'=>'32', 'category_name'=>'shotgun']; $data[] = ['id'=>'5', 'name'=>'s2', 'em'=>'33', 'gm'=>'34', 'category_name'=>'shotgun']; $data[] = ['id'=>'6', 'name'=>'s3', 'em'=>'37', 'gm'=>'38', 'category_name'=>'shotgun']; // index/pivot the fake data like the FETCH_GROUP would produce $item_data = []; foreach($data as $row) { $item_data[ $row['category_name'] ][] = $row; } //echo '<pre>'; print_r($item_data); echo '</pre>'; */ // html document starts here... ?> <html> <head> <style> body { background: black; color: white; } input { background: black; color: white; border: 1px solid orange; text-align: center; } select, selection { background: black; color: white; border: 1px solid orange; } table { border: 1px solid gray; border-collapse: collapse; } </style> </head> <body> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script> $(document).ready(function() { $(".populate_em_gm").change( function() { let em = $(this).find("option:selected").data("em") let gm = $(this).find("option:selected").data("gm") $(this).closest('tr').find('.em').val(em) $(this).closest('tr').find('.gm').val(gm) }) }) </script> <table border="1"> <tr><th>Items</th><th>em</th><th>gm</th><th>+</th><tr> <?php foreach($item_data as $category_name=>$arr) { $cn = ucfirst($category_name); echo "<tr><td colspan='2'>{$cn}s</td><td></td><td><button type='button'>+</button></td></tr>"; echo "<tr><td>"; echo "<select class='populate_em_gm' name='$category_name'>"; echo "<option value=''>Choose a $cn</option>"; foreach($arr as $row) { echo "<option value='{$row['id']}' data-em='{$row['em']}' data-gm='{$row['gm']}'>{$row['name']}</option>"; } ?> </select></td> <td><input type="text" class="em" value="0" disabled size="5"/></td> <td><input type="text" class="gm" value="0" disabled size="5"/></td> <td>+</td> </tr> <?php } ?> </table> </body> </html>

writing out, copy/pasting/overtyping, code for every possible value/category is not programming, that's you being a human-keyboard-macro. you are not using the computer as a tool to accomplish a task, the computer is using you. your database design should have a category table, with id and name columns. this would establish category_ids. you should have an item (or similarly named) table with id, category_id, and name columns. you would then just use one query to get the data that you want and loop over it to produce the output. to add new categories or add items under a category, you would just insert the data into the appropriate table(s), and the code would 'automatically' use the new values without you needing to touch the code. you would take a similar Don't Repeat Yourself (DRY) approach in the html and javascript, where you would dynamically build the output sections by looping over the data, putting each select/option menu and em/gm fields in a 'container', that one instance of general-purpose javascript would operate on to populate the correct em/gm fields when there's a change in the corresponding select/option menu.

that's not the correct format, resulting in different browser/operating system/local system defaulting to what they think they should do. did you read the information at the developer.mozilla.org link that was given?

the format for the value of a datetime-local field is - YYYY-MM-DDThh:mm ref: https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/datetime-local

why are you using two different names for the same meaning data? besides the existence or absence of the id, what's different about the edit vs create form? can you not just always have a hidden id field that has a value if editing an existing entry or is empty when creating a new entry? your post method form processing logic should be - detect if a post method form was submitted perform common tasks, such as trimming all the input data at once, validating the common inputs between both processes test if the id is empty or not if the id is empty, run the unique code for creating a new entry else, run the unique code for updating an exiting entry

the following is the SELECT query prototype definition, with the elements you will need in bold - SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] the select_expr would include a COUNT() term to get the number of matching rows per group, along with any other columns you want in the result. the FROM table_references would include a JOIN between the two (or is it three as the title says) tables. the WHERE where_condition would match the column and value for billable items. the GROUP BY col_name would group by the customer id/name column. the ORDER BY col_name would sort the results the way you want, such as by the customer name. give this a try.

here's a correction to the above, move the closed != 1 codition to the sub-query - SELECT q.id as quoteId, q.job_id as jobId, j.name as jobName, j.client_id as clientId, c.name as clientName, q.currency, q.version FROM quote q JOIN job j ON j.id = q.job_id JOIN client c ON c.id = j.client_id WHERE q.id IN(SELECT MAX(id) FROM quote WHERE closed != 1 GROUP BY job_id)

using the IN(sub-query) method. untested, should work - SELECT q.id as quoteId, q.job_id as jobId, j.name as jobName, j.client_id as clientId, c.name as clientName, q.currency, q.version FROM quote q JOIN job j ON j.id = q.job_id JOIN client c ON c.id = j.client_id WHERE q.closed != 1 AND q.id IN(SELECT MAX(id) FROM quote GROUP BY job_id) in writing the above, i realize that your quote table contains redundant data in that it has the client_id in it. the reason you are joining the client table to the job table is because the client is related to the job, not to the quote. if you post your table definitions, just post an .sql dump of your database so that someone could help by actually testing the query

i/we would assume that's the id, auto-increment primary index for an existing record in the database table, passed in a hidden form field, not one of the user entered fields. you would then use the existence or absence of the id to control if you are editing/updating existing data or inserting new data.

yes. just name it $item, because that is what it is in the code that's calling that function. then reference elements in item, e.g. $item['quantity'] inside the function code. this actually would have prevented the initial error at the top of this thread, because there would be no need to match up all the different parameters. it will also allow you to add more values, such as the lineNotes, simply by selecting that data where you are querying for it, then using that data inside the function. you won't have to edit the list of call-time parameters every time you change the data being passed.

another issue with the posted code. when you are building the 'selected' option choices, in both the quantity and the item menus, you are outputting the option with the selected attribute when the existing value is the same as the value being output AND you are repeating the same option choice without the selected attribute. e.g. if the selected quantity is 3, you are outputting an option choice for 3 that is selected, followed by another option choice for 3 that is not selected. i doubt this is the intended result. the only conditional logic should be to determine what the selected attribute should be, the option markup should only be output once per value, that is either selected or not. note: you can simply put the 'selected' attribute in or leave it out. you don't have to put it in AND set it to a (true, i.e. any non-empty string) value to cause the option to be selected. if by some chance this is the intended result, in the case of the item select/option menu, the data-... attribute(s) are used in the lineTotal/line-total calculation, so they must be output in every option choice. the current code is not outputting them in the 'selected' option choice, which will prevent the calculation code from producing the total.

the intended purpose of having the database name as a call-time parameter is so that you can make multiple connections, each with their own selected database. for this to work, $DBName must be removed from the global ... ; line of code, so that the call-time parameter will be the value that gets used. if there's ever a case of directly making an instance of the DBSQL class, yes. the line $this->DBSQL($DBName); would need to be changed to parent::__construct($DBName); as well.

an additional point about the function implementation. the purpose of the markup being built inside of getItemsForQuote is to edit the existing item row values. to do so, you need to supply all those initial existing values to the code, which actually changes to become the submitted form data, when you redisplay the form upon a validation error. do NOT add a discrete call-time parameter for each of these values. simply pass an array of values as one call-time parameter. when you initially produce the form, the array of data would be that which you query for and fetch from the database. when you redisplay the form upon a validation error, the array of data would be the submitted form data.

this code is killing your database server with connections. a database connection is one of the slowest, resource consuming operations you can do. your main application code should make one database connection. you would then pass it as a call-time parameter to any function that needs it. the getItemsForQuote function has the wrong responsibility. it is mixing the database specific code with the presentation code, it should only get the data, like its name indicates and my not actually be needed to get the data one item at a time. see the next point. this code is executing a select query within a loop (getting all the items for the item_type of the current item), which is also inefficient. even if there's only one item per item_type in a quote, and even worse if there is more than one, you should get all this data in one query, index it by the item_type when you fetch it, then access it when building each itemSelected select/option menu.

see the 4th item under 'Other incompatible Changes' at this link - https://www.php.net/manual/en/migration80.incompatible.php also see the 'Old-style constructors' at this link - https://www.php.net/manual/en/language.oop5.decon.php

i just tried the code, and there is one php version difference. a method named the same as the class is no longer automatically called when an instance of the class is created. you must specifically name the method __construct. change function PicSQL($DBName = ""){ to function __construct($DBName = ""){

there is no password parameter in the pg_connect() call. does the database have a password set for the connection? is this the exact code that has previously worked? note: because you are passing the database name as a call time parameter and also using the global keyword to get the database name into the function, i'm not sure which takes precedence or if the call time parameter value sets the global variable. the only reason why this would have previously worked, is because the same value is being used in both cases. if you have any DBSQL(...) calls that are using a different value than $DBName, this probably won't work the way you think it should. you can try to use echo pg_last_error(); to see if it provides some information (doubtful.) for the error logging, do other types of php errors get logged? if so, use a phpinfo() statement and check if error_reporting is set to E_ALL, so that ALL php errors will get logged. putting the error_log(...); statement right before the pg_connect(...) call would let you know what values are actually being used inside the function. short-version: there's nothing apparent in the code that is php version specific. the connection is failing and is probably returning a false value. you can use var_dump($conn); right after the connection statement to check. therefore, you need to determine why the connection is failing.

the version of your query with quotes around the place-holders should have been producing php/sql errors, about the number of bound parameters not matching the query... do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, and have you set the PDO error mode to use exceptions? also, the error response you get can change depending on an emulated vs true prepared query. have you set the PDO emulated prepared setting to false? edit: which was mentioned in the replies in your previous PDO based thread - https://forums.phpfreaks.com/topic/314929-pdo-uncaught-error-call-to-a-member-function/

moving data is also not the correct way of doing this. it's a lot of work for nothing. the record that was (should be) inserted for each sale is still correct. it defines all the who, what, when, where, and why information about the sale that was made. if something is returned, that's a different transaction that affects the quantity and it is defined by the who, what, when, where, and why information in the void/return record that gets inserted.