Doubts concerning a general Insert method

Hello, I'm trying to understand a general CRUD class that I've seen =
here:
http://www.phpro.org/classes/PDO-CRUD.html

I'm learning PHP and I have some doubts on this method to generally =
insert
data into DB. The class name is crud and here is the method:=20

public function dbInsert($table, $values) {

$this->conn();

$fieldnames =3D array_keys($values[0]);

$size =3D sizeof($fieldnames);
=09
$i=3D1;
=09
//construction of the prepared statment
$sql =3D "INSERT INTO $table";

$fields =3D '( ' . implode(' ,', $fieldnames) . ' )';

$bound =3D '(:' . implode(', :', $fieldnames) . ' )';

$sql .=3D $fields.' VALUES '.$bound;
=09
//prepares statement e saves it on variable $stmt
$stmt =3D $this->db->prepare($sql);

foreach($values as vals)
{
$stmt->execute($vals);
}
}


To place values on the DB we do:

$crud =3D new crud();

$values =3D array
(
array('animal_name'=3D>'bruce', 'animal_type'=3D>'dingo'),
array('animal_name'=3D>'bruce', 'animal_type'=3D>'kangaroo'),
);

$crud->dbInsert('animals', $values);





The doubts:
1) Names convention question:=20
Isn't more correct to call $columname, instead of $fieldname ?=20

2) Why do we have this?
$i=3D1 =20


3) Here:=20
$fieldnames =3D array_keys($values[0]);

We are keeping on variable $fieldnames, the key value of the $values =
array,
when this array is on the position 0 ? And what is *actually* the value
returned, considering our array?
=20
$values =3D array
(
array('animal_name'=3D>'bruce', 'animal_type'=3D>'dingo'),
array('animal_name'=3D>'bruce', 'animal_type'=3D>'kangaroo'),
);


4) Here:
foreach($values as $vals)
{
$stmt->execute($vals);
}

We are telling that, for each (line/element/index ???) of $values array, =
the
actual value will be "given"(?) to vals, and the pointer goes to the =
next
(line/element/index)... ?

We then execute the prepared statement, but I don't get what are we =
passing
as a param? I mean, what kind of think does the execute PDO method =
expects
as a param?=20
Why $stmt->execute($vals); and not only $stmt->execute(); ?


Can I please have your help on clarifying those doubts?


Thanks a lot,
M=E1rcio


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Doubts concerning a general Insert method

2009/7/13 MEM :
> Hello, I'm trying to understand a general CRUD class that I've seen here:
> http://www.phpro.org/classes/PDO-CRUD.html
>
> I'm learning PHP and I have some doubts on this method to generally inser=
t
> data into DB. The class name is crud and here is the method:
>
> public function dbInsert($table, $values) {
>
>        $this->conn();
>
>        $fieldnames =3D array_keys($values[0]);
>
>        $size =3D sizeof($fieldnames);
>
>        $i=3D1;
>
>        //construction of the prepared statment
>        $sql =3D "INSERT INTO $table";
>
>        $fields =3D '( ' . implode(' ,', $fieldnames) =
.. ' )';
>
>        $bound =3D '(:' . implode(', :', $fieldnames) =
.. ' )';
>
>        $sql .=3D $fields.' VALUES '.$bound;
>
>        //prepares statement e saves it on variable $s=
tmt
>        $stmt =3D $this->db->prepare($sql);
>
>        foreach($values as vals)
>        {
>                $stmt->execute($va=
ls);
>        }
> }
>
>
> To place values on the DB we do:
>
> $crud =3D new crud();
>
> $values =3D array
>            (
>                array('animal_name=
'=3D>'bruce', 'animal_type'=3D>'dingo'),
>                array('animal_name=
'=3D>'bruce', 'animal_type'=3D>'kangaroo'),
>            );
>
> $crud->dbInsert('animals', $values);
>
>
>
>
>
> The doubts:
> 1) Names convention question:
> Isn't more correct to call $columname, instead of $fieldname ?

The two terms are interchangeable in the context of a database.

> 2) Why do we have this?
>  $i=3D1

It's not used so I'd guess it's a remnant from an older version of the
method. Safe to remove it.

