Page 1 of 1

need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 8:35 am
by PHPHelpNeeded
Note: the code has been given reference CN:XX to POINT TO LINE NUMBER, so if there is a line where you find the fault of the code, please use that reference, please.
Note: the mastertable will contain information that tells the server program how to initialize, the information that the mastertable contains will tell the server program how to read the other databases, by keeping track of which table insert or created in the masterbase

The following code is the classes which have the code that is giving me problems:

Code: Select all

C1:00 //DatabaseBaseCommand extandable abstract PDO wrapper class 
C1:01 abstract class DatabaseBaseCommand {
C1:02         
C1:03	private $db = null;                         //database PDO object
C1:04	private $dbhost = null;                  //database HOST
C1:05	private $dbport = null;                   //database PORT
C1:06	private $dbuser = null;                   //database USER
C1:07	private $dbpass = null;                  //database PASSWORD
C1:08	private $dbname = null;                 //database DBNAME
C1:09
C1:10        /*
C1:11         * constructor function to initialize the PDO wrapper abstract class DatabaseBaseCommand
C1:12        */
C1:13	public function __construct($dbname, $dbhost, $dbport, $dbuser, $dbpass){
C1:14		$this->dbname = $dbname;
C1:15		$this->dbhost = $dbhost;
C1:16		$this->dbport = $dbport;
C1:17		$this->dbuser = $dbuser;
C1:18		$this->dbpass = $dbpass;
C1:19	}
C1:20
C1:21        //db_connect() connects to the database by host, port, user, and password.
C1:22	public function db_connect(){
C1:23		$this->db = new PDO("mysql:host=".$this->dbhost.";port=".$this->dbport, $this->dbuser, $this->dbpass);
C1:24		return ($this->db == true); // supposed to return true (if, there was a connection) and false (if there isn't, for instance if the MySQL service is not running).
C1:25	}
C1:26
C1:27        //db_query() function to perform a query command in the database connected to database object $db
C1:28	public function db_query($statement){
C1:29		if(trim($statement) !== NULL){
C1:30			return ($this->db->query($statement)); // returns the results if true;
C1:31		} else return false; //return falses if there is no values;
C1:32	}
C1:33
C1:34        ///__get() magic function to get the private parameters of the DatabaseBaseCommand PDO abstract wrapper class
C1:35	public function __get($parameter){
C1:36		switch($parameter){
C1:37			case "DBNAME": return $this->dbname;
C1:38			case "DBHOST": return $this->dbhost;
C1:39			case "DBPORT": return $this->dbhost;
C1:40			case "DBUSER": return $this->dbhost;
C1:41			case "DBPASS": return $this->dbhost;
C1:42			default: return false;
C1:43		}
C1:44	}
C1:45
C1:46} //end of class
C1:47
C1:48//DatabaseManager an extended wrapper class to
C1:49//manage the functions and commands of the wrapper
C1:50//abstract class DatabaseBaseCommand
C1:51class DatabaseManager extends DatabaseBaseCommand {
C1:52
C1:53	public function __construct($dbname, $dbhost, $dbport, $dbuser, $dbpass){
C1:54		parent::__construct($dbname, $dbhost, $dbport, $dbuser, $dbpass);
C1:55	}
C1:56
C1:57        ///initialize() is a driver function which will test the integrity of the MasterDatabase and MasterTable
C1:58	public function initialize(){
C1:59                //connects the PDO to wrapper class to the database server
C1:60		if(parent::db_connect()){
C1:61
C1:62                        //testing whether the MasterDatabase EXISTS
C1:63			$db_master = parent::db_query("SHOW DATABASE ".parent::__get("DBNAME")."");
C1:64			if($db_master){
C1:65				echo "Database ".parent::__get("DBNAME")." FOUND!\n";
C1:66				$db_master_table = parent::db_query("SHOW TABLE MasterTable");
C1:67				if($db_master_table){
C1:68					echo "(MasterTable) FOUND!\n";
C1:69					return true;
C1:70				} else {
C1:71					$db_master_create_table = parent::db_query("CREATE TABLE IF NOT EXISTS MasterTable");
C1:72					if($db_master_create_table){
C1:73						echo "Master Table Created\n";
C1:74						return true;
C1:75					} else {
C1:76						echo "Master Table NOT created\n";
C1:77						return false;
C1:78					}
C1:79				}
C1:80                         //if the MasterDatabase does not exist, it creates it for the first time
C1:81			} else {
C1:82                                //creates the MasterDatabase
C1:83				$db_master_create = parent::db_query("CREATE DATABASE IF NOT EXISTS ".parent::__get("DBNAME")."");
C1:84				if($db_master_create){
C1:85					echo "Master Database Created!\n";
C1:86                                        //it creates the MasterTable for the first time
C1:87					$db_master_create_table = parent::db_query("CREATE TABLE IF NOT EXISTS MasterTable");
C1:88					if($db_master_create_table){
C1:89						echo "Master Table Created\n";
C1:90						return true; ///if MasterTable is Created for the first Time, it returns True (which means, initialize() function call states, Database integrity IS GOOD);
C1:91                                         //if MasterTable Already Exists, which it shouldn't, it returns false (which means, database integrity is NOT GOOD!
C1:92					} else {
C1:93						echo "Master Table NOT created\n";
C1:94						return false;
C1:95					}
C1:96                                 //if MasterDatabase Already EXISTS it is not recreated
C1:97				} else {
C1:98					echo "Master Database NOT created\n";
C1:99					return false;
C1:100      			}
C1:101			}
C1:102               // Throws PDO Exception if the PDO() constructor fails to create a database connection object
C1:103		} else throw new PDOException("Unable Able To Connect To Database Server MYSQL56");
C1:104	}
C1:105
C1:106        //this function checks if there is a new user
C1:107	public function isNewUser($user_name){
C1:108
C1:109		$return = parent::db_query("SELECT * FROM USER_LOGIN WHERE UserName=\"".$user_name."\";");
C1:110
C1:111		if($return){
C1:112			
C1:113			echo "User found\n";
C1:114
C1:115			//print_r($return);
C1:116			
C1:117			return false;
C1:118
C1:119		} else {
C1:120			echo "User NOT found\n";
C1:121			return true;
C1:122		}
C1:123	}
C1:124
C1:125} //end of class
The following code is where I keep getting the messages MasterDatabase created, but the initialize() function returns a false value which tells me Database integrity NOT GOOD.

