Danny

Hi HartleySan Sorry, I should have explained the scenario better. So, to simplify, say I have a text file that has 2 columns, brand_number & brand_name. I read the file into an array and then process it line by line. What I want to do with the data in each line is insert it into a db which also has 2 fields, one for the number and one for the name. Now, as data is added to this file periodically, I will run this process on say, a weekly basis and expect the script to do the following:- 1. If the number does not exist, insert the row into the database. 2. If the number does exist but the name has changed (which it occassionally does), update the name. 3. if the number exists and the name is the same, do nothing. To achieve this, I created the script above. I made the brand number a primary key in the db so that basically the script tries to insert the row but if it is unable to (due to primary key duplication) then it does an update. I'm not sure if this is a better method than first trying to do a select statement and then depending on the result of that query, doing an insert or an update. Hope this helps with the understanding. Danny

Hmmmmm I'm now actually wondering, if the row exists in the db and the statement defaults to UPDATE, will the query update every row, regardless of whether the $brand_name is different or not, or does it automatically do the check for different values first and pass if no changes? If it doesn't, I would probably need to do a comparison of the $brand_name variable => db field value before I do the update?

Hi All Just after a quick sense-check really to make sure I'm utilising mySQL correctly in terms of load. Here is my script which is working through a file array row by row and then inserts the values from the row into the db, unless the row exists in which case it will update the db entry. (first db field is Primary Key => $myRow[1]) $file = file(_PATH_ . 'uploads/' . $filename); // starting at the 3rd row, work through the file array row by row for($i = 3; $i < count($file)-1; $i++) { // explode each column in the row into an array $myRow = explode('|', $file[$i]); // sanitize the description $brand_name = $dbc->real_escape_string($myRow[2]); // insert into the db or if unsuccessful, update existing if(!$dbc->query("INSERT INTO tbl_ecc_brand VALUES ('$myRow[1]','$brand_name')")) { $dbc->query("UPDATE tbl_ecc_brand SET brand_name ='$brand_name' WHERE brand_id = '$myRow[1]'"); } } What are your thoughts, is this how you handle the "insert or update" scenario or is there a better method? Danny

Thanks for the response, HartleySan. I'm plodding along the same path and am navigating table joins. I have to say, I think I'm actually grasping it! I'm one of those guys that can read what Larry has written all day long, but the penny won't drop until I'm tapping away at the keypad myself

Hi all Well, after buying Larry's book almost 7 months ago! and a particular project in mind, I am now in a position to be able to get moving full steam ahead. And so, enjoying the book immensely and throwing myself in at the deep end with my project which is based around what you would no doubt term dynamic forms. To try and explain as simply as possible, imagine a business with 4 offices, each handling data for a particular object, let's say as a fictional example - building a car. To build the car at each office, a form has to be submitted with the relevant data to be able to build the car, things like wheel size, colour, number of doors etc. Now, in an ideal world of 'one business, one process', all four offices will use the same form system, but, they will use them slightly differently. Office 2 for example may only be able to make black cars so would not need the 'colour' option enabled on the form. I'm sure you can imagine various scenarios like this. So, my thought process has gone like this..... Create a database driven form where each form field and its attributes are held in a table within mySQL. There are then relational tables that will define things like whether a field is enabled based on the office that is filling in the form. Now, my initial question is, am I thinking along the right lines! i would hate to invest a week of my time designing the db structure and then have someone tell me it is the wrong thing to do The next two questions are related to the tables I have created or have yet to do so. Firstly, I have a table - 'frm_fields' and this is where the form field elements are stored, example columns are:- field_id field_title (the title that appears beside the field on the form) field_level (defines the level of the organisation at which the field is represented e.g. global or office level) field_view (defines which view the field relates to in the erp system used by the business) field_block (defines the block of fields it will appear in on the form) field_technicalName field_side (value is either 1 or 2 which defines whether it appears on the left or right of the 2 column block) field_type (defines whether it is a text, select, checkbox field etc.) field_length (defines the max character length of the field) field_width (defines the size of the field as it appears on the form) ​This is just a subset of the table columns and I think I'm up to about 14 at the moment, my question being, is there a 'best practise' limit to the number of columns per table? Secondly, once the form is shown to the user, they will need to complete and then submit the form for 2 separate approvals. I obviously need to build a table which will hold the data they have provided and have therefore created a table called 'frm_active' which currently consists of only 3 columns:- form_id (each form request will of course have a unique id) field_id (foreign/primary key of the field from the table above) field_value (the value they have entered for this field) ​And so, my question here is about the field_value datatype. It appears that I will need to have a generic datatype for this field of varchar(40) to accommodate all field values in the form, some of which may be a simple "1" or "yes"/"no" scenario. This seems a little overkill to me and I'm wondering, considering I may eventually end up with 2-300 fields in total, will it affect the performance of the db. Well, I'll halt the post there for now and welcome your feedback. I'm pretty sure there will be lots more to come as I 'find my feet' with all this coding malarkey! Many thanks in advance Danny

Hi guys Before I dive head first into the book, I've been doing a little research on PHP overall and how it has developed since I last had a 'dabble' several years ago. I can't help but notice that there is a lot of discussion on frameworks and this 'MVC' term, which I vaguely follow the concept of. I guess my question is, do I need to be considering using a framework (Zend) or concerning myself with the MVC model during my 'first steps' in PHP & mySQL learning, or is it something that I am more likely to consider as an optimisation step much further down the road when(if) writing large scale, complicated projects? The project I have in mind as a starting point for learning php & mySQL (discussed in a previous post) could be considered complicated I guess, but my intention is to start off with a basic idea and enhance it over the coming months as my programming knowledge increases. Interested in hearing from anyone with an opinion on the above. Thanks in advance Danny

Hi all. New to the forum and pretty new to PHP/mySQL; I did have a little dabble quite a few years ago but only ever really at beginner level. I've now decided I have the time for some self-tutoring and with the help of the book associated with this forum, I hope to be able to build a specific application I have in mind. In the small business I work for, we have a requirement to collect data from the various functions within business e.g. manufacturing/QA/technical/purchasing etc. to feed into our small (and very dated) ERP system. At the moment, this is done using some very old and very cumbersome excel spreadsheets that have to be opened, completed and then forwarded on to the next business stream via email.As you can imagine, this is a very manual process and can also be quite time consuming as it is so linear. My inclination for some while now has been to develop a web-based portal that will allow a data co-ordinator to initiate a form based project that will distribute forms where end users can complete and submit in a more concurrent fashion, also - if possible - introducing some authorisation and validation into the mix. A kind of workflow process I guess is the best way to describe it. I must say, reading that back to myself, it does seem quite 'grand' for someone who is pretty much just starting out, and I'm hoping that I've chosen the right technology with PHP/mySQL -but - having researched a little and having had Larry's books highly recommended to me, I'm also keen to use server side rather than client side technology (e.g. Javascript) as much as possible. If anyone thinks I'm barking up the wrong tree here, or the whole thing is just a flight of fancy that I'm 6 years away from being able to complete, then please do jump in with your comments; all advice is welcome. Anyway, that's a bit of a "This is me and this is my plan" post, so I imagine you will be hearing a lot more from me once I 'dive in' head first, so to speak. Kind regards and many, many thanks in advance for the assistance I am sure many of you will be providing over the coming months... ....Page 1 Danny