1

I am trying to insert data into a MySQL table and one of the columns is inserting 'Array' instead of the array values. What am I missing, please? Thanks. 

 id         studentname     title   academicdiscipline  priority 
 012345678  TEST, NAME      Array   Civil                  3
 012345678  TEST, NAME      Array   Civil                  1
 012345678  TEST, NAME      Array   Civil                  4
 012345678  TEST, NAME      Array   Civil                  5
 012345678  TEST, NAME      Array   Civil                  2

Here is how I insert the data into the database - which works just fine apart from the 'title' column: 

if(isset($_POST['submit'])) { 

$id = $_POST["id"]; 
$studentname = $_POST["studentname"]; 
$title = $_POST["title"]; 
$academicdiscipline = $_POST["academicdiscipline"];
$priority = $_POST["priority"];  

$array = array(); 
foreach ($priority as $priority)  {
    if ($priority >=1) {
        $array[] = "('$id', '$studentname', '$title', '$academicdiscipline', '$priority')"; } 

        $query = "INSERT INTO flux_project_selection (id, studentname, title, academicdiscipline, priority) VALUES" .implode(',', $array); }  
        $retval = mysql_query($query) or die(mysql_error()); }

EDIT: Here is the HTML for the form: 

        <table width="890" cellspacing="0"> 
        <thead><tr>
            <th></th>

            <th width="250"> Title </th>
            <th width="550"> Description </th>
            <th width"90"> Field </th>
            <th width="50"> Select </th>
           </tr></thead>   
    <?php
            $color1 = "#E9E9E9";    
            $color2 = "#FFFFFF"; 
            $row_count = 0; 

            echo "<tr>
            <td valign = \"top\" bgcolor=\"$row_color\">
                <input type=\"hidden\" name=\"id[]\" value=\"$id\">
                <input type=\"hidden\" name=\"studentname[]\" value=\"$studentname\"></td> 

            <td valign = \"top\" bgcolor=\"$row_color\">
                <input type=\"hidden\" name=\"title[]\" value=\"$title\"> $title </td>  

            <td valign = \"top\" bgcolor=\"$row_color\">
                <input type=\"hidden\" name=\"description[]\" value=\"$description\"> $description </td> 

            <td valign = \"top\" bgcolor=\"$row_color\"> 
                <input type=\"hidden\" name=\"academicdiscipline[]\" value=\"$academicdiscipline\"> $academicdiscipline </td>

            <td valign = \"top\" bgcolor=\"$row_color\"> 
            <select name=\"priority[]\" class=\"priority\">
                          <option>  </option>
                          <option value=\"1\"> 1 </option>
                          <option value=\"2\"> 2 </option>
                          <option value=\"3\"> 3 </option> 
                          <option value=\"4\"> 4 </option>
                          <option value=\"5\"> 5 </option>
                          <option value=\"6\"> 6 </option>
                          <option value=\"7\"> 7 </option>
                          <option value=\"8\"> 8 </option>
                          <option value=\"9\"> 9 </option>
                          <option value=\"10\"> 10 </option>
                        </select>   

         </td></tr>
         <tr bgcolor=\"#ffffff\"> </tr>
         <tr bgcolor=\"#902C2E\"> </tr>"; 

      $row_count++;  

 echo"<tr>
  <td>&nbsp;</td>
  <td>&nbsp;</td>
</tr>
<tr>
  <td>&nbsp;</td>
  <td ><input name=\"reset\" type=\"reset\" value=\"Clear selection\"/> 
       <input name=\"submit\" type=\"submit\" value=\"Submit selection\"/>  
</tr></table>";  
?>
NoooSmyth
  • 91
  • 7

3 Answers3

3

A ton of this simply makes no sense. Please format your code properly, this will help with debuging. In addition, please use a library like PDO. It will make programing these tpyes of things easier, faster, and safer.

I am going to try my best to salvage some of this code. NOTE THE CODE IS NOT SAFE AND DOES NOT PROTECT AGAINST MYSQL INJECTION.

Here is the implementation, and then later I will explain my reasoning...

