Update MySQL query doesn't do anything when trying to submit data from HTML edit field to MySQL in PHP
Published: 2020-12-25 17:20:43

I am writing a URL Shortener, and I am having a problem with updating shortened URL into database (url_1) column. I am using MySQL, plain PHP, plain JavaScript, Apache Web Server.

SELECT and INSERT INTO statements are working perfectly fine with the first button (Shorten My URL! which you can find in index.php).
It is very strange to me why UPDATE is not working.

I have unchecked Safe Update option in MySQL Workbench preferences, and also, I am using mysqli instead of mysql connection type.

So, after user enters a full URL and clicks on Shorten My URL! button, it will submit id and link_to_page (which is a long URL).
This is working. After that, Javascript generates a shortened version of a URL, and prints it as a clickable link to the long URL. This is also working.

**Now, second edit field is for submitting and/or customizing shortened URL (UPDATE - Submit Shorten URL!). This is where I am having a problem.
It won't update. Neither, it won't update hard-coded values, I have tried. Why INSERT and SELECT are working, and UPDATE is not working?
I was planning to go back to data security (SQL Injection), but for now I didn't do it because of the simplicity of testing, so please don't comment about this.**

Could someone point out how to resolve this?

Here is my code:

`index.php`:

```
<?php  

include ('init.php');

	if(isset($_GET['id'])){
		include 'functions.php';
		$id  = $_GET['id'];
		$url = getUrlLocation($id);
		$url_1 = getShortcodeLocation($id);
		header('Location: ' . $url);
		

	}
?>

<!DOCTYPE html>
<html>
<head>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
	<title>URL Shortener</title>
</head>
<body>
	<input type="text" name="url">
	<input type="submit" value="Shorten My URL!" id="button_1"><br>
	<p class="errors"></p>
	<input type="text" name="url_1">
	<input type="submit" value="Submit Shorten URL!" id="button_2"><br>

	<script type="text/javascript">
		$(document).ready(function(){
			$('input[id="button_1"]').click(function(e){
				e.preventDefault();

				$('.errors').html('');
				var url = $('input[name="url"]').val();

				if(url.length == 0){
					$('.errors').html('Whoops! Please enter a URL!');
					return false;
				}

				$.post('process.php', {
					url: url
				}, function(data, textStatus, xhr) {
					var od = data;
					//data = 'http://downloadmycode.com/short?id=' + data;
					data = 'http://localhost/url2?id=' + data;
					//$('.errors').html('<a href="' + data + '" target="_blank">sho.rt/' + od + '</a>')
					$('.errors').html('<a href="' + data + '" target="_blank">cut' + od + '</a>')
				});
			});
		});
		
				$(document).ready(function(){
			$('input[id="button_2"]').click(function(e){
				e.preventDefault();

				$('.errors').html('');
				var url_1 = $('input[name="url_1"]').val();

				if(url_1.length == 0){
					$('.errors').html('Whoops! Please enter a URL!');
					return false;
				}

				$.post('submit_shortcode.php', {
					url: url_1
				}, function() {

				});
			});
		});
		
	</script>
	

	
</body>
</html>
```

`process.php`:

```
<?php  
	include 'init.php';
	include 'functions.php';

	$id 	= rand(111,999);
	$url 	= $_POST['url']; 

	if(idExists($id) == true){
		$id = rand(111,999);
	}

	if(urlHasBeenShortened($url)){
		echo getURLID($url);
		return true;
	}
	
	/*if(isset($_POST['button_1']) and !isset($_POST['button_2'])){
	insertID($id, $url);
	}
	
	if(isset($_POST['button_2']) and !isset($_POST['button_1'])){
		updateShortcode($id);
	}*/
	
	insertID($id, $url);

	echo $id;
?>
```
`functions.php`:

```
<?php  
	function idExists($id){
		include 'init.php';
		$row = $conn->query("SELECT * FROM urls WHERE id = '$id'");

		if($row->num_rows > 0){
			return true;
		} else {
			return false;
		}
	}

	function urlHasBeenShortened($url){
		include 'init.php';
		$row = $conn->query("SELECT * FROM urls WHERE link_to_page = '$url'");

		if($row->num_rows > 0){
			return true;
		} else {
			return false;
		}
	}

	function getURLID($url){
		include 'init.php';
		$row = $conn->query("SELECT id FROM urls WHERE link_to_page = '$url'");

		return $row->fetch_assoc()['id'];
	}

	function insertID($id, $url){
		include 'init.php';
		$conn->query("INSERT INTO urls (id, link_to_page) VALUES ('$id', '$url')");

		if(strlen($conn->error) == 0){
			return true;
		}
	}

	function getUrlLocation($id){
		include 'init.php';
		$row = $conn->query("SELECT link_to_page FROM urls WHERE id = '$id'");

		return $row->fetch_assoc()['link_to_page'];
	}
	
	function updateShortcode($id){
		include 'init.php';
include 'index.php';

	//$url_1= $_POST['url_1'];
	
	//$stmt = $conn->prepare("update urls set url_1 = ? where id = ? ");
	$sql = $conn->query("update urls set url_1 = '$url_1' where id = '$id' ");
	


// Perform a query, check for error
if (!mysqli_query($conn,$sql)) {
  die("Error description: " . mysqli_error($conn));
}


		/*if(strlen($conn->error) == 0){
			return true;
		}*/
		
	//return $sql->fetch_assoc()['id'];	
		
	}
	
	function getShortcodeLocation($id){
		include 'init.php';
				$row = $conn->query("SELECT url_1 FROM urls WHERE id = '$id'");

		return $row->fetch_assoc()['url_1'];
	}
?>
```
`submit_shortcode.php`:
```
<?php
	include 'init.php';
	include 'functions.php';

	$id 	= rand(111,999);
	$url 	= $_POST['url']; 

	if(idExists($id) == true){
		$id = rand(111,999);
	}

	if(urlHasBeenShortened($url)){
		echo getURLID($url);
		return true;
	}
	
	updateShortcode($id);
	
	//$url_1= $_POST['url_1'];
	//echo $url_1;
	
	//getShortcodeLocation($id);
	
	echo $id;
?>
```
`init.php`:

```
<?php
$username="root";
$password="";
$hostname="localhost";
$dbname="shortster_schema";

/*Creating a connection*/
$conn= new mysqli($hostname, $username, $password, $dbname);

/*If there are errors, print error message*/
if($conn->connect_error)
{
	die("Connection failed");
}

?>
```