PHP loop adding Array[0] instead of data to MySQL db

Question

I have a form which coaches can enter in multiple pitchers for games. The form allows users to add rows as needed and looks like this:

<tr><td><select name="pitcherteam[]" size="1">
<option>PLEASE SELECT TEAM
<option>Team 1
<option>Team 2
<option>Team 3
</select></td><td>
<input type=text size=50 name="pitcher[]"></td><td>
<select name="pitcherage[]" size="1">
<option>AGE
<option>8
<option>9
</select></td>
<td><input type="text" size=3 name="pitcherpitches[]"></td></tr>

The php code I am using loops through the pitchers and if they are not blank does an insert into the mysql db:

$size_array = count($_POST['pitcher']); 
for ($i=0; $i<$size_array; $i++){ 
    if($_POST['pitcher'][$i] != ''){
    $sql2="INSERT INTO pitchcounts (pitcherteam, pitcher, pitcherage, pitches)
VALUES
    ('$_POST[date]','$_POST[pitcherteam][$i]','$_POST[pitcher][$i]','
$_POST[pitcherage][$i]','$_POST[pitches][$i]')";

    if (!mysqli_query($con,$sql2))
      {
  die('Error: ' . mysqli_error($con));
  }
} 
}

The loop is actually updating the db - BUT - instead of the values from the form - it's putting in zeros and array markers like this:

pitcherteam pitcher pitcherage  pitches
Array[0]    Array[0]    0   0
Array[1]    Array[1]    0   0
Array[3]    Array[3]    0   0

Any ideas on what's going on? Thanks!

check the query before executing it.

sudhakar
– sudhakar

2013-07-30 13:17:47 +00:00
Commented Jul 30, 2013 at 13:17 — sudhakar
– sudhakar, Commented Jul 30, 2013 at 13:17

MDEV · Accepted Answer · 2013-07-30 13:34:20Z

3

Concatenate your strings and variables!

$date = trim(mysqli_real_escape_string($_POST['date'])); //example variable clean
$sql2=" INSERT INTO
            `pitchcounts`
            (`pitcherteam`, `pitcher`, `pitcherage`, `pitches`)
        VALUES
            ('".$date."','".$_POST['pitcherteam'][$i]."','".$_POST['pitcher'][$i]."'
            ,'".$_POST['pitcherage'][$i]."','".$_POST['pitches'][$i]."')";

You should also clean your inputs - you should never put $_POST data straight into a database.

When dealing with cleaning arrays, there is no need to loop through each value applying the cleaning method individually, for those cases you can make use of array_map

function clean($val)
{
     return trim(strip_tags(mysqli_real_escape_string($val)));
}
$pitchers = array_map('clean',$_POST['pitcher']);

However, as mentioned by HamZa below, the best way of dealing with situations like this is to use prepared statements. Though depending on your experience, this may require some extra time and effort in order to convert your code over to that style.

Table and field names should also be surrounded with backticks ` to avoid conflict with reserved keywords

In addition to cleaning variables, you will also do well to validate the inputs as well. Other specialised cleaning methods are available as well for example, for a numeric field call intval or floatval, as other string fixing methods would be unnecessary

edited Jul 30, 2013 at 13:34

answered Jul 30, 2013 at 13:12

MDEV

10.9k2 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

MightyPork Over a year ago

It's entertaining to watch a completely buggy code being updated and fixed in real time :P

HamZa Over a year ago

lolwut ? Why don't you show him the "right" way by using prepared statements ?

MDEV Over a year ago

@MightyPork Yea, noticed something every time I saved an edit lol

MDEV Over a year ago

@HamZa If he can't handle string concatenation, I'll let him off using this method for now

HamZa Over a year ago

@VladimirHraban I'm not being sarcastic, prepared statements won't protect you from everything. Please read my early answer on Security SE for some insight.

|

Collectives™ on Stack Overflow

PHP loop adding Array[0] instead of data to MySQL db

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related