can't update data into MySQL using PHP

Question

Can't update new data into table.

My Tables Structure:

Table book:
-id (Primary, Auto Increment, INT)
-title
-publisher_id (Foreign key to Table publisher.pub_id, INT)

Table publisher:
-pub_id (Primary, Auto Increment, INT)
-pub_name

What I want to do is update new data into TABLE book, but can't. I couldn't find where is wrong.

Code:

<?php
if($_POST){

  $uid = $_GET['id'];

  $title = mysqli_real_escape_string($mysqli, $_POST['title']);
  $publisher = mysqli_real_escape_string($mysqli, $_POST['publisher']);

  // Update publisher    
  $queryid = "SELECT pub_id FROM publisher WHERE pub_name = '$publisher' ";
  $result = $mysqli->query($queryid);
  $pub_id = $result->fetch_assoc();


  //4Update book
  $query = "UPDATE book
      SET
      title = '$title',
      publisher_id = 1
      WHERE id = $uid";

  $mysqli->query($query) or die();

  $msg ='bookinfo updated';
  header('Location: index.php?msg=' . urlencode($msg) . '');
  exit;
}    
?>

when I changed '$pub_id['pub_id']' to '".$pub_id['pub_id']."', the "WHERE" line became yellow like the picture, ...

Changing Code like this didn't work,either:

$query = "UPDATE book
          SET
          title='$title',
          publisher_id= 1
          WHERE id= $uid";

@Webdesigner Sorry, I am new to php, you mean error message or something else? There was no error message after running the code.. — Rufus7, Oct 08 '17 at 02:15
Like @Webdesigner said, check your logs. Also, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Oct 08 '17 at 02:39

score 0 · Answer 1 · answered Jan 20 '18 at 04:11

If you are developing on a MAC try to tail the log file while loading the page:

PHP:

tail -f /Applications/MAMP/logs/php_error.log

MYSQL:

tail -f /Applications/MAMP/logs/mysql_error_log.err

Try to do the same thing when you are on Windows (obviously with different log file location/command).

The problem might be that you cannot connect to your MYSQL database. Also there might be no such publisher defined in your post data.

score 0 · Answer 2 · answered Jan 20 '18 at 04:57

I ran this and it is working. Add some output and make your code check itself.

I ran this on the command line. You can adjust for the web if you wish.

Book

CREATE TABLE `book` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  `publisher_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Publisher

CREATE TABLE `publisher` (
  `pub_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pub_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`pub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data

INSERT INTO `publisher` (`pub_id`, `pub_name`)
VALUES
(1, 'Penguin'),
(2, 'Random');

INSERT INTO `book` (`id`, `title`, `publisher_id`)
VALUES
(1, 'Wrong', 2);

Code

<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

// Set up some test data
$_POST = [
    'id' => 1,
    'title' => 'Right',
    'publisher' => 'Penguin'
];

if($_POST) {

    // $uid = $_GET['id']; // I'll assume this was a mistake and the data should come from $_POST
    $uid = $_POST['id'];
    $title = mysqli_real_escape_string($mysqli, $_POST['title']);
    $publisher = mysqli_real_escape_string($mysqli, $_POST['publisher']);

    // Update publisher    
    $query = "SELECT pub_id FROM publisher WHERE pub_name = '$publisher' ";
    $result = $mysqli->query($query);

    $pub = $result->fetch_assoc();
    print_r($pub);
    echo PHP_EOL;

    // Read the data before updating
    $q1 = "SELECT title FROM book WHERE id = {$uid} ";
    $result = $mysqli->query($q1);
    $bookBefore = $result->fetch_assoc();
    echo "Before " . print_r($bookBefore, true) . PHP_EOL;

    // I assume you need $pub for something but this code does not use it
    // Update book
    $update ="UPDATE book
       SET title='$title',
       publisher_id = 1
       WHERE id= $uid";

    $mysqli->query($update) or die();

    $msg = 'bookinfo updated';
    echo "$msg\n"; 

    $result = $mysqli->query($q1);
    $bookAfter = $result->fetch_assoc();
    echo "After " . print_r($bookBefore, true) . PHP_EOL;

    if ( $bookBefore['title'] != $bookAfter['title'] ) {
        echo "Book title changed" . PHP_EOL;
    }
    else {
        echo "Book NOT changed" . PHP_EOL;            
    }

    if ( $bookAfter['title'] == $_POST['title'] ) {
        echo "Book title updated correctly" . PHP_EOL;
    }
    else {
        echo "Book title not correctly updated." . PHP_EOL;
    }

    //header('Location: index.php?msg='.urlencode($msg).'');
    //exit;
}

can't update data into MySQL using PHP

2 Answers2