Code: Select all

C2:00			$a = new DatabaseManager($MYSQL_MASTER_DATABASE, $MYSQL_HOST, $MYSQL_PORT, $MYSQL_USER, $MYSQL_PASS);
C2:01			if($a->initialize()){
C2:02				echo "Database Ok\n";
C2:03				try{
C2:04					if($a->isNewUser($command_parameters['USER_NAME'])){
C2:05					echo "Is new user!\n";
C2:06											
C2:07					$talkback = "NEW USER!\r\n";
C2:08					socket_write($msgsock, $talkback, strlen($talkback));
C2:09				} else {
C2:10					echo "Existen user!\n";
C2:11					$talkback = "APPROVED\r\n";
C2:12					socket_write($msgsock, $talkback, strlen($talkback));
C2:13				}	
C2:14			} catch(PDOException $e){
C2:15				echo $e ."\n\n";
C2:16				$talkback = "DENIED\r\n";
C2:17	        		socket_write($msgsock, $talkback, strlen($talkback));
C2:18         		}									
C2:19		} else {
C2:20			echo "Database Integrity NOT OK!\n";
C2:21			$talkback = "DENIED\r\n";
C2:22                       socket_write($msgsock, $talkback, strlen($talkback));
C2:23           	}
I am looking for someone to help me find the fault or the error...because through the line number C2:00 to C2:23 I keep getting the message:

Database Created!
Database Integrity NOT OK

The integrity not good comes from whithin the initialize() return a false which tells me Database Integrity is NOT OK.

What I noticed within initialize() function returning false is that after it creates the MasterDatabase for the first time, it tries to also create the MasterTable, but the query returns false.

I dont' know why.

I wish someone can help me, please.

What I think is that I am using the query statements the wrong way, that's probably the problem and I need someone to help me find the fault.

Thanks.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 9:26 am
by Celauran
You're creating a database, but never telling your connection to use it. You're trying to create a table with no columns, so MySQL is returning an error. Rather than simply returning false, it might be helpful to capture PDO errors, log them, and throw some exceptions.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 9:32 am
by Celauran
If you were to replace C1:87 with the following two lines, the error should go away.

Code: Select all

parent::db_query('use ' . parent::__get('DBNAME'));
$db_master_create_table = parent::db_query("CREATE TABLE IF NOT EXISTS MasterTable (id INT(11))");
Although that creates some rigidity in your primary key, which isn't particularly desirable.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 9:47 am
by Celauran
A few other small things, not directly related to this problem, but that I noticed while looking through your code.
  • You have several calls to parent:: where $this-> would work just as well
  • You're calling your get magic method directly. $this->DBHOST will return parent::$dbhost, which is the whole point of the magic method.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 10:48 am
by PHPHelpNeeded
thank you I fixed the some of it.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 11:21 am
by PHPHelpNeeded
this is an improvement, I can see that the database is there.

But now I am getting error number 42502, which means "The authorization ID does not have the privilege to perform the operation as specified."

How can I give myself privileges...I defined my MySQL instance with root (username) and password. But I didn't get any option to give myself privileges.

Now I did notice that I had the option to create an user, but when I created one, the Instance Creation Wizard was not able to create the user so I had to skip that option.

Does that mean I need to create an user, or that I should give my root (username) some privileges?

I think I can find the code, hold on.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 11:52 am
by PHPHelpNeeded
I don't understand, I check in the MySQL instance server whether all users I have created have privileges, and they have full privileges, but yet I am getting error number 42502

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 12:19 pm
by Celauran
You shouldn't need to assign privileges to root; root has all privileges. How are you connecting to MySQL? Are you able to execute the same commands successfully if you're logged in through the MySQL CLI? That will at least tell us if it's a MySQL configuration error or a PHP error.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 12:25 pm
by PHPHelpNeeded
My problem Is finding the sql query syntax...I am getting the hang of this...

Thanks.

Re: need help finding fault on mysql query statement code

Posted: Sat Nov 29, 2014 12:26 pm
by PHPHelpNeeded
Celauran wrote:You shouldn't need to assign privileges to root; root has all privileges. How are you connecting to MySQL? Are you able to execute the same commands successfully if you're logged in through the MySQL CLI? That will at least tell us if it's a MySQL configuration error or a PHP error.
Yeah, but I had to go inside the MySQL server and create the table manually to be able to see use.

I was being ask for privileges using the query() function.

But now I found the right syntax...hopefully I don't lose all my data in the future for making a mistake.

Thanks for the help.