if (isset($_POST['submit'])) { 
    $id = $_POST["id"]; 
    $studentname = $_POST["studentname"]; 
    $title = $_POST["title"]; 
    $academicdiscipline = $_POST["academicdiscipline"];
    $priority = $_POST["priority"];  

    foreach ($priority as $single)  {
        if ($single >=1) {
            $values = "('$id', '$studentname', '$title', '$academicdiscipline', '$single')";

            $query = "INSERT INTO flux_project_selection (id, studentname, title, academicdiscipline, priority) VALUES " . $values;
            $retval = mysql_query($query) or die(mysql_error());
        }
    }
}

Corrections...

  1. Fixed foreach loop variable names, don't have your values and array name be the same.
  2. Got rid of the array variable, it was not doing anything
  3. Nested everything inside the if, so now foreach selected priority, the script will insert a row into the DB with that priority.

I am not sure if this is what you want the program to do, please let me know.

Hurricane Development
  • 2,449
  • 1
  • 19
  • 40
  • Thanks @Hurricane Development. (3) is exactly what I need - but it does exactly what my orginal code does - inserts all correct values but 'Array' in title column. – NoooSmyth Jul 30 '15 at 04:09
  • @NoooSmyth I am not sure why that is happening, `$single` should not be an array, maybe make sure that php is registering it as an int, so try `is_int()` – Hurricane Development Jul 30 '15 at 17:07
  • `$single` registers as an int @Hurricane Development. An interesting thing happens with this code - the last entry is always inserted twice into the database. – NoooSmyth Aug 02 '15 at 03:47
  • @NoooSmyth Well for the double insertion I have no idea, but I think I know how to fix the array issue. remove the `[]` from all inputs except for priority (I think you did this already actually). For debugging purposes, do `print_r($priority);` after the for loop and let me know what it outputs. – Hurricane Development Aug 02 '15 at 15:33
  • Thanks @Hurricane Development. Taking `[ ]` from all inputs except priority inserts all values right - except title again. It inserts 'Title 8' in all rows, which is the last row in the table. I am assuming id, studentaname, and discipline insert the last row too - but that doesn't matter because it's supposed to be the values anyway. `print_r($priority` gives `Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 [4] => 5 [5] => [6] => [7] => )` as expected. There is no problem inserting priority values at all. – NoooSmyth Aug 02 '15 at 16:00
  • @NoooSmyth Is the only problem that it is inserting the last row twice? if so, change the if statement to `if (is_int($single) && $single >=1) {`. If there is another issue you have to explain it more, i just am not getting it. – Hurricane Development Aug 03 '15 at 07:12
  • No @Hurricane Development. The original problem of inserting `Array` in the title column instead of the values, `Tile 1`, `Ttitle`, etc. If I remove the `[ ]` from the title input it inserts `Title 8` in all columns, i.e. inserting the last row in the table whether or not it is selected. – NoooSmyth Aug 03 '15 at 10:29
1

EDIT: Try this first.

I'm not sure why most of your values are working but I'd remove the [] from all of the input names except priority.

These are some helpful ideas too

