Multiple feilds in update.

[CB150Special]

I have 60 plus fields to update and was looking for a better way to write the prepared statement.

 

Doing something like this is prone to many errors. 

INSERT INTO tabel (MemID,MemName,RaceNo,Make,Status....x60...) VALUES (?,?,?,?,?,?.....x60....)

Thanks.

 

 

[mac_gyver]

first of all, is your data properly normalized, especially since you have column(s) with numerical endings? it would be unusual to have that many unique meaning/purpose columns in one database table.

 

if you do have one or more series of name-numbered columns, these should be stored in other table(s), one row per data item, in which case it would be straight-forward to dynamically process and insert/update/delete the row(s) of data.

[CB150Special]

This is messy but looks like it can work. Not working as yet though !!! Call to a member function bind_param() on boolean

            $values = '?'.str_repeat(',?', 59);            
            $para = str_repeat("s", 30);
            // php Var
            $raceclassname ='$raceclassname1';
            $bikeID = '$bikeID1';
            // db Var
            $RaceClassName ='$RaceClassName1';
            $BikeID = '$BikeID1';            
            for ($i = 2; $i <31; $i++){
                $raceclassname = $raceclassname.',$raceclassname'.$i;
                $bikeID = $bikeID.',$bikeID'.$i;
                $RaceClassName = $RaceClassName.',$RaceClassName'.$i;
                $BikeID = $BikeID.',$BikeID'.$i;                
            }
            $sql_fees = $conn->prepare('INSERT INTO fees '.$RaceClassName.','.$BikeID.') VALUES ('.$values.')') ;
    -->     $sql_fees->bind_param($para.','.$raceclassname.','.$bikeID);
                       
            
            for ($i = 1; $i <31; $i++){
                if (isset($_POST['RaceClassName'.$i])){
                    '$raceclassname'.$i =  $row_series['RaceClassName'.$i];    
                    '$bikeID'.$i =  $row_series['BikeID'.$i];       
                 }else{
                    '$raceclassname'.$i = ''; 
                    '$bikeID'.$i = '';  
                }
            }            
            $sql_fees->execute(); 

[mac_gyver]

so, your data is not normalized, making all your code and queries, to find and display, insert, update, or delete data, either written out by you or dynamically produced by code, overly complicated.

 

the solution, is to properly store your data, then the code and each query, no matter how it gets produced, will be simple.

 

the following is something that was already written as a reply in one of your threads - 

^^^ this indicates a bad database design, where you are trying to use a database table like it is a spread sheet. using a series of numbered columns requires more code to perform any operation on the data and wastes storage.

 

you should be storing each data item as a separate row in a table, not in numbered columns in a single row. this will simplify and speed up all your code.

 

 

 

your current table design is making it harder for you to accomplish any task associated with the data. what you are doing is not that complicated, but you are making a wall of code that won't work. you even have the ->execute() method call outside of and after the end of the loop where it would need to be.

Edited September 3, 2017 by mac_gyver

[Jacques1]

This is messy but looks like it can work.

 

No, it doesn't look like that.

 

You need to understand the fundamental difference between code and data. You cannot put strings (i. e. data) with PHP fragments in the middle of your script and assume they magically turn into code. This is like writing the word “cat” on a piece of paper and assuming you now have a living cat.

 

A string is dead text. It doesn't “do” anything. If you want to pass a variable number of arguments to a function, then you need to build an array and use the splat operator. You cannot write down a string of comma-separated values and pass it to fhe function, because that gives you a single argument (the string) with gibberish in it. But as you've already been told multiple times, the 60-column design is suspect to begin with.

[CB150Special]

But as you've already been told multiple times, the 60-column design is suspect to begin with.

I need all 60 columns. There can be 60 different entries per record.

 

I suppose the easiest way is to do something like this and the copy and paste the echo into the bind_param line.

            for ($i = 2; $i <31; $i++){
                $raceclassname = $raceclassname.',$raceclassname'.$i;
                $bikeID = $bikeID.',$bikeID'.$i;
            }
            echo $raceclassname.','.$bikeID;

[Jacques1]

If you repeat the mistake 10 more times, maybe a fairy will come along and change the rules of reality.

[CB150Special]

You need to understand the fundamental difference between code and data. You cannot put strings (i. e. data) with PHP fragments in the middle of your script and assume they magically turn into code.

 

As PHP is an interpretive language, I would have assumed it would. I presume there is no code that can do this? Dbase had a great feature & (eg &string) which meant that the string was actually code.   Clipper even compiled the & parameter correctly into making a exe. Guess it depends how clever the code writers are.  

[Jacques1]

Do you not see the difference between actively telling the interpreter to perform macro substitution (like you did in dBASE) and expecting the interpreter to somehow magically figure out that a string is code (like you try with PHP)? Of course you can tell PHP to evaluate a string. But a) this requires you to actively call eval() and b) is so horrendously bad that you should forget that it exists.

 

If you can make dBASE perform substitution without using the ampersand operator, just by writing down a string and having the interpreter read your mind, you qualify for the One Million Dollar Paranormal Challenge.