Re: No Subject....

Thanks for your answer! The main problem is that in this case I have a 9(products) x 13(months) matrix, and the client may also request to add more items, increasing the number of products. The number of months never increases because every time a new month comes in I create a new table. This is needed also for statistics, the main objective of the tool. What I am doing right now is populating the table with zeros when a new product is added so that I can send a full update of the fields at the same time. The problem is that the number of fields on the main interface may increase by 13 every time a new product is added. I have created a variable that is supposed to store updates using a loop to concat them and send the query just once, but MySQL is returning an error message although the query seems to be alright. So I'm sending all the queries individually using a loop with a permanent connection to MySQL. Questions: - How can I send more than one query at the same time using MySQL? Do I have to use \n after each ";"? - Which method requires less resources usage? Sending one by one using a MySQL permanent connection or all the queries at once? - Is it a good idea to create a routine that verifies which fields didn't change after the update before sending the query to MySQL? - Is there any way to create a MySQL routine that tells something like "if $a doesn't exist then insert $a else update $a"? Thanks for your time and patience. Best regards, Fábio Ottolini ----- Original Message ----- From: "Doug Semig" <dougslist@semig.com> To: <php-db@lists.php.net> Sent: Wednesday, November 22, 2000 5:13 PM Subject: Re: [PHP-DB] No Subject.... > There are three basic ways I remember seeing this situation handled: > > 1. First try to SELECT the product's forecast you are to insert or update. > If you get it back, then you know you have to UPDATE, if not, you have to > INSERT. This is less efficient than #2 because you are always sending two > queries to the RDBMS. > > 2. Just go ahead and run an UPDATE for everything. But if you get an > error back, then send an INSERT because the UPDATE didn't actually work. > This is the more popular way I've seen, and it's more efficient than #1 > because you can decide which to send first (if you have a properly designed > schema, it doesn't matter if you try the UPDATE before the INSERT or the > INSERT before the UPDATE, but you should wisely choose the order to reduce > the number of times you send two queries). > > 3. Lots of modern front ends specifically distinguish between the > operations to be performed. That's why you see an "Add" or "New" > button as > well as an "Edit" button. If the user clicks the "Add" or "New" > button, > the operation to be performed is an INSERT. If they clicked the "Edit" > button, then it's an UPDATE. This is the most efficient and cleanest way > of deciding between UPDATE vs INSERT, but it may be more work for your users. > > Doug > > At 01:30 PM 11/22/00, Fábio Ottolini wrote: > >Hej All! > > > >Which is the best way to discover if a entry on a MySQL table must be > updated or created? > >Example: > >Suppose I have a table called products and on this table I am going to > save information about product's forecast using fields 'name', > 'mont_year' > and 'quantity'. In some situations it will be necessary to update fields > (client has decided to change quantities for a specific product and month > already defined on the forecast) and in others it will be necessary to add > a new entry (client added a new month or product on the forecast - the > period covered with this forecast tool advances in time according to the > month). If I use update for every variable of this form (the main interface > is obviously a form) then what didn't exist before will continue not to > exist and probabily I'll receive some error messages from MySQL regarding > the update of fields that don't match the specified criteria. If I use > insert I'll have double or even triple entries for certain products on the > same month, but by the same time I'll be able to include the new ones. > >Do you have some ideas about the best approach for this problem or it's > just matter of making comparisions between queries? > > > >Best regards, > > > >Fábio Ottolini