It seems like your if ($priority >=1) { should wrap the query too or else this could generate an error if the first empty option is selected since $array won't have anything in it.

I'm not really sure of the purpose of $array[] is and if you tried to wrap "('$id', '$studentname', '$title', '$academicdiscipline', '$priority')" in mysql_real_escape_string this would definitely not give you the result you wanted.

First, I wasn't even sure foreach ($priority as $priority) worked. I ran a test and it does, but this will be confusing for you or anyone else looking at the code in the future. I encourage you to name your variables differently like foreach ($priorities as $priority)

Normally I'd try to answer your question but I feel like you should take a look at this method instead.

EDIT: Use this if priority is the only input with multiple values

priorities = $_POST['priority'];
foreach($priorities as $priority){
    if($priority > 0)
    {
       $query = "
          INSERT INTO flux_project_selection 
          (
              id, studentname, title, academicdiscipline, priority
          ) 
          VALUES
          (
               " . mysql_real_escape_string($id) . ",
               '" . mysql_real_escape_string($studentname) . "',
               '" . mysql_real_escape_string($title) . "',
               '" . mysql_real_escape_string($academicdiscipline) . "',
               " . mysql_real_escape_string($priority) . "
          )";
       $retval = mysql_query($query) or die(mysql_error());
    }
}

EDIT: Edited The following is an example of how you'd loop through the inputs if there were multiple of every input as indicated by the [] included after the input name. <input type=\"hidden\" name=\"id[]\" value=\"$id\">

for($i = 0; $i < count($_POST['id']); $i++) {
    if($priority > 0)
    {
       $query = "
          INSERT INTO flux_project_selection 
          (
              id, studentname, title, academicdiscipline, priority
          ) 
          VALUES
          (
               " . mysql_real_escape_string($_POST['id'][i]) . ",
               '" . mysql_real_escape_string($_POST['studentname'][i]) . "',
               '" . mysql_real_escape_string($_POST['title'][i]) . "',
               '" . mysql_real_escape_string($_POST['academicdiscipline'][i]) . "',
               " . mysql_real_escape_string($_POST['priority'][i]) . "
          )";
       $retval = mysql_query($query) or die(mysql_error());
    }
}

Also you should look into using mysqli instead of mysql. It's more secure.

MySQL vs MySQLi when using PHP

This method will minimize the changes to your code. A better technique is to use prepared statements to prevent injections but this requires mysqli or PDO

mysqli: http://php.net/manual/en/mysqli.prepare.php

PDO: http://php.net/manual/en/pdo.prepare.php

Community
  • 1
  • 1
Ethan22
  • 747
  • 7
  • 25
  • The implementation of the `$_POST` variables makes no sense, id is not an array of id's – Hurricane Development Jul 30 '15 at 03:44
  • It's not? `` that's his input – Ethan22 Jul 30 '15 at 03:47
  • Ah yes I did not elaborate my bad, It is the counter that makes no sense (unless I am really screwing up my HTML). Like `$_POST['id'][i]` is a bit odd considering the for loop. The id is the same for all records, so this loop will only run once (But it needs to run as many times as there are priorities). So `$i` is only going to be `0`, and it does not satisfy what he needs. Just rethink you lop conditions maybe. No worries though, his code makes no sense – Hurricane Development Jul 30 '15 at 03:50
  • His input should just be ``. When he includes the [] after the name, it SHOULD indidate that there are going to be multiple post values of that input name. Therefore I was coding to account for multiple of EVERY input. – Ethan22 Jul 30 '15 at 03:52
  • @Ethan22 thank you. The first version worked like my original code - but instead of inserting 'Array' in title it leaves it empty. The edit doesn't work though. Ditto mysqli and mysql_real_escape_string. Trying to get insert working and then cleaning up. – NoooSmyth Jul 30 '15 at 03:57
  • @Ethan22 Yes but take a look at his sample case, the ID is always the same, and you loop does not account for what he needs the script to do. – Hurricane Development Jul 30 '15 at 03:58
  • 1
    I edited it to include both versions depending on what he's trying to do. There's much more than this that needs fixing anyways. You're right though. The [$i] method would not working if priority was the only one with multiple values. – Ethan22 Jul 30 '15 at 04:11
  • @Ethan22 - removing [] from all input names except priorities worked! Thanks. Now on to fixing everything else :-) – NoooSmyth Jul 30 '15 at 04:32
  • Don't be afraid to var_dump($_POST); at the top of your script to see what your post array looks like. – Ethan22 Jul 30 '15 at 04:38
  • Thanks @Ethan22 - the second code example looping through every input only inserts the first row into the database. The first code that assumes only priority has multiple values inserts all rows but with the same problem of not inserting values in the title column. But since I need to loop through all rows, the second code would have been idea if it worked. – NoooSmyth Aug 02 '15 at 03:55
0

Finally got this: 

if(isset($_POST['submit']) && !empty($_POST['submit'])) {
$ids = $_POST['id']; 
$names = $_POST['studentname']; 
$titles = $_POST['title'];  
$disciplines = $_POST['academicdiscipline']; 
$priorities = $_POST['priority']; 

                foreach($priorities as $key => $priority) { 
                    if ($priority > 0) { 

                    $query = "INSERT INTO flux_project_selection (id, studentname, title, academicdiscipline, priority) 
                                VALUES ( " . mysql_real_escape_string($ids[$key]) . ",
                                        '" . mysql_real_escape_string($names[$key]) . "',
                                        '" . mysql_real_escape_string($titles[$key]) . "',
                                        '" . mysql_real_escape_string($disciplines[$key]) . "',
                                        " . mysql_real_escape_string($priority) . "  )";

                    $retval = mysql_query($query) or die(mysql_error()); 
                        } 
                    } 

                    echo "Worked. <br />"; 
        }

Thanks everyone.

NoooSmyth
  • 91
  • 7