Jump to content


Staff Alumni
  • Posts

  • Joined

  • Days Won


Everything posted by mac_gyver

  1. yes, see the reply posted above your's that the ipb forum programmers Chimps couldn't figure out how to reliably notify you of while you were posting your reply. the code you posted implements some of the things i mentioned, but still has some issues - 1) by testing $result after you have tried to fetch data from the query, that code will throw a php error at the fetch statement any time the query fails due to an error. you would need to test $result before you try to fetch any data. however, if you enable exceptions like suggested, you don't need to have any logic in your code testing $result. if there is a query error, an exception will be thrown and the code trying to fetch data will never be executed. 2) by hard-coding the echoing of $mysqli->errno and $mysqli->error in the code, you will expose information in the errors to a visitor. if you use the suggested exception method, what happens with the error information is controlled by php settings, so that on a live server, you would log the information, rather than to display it, simply by changing a couple of php settings. 3) the first code has this - $row = $result->fetch_assoc() for the fetch statement. you now have this - $row=mysqli_fetch_array($result,MYSQLI_ASSOC). while both are functionally equivalent, why are you making unnecessary changes and going from simpler to more verbose syntax? Keep It Simple. 4) see what i wrote above about not creating discrete variables. your current method has you typing things in the SELECT list, two times in each $some_var = $row['some_column']; assignment statement, and then where you use the data. using the suggested method eliminates all the assignment statements. you would typically fetch the data into a variable named to indicate what the data means. so, rather than $row, you would use something like $stream_data. 5) lastly, i didn't write it above, but php closes the database connection for you when the php script ends. unless your code is taking a large amount of time to run after fetching the data from the database, there's no good reason to close the database connection yourself. so, for the last posted code, this is all you would really need - $sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 1"; $result = $mysqli->query($sql); $stream_data = $result->fetch_assoc(); // just use the elements in $stream_data in the rest of the code
  2. your current code is missing a opening { for the while() loop. this would be producing a php syntax error if you had php's error_reporting set to E_ALL and display_errors set to ON in the php.ini on your development system. if you put these settings into your code, they won't help with php syntax errors in the same file, because your code never runs to cause the settings to be in effect. speaking of (writing about) the while() loop, if you are running a query that you expect to match a single row, don't loop to fetch the results. this is just cluttering up your code. just fetch the row as an array into a variable. speaking of (writing about) variables, what's wrong with an array variable? by creating discrete variables from each element of an array, all you are doing is spending time typing, fixing typo errors, and changing the code every time you change what gets SELECTEd by a query or reuse the code for a different query and also make the same changes to the code that uses the data. you should just fetch the data from the query into one php array variable, then use that array variable everywhere in the rest of your code. Keep It Simple. Programming is already a tedious task. Don't make more work for yourself by typing out line after line after line of code that you must keep editing any time something changes. next, what is this - if (!$sql) { ? the $sql variable is a php string that represents the sql query statement you have built. unless it's an empty string, it will never be false. i suspect this usage is trying to handle errors? if so, you need to use exceptions to handle errors. this will eliminate the need to write logic around every database statement that can fail. your main code will only have to deal with error free database statement execution. if you enable exceptions for the mysqli extension, any error will throw an exception. if you let php handle the uncaught exception, it will use the php error_reporting/display_errors/log_errors settings to determine what happens with the actual error information. for the mysqli extension, if you enable exceptions before you try to make the database connection, any connection error will also throw an exception. to enable exceptions for the mysqli extension, add the following before your connection code - $driver = new mysqli_driver(); $driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <-- w/index checking; w/o index checking --> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT
  3. it requires nothing. it's just a dang example and this stuff is called software for a reason. if it doesn't do what you want, you can change it so that it does.
  4. you should use an array name for the form field, with the array index being a unique name if you need to identify which field a file was selected for. if you leave the index value out, you will get integer indexes, starting at 0. next, you MUST test if a file was successfully uploaded before you can reference any of the file information. if a file wasn't selected, the ['error'] element of the uploaded file information will be - the ['error'] element will be - UPLOAD_ERR_OK if the file was successfully uploaded. see example #3 at this link - http://php.net/manual/en/features.file-upload.post-method.php for how you can define the form field and loop over the file information.
  5. i recommend that you tell or show us what symptom or error you are getting that leads you to believe that something doesn't work. we are not sitting there with you and don't know what it is you are seeing in front of you. why are you using both the msyql and mysqli extensions in one script? do you even have a connection using the mysql extension? there would be php error's if you don't. do you have php's error_reporting set to E_ALL and display_errors set to ON so that php would help you by reporting and displaying all the errors it detects? your code should ALWAYS detect and handle errors with database statements, so that it doesn't try to run logic that's dependent on database statements working when they haven't and test to make sure that a query matched a row of data before using the data. why are you using a multi_query() statement for a single query? this opens the door for sql injection to run ANY type of query, especially since you are not doing anything to protect against sql injection. even if you currently trust the data source, someone at some point can get nefarious data onto the site you are getting your data from. you must ALWAYS protect against sql injection. btw - the php mysql extension has been removed from php for almost a year. you should not be using it at all at this point in time.
  6. your $_POST data isn't the problem. your code is most likely being executed. why you are getting a symptom that makes it appear like your code isn't being executed is due to either php's output_buffering (hides output from your code and php errors if there is a header() redirect) and/or php's error reporting/display_errors (php errors are not being reported/displayed) settings that are set on your server. you should be getting an error like this - you need to have the following set in the php.ini on your development system - error_reporting = E_ALL display_errors = ON output_buffering = OFF make sure these are set as shown, restart your web server to get any changes made to the php.ini to take effect, and confirm that the settings actually got changed by checking the values with a phpinfo() statement in a .php script. this will get your development system set up so that it will help you by showing any output from your code and from php errors. once you are getting that particular Notice: ... error, it should be easy to find and fix the problem in your code. next, you need to use prepared queries, with place-holders in the sql query statement for data values and use exceptions to handle database errors. this will actually simplify both your sql query and php program logic. unfortunately, the php mysqli extension is not the best choice. if you can, switch to use the php PDO extension. it is more consistent and easier to use than the mysqli extension.
  7. you would start by going through the examples in the documentation. mpdf expects you to either capture the html you want to convert or to add each line of html as you are producing it. the easiest way of coding this would be to do it on the page where you are producing the html output, so that you don't need to duplicate the code that's responsible for searching the database and producing the html. you would also want to do it completely on the server-side, since accepting the html input from the browser would leave the application open to inserting 'any' content into the pdf document, not just the output from your report code.
  8. to make this general purpose (what if a different door frame material requires more or less screws), the easiest method is to simply enter the number of screws as the multiplier in the rule for each different door item id, not for the category of sliding doors.
  9. see post #2 in the thread, point #2, about how i came up with categories from your existing code. this example is for your hard-coded rules for 'ManDoors' and 'Windows', converted into general purpose, data driven code. 'ManDoors' and 'Windows' are not specific items, they are at best categories that items are organized under. in general, you would use id's. since your current code doesn't have id's for categories, only names as part of method calls, i used the only information that i had available in the example. the offset is there for when you want to offset the quantity by a value, not dependent on how may 'used on' items there are. different variations of something, insulated vs non-insulated, are/should be different items/part numbers. each different item would have their own unit rule. since ManDoors seems to be a category, you would have categories for insulated ManDoors and un-insulated ManDoors. for the example given, you would add as many rules are there are cases - for white caulking rules - $unit_rules['ManDoors'] = array('mult'=>.75,'offset'=>0); $unit_rules['Windows'] = array('mult'=>.75,'offset'=>0); $unit_rules['ManDoors-insulated'] = array('mult'=>1.2,'offset'=>0); $unit_rules['Windows-insulated'] = array('mult'=>.8,'offset'=>0); for any cases like this, i would add a line item to the BOM, 'building height adder' that the rules in the the scissor lift quantity calculation would use - $unit_rules[building_height_adder_item_id_goes_here] = array('mult'=>0,'offset'=>1);. then if the building height adder is on the BOM, the scissor lift quantity calculation would result in 1 scissors lift rental. see the commentary in post #2. the whole point of what i posted is to move the values that are hard-coded in the program logic into a data structure, making this a data-driven design. once you get the hard-coded values out of the code, you can define them in a database table and let new rules get added or existing rules get edited by providing a user interface for the database table.
  10. the easy answer to the Sliding Tec Screws, is, to just enter the number of screws per door as the multiplier for each door entry.
  11. creating a new entry would be fairly straight forward - 1) enter the name and select the category for the new item. inserting this into the product/item table would assign an item id to it. the item id would be used in any related data and logic. 2) if the new item is used by other unit items or categories of items, you would need a way of searching/listing and selecting (checkboxes) which of the existing items/categories it goes with. the category id or item id of the selected item would become the $unit_rules key/index. you would enter any multiplier/offset or other values you determine you may need. any group rules you have define would have a way of selecting just the possible choices. if at all possible, i would avoid allowing any arbitrary logic/tests to be entered. i'll have to give some thought to the Sliding Tec Screws case.
  12. feel free to use, modify, or ignore any of these thoughts - 1) in general, if you find yourself making variables/properties/constants and functions/methods where the name is specific to the name of a data item, so that any time you change the amount of data items, you must write new/delete variables/properties, constants, functions/methods, you have an inflexible hard-coded design, that is both taking your time writing and testing, but also maintaining. your code should instead be general purpose, where the names of things indicate their purpose or function, not the name of the data they operate on. see the next item for some examples - 2) $this->getTotalQuantityManDoors(true) + $this->getTotalQuantityWindows(true)). i'm betting that the code for each of these methods is identical, except for some values being used, and you have a bunch more of these type of named methods. these are operating on categories. if your product/item table has categories, you could simply use one common getTotalQuantityByCategory(category_goes_here) method. the same is probably true for a bunch of other hard-coded functions getting quantities of items. just have a common getTotalQuantityByItem(item_id_or_name_goes_here) method. 3) customer supplied items. it sounds like you have a bunch of code/logic/parameters to handle this special case, repeated many times? i'm assuming prices are stored in a product/item table and are multiplied by the BOM table quantity to come up with the total price for each item? to handle customer supplied items, just add a price multiplier column to the BOM table. for items that are customer supplied, store a zero in this column. for items that are purchased for the project, store a one/use a one as the default value for the column. multiply the item price * bom quantity * this new column to come up with the total price for any item. no extra handling is needed. the BOM quantity can be used as is to calculate any dependent quantities. 4) i would use a data driven design, where the rules are defined in data (that can be stored/retrieved from a database table.) this will remove the values from the logic and let you have just one copy of the logic that is re-used with different sets of rules. you can have 'unit' rules, that apply to each quantity of an item/category, and group rules, that apply to the total quantity in a group (i would put rounding up/down, box quantities .. here). for each item/category, define a multiplier and an offset. as you are looping over the main items, just apply the multiplier and offset to come up with the quantity. for any group, once you have the quantity for the group, apply the group rules. here is a pseudo code example for the white caulking - case (self::whiteCaulking) : $unit_rules = array(); // note: this example is for categories, not individual items, though you could mix them easily by adding an indicator to the defining array if the $key it a category or an item and call the appropriate method to get the correct quantity $unit_rules['ManDoors'] = array('mult'=>.75,'offset'=>0); $unit_rules['Windows'] = array('mult'=>.75,'offset'=>0); $group_rules = array(); $group_rules[] = array('operation'=>'function','name'=>'ceil'); $quantity = 0; // apply any unit rules foreach($unit_rules as $key->$rule) { $quanity += ($this->getTotalQuantityByCategory($key) * $rule['mult']) + $rule['offset']; } // apply any group rules foreach($group_rules as $rule) { switch($rule['operation']) { case 'function': $quantity = $rule['name']($quantity); break; } } $quantities[] = $quantity; break; i didn't work out how this would apply to the slidingTecScrews example, but it should be possible to make this work for any case you have now. you will find that the actual php logic for each case statement is or can be made to be the same, just with different defining arrays. when you get to that point, you would just set up the two arrays of data for each case, then call a common function/method to calculate the quantity and return it to the calling code. once you get to this point, you can eliminate all the switch/case logic and just store/retrieve all the rules in a database table and loop over them in turn to calculate the bom quantities.
  13. i don't really think i can help you 'get' what it is you need to do to be able to write code that does something useful. you are randomly changing code, because you haven't taken the time to learn what the statements in the code actually mean and do, then dumping the code on a forum for someone to tell you what to do to fix it. you won't learn anything by getting some to fix your code for you. some problems in the posted code - you are no longer preparing the query, yet you are trying to bind parameters to it. you are using a wrong PDO defined constant in the bindParm() statement, you are using global $per_page; that does absolutely nothing in the context of this code. the only thing you are currently doing different, from at the start of this thread, is changing the sql query statement, specifically just the WHERE ... term. the only code you need to change are the lines that are building the WHERE term. the rest of the code needs to remain the same. and even you were doing this for a completely different sql query statement, the only things you would change in the code are the two queries, the SELECT COUNT(*) ... query , that gets the total number of matching rows and the SELECT list of columns ... query, that retrieves the matching data. this is the code, from reply #10, that is building the WHERE term - // define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on $search_fields = array('title','name','description'); $and_terms = array(); // WHERE terms to be AND'ed $params = array(); // bound input parameters for a prepared query foreach($search_fields as $field) { if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL' { // add the search field to the WHERE terms $and_terms[] = "$field = :$field"; $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR); } } $where_term = ''; if(!empty($and_terms)) { $where_term = "WHERE " . implode(' AND ', $and_terms); } all you have to do is change this section of code so that it produces WHERE memberID = :memberID as the where term and then add the correct entry to the $params array for the $_SESSION['memberID'] value. in its simplest, bespoke form, this is what you would have left - $params = array(); // bound input parameters for a prepared query $where_term = "WHERE memberID = :memberID"; $params[] = array(":memberID",$_SESSION['memberID'],PDO::PARAM_INT); the rest of the code, starting with - $query = "SELECT COUNT(*) FROM table $where_term"; doesn't change, unless you want it to do something differently, such as output the data differently or you want the pagination links to be different.
  14. no, the number of entries in the array definition is not related to the bindValue() list of parameters. you must know, by reading the php.net documentation, what parameters the bindValue() method takes, in order to understand what the code is doing and why there are three parameters. to search more columns, yes, you only add them to the $search_fields array.
  15. there's two reasons you are getting an error at the ->execute() method call - 1) emulated prepared queries are on by default (and not well documented, thanks php). meaning, you are not running real prepared queries. the ->execute() method is forming the complete sql query statement, with the bound data inserted into it, then calling the ->query() method internally to execute the query. when you make the database connection, you need to set emulated prepared queries to false. add this line after the connection - $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries and you might as well do something else i stated and add this line - $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode 2) the error you are getting should contain information about an sql syntax error (type, the name of one of your columns, is a reserved keyword), though the error information at the execute() method is sometimes lacking, more than normal..once you set emulated prepared queries to false, you will be getting an explicate sql syntax error at the ->prepared() method call. you should avoid using reserved keywords as database, table, and column names, but if you must, you can add back-ticks around the $field name in the following line of code (in each place the query is being prepared, or in one place, if you make the general purpose prepared query method as suggested) - $and_terms[] = "`$field` = :$field"; by removing the name='submit' from the submit button in the form. for a get method form, there's no need for a name for the submit button. the form field data will either exist or it won't and your code just needs to check for the field data, not a submit button. you also need to remove the if(isset($_GET['submit'])){ and the matching } from the code. and remove the three lines of code that are right after the if(isset($_GET['submit'])){ statement.
  16. the OP's code is actually setting a default value of 1, if there is no page get parameter, validating the value as an integer (within php's integer range), and is limiting the value between 1 and the total number of pages. don't copy or use code unless you understand it. this bit of code is repetitive and unnecessarily bespoke/hard-coded, and isn't using the correct entity version of an & for links on a page. your code still has a number of unnecessary and problematic things in it, which i am betting you have been informed of most of them in previous threads - 1) use the correct input variables where you expect the data to be. use $_GET, not $_REQUEST. 2) i don't know what your clean() function code is, but i'm betting it isn't effective and is best left out. 3) the try/catch block will catch errors with the prepare/execute methods. this means that there was a sql syntax error or wrong bound parameters. this does not mean that the query ran, but didn't match anything. there's no need for a local try/catch block unless you are specifically handling a type of query error in your code, such as a duplicate key error from and INSERT/UPDATE query. 4) the global keyword only has meaning when used inside a function definition and even there it indicates bad coding. don't use global $var_name; 5) the code to build the WHERE term for the sql query statement should not be repeated. build the term in a variable, then use that variable each place it is needed. 6) the external data you are putting into the WHERE term needs to be handled using a prepared query with bound input parameters. 7) the $start and $end variables are not used in the posted code and should be removed. the sql query statements should be built in a php variable, this supports debugging (you can echo/log the statement, though the pdo statement object has a queryString property you can use) and it leads to writing general purpose code. 9) you need to set the default fetch mode in your connection code, so that you don't need to specify it every time you run a query. 10) the pdostatement object is already traversable. you don't need and shouldn't be applying the new IteratorIterator() to it. 11) for queries that will return a set of data, just fetch all the data using the fetchAll() method. you can then use count() on the data to find how many rows the query matched (the ->rowCount() method doesn't work with SELECT queries for all database types, so, using fetchAll()/count() will work regardless of which type of database you are using.) 12) to produce the pagination output, you need to test how many pages were calculated, not if the data retrieval query returned any rows (there's a condition that's mentioned in the code where you would need to produce the pagination links even if the data retrieval query didn't match any data) 13) your <span> tags are reusing the same id='...' value, which is invalid. if you are doing something unique with each span (which is doubtful), you would need to use unique id values. if you are not doing anything with the span, don't include it in the markup. the following example code shows how you might do this. this code adds the following features - 1) shows how to do a data driven design, where you define somewhere (an array or database table) a set of data that general purpose code operates on. this eliminates repeating block after block of same functioning code. this also has the benefit of helping to implement DRY (Don't Repeat Yourself) programming, since it eliminates the repetitive logic. 2) it implements the suggestion of leaving a particulate field out of the sql query statement when it is the 'ALL' choice or when the choice isn't present at all in the $_GET parameters. 3) shows how you would apply http_build_query() when building each link. // define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on $search_fields = array('title','name','description'); $and_terms = array(); // WHERE terms to be AND'ed $params = array(); // bound input parameters for a prepared query foreach($search_fields as $field) { if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL' { // add the search field to the WHERE terms $and_terms[] = "$field = :$field"; $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR); } } $where_term = ''; if(!empty($and_terms)) { $where_term = "WHERE " . implode(' AND ', $and_terms); } // get the total matching rows $query = "SELECT COUNT(*) FROM table $where_term"; // note: the following logic should be in a general purpose prepared query method that you extend the PDO class with if(empty($params)) { // no bound inputs, just execute the query $stmt = $db->query($query); } else { // there are bound inputs, produce a prepared query, bind the inputs, and execute the query $stmt = $db->prepare($query); foreach($params as $param) { $stmt->bindValue($param[0],$param[1],$param[2]); } $stmt->execute(); } $total = $stmt->fetchColumn(); // calculate total number of pages $pages = ceil($total / $per_page); // limit the page number $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array( 'options' => array( 'default' => 1, 'min_range' => 1, ), ))); // calculate starting row for LIMIT $offset = ($page - 1) * $per_page; // add limit values to the array of bound parameters $params[] = array(':per_page',$per_page, PDO::PARAM_INT); $params[] = array(':offset',$offset, PDO::PARAM_INT); // query for the data $query = "SELECT * FROM table $where_term ORDER BY id DESC LIMIT :per_page OFFSET :offset"; // note: the following logic should be in a general purpose prepared query method that you extend the PDO class with if(empty($params)) { // no bound inputs, just execute the query $stmt = $db->query($query); } else { // there are bound inputs, produce a prepared query, bind the inputs, and execute the query $stmt = $db->prepare($query); foreach($params as $param) { $stmt->bindValue($param[0],$param[1],$param[2]); } $stmt->execute(); } $result = $stmt->fetchAll(); if(!count($result)) { // query didn't return any row(s) - this doesn't mean there isn't any matching data, just that the query for the requested LIMIT range didn't return anything (there's a race condition, where if data gets deleted between the COUNT() query and the data retrieval query, queries for data near the end can return nothing) echo '<p>Nothing found.</p>'; } else { // query matched one or more row(s), display the data foreach ($result as $row) { echo $row['id']; } } // if there are any pages, display the pagination if($pages) { echo '<div id="pagination"> <div id="pagiCount">'; $q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links $prevlink = ''; if($page > 1) // not on the first page { $q['page'] = 1; $qs = http_build_query($q,'','&'); $prevlink = "<a href='?$qs' title='First page'>First</a> "; $q['page'] = $page - 1; $qs = http_build_query($q,'','&'); $prevlink .= "<a href='?$qs' title='Previous page'><<</a>"; } $nextlink = ''; if($page < $pages) // not on the last page { $q['page'] = $page + 1; $qs = http_build_query($q,'','&'); $nextlink = "<a href='?$qs' title='Next page'>>></a> "; $q['page'] = $pages; $qs = http_build_query($q,'','&'); $nextlink .= "<a href='?$qs' title='Last page'>Last</a></span>"; } echo "<div id='paging'><p><small>$prevlink Page $page of $pages $nextlink </small></p></div>"; echo '</div></div>'; }
  17. you need to build the query string part of the url (the part after the ?), using http_build_query() and any existing $_GET parameters - $q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links // when you build each link - first, last, previous, next do this - $q['page'] = // set the page value to the value you want for the link you are building from whereever the code is getting the value now $qs = http_build_query($q,'','&'); // build the query string part of the url echo "<a href='?$qs'>the label you want for the link you are building from whereever the code is getting the lable now</a>"; // output the link
  18. the code developed in the thread, but for an update query - $cols = array(); $types = ''; // string holding the i,s,d,b types $params = array(); // holds references to data values in the same order as the field names and types foreach($fieldarray as &$field){ $cols[] = "SET {$field[2]} = ?"; $types .= $field[26]; $params[] = &$field[25]; } // add the types/params for the id $types .= 'i'; $params[] = $id; $query = "UPDATE $ap ".implode(',',$cols)." WHERE ID = ?"; $stmt = $db->prepare($query); $refArr = array_merge(array($types),$params); // array of the bind_param parameters $ref = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically $method = $ref->getMethod("bind_param"); $method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method $stmt->execute();
  19. your code to build and run an UPDATE query would use the same method as for the insert query. you need to build the complete sql query statement in $query, a string of the input types in $types, and an array of input parameters in $params. after you have finished looping to build the set col = ? terms in an array, implode them and put them into the sql query statement. then add the final parts to $query, $types, $params for the WHERE id = ? part of the query. you would reuse the common code from the ->prepare() statement through the ->execute() statement. in fact, in general, the code from the ->prepare() statement through the ->execute() statement would be part of a class method that would accept the $query, $types, and $params as inputs that you just call any time you need to run a prepared query.
  20. if you had php's error reporting set to E_ALL and display_errors set to ON, you would be getting a warning that would alert you to what's going on. amazingly, i have used (past tense, i don't think anyone uses mysqli with prepared queries) this code, error free, with a value coming from the array being looped over. perhaps a php version change. to get the current code to work, you need one more & in the foreach() - foreach($fieldarray as &$field){ are you sure you don't want to switch to PDO? this is a lot of extra code just to call one php built in method.
  21. every single character in the example code i posted was there for some reason. in particular there's one & that's required to make it work. also see the comment on the $params array - // holds references to data values in the same order as the field names and types
  22. the following is what you would have to do if using mysqli (thanks php, just because you can make a function with a variable number of arguments for same meaning values, doesn't mean you should) - $fieldarray[] = array('name'=>'firstname','type'=>'s','value'=>'john'); $fieldarray[] = array('name'=>'lastname','type'=>'s','value'=>'doe'); $fieldarray[] = array('name'=>'email','type'=>'s','value'=>'someone@somewhere.com'); $cols = array(); $types = ''; // string holding the i,s,d,b types $params = array(); // holds references to data values in the same order as the field names and types foreach($fieldarray as $field){ $cols[] = $field['name']; $types .= $field['type']; $params[] = &$field['value']; } $holders = array_fill(0,count($cols),'?'); $query = "INSERT INTO $table_name (".implode(',',$cols).") VALUES (".implode(',',$holders).")"; $stmt = $conn->prepare($query); $refArr = array_merge(array($types),$params); // array of the bind_param parameters $ref = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically $method = $ref->getMethod("bind_param"); $method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method $stmt->execute();
  23. my post states what you would need to do to do this using the mysqli database library. the code i posted uses the PDO database library, which was mentioned both in the text and in a comment in the code. you cannot mix calls to different database libraries.
  24. to dynamically build and run the mysqli bind_param() statement requires that you use either call_user_func_array() or use reflection to call the method. there are examples of doing this in the php.net user contributed notes for the bind_param() statement. however, this is a problem with using mysqli. it is much easier if you use PDO, since each bindParam()/bindValue() statement is called separately. you can just loop over the array of input values and call bindParam()/bindValue() inside the loop. note: if you are only using a query once, use bindValue() as it avoids the overhead of evaluating the bound variables when the ->execute() method is called. for your insert query specific code, see the following - $fieldarray[] = array('name'=>'firstname','type'=>PDO::PARAM_STR,'value'=>'john'); $fieldarray[] = array('name'=>'lastname','type'=>PDO::PARAM_STR,'value'=>'doe'); $fieldarray[] = array('name'=>'email','type'=>PDO::PARAM_STR,'value'=>'someone@somewhere.com'); $cols = array(); foreach($fieldarray as $field){ $cols[] = $field['name']; } $holders = array_fill(0,count($cols),'?'); $query = "INSERT INTO $table_name (".implode(',',$cols).") VALUES (".implode(',',$holders).")"; $stmt = $conn->prepare($query); // using PDO, the bind loop would look like - $parm = 1; foreach($fieldarra as $field){ $stmt->bindValue($parm++, $field['value'],$field['type']); } $stmt->execute(); if you are at the database-layer in your code, your application would have already validated any external data so that it makes sense to the application, required fields are not empty, any specific constraints/formats have been checked. at the database-layer, you are protecting against things like sql special characters from breaking the sql syntax or in the case where the format permits content that could contain sql statements (a blog/forum post for example), to protect against sql injection.
  25. here's an edit to the above (the forum software cut my edit time limit off mid edit) - edit: here's trick that should work. you can use sqlsrv_num_fields($stmt) to find if the result set contains any field/column definition, from a SELECT/SHOW query, regardless of there being any rows in the result set. you could loop while sqlsrv_next_result($stmt) is true and if sqlsrv_num_fields($stmt) is true, there's a SELECT/SHOW result set that you can fetch row(s) from.
  • 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.