How to Display a MySQL Error in PHP for a Long Query That Depends on the User Input

How do I display a MySQL error in PHP for a long query that depends on the user input?

Use this:

mysqli_query($this->db_link, $query) or die(mysqli_error($this->db_link)); 
# mysqli_query($link,$query) returns 0 if there's an error.
# mysqli_error($link) returns a string with the last error message

You can also use this to print the error code.

echo mysqli_errno($this->db_link);

Take a look here and here

MySQL syntax error when joining tables based on user input

I don't know what version of PHP you're using, but I'd encourage you to switch to PDO and to also use prepared statements (regardless of your version). However the syntax for your statement should be:

SELECT [columns] FROM [first table] INNER JOIN [other tables] ON [first table] WHERE [conditions]

With that being said, take a look at this example:

<?php
$config =  array(
  'host' => '[db host ip]',
  'username' => '[db username]',
  'password' => '[db password]',
  'name' => '[db name]'
);

$dsn = "mysql:dbname=$config[name];host=$config[host];charset=utf8mb4";
$db = new PDO($dsn, $config['username'], $config['password']);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $db->prepare('
SELECT
  *
FROM
  `temperature` AS t
  INNER JOIN `partSize`  AS p ON t.`type` = p.`type`
  INNER JOIN `volumne`   AS v ON t.`type` = v.`type`
  INNER JOIN `stiffness` AS s ON t.`type` = s.`type`
  INNER JOIN `weight`    AS w ON t.`type` = w.`type`
WHERE
  t.`temperature` LIKE CONCAT('%', :temperature, '%') AND
  t.`partSize`    LIKE CONCAT('%', :partSize, '%')    AND
  t.`volumne`     LIKE CONCAT('%', :volumne, '%')     AND
  t.`stiffness`   LIKE CONCAT('%', :stiffness, '%')   AND
  t.`weight`      LIKE CONCAT('%', :weight, '%');');

// Execute the query passing the $_POST values as the parameters
$stmt->execute(array(
  ':temperature' => $_POST['temperature'],
  ':partSize'    => $_POST['partSize'],
  ':volume'      => $_POST['volume'],
  ':stiffness'   => $_POST['stiffness'],
  ':weight'      => $_POST['weight']
);

// Get all of the rows returned by the query and store them in an associative array
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

MySQL UPDATE Query PHP doesn't work

It seems like your $param1 value may be empty, or otherwise invalid.

[You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]

The '' implies that the value is empty; so the SQL is doing:

UPDATE wp_projects SET nbrDonation=(nbrDonation+1) WHERE projectID=''

Which is invalid as nothing ('') is not an integer value as expected.

Solution:

You need to force the $param1 value to be interger. You can do this by typecasting in PHP.

so:

$param1 = (int)$_GET['projectID']; // forces it to a numeric value, 1 or 0

This will then mean the SQL will work correctly:

$sql1 = "UPDATE wp_projects SET nbrDonation = nbrDonation+1 WHERE projectID = $param1";

You do not need the brackets around the nbrDonation+1 and you do not need quotes around the ID number, because it's numeric.

---

Please also note:

How to Prevent SQL Injection compromise in MySQL with PHP

PHP and MySQL query based on user input not working

mysql_real_escape_string() requires a db connection.
Try this ....

<?php
$un = $_POST["u"];
$pk = $_POST["p"];
$ok = $_POST["o"];

$link = mysql_connect('localhost', 'root', 'randompassword');
if (!$link){
  die(' Oops. We Have A Problem Here: ' . mysql_error());
}

if ($link){
  echo 'connected succesfully';
}

mysql_select_db("forum") or die(' Oops. We Have A Problem Here: ' . mysql_error());

$u = mysql_real_escape_string($un);
$p = mysql_real_escape_string($pk);
$o = mysql_real_escape_string($ok);
$sql = "INSERT INTO users (username, password, occupation) VALUES ('$u', '$p', '$o')";
$ins_sql = mysql_query($sql);
IF($ins_sql) {
  echo 'Inserted new record.';
}ELSE{
  echo 'Insert Failed.';
}
?>

How to insert values in table

Two things I can think of top my head;

1. mysql_ has been deprecated, thus the else kicks in.
2. Your syntax maybe wrong for mysql_query?

Nonetheless, start over and start over with code that is functional and up-to-date...

Given that your connection is working properly update it to a new mysqli syntax, it's very simple and much more elegant:

$connect = new mysqli( 'localhost', 'USERNAME', 'PASSWORD', 'DATABASE' );
// check for an error
if ($this->_connection->connect_error)
{
    trigger_error("Connection Error: " . $this->_connection->connect_error(), E_USER_ERROR);
}

Now that you are connected walk-through a new process for your code.

Start by checking like you currently are for a submit $_POST so that you can start running the script:

if ( isset( $_POST['submit'] ) )
{
    // Encode the URL when creating the variables
    $name = htmlentities( $_POST['name'] );
    $phone = htmlentities( $_POST['phone'] );
    $cash = htmlentities( $_POST['cash'] );
    $date = date( 'l jS \of F Y h:i:s A' );

    // create sql
    // DO NOT INSERT VALUES STRAIGHT INTO YOUR QUERY
    $sql = "INSERT INTO tbl2 ( name, phone, cash, date ) VALUES ( ?, ?, ?, ? )";

Note: before continuing, let me explain that you should never insert content into your query because that would throw raw user input in the mist of your code. Now, most users will never try anything fishy. But anyone could easily throw a few SQL commands inside of your inputs and DELETE, SELECT, and UPDATE your database content and cause numerous problems.

Here is some reference: https://en.wikipedia.org/wiki/SQL_injection

To work around that problem, use prepared statements. You can read all about it on PHP manual; and also see some real-life examples.

    // prepare query
    // USE PREPARED STATEMENTS
    if ($stmt = $connect->prepare( $sql ))
    {
        // bind the params
        $stmt->bind_param('ssss', $name, $phone, $cash, $date);
        // execute the query
        $stmt->execute();

        // check for errors
        if ($stmt->errno)
        {
            $message = array(
                'is_error' => 'danger',
                'message' => 'Error: ' . $stmt->error
            );
        }

        // make sure at least 1 or more rows were affected
        if ($stmt->affected_rows > 0)
        {
            $message = array(
                'is_error' => 'success',
                'message' => 'Success: ' . $stmt->affected_rows . ' rows were inserted.' // value should be 1
            );
        }
        else
        {
            // if not, send warning to user
            $message = array(
                'is_error' => 'warning',
                'message' => 'Warning: ' . $stmt->affected_rows . ' rows were updated.'
            );
        }
        // close your connection
        $stmt->close();
    }
    else
    {
        $message = array(
            'is_error' => 'danger',
            'message' => 'QUERY: error. Try again.'
        );
        exit;
    }
}
else
{
    $message = array(
        'is_error' => 'warning',
        'message' => 'There was no submission attempt. Try again.'
    );
    exit;
}

Notice in the code is broken down into parts where you can catch multiple errors, and it's important for debugging; it will allow you to know exactly where the code went wrong, and localize your problem to a single section of it.

mysqli_query() expects parameter 1 to be mysqli, boolean given

To answer your question: It's not working because you're wrapping the column names in brackets, remove these and it should work. You also have a typo. ($comapnyname = $_POST['companyname'];), should be $companyname.

However, there's a few other, bigger issues with your code. You're using the mysql functions, which are deprecated and completely removed from PHP7.

Next to that, you should use prepared statements and bind_param to prevent SQL injections, escaping strings will not accomplish this.

---

This what it would look like using prepared statements.

// ... Set your database connection variables

/*
 * Create the database connection, notice the
 * usage of mysqli instead of mysql
 */
$connect = new mysqli($host, $user, $password, $database);

/*
 * The query, notice the usage of a question mark
 * on the place of the variables to be inserted
 */
$sql = "INSERT INTO client (cname, tname, pname, ename) VALUES (?, ?, ?, ?)";

// Prepare the query using $connect->prepare()
$stmt = $connect->prepare($sql);

// Check if the query was prepared
if(!$stmt) {
  // ... Handle your error
}

// Get $_POST variables
$companyname = $_POST['companyname'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

if(!$stmt->bind_param('ssss', $companyname, $username, $password, $email)) {
  // ... Handle your error
}

if(!$stmt->execute()) {
  // ... Handle your error
} else {
  echo 'Record inserted.';
}

It also seems that you're inserting the passwords into your database as clear-text, this is a big issue. You should hash them. Write two functions, one to hash the password and one to verify it when users log in.

The first function will return the password hash and the second one will return TRUE or FALSE if the password is correct or incorrect.

function hashPassword($password) {
  return password_hash($password, PASSWORD_DEFAULT);
}

function verifyPassword($password, $hash) {
  return password_verify($password, $hash);
}

Can I detect and handle MySQL Warnings with PHP?

For warnings to be "flagged" to PHP natively would require changes to the mysql/mysqli driver, which is obviously beyond the scope of this question. Instead you're going to have to basically check every query you make on the database for warnings:

$warningCountResult = mysql_query("SELECT @@warning_count");
if ($warningCountResult) {
    $warningCount = mysql_fetch_row($warningCountResult );
    if ($warningCount[0] > 0) {
        //Have warnings
        $warningDetailResult = mysql_query("SHOW WARNINGS");
        if ($warningDetailResult ) {
            while ($warning = mysql_fetch_assoc($warningDetailResult) {
                //Process it
            }
        }
    }//Else no warnings
}

Obviously this is going to be hideously expensive to apply en-mass, so you might need to carefully think about when and how warnings may arise (which may lead you to refactor to eliminate them).

For reference, MySQL SHOW WARNINGS

Of course, you could dispense with the initial query for the SELECT @@warning_count, which would save you a query per execution, but I included it for pedantic completeness.

---

Related Topics