> 3) Here:
> $fieldnames =3D array_keys($values[0]);
>
> We are keeping on variable $fieldnames, the key value of the $values arra=
y,
> when this array is on the position 0 ? And what is *actually* the value
> returned, considering our array?
>
> $values =3D array
>            (
>                array('animal_name=
'=3D>'bruce', 'animal_type'=3D>'dingo'),
>                array('animal_name=
'=3D>'bruce', 'animal_type'=3D>'kangaroo'),
>            );

You can use the var_dump function to dump the contents of $fieldnames
after that line has been executed and see for yourself.

In this case let's break it down...

$values[0] will give you the first element of $values, namely
array('animal_name'=3D>'bruce', 'animal_type'=3D>'dingo').

array_keys will return an array containing the keys from the
passed array, so in this case you'll get array('animal_name',
'animal_type').

> 4) Here:
> foreach($values as $vals)
>        {
>                $stmt->execute($va=
ls);
>        }
>
> We are telling that, for each (line/element/index ???) of $values array, =
the
> actual value will be "given"(?) to vals, and the pointer goes to the next
> (line/element/index)... ?
>
> We then execute the prepared statement, but I don't get what are we passi=
ng
> as a param? I mean, what kind of think does the execute PDO method expect=
s
> as a param?
> Why $stmt->execute($vals); and not only $stmt->execute(); ?

After it's finished building $sql use var_dump to look at it. You'll
see that the values are specified as :animal_name and :animal_type.
The : indicates to PDO that these are replaceable values.

The foreach will go through the $values array and for each row it will
pass the data (e.g. array('animal_name'=3D>'bruce',
'animal_type'=3D>'dingo') for the first time round the loop) to the
execute function which will effectively replace those elements in the
SQL statement and execute it.

For more info I suggest you Google for "PDO prepared statements" for
further reading.

-Stuart

--=20
http://stut.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Doubts concerning a general Insert method

> $values[0] will give you the first element of $values, namely
> array('animal_name'=3D>'bruce', 'animal_type'=3D>'dingo').
>=20
> array_keys will return an array containing the keys from the
> passed array, so in this case you'll get array('animal_name',
> 'animal_type').
>

So... since $value is an associate array of arrays, we will get, on the =
first key, not an array with "0, 1", like array(0,1); but =
array('animal_name','animal_type'), yes?
When we use the implode over this array, we get:
animal_name, animal_type that is the string that will pass to be prepare =
using the PDO prepare().
=20
=20
> After it's finished building $sql use var_dump to look at it. You'll
> see that the values are specified as :animal_name and :animal_type.
> The : indicates to PDO that these are replaceable values.

Yes. And normally, to fill those replaceable values, I was used to use =
bindParam();
I like this bindParam method because we can then use PDO::PARAM_INT and =
PDO::PARAM_STR to more accurately control the data type flow...

>=20
> The foreach will go through the $values array and for each row it will
> pass the data (e.g. array('animal_name'=3D>'bruce',
> 'animal_type'=3D>'dingo') for the first time round the loop) to the
> execute function which will effectively replace those elements in the
> SQL statement and execute it.

Ok, so:
Our $sql will be: INSERT INTO $table (animal_name, animal_type) VALUES =
(:animal_name, :animal_type)=20

We then prepare this $sql by doing:=20
prepare($sql); and the value of this preparation will be kept on a =
variable name $stmt.

Finally, on the foreach, we will grab each value of the $values array, =
and keep him, on a variable called $vals,=20

The $vals will contain this on the first occurrence of the loop:
array('animal_name'=3D>'bruce', 'animal_type'=3D>'ding')

and then, the var $vals will have this on the second occurrence of the =
loop:
array('animal_name'=3D>'bruce', 'animal_type'=3D>'kanguro')

etc.,

