Receiving query failed error

Question

I am trying to pull information from a mySQL database. The code I am using for this is:

  <?php global $current_user;
  get_currentuserinfo();

  //echo 'Username: ' . $current_user->user_login . "\n";
  //echo 'User email: ' . $current_user->user_email . "\n";
  //echo 'User first name: ' . $current_user->user_firstname . "\n";
  //echo 'User last name: ' . $current_user->user_lastname . "\n";
  //echo 'User display name: ' . $current_user->display_name . "\n";
  echo  $current_user->display_name . "\n";
  //echo 'User ID: ' . $current_user->ID . "\n";
?>
<?php
            $members_settings = get_option('members_db');
        $host       = $members_settings['members_db_server']; // ''; //replace your IP or hostname
    $user       = $members_settings['members_db_user']; // ''; //database user
    $password   = $members_settings['members_db_pass']; //'';//database password
    $database   = $members_settings['members_db_database']; /*''; //database name
    $prefix     = ''; //prefix if any else just give any random value
    $driver     = 'mysql'; //here u can also have ms sql database driver, postgres, etc
    $options    = array ( 'driver' => $driver, 'host' => $host, 'user' => $user, 'password' => $password,  'database' => $database, 'prefix' => $prefix );

    //$db = JDatabase::getInstance($options);
    $con = mysql_connect($host,$user,$password);
            $db_selected = mysql_select_db($database, $con);
           // if (!$db_selected) { die ("Can\'t use ".$database." : " . mysql_error()); }


            $userid = $current_user->user_login;
    $sql = "SELECT * FROM  ibew_Members WHERE Card = $userid";
            //echo $sql;
    $result = mysql_query($sql);
            if ($result) {
                list($id) = mysql_fetch_array(mysql_query($sql));
                //echo $id;
            } else {
                echo "query failed";
            }

    if (mysqli_connect_errno($con))
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

?>

Currently I am receiving query failed as my error and am not sure where the issue is as I am still a bit wet behind the ears when it comes to this area of expertise.

any insight as to how I can fix this issue would be gratefully appreciated.

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 06 '15 at 15:53
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Aug 06 '15 at 15:53
You're mixing `mysql_*` and `mysqli_*` functions. That won't work. — Jay Blanchard, Aug 06 '15 at 15:53
try using `echo mysql_error();` immediately after any of your `mysql_` function calls for more detailed information about what the problem might be. — oliakaoil, Aug 06 '15 at 15:58
never output a fixed (and totally useless) error message, when you could use `mysql_error() ` and be TOLD what the problem is. — Marc B, Aug 06 '15 at 16:01
thanks for all the insight, I am not familiar with the new language additions noted but will review them. so basically I will need to rewrite the entire thing? Thanks for all the insight and I will keep checking back if anyone can assist. I love this forum such a great community. — Chad Warford, Aug 06 '15 at 16:13

Silvertiger · Answer 1 · 2015-08-06T16:42:35.097

Mysql_ and mysqli_ are deprecated, you should consider migrating to a PDO framework. Here is an example:

// Database connection
$dbhost = 'localhost';
$dbname = 'databaseName';
$dbuser = 'userName';
$dbpass = 'userPassword';
$db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname.';charset=utf8', $dbuser, $dbpass);
$db->exec("set names utf8");
// Disable enumeration of prepared statements to instantiate "real" prepared statements
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// to implement try{} catch{} use PDO:ERRMODE_EXCEPTION
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

// New PDO Query
$userQuery = "SELECT * FROM  ibew_Members WHERE Card = :userid";
$userQueryExec = $db->prepare($userQuery);
$userQueryArray = array(":userid" => $userid);
// Execution and Error reporting
try {
    $userQueryExec->execute($userQueryArray);
    if ($userQueryExec->rowCount() != 0) { // we have results 
        $results = $userQueryExec->fetchAll(PDO::FETCH_ASSOC);                  
        foreach($results as $result) { 
            echo "Relevant Info: ".$result['fieldname'];
        }
    } else {
        echo "No results found in the database for this user<br>\n";
    }
}
catch (Exception $e) {
    echo "Unable to complete Transaction: ".$e."<br>\n";
}

This gives you a cleaner Database transaction and helps prevent injection using the PDO objects. The Try/Catch will also provide errors as reported and will give more detailed system errors.

i cannot tell you how much I appreciate your assistance, thanks for taking the time to better elaborate and help me learn the new framework. I will review the code you have provided right away. — Chad Warford, Aug 06 '15 at 18:07

score 0 · Answer 2 · answered Aug 06 '15 at 16:56

As an aside I see you're commenting out JDatabase::... if this is in joomla you can still use the Joomla framework to get your query data:

define( '_JEXEC', 1 );
define( 'JPATH_BASE', '/Your local path to root/' );
require_once ( JPATH_BASE.'/includes/defines.php' );
require_once ( JPATH_BASE.'/includes/framework.php' );
$mainframe  = JFactory::getApplication('site');
$mainframe->initialise();
$user       = JFactory::getUser();
$config     =& JFactory::getConfig();
$session    =& JFactory::getSession();
// get the Joomla DBO config
$db         = JFactory::getDbo();
$query      = $db->getQuery(true);

$cardsQuery = "SELECT * FROM ibew_Members WHERE Card = ".$db->quote($user->id);
$db->setQuery($cardsQuery);
try {
    $results = $db->loadAssocList();
    if (count($results) > 0) { // we have results
        foreach($results as $result) { 
            echo "Relevant Info: ".$result['fieldname'];
        }
    } else {
        echo "No results found in the database for this user<br>\n";
    }
}
catch (Exception $e) {
    echo "Unable to complete Transaction: ".$e."<br>\n";
}

Same basic idea as my other answer, just under the Joomla PDO framework instead.

initially in joomla, and now migrating to WordPress, may be why the commented out area. — Chad Warford, Aug 06 '15 at 18:08

Receiving query failed error

2 Answers2