Is there a good way to concatenate this so I can have a single query instead of 3 queries in mysql?

PHP
1

In this example, I have 3 separate queries. I would like just one query under the condition, that it only updates a changed column in a record. I would like to do that, so I can also return an error on just 1 query instead of 3.

<?php

$servername = "127.0.0.1";
$username = "root";
$password = "root";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}

$title = $_POST["title"];
$_title = mysqli_escape_string($conn, $title);	

$publisher = $_POST["publisher"];
$_publisher = mysqli_escape_string($conn, $publisher);

$developer = $_POST["developer"];
$_developer = mysqli_escape_string($conn, $developer);	

if (!empty($_title) or !empty($_publisher) or !empty($_developer) ) {
	if (!empty($_title)) {
		$sql = "UPDATE Games SET title='$_title' WHERE id=2";
		mysqli_query($conn, $sql);
	}

	if (!empty($_publisher)) {
		$sql = "UPDATE Games SET publisher='$_publisher' WHERE id=2";
		mysqli_query($conn, $sql);
	}

	if (!empty($_developer)) {
		$sql = "UPDATE Games SET developer='$_developer' WHERE id=2";
		mysqli_query($conn, $sql);
	}

	echo "Records updated!";
}

mysqli_close($conn);
?>
2

You could do something like:

if (!empty($_title) or !empty($_publisher) or !empty($_developer) ) {
        $sql = "UPDATE Games SET ";
        $first = true;
	if (!empty($_title)) {
		$sql .= "title='$_title'";
                $first = false;
	}

	if (!empty($_publisher)) {
                if ($first == false) $sql .= ", "; 
		$sql .= "publisher='$_publisher'";
                $first = false;
	}
	if (!empty($_developer)) {
                if ($first == false) $sql .= ", "; 
		$sql .= "developer='$_developer'";
	}
        $sql .= " WHERE id=2";
        if (! mysqli_query($conn, $sql))  {
             echo "Something went wrong.";
             exit();
             }
        else {
	echo "Records updated!";
        }
}

But you should really look at using prepared statements instead, which would make your query that little bit safer, especially if it uses user-supplied text.

3

exactly what I was looking for. I had a little confusion about how to accurately update commas ‘,’ in the sql string. Thanks!

4

It’s just a case of noting whether you’ve previously put an update field in, and if you did, add the comma.

But, you could still change it to use a prepared statement. If this is new code, it’d be worth doing that as you create it rather than trying to go back later on and change working code.

5

I don’t know what a prepared statement is, but I heard of it mentioned before. I will look into this.

6

Ok, I looked it up. very helpful!

7

One of the things it will help with, in your specific example above, is if the title is something like “Fred’s Quest”. In the code above, there will be a syntax error because of the apostrophe, with a prepared statement there will not.

8

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