0

I'm interested to know, what would be the best way to go about inserting 150+ values (values being the data collected by the form, e.g name, age, etc.) into a database with prep'd statements.

The perimeters are thus:

1 form which inputs all values to the table on submit, not all parts of the form might be filled by the user so there needs to be the ability to submit the form regardless of if there has been input (sans required data).

Using the following basic INSERT query with a prep'd statement as an example

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");$stmt->bind_param("sss", $firstname, $lastname, $email);

would you have to manually input all 150+ data type wassnames, the "sss", part of the bind, individually or is there another, quicker way to do this?

Twisted
  • 59
  • 7
  • 1
    You can map your binding parameters and columns and dynamically assign them over a loop iteration using arrays. Check this https://stackoverflow.com/questions/51010509/how-to-dynamically-bind-parameters-using-prepared-statement – SRK45 Jul 15 '21 at 15:15
  • 2
    150 parameters should not be an issue. For example, Oracle can accept a thousand easily. Also, ORMs tend to have pseudo code to help with this, like `` in MyBatis. – The Impaler Jul 15 '21 at 15:15
  • Your question is not clear. Are you saying your insert has 150 columns of data or that you want to execute the statement 150 times? Either way, what's the problem? – Dharman Jul 15 '21 at 15:24
  • @SRK45 & KhorneHoly. The answer you linked is based on OOP rather than procedural, would it still work? – Twisted Jul 15 '21 at 15:29
  • @Dharman There is over 150 values to enter into the database in one execution, there's no problem as such it's more I thought it was an interesting question. Thought it was a question (and answer) that someone might find useful in the future – Twisted Jul 15 '21 at 15:31
  • @Dharman You're right, sorry not PDO, OOP, my bad. Edited to PDO to OOP in the above comment, thanks. – Twisted Jul 15 '21 at 15:32
  • Please [edit] the question to explain clearly what do you mean by values. Do you mean 150 columns or rows? How is it any different from the single row with 3 columns you gave as an example? – Dharman Jul 15 '21 at 15:33
  • Your own example is using OO style. Why would you even want to use procedural style. Why would you want to use mysqli for that matter? You can mix mysqli OO and procedural style, but it's recommended to stick to OO. – Dharman Jul 15 '21 at 15:34
  • @Dharman My example is the basic example used on most sites like w3schools and taught 1st year in college, I'm still fairly novice myself so I don't actually have a solid grasp on what OOP, PDO etc, entail, only what little was gone over in a 1 hour lecture and I'm just going with what I've been taught. Can you tell me why I wouldn't want to use mysqli and what would be the better alternative, enlighten me please :) – Twisted Jul 15 '21 at 15:47
  • @Dharman Thank you, I'll look into that but like I said I've used what I've been taught in the example and I think it's therefore expected of me that I use what's being taught rather than an alternate method. I don't have this issue(my question) currently but I can see it possibly raising it's head later down the line. I've bookmarked those pages and once I'm done doing things the way I'm being taught I'll try to redo them with PDO, thanks again. – Twisted Jul 15 '21 at 15:55

0 Answers0