Why isnt mysql table updating

PHP
1

Why is the following code saying it is working but I am not getting any updates in my mysql table

<?php
define('DB_NAME', 'comics');
define('DB_USER', 'root');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
	die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}

if (!isset($_GET['update'])){
		$q = "SELECT * FROM comics where ID = '$_GET[id]'";
		$result = mysql_query($q);
 		$comics = mysql_fetch_array($result);
	}

if(isset($_GET['update']))
{
	
	
	$email = mysql_real_escape_string($_GET["email"]);
	$title = mysql_real_escape_string($_GET["title"]);
	$issue = mysql_real_escape_string($_GET["issue"]);
	$edition = mysql_real_escape_string($_GET["edition"]);
	$publisher = mysql_real_escape_string($_GET["publisher"]);
	$condition = mysql_real_escape_string($_GET["condition"]);
	$price = mysql_real_escape_string($_GET["price"]);
	
	$targetid = intval($_GET["id"]);
	
	$sql =  "UPDATE comics SET Email='$email', Title='$title', Issue='$issue', Edition='$edition', Publisher='$publisher', Quality='$condition', Cost='$price' WHERE ID = '$targetid' ";

	$myData = mysql_query($sql,$link);
	if($myData == FALSE) {die ("error: " .mysql_error());}

	if ($myData == TRUE) {
header('Location: index.html');
	}

	
mysql_close($link);
}

?>
2

You’re a sitting duck for SQL Injection attack, prepared statements should always be used when sending any sort of data to the database. Also all user submitted data needs to always be validated, never trust any user submitted data no matter how much you trust the user.

Also the mysql_* extension that you’ve used was removed from PHP version 7

3

Is it down to quotes around the id condition in the update query? I’m never sure whether that matters or not, I suspect not. But if you moved it to PDO, you could use a prepared statement and obviate the need for quotes in the query.

If you display the value of $sql in your code, then enter that query in phpmyAdmin or whatever equivalent you use, does it work?

4

Either of the two interfaces that still exist allow prepare statements - only the mysql_ interface that ceased to exist late last year doesn’t.

5

True, I usually type mysqli or PDO. But then the OP usually asks for an opinion on which is “better”, so as I personally (and not for any reason I can explain, other than what I’ve read mainly on here) prefer PDO, that’s the one I mentioned.

6

I made the mistake of converting all my code to use mysqli before finding out that PDO was better (when I started writing a series of articles comparing how the code to do various tasks compare - in almost every case PDO results in shorter easier to maintain code.

3 Likes
7

I too feel that PDO is most often better than mysqli.
I think what mysqli has going for it are two things

  • it is mysql Improved, so it will be more familiar to those used to using DEPRECATED mysql
  • it has both Procedural and OOP so those that shy away from OOP can still use it.

@ses0713 I’ve long given up trying to “fix” broken mysql code. Take a look at this and give it a try. (not tested, and user supplied input is not sanitized)
* I don’t see where you’re using $result anywhere ??

<?php
define('DB_NAME', 'comics');
define('DB_USER', 'root');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'localhost');
//$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (!link) {
//	die('Could not connect: ' . mysql_error());
	die('Could not connect: ' . mysqli_connect_error());
}
/*
$db_selected = mysql_select_db(DB_NAME, $link);
if (!$db_selected) {
	die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}
*/
/* Useless as $result is not being used anywhere?
//if (!isset($_GET['update'])){
if ( (!isset($_GET['update'])) && (isset($_GET['id'])) ){
//		$q = "SELECT * FROM comics where ID = '$_GET[id]'";
//		$result = mysql_query($q);
// 		$comics = mysql_fetch_array($result);
        
		$q = "SELECT * FROM comics where ID = ?";
        $stmt = mysqli_prepare($link, $q);
        mysqli_stmt_bind_param($stmt, "i", $_GET[id]);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
        mysqli_free_result($result);
	}
*/
if(isset($_GET['update']))
{
/*
	$email = mysql_real_escape_string($_GET["email"]);
	$title = mysql_real_escape_string($_GET["title"]);
	$issue = mysql_real_escape_string($_GET["issue"]);
	$edition = mysql_real_escape_string($_GET["edition"]);
	$publisher = mysql_real_escape_string($_GET["publisher"]);
	$condition = mysql_real_escape_string($_GET["condition"]);
	$price = mysql_real_escape_string($_GET["price"]);
	$targetid = intval($_GET["id"]);
*/
//	$sql =  "UPDATE comics SET Email='$email', Title='$title', Issue='$issue', Edition='$edition', Publisher='$publisher', Quality='$condition', Cost='$price' WHERE ID = '$targetid' ";
	$sql =  "UPDATE comics SET Email = ?, Title = ?, Issue = ?, Edition = ?, Publisher = ?, Quality = ?, Cost = ? WHERE ID = ?";
    $stmt = mysqli_prepare($link, $sql);
    mysqli_stmt_bind_param($stmt, "sssssssi", $_GET["email"], $_GET["title"], $_GET["issue"], $_GET["edition"], $_GET["publisher"], $_GET["condition"], $_GET["price"], $_GET[id]);
    mysqli_stmt_execute($stmt);
    $affected_rows = mysqli_stmt_affected_rows($stmt);
    mysqli_stmt_close($stmt);
    mysqli_close($link);
/*
	$myData = mysql_query($sql,$link);
	if($myData == FALSE) {die ("error: " .mysql_error());}
	if ($myData == TRUE) {
*/
	if(!$affected_rows >= 1) { 
        die ("error: " .mysqli_stmt_error($stmt));
    }
	if ($affected_rows > 0) {
header('Location: index.html');
	}
//mysql_close($link);
}
?>
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.