At the end of each of these loops, we will process the execute (that =
will send the statement to the database).
$stmt->execute(array('animal_name'=3D>'bruce', =
'animal_type'=3D>'kanguro').

So this execute will do A LOT, it will take away the 'array(' part, will =
see the keys of these arrays (e.g. animal_name and animal_type) compare =
them with the placeholder names given on the prepare statement and, =
replace the placeholder names with the values inside on each of this =
array keys.


Is this correct?



Regards,
Márcio




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Doubts concerning a general Insert method

2009/7/13 MEM :
>
>>     $values[0] will give you the first element of $values, nam=
ely
>> array('animal_name'=3D>'bruce', 'animal_type'=3D>'dingo').
>>
>>     array_keys will return an array containing the keys from t=
he
>> passed array, so in this case you'll get array('animal_name',
>> 'animal_type').
>>
>
> So... since $value is an associate array of arrays, we will get, on the f=
irst key, not an array with "0, 1", like array(0,1); but array('animal_name=
','animal_type'), yes?
> When we use the implode over this array, we get:
> animal_name, animal_type that is the string that will pass to be prepare =
using the PDO prepare().

Indeed.

>> After it's finished building $sql use var_dump to look at it. You'll
>> see that the values are specified as :animal_name and :animal_type.
>> The : indicates to PDO that these are replaceable values.
>
> Yes. And normally, to fill those replaceable values, I was used to use bi=
ndParam();
> I like this bindParam method because we can then use PDO::PARAM_INT and P=
DO::PARAM_STR to more accurately control the data type flow...

I'm not overly familiar with PDO, but I believe that's an alternative
way to do it. The execute method lets you do it in one method call.

>> The foreach will go through the $values array and for each row it will
>> pass the data (e.g. array('animal_name'=3D>'bruce',
>> 'animal_type'=3D>'dingo') for the first time round the loop) to the
>> execute function which will effectively replace those elements in the
>> SQL statement and execute it.
>
> Ok, so:
> Our $sql will be: INSERT INTO $table (animal_name, animal_type) VALUES (:=
animal_name, :animal_type)
>
> We then prepare this $sql by doing:
> prepare($sql); and the value of this preparation will be kept on a variab=
le name $stmt.
>
> Finally, on the foreach, we will grab each value of the $values array, an=
d keep him, on a variable called $vals,
>
> The $vals will contain this on the first occurrence of the loop:
> array('animal_name'=3D>'bruce', 'animal_type'=3D>'ding')
>
> and then, the var $vals will have this on the second occurrence of the lo=
op:
> array('animal_name'=3D>'bruce', 'animal_type'=3D>'kanguro')
>
> etc.,
>
> At the end of each of these loops, we will process the execute (that will=
send the statement to the database).
> $stmt->execute(array('animal_name'=3D>'bruce', 'animal_type'=3D>'kanguro'=
).
>
> So this execute will do A LOT, it will take away the 'array(' part, will =
see the keys of these arrays (e.g. animal_name and animal_type) compare the=
m with the placeholder names given on the prepare statement and, replace th=
e placeholder names with the values inside on each of this array keys.
>
>
> Is this correct?

Indeed.

-Stuart

--=20
http://stut.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Doubts concerning a general Insert method

Nice. :-) Thanks a lot Stuart for your time and explanations.=20
Now that I have understand, I will try to move on, and understand how =
can we introduce bindParams on it:

For a recall, here is the original class:

> public function dbInsert($table, $values) {
>
> $this->conn();
>
> $fieldnames =3D array_keys($values[0]);
>
> $size =3D sizeof($fieldnames);
>
> $i=3D1;
>
> //construction of the prepared statment
> $sql =3D "INSERT INTO $table";
>
> $fields =3D '( ' . implode(' ,', $fieldnames) . ' )';
>
> $bound =3D '(:' . implode(', :', $fieldnames) . ' )';
>
> $sql .=3D $fields.' VALUES '.$bound;
>
> //prepares statement e saves it on variable $stmt
> $stmt =3D $this->db->prepare($sql);
>
> foreach($values as vals)
> {
> $stmt->execute($vals);
> }
> }


However I do have some questions that maybe someone more experimented =
then me could easily solve:

1)
The bindParams should look similar to this:

$stmt->bindParam(':animal_name', $animals->getName(), PDO::PARAM_STR );
$stmt->bindParam(':animal_type', $animals->getType(), PDO::PARAM_STR );

So, instead of looping trough an array of values, I will to do it for =
objects, something like:
foreach($animals->listaAnimals() as $row) ...

Can I have some words on this so that I can properly try to add =
bindParam on this class method.

2)
I also need to have a way to add PDO::PARAM_STR if the values is a =
string or PDO::PARAM_INT if the values is int, PDO::PARAM_BOOL etc...=20
Is there a way to control this? Using something like is_integer() and =
is_string(), inside if statement perhaps? If so, what about the Boolean?


Thanks a lot,
Márcio







--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Doubts concerning a general Insert method

Ok... according to the above posts, I've started to create my generic =
CRUD class however, I'm wondering:

Any of you have already used a DAO design pattern in conjunction with a =
CRUD generic class?=20
Know that I'm trying to create a generic CRUD class on a DAO Design =
pattern, it seems that it makes no sense at all.

Is there an advantage on doing this?

My thought:
On a "insert to database" scenario for example:
Without the CRUD generic class: to do an insert, we instantiate a DAO =
class and then call the insert method. Nothing more. Done.

With the CRUD generic class: maybe we will have less code on the DAO =
site but, at the end, to insert a record, we still need to instantiate a =
DAO and call a insert method SO...

I see not big advantage on using both...

Can I have your advice on this please?


Thanks,
Márcio


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php