CODEWITHSUNDEEP
phpmysql
user2403940
user2403940

Reputation:

Mysql PHP Access denied for user ''@'localhost' to database

Using the following code, the database can be connected to successfully. However, when trying to execute a query using the function 'executeNonQuery' I get the error { Access denied for user ''@'localhost' to database 'photobook' }. The user has full privileges, although strangely the query 'show grants' will not display that fact. However, when looking at user privileges in the GUI of mysql workbench it is. What can be the problem? I even used root, but the query is denied with the same error. The user can connect, but can't issue a query.

file: class.database.php

<?php
    class Database {
        function __construct(){
        }

        public function connect($host, $user, $pass){
            $dbcnct = mysqli_connect($host, $user, $pass);
            if(!$dbcnct){
                die("Couldn't Connect: " . mysql_error());
            }else{
                echo "Connected Successfully";
            }          
        }

        public function disconnect(){
            mysql_close($dbcnct);
            if(!$dbcnct){
                echo "Disconnected Successfully";
            }
        }

        public function executeNonQuery($database, $sql){
            mysql_select_db($database);
            $retval = mysql_query($sql, $dbcnct);
            if(!$retval){
                die("Couldn't Update Data: " . mysql_error());
            }
            echo "Update Successful";
        }
    }
?>


<?php
 include 'class.database.php';
 $db = new Database;
 $db->connect("localhost", "user", "pass"); 
 $userdata=$_GET['data'];
 $index=$_GET['index'];
 echo $index . "<br/>";
 foreach($userdata as $data){
     $sql = "update photobook set photoName='" . $data . "' where photoPosition=" . $index;

    $db->executeNonQuery("photoBook", $sql);
     echo $data . "<br/>";
 }
 $db->disconnect();

?>

Upvotes: 2

Views: 4107

Answers (4)

Joseph Collins
Joseph Collins

Reputation: 461

Put your connect script in the __construct() section, this initialize connection to call new Database; and make available to all functions in the Database Class without request new connection every time you run a query.

Upvotes: 0

Roscoe
Roscoe

Reputation: 19

i suggest you keeping $dbcnct as a variable in the database class so that when you call function executeNonQuery,the mysql connection won't be lost.

Upvotes: 0

Liam J
Liam J

Reputation: 163

Your $dbcnct variable is only defined for the function where you use it, not the whole class. You need to use class variables so you can share variables throughout the different functions :

<?php
    class Database 
    {
        public $dbcnct;

        function __construct()
        {
            // Nothing
        }

        public function connect($host, $user, $pass){
            $this->dbcnct = mysqli_connect($host, $user, $pass);
            if(!$this->dbcnct){
                die("Couldn't Connect: " . mysql_error());
            }else{
                echo "Connected Successfully";
            }          
        }

        public function disconnect(){
            mysql_close($this->dbcnct);
            if(!$this->dbcnct){
                echo "Disconnected Successfully";
            }
        }

        public function executeNonQuery($database, $sql){
            mysql_select_db($database);
            $retval = mysql_query($sql, $this->dbcnct);
            if(!$retval){
                die("Couldn't Update Data: " . mysql_error());
            }
            echo "Update Successful";
        }
    }
?>

Now all you need to do is :

$database->connect("host", "user", "pass");
$database->executeNonQuery("database", "sql");

But first of all you need to fix the mysql_ and mysqli_ mixups :)

Upvotes: 2

sreenivas
sreenivas

Reputation: 395

Try this: Adding users to MySQL

You need grant privileges to the user if you want external acess to database(ie. web pages).

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

CREATE USER 'a_project'@'%' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON a_database.* TO 'a_project'@'%';

Upvotes: 0

Related Questions