prepare array for mysql insert

Question

I have a form that has mutiple checkboxes with the name itm This obviously stores all checked values into the array $_POST['itm'][]

My question is I want the values of the array stored in the relevant column in the format 1:88:99:77: The : is the separator and the digits are the checkbox values

However when I submit my form it just displays Array() I then tried the serialize() function.. not what I want at all

So can anyone help please

Code Below``

        if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    $forname = $_POST['forename'];
    $surname = $_POST['surname'];
    $newfname = str_replace("'","",$_POST['forename']);
    $newsname = str_replace("'","",$_POST['surname']);
    $year = date('y');
    $month = date('m');
    $day = date('d');
    $username = $newfname.$newsname.$month.$year;


    // Perform queries 
    $addq = mysqli_query($con,"INSERT INTO `workshop-jobs` (wsjid,  wscid,  wsccid, summary,  jobtype,  caterhamwarrantyid,  worktobedone,  quoteinvref,  datearrived,  workstartdate,  workstarttime,  workfinishdate,  hours,  reqbydate,  technician,  status,  completed,  invoiced,  notes) 
    VALUES ('','$_POST[wscid]','$_POST[wsccid]','$_POST[summary]','$_POST[jobtype]','$_POST[caterhamwarrantyid]','$_POST['itm']','$quoteinvref','$_POST[datearrived]','$_POST[workstartdate]','$_POST[workstarttime]','$_POST[workfinishdate]','0','$_POST[reqbydate]','$_POST[technician]','$_POST[status]','0','0','$_POST[notes]')");
    if($addq){
    $last_id = $con->insert_id;
    $lastid = mysqli_query($con,"SELECT * FROM `users` WHERE userid = '$last_id'");
    $lastidr = mysqli_fetch_assoc($lastid);
    header("Location: addjobbat.php?wscid=$last_id");   
    }
    echo mysqli_error($con);
    mysqli_close($con);

Form Code

<div class="panel-body">
                    <input name="itm[]" class="serviceitem" type="checkbox" value="1:">
  &nbsp;&nbsp;1000 / 3 Month Service ( Sigma / Duratec / CSR )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="2:">
  &nbsp;&nbsp;1000 / 3 Month 620 Service<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="3:">
  &nbsp;&nbsp;Annual Service ONE ( Sigma / Duratec / CSR )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="4:">
  &nbsp;&nbsp;Annual Service ONE ( Duratec R500 )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="5:">
  &nbsp;&nbsp;620 Annual Service ONE<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="6:">
  &nbsp;&nbsp;Annual Service TWO ( Sigma / Duratec / CSR )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="7:">
  &nbsp;&nbsp;620 Annual Service TWO<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="8:">
  &nbsp;&nbsp;4 year add on (Sigma / Duratec / CSR)<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="9:">
  &nbsp;&nbsp;6 year add on (Sigma / Duratec / CSR)<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="10:">
  &nbsp;&nbsp;500 Mile Service ( K-Series )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="11:">
  &nbsp;&nbsp;3000 Mile Service ( K-Series )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="12:">
  &nbsp;&nbsp;6000 Mile Service ( K-Series ) <br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="13:">
  &nbsp;&nbsp;12000 Mile Service ( K-Series )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="14:">
  &nbsp;&nbsp;24000 Mile Service ( K-Series )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="15:">
  &nbsp;&nbsp;Race Car Service ( All Models )<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="16:">
  &nbsp;&nbsp;Suzuki 160 1st Service<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="17:">
  &nbsp;&nbsp;Suzuki 2 Year Addon<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="18:">
  &nbsp;&nbsp;Suzuki 3000 Mile Service<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="19:">
  &nbsp;&nbsp;Suzuki 4 Year Addon<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="20:">
  &nbsp;&nbsp;Suzuki 6000 Mile Service<br>
                    <input name="itm[]" class="serviceitem" type="checkbox" value="21:">
  &nbsp;&nbsp;Suzuki 6 Year Addon<br>
                  </div>

Firstly, you will not see the possible errors in your placement of `echo mysqli_error($con);` should there be any, since the header is redirecting before you can see that. You should also edit your post to include the HTML (form) for this, since we don't know if the checkboxes are treated as an array or not. Your code is also prone to an sql injection; use a prepared statement. — Funk Forty Niner, May 16 '17 at 14:07
regarding sql injection.. its on a local machine with only me and 2 others having access to this system so not too fussy — Chris Yates, May 16 '17 at 14:08
ok; you answered something that doesn't relate to the possible problem here. In any case; you've been given an answer, see that. — Funk Forty Niner, May 16 '17 at 14:10
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, May 16 '17 at 14:18
Storing data like that i.e. `val:val:val` in one column puts you on the road to hell. Think about how you are going to query that data in that format. That is where hell is — RiggsFolly, May 16 '17 at 14:21
I don't think you realize what you're getting yourself into here, it's a bad idea. Why would you want to do this? Querying later on will be a pain in the neck, *believe me* when I say this. Edit: GMTA @RiggsFolly — Funk Forty Niner, May 16 '17 at 14:21
@RiggsFolly I dont need to query that column .. the query is done on the customer & vehicle .. All i need to do is explode at : and then can determine what the values are assigned to in another table — Chris Yates, May 16 '17 at 14:26
Right: So there is no possible way of using a JOIN to write sensible queries using this method — RiggsFolly, May 16 '17 at 14:27

score -1 · Accepted Answer · edited May 16 '17 at 14:23

-1

Why don't you create a new variable to be inserted?

Implode the array of $_POST['itm'] into a new string, separated by :

$insertThis = implode(":",$_POST['itm']);

Implode splits an array into a string, separating each element by the first parameter, the second parameter is your array

edited May 16 '17 at 14:23

RiggsFolly

93,638
21
103
149

answered May 16 '17 at 14:08

clearshot66

2,292
1
8
17

where exactly in my code would that go ? and this is exactly what i wanted to do – Chris Yates May 16 '17 at 14:12
Before your insert statement, under your variable declarations – clearshot66 May 16 '17 at 14:15
saying error in this line : for($x=0;$x – Chris Yates May 16 '17 at 14:19
Try the new I just put in, if $_POST['itm'] exists, that code will function. – clearshot66 May 16 '17 at 14:19
Excellent ! Knew implode would help but had a mental block lol Many thanks – Chris Yates May 16 '17 at 14:23
Yes because your values all are 1: 2: 3: Remove the : in the value for your HTML so they are just 1,2,3... etc . – clearshot66 May 16 '17 at 14:46

prepare array for mysql insert

1 Answers1