Inserting to database with OOP

[Clandestinex337]

Hello, here is my code so far.

 

<?php
include 'config_fns.php';

class MySql
{
	private $host;
	private $user;
	private $password;
	private $database;
	public  $db_handle;

	public function connect($db_host, $db_user, $db_password, $db_database)
	{
		$this->host = $db_host;
		$this->user = $db_user;
		$this->passowrd = $db_password;
		$this->database = $db_database; 

		$this->db_handle = mysql_connect($this->host, $this->user, $this->password)or die('could not connect to db');
		mysql_select_db($this->database)or die('could not select db');
	}

	public function query($sql)
	{
		return mysql_query($sql,$this->db_handle);
	}

	public function fetch($sql)
	{
		return mysql_fetch_assoc($this->query($sql));
	}
}

$connect = new MySql();
$connect->connect($db_host, $db_username, $db_password, $db_name);

class CreateUser
{
	var $username;
	var $password;

	public function setUsername($name)
	{
		$this->username = $name;
	}

	public function setPassword($password)
	{
		$this->password = $password;
	}
}

$newUser = new CreateUser();
$newUser->setUsername('devyn');
$newUser->setPassword('password');
$mysql = new MySql();
$mysql->query("INSERT INTO `user` WHERE `username` = ???")
print_r($newUser);
?>

 

I am stuck at the $mysql->query("INSERT")

 

I am able to see with the print_r that they are in an array and retrieving the information for username and password, I am just not sure how to pass that into the sql query.

 

Also, I noticed when I put private $username private $password I get this with the print_r

CreateUser Object ( [username:CreateUser:private] => devyn [password:CreateUser:private] => password )

 

And when I use the var $username var $password I get this

CreateUser Object ( [username] => devyn [password] => password )

 

is there a difference?

 

Thanks

[Clandestinex337]

I tried

$mysql->query("INSERT INTO `user` WHERE `username` = $newUser[username], `password` = $newPassword[password]");

 

but I got Fatal error: Cannot use object of type CreateUser as array in

[gizmola]

You probably want to take a look at this:

 

$this->passowrd = $db_password;

 

 

[Clandestinex337]

You probably want to take a look at this:

 

$this->passowrd = $db_password;

 

 

 

Thanks for the catch, I fixed it, but its still gives me the error

[Clandestinex337]

I changed the code to

 

$mysql->query("INSERT INTO `user` WHERE `username` = $CreateUser[username], `password` = $CreateUser[password]");

 

and now I am getting this error instead  Warning: mysql_query() expects parameter 2 to be resource, null

 

hrmm..

[trq]

Surely you would want to put your actual query code within the CreateUser class?

[Clandestinex337]

sorry for my ignorance, but would you be able to help with an example?

 

 

edit:

 

class CreateUser
{
	var $username;
	var $password;

	public function setUsername($name)
	{
		$this->username = $name;
	}

	public function setPassword($password)
	{
		$this->password = $password;
	}

	public function query($sql)
	{
		$sql = "INSERT INTO `user` WHERE `username` = $this->username, `password` = $this->password";
	}
}

 

like so?

 

and if so, how would I get that function to connect to the query function is the MySql class.

[gizmola]

Well in the code you have you make a mysql class instance and call it $connection, and then you call $connection->connect(...);  Alll good there.  But then you seem to forget about that and make a new instance of the mysql class, and you don't connect, but try and query with it.  Just use the $connection you already made previously.

 

 

While we're picking at problems your insert syntax is way wrong.  There is a weird SET= syntax but I don't recommend that at all.  The syntax you should use is:

 

 

INSERT INTO TABLE (column) VALUES (value)

 

Your statement would be:

 

$mysql->query("INSERT INTO `user` (`username`, `password`) VALUES ({$CreateUser['username']}, {$CreateUser['password']})");

 

However that doesn't in any way match your classes or the code you showed before.  It might give you insight into the syntax at least.

 

[Clandestinex337]

Thanks for the reply, and tips. That helps out a lot.

 

This is the mysql_error(); I am getting now after the changes.

 

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 1CreateUser Object ( [username:CreateUser:private] => devyn [password:CreateUser:private] => password )

 

this is what I changed

 

