# problems with MySQL, PHP, INSERT METHOD.



## CilVine (Apr 7, 2013)

Hi, One And All.

I have been playing around with a few classes for managing a login system, and have run into a small conundrum.

after creating the Database wrapper, other classes were also creeated, and everything seemed to be on course. However, within the database, and validation classes, there seems to be some sort of inconsistency. All of the methods created for Updating, deleting, and connecting to the database, are working. but, the insert method isn't.

The database connection seems fine. At least when I am using the update, delete, and read methods.

The table fields, are correct, since the RUD methods/queries are working, and returning results. The table fields are set to "not NULL". However, I tried to set them to null (Except for the ID value), but, that didn't work either.

I somehow seem to think that there is a problem with the Database, against the insert() method. I tried running an insert using a conventional PDO method, and the table created a new record (succeeded). Therefore, I am stumped as to whether it is the database set-up, or the validation class that is missing something.

I am using Apache, PHP7, and MySQL 5.7. I installed them separately on my development machine.

The code for the insert method (from "class DB") is thus:

public function insert($table, $fields = []) 
{ 
$keys = array_keys($fields);
$values = "";
$x = 1;

foreach($fields as $field) 
{ 
$values .= "?"; 
if($x < count($fields)) 
{ 
$values .= ", "; 
} 
$x++;
} 
$sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")"; 
if(!$this->query($sql, $fields)->error()) 
{ 
return true; 
} 
return false; 
} 
-----------------------------------------------------------------------------------------------------------------------------

When I run the "registration.php" script, I get this response: 
----------------------------------------------------------------
" I have been run

I have been run

There Was A Problem Creating An Account. "
------------------------------------------------------------------

As you can see, I have nested a few 'echo' s within the code.

The registration form is thus:

<?php

require_once("/central/init.php");

if(Input::exists()) 
{
if(Token::check(Input::get('token'))) 
{
echo "I have been run";

$validate = new Validation();

$validation = $validate->check($_POST, [
'username' => [
'required' => TRUE, 
'min' => 2, 
'max' => 20, 
'unique' => 'users',
], 
'password' => [
'required' => TRUE,
'min' => 6,
], 
'reTypePassword' => [
'required' => TRUE,
'matches' => 'password',
], 
'name' => [
'required' => TRUE,
'min' => 2, 
'max' => 50,
],
]);

if($validation->passed()) 
{
echo "I have been run";

// Register User.
$user = new User();

$salt = Hash::salt(32);

try {

$user->create([
'username' => Input::get('username'), 
'password' => Hash::make(Input::get('password'), $salt), 
'salt' => $salt, 
'name' => Input::get('name'), 
'joined' => date('Y-m-d H:i:s'), 
'group' => 1, 
]);

} catch(Exception $e) 
{

die($e->getMessage());

}

} else {
echo "I have been run";
// Output Errors.
foreach($validation->errors() as $error) 
{

echo $error, "
";

}

}

}

}

?>

REGISTRATION FORM

please enter your choice of username in below: 
"/>

please enter your first name in the space provided:
"/>

please enter your new password in field below:

please re-enter your password in field provided:

"/>

---------------------------------------------------------------------------------------------------------------------------

The validation class, is thus:

<?php

class Validation {

private $_passed = FALSE,

$_errors = [],

$_db = NULL;

public function __construct() 
{

$this->_db = DB::getInstance();

}

public function check($source, $items = []) 
{

foreach($items as $item => $rules) 
{

foreach($rules as $rule => $ruleValue) 
{

$value = trim($source[$item]);

$item = escape($item);

if($rule === "required" && empty($value)) 
{

$this->addError("{$item} is required");

} else if(!empty($value))
{

switch($rule) 
{

case "min":

if(strlen($value) < $ruleValue) 
{

$this->addError("{$item} must be a minimum of {$ruleValue} characters.");

}

break;

case "max":

if(strlen($value) > $ruleValue) 
{

$this->addError("{$item} must be a maximum of {$ruleValue} characters.");

}

break;

 case "matches":

if($value != $source[$ruleValue]) 
{

$this->addError("{$ruleValue} must match {$item}.");

}

break;

case "unique":

$check = $this->_db->get($ruleValue, array($item, '=', $value));

if($check->count()) 
{

$this->addError("{$item} already exists.");

}

break;

}

}

}

}

if(empty($this->_errors)) 
{

$this->_passed = true;

}

return $this;

}

private function addError($error) 
{

$this->_errors[] = $error;

}

public function errors() 
{

return $this->_errors;

}

public function passed() 
{

return $this->_passed;

}

}

