TOtally newbie here. I have a problem about insert syntax. I want my table to look like this with ID as primary key(AI, NN,) and I can input multiple items on document with the same case and name. I am currently using a php script that extracts those info from a result to insert them into the database but have problem regarding the syntax.
ID| Case| Name| Document| Date
----------------------------------------------
1 233 alex ITR 2015-1-1
2 233 alex ITR 2015-1-1
3 233 alex NULL 0000-0-0
4 234 ben COC 2015-3-3
5 234 ben VAT 2015-3-3
6 234 ben NULL 0000-0-0
my code is something like this:
$sql="INSERT INTO table1 (Case, Name, Document, Date) VALUES ('$casenum','$name','$doc1-5(*this is where I dont know what todo)','$date1-5(this also)
If you just have specific list of documents and dates, can't you just use an array?
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$docs =('ITR', 'COC', etc);
$date =('2015-1-1', etc);
// prepare and bind
$stmt = $conn->prepare('INSERT INTO table1 (Case, Name, Document, Date) VALUES (?, ?, ?, ?)');
$stmt->bind_param('ssss', $casenum, $name, $docs[0-4], $date[0-4]);
$stmt->execute();
$stmt->close();
$conn->commit(); //otherwise your data won't save
Also make sure to use prepare and bind_param all the time in order to prevent sql injection - crucially important or your whole db can get messed up
If you want to add multiple values in one sql statement, you can catenate them at the end:
INSERT INTO table1 (Case, Name, Document, Date) VALUES (233, 'alex', 'ITR', '2015-1-1') , (233, 'alex', 'NULL', 0000-0-0) , …
If you have a $values array like this one:
Array (
Array (
[Case] => 233,
[Name] => 'alex',
[Document] => 'ITR',
[Date] => '2015-1-1'
),
…
)
You can consider doing something like that:
$values = // def you array here
$texts = array_map( function($value) { return "({$value['Case']}, '{$value['Name']}', '{$value['Document']}', '{$value['Date']}')"; },
$values );
$sql = 'INSERT INTO table1 (Case, Name, Document, Date) VALUES ' . implode(',', $texts);
It sounds like you want to reuse the name multiple times while the document and date may change for that person. So you really only want to insert records for 2 people, but they have multiple documents to insert. You SQL isn't really normalized and there is probably a better way to do this but to avoid confusion here's how you can get it done right now.
$values = array($case, $name, $document, $date)
INSERT INTO table1 (case, name, document, date) VALUES (implode(", ", $values);
The array of values can be repeated as many times as you want, but that's what the first record would look like. You would have it in the same order it will be inserted in with your MySQL insert statement and then the next 4 items in the array would be the 2nd insert, the next 4 would be the 3rd, etc.
You will have to repeat the case and name with each of these though, so a very rough example of the array might look like this:
$values = array($case0, $name0, $document0, $date0, $case1, $name1, $document1, $date1)
I would make use of array_push() though and push each of those items in with each loop to build the array.
Hope this helps. When you start using mysqli and start getting into OOP you can do this differently in an easier way. For now though, this should get the job done.