How to Check If a MySQL Query Using the Legacy API Was Successful

How to check if a MySQL query using the legacy API was successful?

This is the first example in the manual page for mysql_query:

$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

If you wish to use something other than die, then I'd suggest trigger_error.

Show ERROR / Success messages after executes query

If I understand correctly, you want to be able to run MySQL queries via user input? Just have the submit button redirect to a PHP script that grabs the user input(e.g $_REQUEST['query']) then connect to and query the database.
Code example:

PHP:

<?php

    /* query.php */
    $query = $_REQUEST['query']; //do SQLi prevention
    $conn  = new mysqli('localhost', 'root', '', 'db');
    if($conn->connect_error) throw new \Exception('Failed to connect to MySQL server.'); //have the script handle the exception elsewhere
    if($conn->query($query) !== false)
    {
        echo 'Query executed successfully.';
    }
    else
    {
        throw new \Exception('MySQL error: ' . $conn->error); //have the script handle the exception elsewhere
    }
    $conn->close();

HTML:

<!DOCTYPE html>
  <!-- index.html -->
  <html>
    <head>
    </head>
    <body>
      <form method='POST' action='query.php'>
        <input type='text' name='query' />
        <br />
        <input type='submit' />
      </form>
    </body>
  </html>

Is mysql return data to php variable when error

or die() will run only if something goes wrong with the query. Several things could go wrong. Some of them are:

The database table does not exist
One of the specified columns does not exist
There is a syntax error in the query
The database is offline or the connection has been lost

So it will run on critical errors. It won't run if your table or a specific column is empty.

Also it is very important to stop using mysql_ functions!. The mysql extension is deprecated from PHP 5.5 and completely removed from PHP 7.

Use mysqli or PDO instead. You should also use Prepared Statements instead of directly interpolating variables in your query. More on Prepared Statements here

How to check if a result set is empty?

cursor.rowcount will usually be set to 0.

If, however, you are running a statement that would never return a result set (such as INSERT without RETURNING, or SELECT ... INTO), then you do not need to call .fetchall(); there won't be a result set for such statements. Calling .execute() is enough to run the statement.

Note that database adapters are also allowed to set the rowcount to -1 if the database adapter can't determine the exact affected count. See the PEP 249 Cursor.rowcount specification:

The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface.

The sqlite3 library is prone to doing this. In all such cases, if you must know the affected rowcount up front, execute a COUNT() select in the same transaction first.

How to tell if a db update was successful?

Use the first Execute method parameter to get the rows affected:

On Error resume next
Dim RecordsAffected as long
Dim cmd
Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = "Select stuff here"
cmd.CommandType = adCmdText

cmd.Execute RecordsAffected, , adExecuteNoRecords
If err.number > 0 or RecordsAffected = 0 then
    Response.Write "No record affected or SQL error or something"
end if

Using adExecuteNoRecords will gain performance according to several sources

Source: Execute method in MSDN

When mysql_query returns false

See the reference guide:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning
resultset, mysql_query() returns a resource on success, or FALSE on
error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc,
mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(),
and other functions for dealing with result tables, to access the
returned data.

Use mysql_num_rows() to find out how many rows were returned for a
SELECT statement or mysql_affected_rows() to find out how many rows
were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not
have permission to access the table(s) referenced by the query.

http://php.net/manual/en/function.mysql-query.php

Edit: Clarification of what those errors actually are.

So we have list of things that can return false:

When a MySQL statement which returns a resultset gets an error
When a MySQL statement which doesn't return anything gets an error
When a user does not have MySQL permission to access a table reference

In my opinion the first 2 are the ones that are a bit diffuse. What are the possible errors? There are 59 different client errors you can get from MySQL. These are more system related errors which we can presume that php will handle and probably wrap into a smaller amount of abstract errors.

Except for those client errors you have a set of more abstract errors which you can encounter during usage which is more related to using the actual API inside the application rather than the raw access to the MySQL server. Those are:

Access denied
Can't connect to [local] MySQL server
Lost connection to MySQL server
Client does not support authentication protocol
Password Fails When Entered Interactively
Host 'host_name' is blocked
Too many connections
Out of memory
MySQL server has gone away
Packet too large
Communication Errors and Aborted Connections
The table is full
Can't create/write to file
Commands out of sync
Ignoring user
Table 'tbl_name' doesn't exist
Can't initialize character set
Table corruption issues
Syntax related issues

Here are the references of what I just said:

List of the client errors
List of the common errors dealing with the API
References about query related issues
Table related issues
Other issues related to known bugs

Efficient SQL test query or validation query that will work across all (or most) databases

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

In a loop, some queries succeed, other queries fail 'No database selected'

The syntax of your DELETE query may be causing the problem

DELETE T FROM

I know this has caused issues with UPDATE queries sometimes before due to a mysql bug.

Re-writing your query to avoid the T reference should resolve this issue.

How can I see how many MySQL connections are open?

I think there are a couple of ways:

SHOW STATUS WHERE `variable_name` = 'Threads_connected'

or you can do a SHOW PROCESSLIST and find out unique values in the Id column. In old PHP API mysql, there is mysql_list_processes function that does the same as SHOW PROCESSLIST, too.

But first one should work for you. And perhaps you might like to check on other STATUS variables

How to Check If a MySQL Query Using the Legacy API Was Successful