?>

--------------------------------------------------------------------------------------------------------------------------

The User Class:

class User 
{

private $_db;

public function __construct($user = NULL) 
{

$this->_db = DB::getInstance();

}

public function create($fields = []) 
{

if(!$this->_db->insert("users", $fields)) 
{

throw new Exception("There Was A Problem Creating An Account.");

}

}

}


----------



## JiminSA (Dec 15, 2011)

```
public function insert($table, $fields = [])
{
    $keys = array_keys($fields);
    $values = "";
    $x = 1;

    foreach($fields as $field)
    {
        $values .= "?";
        if($x < count($fields))
        {
            $values .= ", ";
        }
        $x++;
    }
    $sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")";
    if(!$this->query($sql, $fields)->error())
    {
        return true;
    }
    return false;
}
```
As far as I can see, your insert function will always return false!
Why not put an else condition on your last if statement? Also you are not putting any data except commas, spaces and question marks into your $values field for insertion into your db?? Shouldn't $field replace the "?" within you foreach?


----------



## CilVine (Apr 7, 2013)

JiminSA said:


> ```
> public function insert($table, $fields = [])
> {
> $keys = array_keys($fields);
> ...


Hi, JimInSA (South Africa?).

Thanks for the reply.

I will try what you said.

So, what you are saying, is that, maybe, the "return False", should actually be placed within an "else" clause?

I had been looking at the "values", and asked myself a similar question, but, just wasn't too sure.

Thanks so much for the reply.


----------



## JiminSA (Dec 15, 2011)

CilVine said:


> JimInSA (South Africa?)


 Yes Johannesburg
I am sorry, but my 'else' suggestion is a load of twaddle - php return passes control back to the instruction following the function call immediately and will not drop through to return false as I inferred - my bad
I am curious to know what is in variable '$table' which is passed to but not used by the function - and also curious as to what $fields = [] does (I think it might be shorthand for $fields = array(), which initialises an array (sets it to null), in which case that may well be your answer - the function is processing a null array). But I don't think that should be the case. 
So let's find out what $fields = [] does. Would you mind putting in a temporary line at the head of your function, in order to do this - viz:

```
print_r($fields);echo "
";exit;
```
If you could report back on your findings? ...


----------



## CilVine (Apr 7, 2013)

Hi, Jim. 

Okay will try that, and come back with results.


----------



## CilVine (Apr 7, 2013)

JiminSA said:


> Yes Johannesburg
> I am sorry, but my 'else' suggestion is a load of twaddle - php return passes control back to the instruction following the function call immediately and will not drop through to return false as I inferred - my bad
> I am curious to know what is in variable '$table' which is passed to but not used by the function - and also curious as to what $fields = [] does (I think it might be shorthand for $fields = array(), which initialises an array (sets it to null), in which case that may well be your answer - the function is processing a null array). But I don't think that should be the case.
> So let's find out what $fields = [] does. Would you mind putting in a temporary line at the head of your function, in order to do this - viz:
> ...


Yes, "$field[]", should be the same as "$array()".


----------



## CilVine (Apr 7, 2013)

CilVine said:


> Yes, "$field[]", should be the same as "$array()".


Hi Jim.

Ran few print_r s within the insert method like so:

public function insert($table, $fields = []) 
{ echo ''; print_r($fields);echo '';

$keys = array_keys($fields);

$values = "";

$x = 1;

foreach($fields as $field) 
{

$values .= "{$field} = ?";

if($x < count($fields)) 
{

$values .= ", ";
echo ''; print_r($fields); echo '';
}

$x++;

echo ''; print_r($field); echo '';
}

$sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")";

echo ''; print_r($sql); echo '';
if(!$this->query($sql, $fields)->error()) 
{

return true;

}

return false;

} 
----------------------------------------------------------------------------------------------------------------------------------------------------------------
I then ran this query:

$user = DB::getInstance()->insert('users', array(
'username' => 'JohnDoe1234', 
'password' => 'password', 
'salt' => 'salt10000', 
'firstName' => 'John', 
'joined' => date('Y-m-d H:i:s'), 
'group' => 1, 
));

I got this response:

Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)
Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)

JohnDoe1234
Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)

password
Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)

salt10000
Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)

John
Array
(
[username] => JohnDoe1234
[password] => password
[salt] => salt10000
[firstName] => John
[joined] => 2015-12-28 03:08:06
[group] => 1
)

2015-12-28 03:08:06
1
INSERT INTO users (`username`, `password`, `salt`, `firstName`, `joined`, `group`) VALUES (JohnDoe1234 = ?, password = ?, salt10000 = ?, John = ?, 2015-12-28 03:08:06 = ?, 1 = ?)

--------------------------------------------------------------------------------------------------------------------------------------

Seems like there is a problem with "$sql". The whole statement is being constructed wrongly.


----------



## JiminSA (Dec 15, 2011)

According to PHP manual for mysqli_query, the syntax of the statement should include a db link as the first parameter. Something like this ...

```
$link = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");
INSERT INTO users ($link, `username`, `password`, `salt`, `firstName`, `joined`, `group`) VALUES ( ....
```
But the main problem with the generated statement is, as you pointed out, the VALUES syntax, which is derived largely from this code ...

```
foreach($fields as $field) 
{

  $values .= "{$field} = ?";

  if($x < count($fields)) 
  {

    $values .= ", ";
    echo ''; print_r($fields); echo '';
  }

  $x++;

  echo ''; print_r($field); echo '';
}

$sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")";
```
We need to change this so that $values is set up correctly in the mysqli statement, something like ...

```
foreach($fields as $field)
{

$values .= "'$field'";

if($x < count($fields))
{

$values .= ", ";
echo ''; print_r($fields); echo '';
}

$x++;

echo ''; print_r($field); echo '';
}

$sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")";
```
Note that I have enclosed $field with single quotes and taken out the = ?, which I guess was some sort of hint help, originally and escaped into the code!
Try that and see what ensues ...


----------



## CilVine (Apr 7, 2013)

Hi JimInSA.

I have tried the above, and the Query works. eg, :

$user = DataBases::getInstance()->insert('users', array(
'username' => 'JillDoe1234', 
'password' => 'password234', 
'salt' => 'salt10000', 
'firstName' => 'Jillie', 
'joined' => date('Y-m-d H:i:s'), 
'community' => 4, 
));

if($user) 
{
echo "Success!";
} else 
{
echo "There was a problem with this operation.";
}

--------------------------------------------- The above query returns:

INSERT INTO users (`username`, `password`, `salt`, `firstName`, `joined`, `community`) VALUES ('JillDoe1234', 'password234', 'salt10000', 'Jillie', '2015-12-28 08:36:36', '4')
Success!

---------------------------------------------------------------------
However, from my perspective, the problem is now 90% solved.

You see, If I run The Insert query as such: It gives the above feedback.

$user = DataBases::getInstance()->insert('users', array(
'username' => 'JillDoe1234', 
'password' => 'password234', 
'salt' => 'salt10000', 
'firstName' => 'Jillie', 
'joined' => date('Y-m-d H:i:s'), 
'community' => 4, 
));

if($user) 
{
echo "Success!";
} else 
{
echo "There was a problem with this operation.";
}

--------------------------------------------------------------------------------------------------------------------

However, the Update query, using the update method, when run as such:

$update = DataBases::getInstance()->update('users', 12, array(
'username' => 'JillDoe99', 
'password' => 'passwordnew', 
'salt' => 'salt122000', 
'firstName' => 'Jill', 
'joined' => date('Y-m-d H:i:s'),
));

if($update) 
{
echo "Success!";
} else 
{
echo "There was a problem with this operation.";
}

------------------------------------------------------------------

I get this result:

UPDATE users SET username = ?, password = ?, salt = ?, firstName = ?, joined = ? WHERE id = 12

Success!
-----------------------------------------------------------------

As you can see, the other methods are managing to bind the "?" to the value(s). Isn't this what the insert method is supposed to be doing?

I mean, adding this facet, to my code, should add an extra facet of security to my system: shouldn't it? Especially, considering the fact that the login, profile, and other core interactions will be using the database class.

-----------------------------------------------------------------

I know that I have sent it before, but, here is the update method, once again:

public function update($table, $id, $fields) 
{

$set = "";

$x = 1;

foreach($fields as $name => $value) 
{

$set .= "{$name} = ?";

if($x < count($fields)) 
{

$set .= ", ";

}

$x++;

}

$sql = "UPDATE " . $table . " SET " . $set . " WHERE id = " . $id; 
echo ''; print_r($sql);echo '';
if(!$this->query($sql, $fields)->error()) 
{

return true;

}

return false;

}


----------



## JiminSA (Dec 15, 2011)

As per the INSERT solution, try re-coding this line - $set .= "{$name} = ?";
to this
$set .= "'$name'";


----------



## CilVine (Apr 7, 2013)

Yep. But, I would actually prefer if the database query worked the same way as with the update method. The update method works the way it should, because the results are being binded to the "?". Which should be a more secure option. 

By removing the "= ?" from the insert method, the query works, but, what is stumpimg me, is why "{$name} = ?" works in the update method, whilst "{$fields} = ?" doesn't work in the insert method.


----------



## JiminSA (Dec 15, 2011)

Forgive my ignorance CilVine, I have never had the need to use the bind methodology and so did not recognise it is such - please allow me some time to investigate further (and educate myself), especially with the holiwols underway


----------



## CilVine (Apr 7, 2013)

JiminSA said:


> Forgive my ignorance CilVine, I have never had the need to use the bind methodology and so did not recognise it is such - please allow me some time to investigate further (and educate myself), especially with the holiwols underway


Okay. No probs.


----------



## JiminSA (Dec 15, 2011)

OK, I've done a little research and found the bind_param used in conjunction with mysqli prepare statement. Can you show me the code for both insert and update where the bind is declared?


----------



## CilVine (Apr 7, 2013)

The insert method:

public function insert($table, $fields = [])
{
$keys = array_keys($fields);
$values = "";
$x = 1;

foreach($fields as $field)
{
$values .= "'{$field}'"; // This is where the code works, when we remove the "{$field} = ?", and leave it as "'{$field}'".
if($x < count($fields))
{
$values .= ", ";
}
$x++;
}
$sql = "INSERT INTO users (`" . implode("`, `", $keys) . "`) VALUES (" . $values . ")";
if(!$this->query($sql, $fields)->error())
{
return true;
}
return false;
}

--------------------------------------------------------------------------------------------------------------

The update method:

public function update($table, $id, $fields)
{
$set = "";
$x = 1;
foreach($fields as $name => $value)
{
$set .= "{$name} = ?"; // It seems to work here, but not in the insert method equivalent.
if($x < count($fields))
{
$set .= ", ";
}
$x++;
}
$sql = "UPDATE " . $table . " SET " . $set . " WHERE id = " . $id;
echo ''; print_r($sql);echo '';
if(!$this->query($sql, $fields)->error())
{
return true;
}
return false;
}

-----------------------------------------------------------------------------------------------------------

Then, all I do, when committing queries, is:

(update method. Which actually works)

$update = DataBases::getInstance()->update('users', 12, array(
'username' => 'JillDoe223',
'password' => 'passwordchanged', 
'firstName' => 'Jillian',
));

If you look at the returned update query (in the browser), after it has successfully updated a record, you get this: 

UPDATE users SET username = ?, password = ?, salt = ?, firstName = ? WHERE id = 12 

So, what it is doing here, is actually associating, or binding each value to the "?", in a similar fashion as to the way prepared statements work. This should be the format that the insert method should be returning as the query within the browser.

----------------------------------------------------------------------------------------
(insert method)

$insert = DataBases::getInstance()->insert('users', array(
'username' => 'JillDoe99',
'password' => 'passwordnew',
'salt' => 'salt122000',
'firstName' => 'Jill',
'joined' => date('Y-m-d H:i:s'),
));

This query works when we replace the "'{$field} = ?" with the "'{$field}'" within the insert method. 

If you use the insert method with the $values = "'{field}'", instead of the $values = "{$field} = ?", the insert happens successfully. However, the resulting query, is thus:

(Query)
$insert = DataBases::getInstance()->insert('users', array(
'username' => 'JohnnyDoe44',
'password' => 'passwordJohnny44',
'salt' => 'salt446600000',
'firstName' => 'JohnnyD44',
'joined' => date('Y-m-d H:i:s'),
'group' => 2,
));

(RESULT)
INSERT INTO users (`username`, `password`, `salt`, `firstName`, `joined`, `group`) VALUES ('JohnnyDoe44', 'passwordJohnny44', 'salt446600000', 'JohnnyD44', '2016-01-03 16:53:26', '2')

(no "?" in place of each entry).

----------------------------------------------------------------------------------------------

When we return the "{$field} = ?" into the $values property of the insert method, this happens:

(QUERY)

$insert = DataBases::getInstance()->insert('users', array(
'username' => 'JohnnyDoe22', 
'password' => 'passwordJohnny', 
'salt' => 'salt6600000', 
'firstName' => 'JohnnyD', 
'joined' => date('Y-m-d H:i:s'), 
'group' => 1,

(RESULT)

INSERT INTO users (`username`, `password`, `salt`, `firstName`, `joined`, `group`) VALUES (JohnnyDoe22 = ?, passwordJohnny = ?, salt6600000 = ?, JohnnyD = ?, 2016-01-03 16:29:11 = ?, 1 = ?)
But, the record is not inserted into the database table.

---------------------------------------------------------------------------

I am missing something quite minor, here, But, I seem to be having problems figuring it out.


----------



## CilVine (Apr 7, 2013)

Just one more thing, Jim: The inserts, and update methods work alongside the 'query' method. So, you may need to check this out, as well. In fact, the database class that I included the first time round, pretty much uses the 'query', 'error', and __construct methods regularly throughout.


----------



## JiminSA (Dec 15, 2011)

There is a difference between the insert and the update code submitted to getInstance() ...
Insert passes 2 parameters and Update passes 3 (the record key is included).
Perhaps if you cater for the 3rd parameter with a double comma (i.e. empty) it may have a positive affect ...

```
$user = DB::getInstance()->insert('users', array( .......
becomes
$user = DB::getInstance()->insert('users',, array(
```


----------



## CilVine (Apr 7, 2013)

Yes, the Update method requires the id number, which is the reason why that method has an extra key. 

However, the insert method requires 2 arguments. 

Having tried your suggestion, I get a Syntax error in return.


----------



## JiminSA (Dec 15, 2011)

Worth a try though as I'm running short on ideas for a solution. But let's not give up!


----------



## CilVine (Apr 7, 2013)

Okay Jim. I will continue to tug away at it, and post any developments. Hadn't worked on this problem as much, over the last few days, because of my problem with session variables.


----------



## CilVine (Apr 7, 2013)

Nonetheless, Just to be more precise, the problem is actually solved. The whole point was that of finding a way of getting the insert method to insert rows into the database. we have solved that problem. However, the problem of applying the extra security measure is the remaining problem. 

You have helped me to think with a little more perspective, though.


----------