$newUser = new CreateUser();
$newUser->setUsername('devyn');
$newUser->setPassword('password');
$connect->query("INSERT INTO `user` (`username`, `password`) VALUES ({$CreateUser['username']}, {$CreateUser['password']})");
echo mysql_error();
print_r($newUser);

 

and after what thorpe said, it makes sense to have the sql query to be in the class that is using it. Would you mind helping me implement that in my current code? I posted the function in the post above, just let me know if I am on the right track

 

edit: or instead of making the query a function in the CreateUser class, I can just make it a public variable?

 

public $sql = "INSERT INTO `user` (`username`, `password`) VALUES ({$CreateUser['username']}, {$CreateUser['password']})";

[gizmola]

This syntax is just completely wrong.

$connect->query("INSERT INTO `user` (`username`, `password`) VALUES ({$CreateUser['username']}, {$CreateUser['password']})");

 

You don't have a variable $CreaterUser, you have a class, and you made an instance $newUser.  You need to use that instance to access the variables.

 

 

$connect->query("INSERT INTO `user` (`username`, `password`) VALUES ('$newUser->username', '$newUser->password')");

 

Yes, I could rewrite things the way Thorpe is suggesting, and that would be good, but I think you need to get clarity on how php oop works before you start trying to understand dependency injection

 

[trq]

Your pretty close. Instead of making specific CreateUser class though, I would more than likely create something a little more generic.

 

class User
{
  private $db;
  private $id;
  private $username;
  private $password;

  public function __construct(MySql $db, $id = 0) {
    $this->db = $db;
    $this->id = $id;
    if ($this->id != 0) {
      $results = $this->db->query("
        SELECT username, password FROM users WHERE id = '{$this->id}'
      ");
      if ($results) {
        $this->username = $results->username;
        $this->password = $results->password;
      }
    }
  }
      
  public function setUsername($name) {
    $this->username = $name;
    return $this;
  }

  public function setPassword($pass) {
    $this->password = $pass;
    return $this;
  }

  public function getUserName() {
    return $this->username;
  }

  public function getPassword() {
    return $this->password;
  }

  public function save() {
    if ($this->id = 0) {
      $id = $this->db->query("
        INSERT INTO users (username, password
        ) VALUES (
        '{$this->username}', '{$this->password}'
      ");
      $this->id = $id;
    } else {
      $this->db->query("
        UPDATE users
        SET username = '{$this->username}', password = '{$this->password}'
        WHERE id = '{$this->id}'
      ");
    }
    return $this->id;
  }
}

 

You can then use this to create and retrieve users.

 

$user = new User(new Mysql);
$user->setUsername('thorpe');
$user->setPassword('dontbecrazy');
$id = $user->save();

echo "New user {$user->getUsername()} created with an id of $id";

 

And to get user 101.

 

$user = new User(new Mysql, 101);
echo $user->getUsername();

 

Of course there is allot of things missing in this example, but this is a basic model. You would want to ensure that your database class itself takes care of sanitizing data, and you would very much want to have it implement some interface or Abstract class.

[Clandestinex337]

 

 

Yes, I could rewrite things the way Thorpe is suggesting, and that would be good, but I think you need to get clarity on how php oop works before you start trying to understand dependency injection

 

 

Thanks for the reply! And yea, I am learning it as I go. I can't read something and learn that way, I have to build something to fully get how it works

[Clandestinex337]

Hey Thorpe, thanks for the code you posted. That seems a lot more logical to put it that way

 

With that code, I am confused with $id = $this->db->query

 

Where did query come from? Is this based of my MySql class?

 

And also the public function __construct(MySql $db, $id = 0)

 

is the MySql based of the class above?

 

Thanks a lot..

[trq]

With that code, I am confused with $id = $this->db->query

 

Where did query come from? Is this based of my MySql class?

 

$this->db stores the database object that you pass in via the __construct(). In my example, I was using a fictional database class, you would need to modify the code somewhat to make it work with yours.

 

And also the public function __construct(MySql $db, $id = 0)

 

is the MySql based of the class above?

 

It's named the same, but obviously your class has a slightly different interface. See above.

[Clandestinex337]

Thanks again, I don't want to bother you to explain the code step by step. Would you happen to know a tutorials step by step on setting up an optimal class to do pretty much what you have there, but they go more in depth?

 

Thanks a lot, and I do appreciate the time to help me out.