why does my PHP foreach loop is inserting wrong value on mysql?

Question

My foreach loop in PHP is inserting the wrong value. I have a table that collects data regarding a book using html form with jquery. In a table I was inserting array value, here is my code from the form.

form.php

include_once 'config/database.php';
include_once 'config/bookclass.php';

$database = new Database();
$db = $database->getConnection();

$book = new Book($db);

if($_POST){

      $book->book_title=$_POST['book_title'];
      $book->number_of_ page=$_POST['number_of_ page'];
      $book->genre=$_POST['genre'];
      $book->author=$_POST['author'];
      $book->type=$_POST['type'];
      $book->publication=$_POST['publication'];

      if($book->book()){
        echo "saved";
      }
      else{
      echo "not saved";
 }
 }

and here is my code from bookclass.php that I used to insert the data

public function book(){

foreach($this-> book_title AS $this->key => $this->value) {

                  $query = "INSERT INTO

                    table_a 

                    SET
                    book_title=:book_title,
                    number_of_ page = :number_of_page,
                    genre = :genre,
                    author = :author,
                    type = :type,
                    publication = :publication";

                    $stmt = $this->conn->prepare($query);



                    $this->book_title=$this->value;
                    $this->number_of_ page= $this->number_of_ page[$this->key];
                    $this->genre = $this->genre[$this->key];
                    $this-> author= $this-> author[$this->key];
                    $this-> type= $this-> type[$this->key];
                    $this-> publication = $this->publication[$this->key];

                    $stmt->bindParam(':book_title', $this->book_title);
                    $stmt->bindParam(':number_of_ page', $this->number_of_ page);
                    $stmt->bindParam(':genre ', $this->genre );
                    $stmt->bindParam(':author', $this->author);
                    $stmt->bindParam(': type', $this-> type);
                    $stmt->bindParam(':publication', $this->publication);

                   $stmt->execute();
                  }

here is data that I am inserting in the table.

 +-----------+---------------+-------+--------------+------------+-----------+
 | book_title|number_of_ page| genre | author       | type       |publication|
 +-----------+---------------+-------+--------------+------------+-----------+
 | carrie    |     199       | horror| stephen king | softcover  |   1974    |
 +-----------+---------------+-------+--------------+------------+-----------+
 | dune      |     412       | scifi | frank herbert| hardcover  |   1965    |

but here is what the result on the backend.

 +-----------+---------------+-------+--------------+------------+-----------+
 | book_title|number_of_ page| genre | author       | type       |publication|
 +-----------+---------------+-------+--------------+------------+-----------+
 | carrie    |     199       | horror| H            | softcover  |   1974    |
 +-----------+---------------+-------+--------------+------------+-----------+
 | dune      |     9         | o     | I            | o          |   9       |

I am not sure what is the problem. Maybe someone can share their expert opinion on this matter I would gladly appreciate it. Thank you very much in advance.

EDIT : I am able to insert the data on the first row correctly, here is what it looks like now. The author from the first row was correct, but on the second one from "I", it now became "t".

 +-----------+---------------+-------+--------------+------------+-----------+
 | book_title|number_of_ page| genre | author       | type       |publication|
 +-----------+---------------+-------+--------------+------------+-----------+
 | carrie    |     199       | horror| stephen king | softcover  |   1974    |
 +-----------+---------------+-------+--------------+------------+-----------+
 | dune      |     9         | o     | t            | o          |   9       |

Additional Edit:

So I add these code in form.php

include_once 'config/database.php';
include_once 'config/bookclass.php';

$database = new Database();
$db = $database->getConnection();

$book = new Book($db);

if($_POST){

  $book->book_title=$_POST['book_title'];
  $book->number_of_ page=$_POST['number_of_ page'];
  $book->genre=$_POST['genre'];
  $book->author=$_POST['author'];
  $book->type=$_POST['type'];
  $book->publication=$_POST['publication'];

  if($book->book()){
        var_dump ($value);
        var_dump($book->number_of_ page[$key]);
        var_dump($book->genre[$key]);
        var_dump($book->author[$key]);
        var_dump($book->type[$key]);
        var_dump($book->publication[$key]);
  }
  else{
  echo "not saved";
}
}

And here is what I got:

Notice: Undefined variable: value in /var/www/html/library/form.php on line 106
NULL 
Notice: Undefined variable: key in /var/www/html/library/form.php on line 107

Notice: String offset cast occurred in /var/www/html/library/form.php on line 107
string(1) "9" 
Notice: Undefined variable: key in /var/www/html/library/form.php on line 108

Notice: String offset cast occurred in /var/www/html/library/form.php on line 108
string(1) "t" 
Notice: Undefined variable: key in /var/www/html/library/form.php on line 109

Notice: String offset cast occurred in /var/www/html/library/form.php on line 109
string(1) "o" 
Notice: Undefined variable: key in /var/www/html/library/form.php on line 110

Notice: String offset cast occurred in /var/www/html/library/form.php on line 110
string(1) "o" 
Notice: Undefined variable: key in /var/www/html/library/form.php on line 111

Notice: String offset cast occurred in /var/www/html/library/form.php on line 111
string(1) "9"

What have you tried to debug the problem? What does $this-> book_title contain? — Nico Haase
– Nico Haase, Commented Mar 9, 2020 at 15:49

TheSynt4x · Accepted Answer · 2020-03-09 10:11:23Z

2

Why are you using class members for your foreach loop? You can just do:

foreach ($this->book_title as $key => $value).

You also have a lot of whitespaces surrounding $this-> calls. Your code is also XSS injectable, because you do not sanitize your input with htmlspecialchars or htmlentities. But all of that aside. The best way of debugging PHP code is to use var_dump. Check first and foremost the $this->author by doing var_dump($this->author). I can unfortunately not tell you anymore because the code you've provided is not complete and to make a valid judgement and give you the help you need, you would need to provide the parts where you assign the author class member.

Are you sure that the values that you're inserting to the database are truly arrays and not strings? Judging from your output, the values seem like strings, otherwise you wouldn't have gotten just characters.

edited Mar 9, 2020 at 10:11

answered Mar 9, 2020 at 10:06

TheSynt4x

816 bronze badges

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

4 Comments

aaa28 Over a year ago

That is my theory too , if whether I am actually inserting arrays? I am not sure exactly where can I check it or where to put var_dump, may I should not bind the variable or maybe I should put it outside, and maybe the problem is in the form where $_POST happens.

aaa28 Over a year ago

I am able to insert all the data in the first row but on the second one is still the same

Nico Haase Over a year ago

Please explain that XSS part further - through prepared statements, there should not be a problem on the database side. Typical XSS problems occur when printing stuff from the database without escaping them, but this part isn't even covered in the given code

TheSynt4x Over a year ago

Typically, you would want to sanitize your input before you insert into the database. The part is covered in the code, if you look closely there's that if statement that checks if $_POST is set or not. He does not sanitize the input with the use of htmlentities or htmlspecialchars. Even when using prepared statements, you're absolutely not safe since there are advanced SQL injection techniques and bypasses as well as advanced XSS techniques. You always want to be extra paranoid when it comes to the security of your website. Never trust the people using your site.

aaa28 · Accepted Answer · 2020-03-09 15:45:40Z

Ok Im posting an answer because I finally cracked it,

I just changed this :

public function book(){

foreach($this-> book_title AS $this->key => $this->value) {

              $query = "INSERT INTO

                table_a 

                SET
                book_title=:book_title,
                number_of_ page = :number_of_page,
                genre = :genre,
                author = :author,
                type = :type,
                publication = :publication";

                $stmt = $this->conn->prepare($query);

                $this->book_title=$this->value;
                $this->number_of_ page= $this->number_of_ page[$this->key];
                $this->genre = $this->genre[$this->key];
                $this-> author= $this-> author[$this->key];
                $this-> type= $this-> type[$this->key];
                $this-> publication = $this->publication[$this->key];

                $stmt->bindParam(':book_title', $this->book_title);
                $stmt->bindParam(':number_of_ page', $this->number_of_ page);
                $stmt->bindParam(':genre ', $this->genre );
                $stmt->bindParam(':author', $this->author);
                $stmt->bindParam(': type', $this-> type);
                $stmt->bindParam(':publication', $this->publication);

               $stmt->execute();
              }

to this:

              public function book(){

    foreach($this-> book_title AS $key => $value) {

              $query = "INSERT INTO

                table_a 

                SET
                book_title=:book_title,
                number_of_ page = :number_of_page,
                genre = :genre,
                author = :author,
                type = :type,
                publication = :publication";

                $stmt = $this->conn->prepare($query);

                $this->book_title=$value;
                $this->number_of_ page= $this->number_of_ page;
                $this->genre = $this->genre;
                $this-> author= $this-> author;
                $this-> type= $this-> type;
                $this-> publication = $this->publication;

                $stmt->bindParam(':book_title', $value);
                $stmt->bindParam(':number_of_ page', $this->number_of_ page[$key]);
                $stmt->bindParam(':genre ', $this->genre[$key] );
                $stmt->bindParam(':author', $this->author[$key]);
                $stmt->bindParam(': type', $this-> type[$key]);
                $stmt->bindParam(':publication', $this->publication[$key]);

               $stmt->execute();
              }

Collectives™ on Stack Overflow

why does my PHP foreach loop is inserting wrong value on mysql?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related