Re: Loop with select and insert not working as expected
Thanks for the information... unfortunately I am still struggling to
understand this.
What it boils down to is that the following code never returns an
$order_id, even if there is a record in orders that has a student_id =
1
code:
$match_student_order =
"Order.student_id = '$student_id' ";
$order_id =
$this->Order->field("Order.id",
$match_student_order);
which generates:
13 SELECT `Order`.`id` FROM `orders` AS `Order` WHERE
`Order`.`student_id` = '1' LIMIT 1
So I must be coding it wrong... I tried klonae's suggestion, but that
didn't work - and neither did any variation on it that I tried... I
also took Pablo's advice and read the code behind Model::field() and
learned a lot that helped with other problems I was having, but I am
still stumped! Can anyone tell me what I am doing wrong?
On Aug 9, 5:04 pm, "Pablo Viojo" <[EMAIL PROTECTED]> wrote:
> Read:
>
> http://api.cakephp.org/1.2/class_model.html#029fcc94396edba60df5648e7...
>
> Or:
>
> http://api.cakephp.org/class_model.html#029fcc94396edba60df5648e7086e01d
>
> (Don't know which version are you using)
>
> Regards,
>
> Pablo
>
> On 8/9/07, kionae <[EMAIL PROTECTED]> wrote:
>
>
>
> > Have you tried $household_id = $this->Household->field("id",
> > array('id' => $match_userid)); instead? That seems to work for me...
>
> > On Aug 9, 1:34 pm, phalvrson <[EMAIL PROTECTED]> wrote:
> > > True - I changed to use the buit-in functions - ie save() but I still
> > > have the same problem. The problem is that the line "$household_id =
> > > $this->Household->field("id", $match_userid);" doesn't always issue
> > > it's underlying SQL SELECT `Household`.`id` FROM `households` AS
> > > `Household` WHERE `Household`.`userid` = '115603' statement - it
> > > seems to assume that id is already set correctly or something Can
> > > you please explain this behavior? I've tried inserting a
> > > "$this->Household->create()" prior to that statement to re-initialize
>
> > > things, but that seems to have no effect on this behavior
>
> > > Latest controller code:
>
> > > function process_csv_file()
> > > {
> > > $this->pageTitle = 'Process CSV File';
> > > $checkFileFields = Array ( "id", "Item", "Qty",
> > > "Household", "HHID",
> > > "FirstName", "LastName", "UserID", "Processed", "SchoolLocation",
> > > "HouseholdEmail", "Address1", "Address2", "City", "State", "Zip",
> > > "REFERENCE#2" );
> > > if (!empty($this->params['form'])) {
> > > $file_handle = fopen($this->params['form']['File']
> > > ['tmp_name'],"r");
> > > $label_fields = fgetcsv ($file_handle);
> > > $last_household_id = null;
> > > if ($checkFileFields == $label_fields) {
> > > $last_processed_student = null;
> > > $current_order_id = null;
> > > while (!feof($file_handle)) {
> > > $csv_fields = fgetcsv
> > > ($file_handle);
> > > /* Remember who we are processing
> > > - one order per student */
> > > $first_name = $csv_fields[5];
> > > $last_name = $csv_fields[6];
> > > $check_name = $first_name . ' ' .
> > > $last_name;
> > > /* Process the households db
> > > part. */
> > > $this_userid = $csv_fields[7];
> > > $match_userid = "Household.userid
> > > = '" . $this_userid . "'";
> > > $household_id =
> > > $this->Household->field("id", $match_userid);
> > > if (($household_id) ||
> > > ($this->Household->exists())) {
> > > /* Nothing to do -
> > > already have household id */
> > > } else {
> > >
> > > $this->data['Household']['last_name'] = $last_name;
> > >
> > > $this->data['Household']['HHID'] = $csv_fields[4];
> > >
> > > $this->data['Household']['userid'] = $this_userid;
> > >
> > > $this->data['Household']['email'] = $csv_fields[10];
> > >
> > > $this->data['Household']['address_1'] = $csv_fields[11];
> > >
> > > $this->data['Household']['address_2'] = $csv_fields[12];
> > >
> > > $this->data['Household']
Re: Loop with select and insert not working as expected
Read:
http://api.cakephp.org/1.2/class_model.html#029fcc94396edba60df5648e7086e01d
Or:
http://api.cakephp.org/class_model.html#029fcc94396edba60df5648e7086e01d
(Don't know which version are you using)
Regards,
Pablo
On 8/9/07, kionae <[EMAIL PROTECTED]> wrote:
>
> Have you tried $household_id = $this->Household->field("id",
> array('id' => $match_userid)); instead? That seems to work for me...
>
>
>
> On Aug 9, 1:34 pm, phalvrson <[EMAIL PROTECTED]> wrote:
> > True - I changed to use the buit-in functions - ie save() but I still
> > have the same problem. The problem is that the line "$household_id =
> > $this->Household->field("id", $match_userid);" doesn't always issue
> > it's underlying SQL SELECT `Household`.`id` FROM `households` AS
> > `Household` WHERE `Household`.`userid` = '115603' statement - it
> > seems to assume that id is already set correctly or something Can
> > you please explain this behavior? I've tried inserting a
> > "$this->Household->create()" prior to that statement to re-initialize
> >
> > things, but that seems to have no effect on this behavior
> >
> > Latest controller code:
> >
> > function process_csv_file()
> > {
> > $this->pageTitle = 'Process CSV File';
> > $checkFileFields = Array ( "id", "Item", "Qty",
> > "Household", "HHID",
> > "FirstName", "LastName", "UserID", "Processed", "SchoolLocation",
> > "HouseholdEmail", "Address1", "Address2", "City", "State", "Zip",
> > "REFERENCE#2" );
> > if (!empty($this->params['form'])) {
> > $file_handle = fopen($this->params['form']['File']
> > ['tmp_name'],"r");
> > $label_fields = fgetcsv ($file_handle);
> > $last_household_id = null;
> > if ($checkFileFields == $label_fields) {
> > $last_processed_student = null;
> > $current_order_id = null;
> > while (!feof($file_handle)) {
> > $csv_fields = fgetcsv
> > ($file_handle);
> > /* Remember who we are processing -
> > one order per student */
> > $first_name = $csv_fields[5];
> > $last_name = $csv_fields[6];
> > $check_name = $first_name . ' ' .
> > $last_name;
> > /* Process the households db part.
> > */
> > $this_userid = $csv_fields[7];
> > $match_userid = "Household.userid =
> > '" . $this_userid . "'";
> > $household_id =
> > $this->Household->field("id", $match_userid);
> > if (($household_id) ||
> > ($this->Household->exists())) {
> > /* Nothing to do - already
> > have household id */
> > } else {
> >
> > $this->data['Household']['last_name'] = $last_name;
> >
> > $this->data['Household']['HHID'] = $csv_fields[4];
> >
> > $this->data['Household']['userid'] = $this_userid;
> >
> > $this->data['Household']['email'] = $csv_fields[10];
> >
> > $this->data['Household']['address_1'] = $csv_fields[11];
> >
> > $this->data['Household']['address_2'] = $csv_fields[12];
> >
> > $this->data['Household']['city'] = $csv_fields[13];
> >
> > $this->data['Household']['state'] = $csv_fields[14];
> >
> > $this->data['Household']['zipcode'] = $csv_fields[15];
> > $this->Household->save(
> > $this->data);
> > $household_id =
> > $this->Household->getLastInsertID();
> > }
> > print_r($household_id);
> > print_r($this->Household->id);
> > /* Process the students db part. */
> > $match_student_name =
> > "Student.first_name = '$first_name' AND
> > Student.last_name = '$last_name' ";
> > $student_id =
> > $this->Student->field("id", $match_student_name);
> > if (($student_id) ||
> > ($this->Student->exists())) {
> >
Re: Loop with select and insert not working as expected
Have you tried $household_id = $this->Household->field("id",
array('id' => $match_userid)); instead? That seems to work for me...
On Aug 9, 1:34 pm, phalvrson <[EMAIL PROTECTED]> wrote:
> True - I changed to use the buit-in functions - ie save() but I still
> have the same problem. The problem is that the line "$household_id =
> $this->Household->field("id", $match_userid);" doesn't always issue
> it's underlying SQL SELECT `Household`.`id` FROM `households` AS
> `Household` WHERE `Household`.`userid` = '115603' statement - it
> seems to assume that id is already set correctly or something Can
> you please explain this behavior? I've tried inserting a
> "$this->Household->create()" prior to that statement to re-initialize
>
> things, but that seems to have no effect on this behavior
>
> Latest controller code:
>
> function process_csv_file()
> {
> $this->pageTitle = 'Process CSV File';
> $checkFileFields = Array ( "id", "Item", "Qty", "Household",
> "HHID",
> "FirstName", "LastName", "UserID", "Processed", "SchoolLocation",
> "HouseholdEmail", "Address1", "Address2", "City", "State", "Zip",
> "REFERENCE#2" );
> if (!empty($this->params['form'])) {
> $file_handle = fopen($this->params['form']['File']
> ['tmp_name'],"r");
> $label_fields = fgetcsv ($file_handle);
> $last_household_id = null;
> if ($checkFileFields == $label_fields) {
> $last_processed_student = null;
> $current_order_id = null;
> while (!feof($file_handle)) {
> $csv_fields = fgetcsv ($file_handle);
> /* Remember who we are processing -
> one order per student */
> $first_name = $csv_fields[5];
> $last_name = $csv_fields[6];
> $check_name = $first_name . ' ' .
> $last_name;
> /* Process the households db part. */
> $this_userid = $csv_fields[7];
> $match_userid = "Household.userid =
> '" . $this_userid . "'";
> $household_id =
> $this->Household->field("id", $match_userid);
> if (($household_id) ||
> ($this->Household->exists())) {
> /* Nothing to do - already
> have household id */
> } else {
>
> $this->data['Household']['last_name'] = $last_name;
>
> $this->data['Household']['HHID'] = $csv_fields[4];
>
> $this->data['Household']['userid'] = $this_userid;
>
> $this->data['Household']['email'] = $csv_fields[10];
>
> $this->data['Household']['address_1'] = $csv_fields[11];
>
> $this->data['Household']['address_2'] = $csv_fields[12];
>
> $this->data['Household']['city'] = $csv_fields[13];
>
> $this->data['Household']['state'] = $csv_fields[14];
>
> $this->data['Household']['zipcode'] = $csv_fields[15];
> $this->Household->save(
> $this->data);
> $household_id =
> $this->Household->getLastInsertID();
> }
> print_r($household_id);
> print_r($this->Household->id);
> /* Process the students db part. */
> $match_student_name =
> "Student.first_name = '$first_name' AND
> Student.last_name = '$last_name' ";
> $student_id =
> $this->Student->field("id", $match_student_name);
> if (($student_id) ||
> ($this->Student->exists())) {
> /* Nothing to do - already
> have student id */
> } else {
>
> $this->data['Student']['first_name'] = $first_name;
>
> $this->data['Student']['last_name'] = $last_name;
>
> $this->data['Student']['household_id'] = $household_id;
>
Re: Loop with select and insert not working as expected
True - I changed to use the buit-in functions - ie save() but I still
have the same problem. The problem is that the line "$household_id =
$this->Household->field("id", $match_userid);" doesn't always issue
it's underlying SQL SELECT `Household`.`id` FROM `households` AS
`Household` WHERE `Household`.`userid` = '115603' statement - it
seems to assume that id is already set correctly or something Can
you please explain this behavior? I've tried inserting a "$this-
>Household->create()" prior to that statement to re-initialize
things, but that seems to have no effect on this behavior
Latest controller code:
function process_csv_file()
{
$this->pageTitle = 'Process CSV File';
$checkFileFields = Array ( "id", "Item", "Qty", "Household",
"HHID",
"FirstName", "LastName", "UserID", "Processed", "SchoolLocation",
"HouseholdEmail", "Address1", "Address2", "City", "State", "Zip",
"REFERENCE#2" );
if (!empty($this->params['form'])) {
$file_handle = fopen($this->params['form']['File']
['tmp_name'],"r");
$label_fields = fgetcsv ($file_handle);
$last_household_id = null;
if ($checkFileFields == $label_fields) {
$last_processed_student = null;
$current_order_id = null;
while (!feof($file_handle)) {
$csv_fields = fgetcsv ($file_handle);
/* Remember who we are processing - one
order per student */
$first_name = $csv_fields[5];
$last_name = $csv_fields[6];
$check_name = $first_name . ' ' .
$last_name;
/* Process the households db part. */
$this_userid = $csv_fields[7];
$match_userid = "Household.userid = '"
. $this_userid . "'";
$household_id =
$this->Household->field("id", $match_userid);
if (($household_id) ||
($this->Household->exists())) {
/* Nothing to do - already have
household id */
} else {
$this->data['Household']['last_name'] = $last_name;
$this->data['Household']['HHID'] = $csv_fields[4];
$this->data['Household']['userid'] = $this_userid;
$this->data['Household']['email'] = $csv_fields[10];
$this->data['Household']['address_1'] = $csv_fields[11];
$this->data['Household']['address_2'] = $csv_fields[12];
$this->data['Household']['city'] = $csv_fields[13];
$this->data['Household']['state'] = $csv_fields[14];
$this->data['Household']['zipcode'] = $csv_fields[15];
$this->Household->save(
$this->data);
$household_id =
$this->Household->getLastInsertID();
}
print_r($household_id);
print_r($this->Household->id);
/* Process the students db part. */
$match_student_name =
"Student.first_name = '$first_name' AND
Student.last_name = '$last_name' ";
$student_id =
$this->Student->field("id", $match_student_name);
if (($student_id) ||
($this->Student->exists())) {
/* Nothing to do - already have
student id */
} else {
$this->data['Student']['first_name'] = $first_name;
$this->data['Student']['last_name'] = $last_name;
$this->data['Student']['household_id'] = $household_id;
$this->data['Student']['school_location'] = $csv_fields[9];
$this->Student->save(
$this->data);
$student_id =
$this->Student->getLastInsertID();
}
print
Re: Loop with select and insert not working as expected
On 8/8/07, phalvrson <[EMAIL PROTECTED]> wrote:
> The Household Model:
>
> class Household extends AppModel
> {
> var $name = 'Household';
> /* Creates new record in households and returns it's ID */
> function createRecord ($last_name, $HHID, $userid, $email,
> $address_1, $address_2, $city, $state, $zipcode)
> {
> $execString = "INSERT households SET ";
> $execString .= " last_name = '" . $last_name . "',";
> $execString .= " HHID = '" . $HHID . "',";
> $execString .= " userid = '" . $userid . "',";
> $execString .= " email = '" . $email . "',";
> $execString .= " address_1 = '" . $address_1 . "',";
> $execString .= " address_2 = '" . $address_2 . "',";
> $execString .= " city = '" . $city . "',";
> $execString .= " state = '" . $state . "',";
> $execString .= " zipcode = '" . $zipcode . "'";
> $this->execute($execString);
> return;
> }
> }
>
Why aren't you using the built-in Model functions? For what you are
doing there is no need for custom SQL queries.
--
Chris Hartjes
Senior Developer
Cake Development Corporation
My motto for 2007: "Just build it, damnit!"
@TheBallpark - http://www.littlehart.net/attheballpark
@TheKeyboard - http://www.littlehart.net/atthekeyboard
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Loop with select and insert not working as expected
I am running CakePHP v 0.2.9, Revision: 4409 and PHP 5
I have a function in a controller whose intent is to read a CSV file
and put the fields from each record into the appropriate database.
Several of the records can have the same Household information, so I
want to only create a new record if that Household is NOT already in
the database. If it is in the database, I need to get the
Household.id to put into some of the other tables as a foreign key
(household_id).
If I run the function process_csv_file() (copied below) with a file
that already contains some of the Households (identified by
Household.userid) -- but not all of the Households, the code seems to
stop issuing the following line. (Actually the first 2 records already
exist followed by several records for a new household. The new record
is created (INSERT) but the subsequent records for that same household
don't find their match and try to INSERT that household again.)
$household_id = $this->Household->field("id", $match_userid);
- actually it seems to know that this is the same Household and
doesn't bother to issue the SELECT statement - that leaves
$household_id empty and then the code tries to insert the Household
information again - which is wrong.
Here is the SQL dump output:
7 SELECT `Household`.`id` FROM `households` AS `Household` WHERE
`Household`.`userid` = '115603' LIMIT 1
8 SELECT `Household`.`id` FROM `households` AS `Household` WHERE
`Household`.`userid` = '126448' LIMIT 1
9 SELECT `Household`.`id` FROM `households` AS `Household` WHERE
`Household`.`userid` = '136285' LIMIT 1
10 INSERT households SET last_name = 'Bradford', HHID = '21550',
userid = '136285', email = '[EMAIL PROTECTED]', address_1 = '1
Anystreet Ave', address_2 = '', city = 'Tucson', state = 'AZ', zipcode
= '85730'
11 INSERT households SET last_name = 'Bradford', HHID = '21550',
userid = '136285', email = '[EMAIL PROTECTED]', address_1 = '1
Anystreet Ave', address_2 = '', city = 'Tucson', state = 'AZ', zipcode
= '85730' 1062: Duplicate entry '21550' for key 2 1
12 INSERT households SET last_name = 'Bradford', HHID = '21550',
userid = '136285', email = '[EMAIL PROTECTED]', address_1 = '1
Anystreet Ave', address_2 = '', city = 'Tucson', state = 'AZ', zipcode
= '85730' 1062: Duplicate entry '21550' for key 2 1
13 INSERT households SET last_name = 'Bradford', HHID = '21550',
userid = '136285', email = '[EMAIL PROTECTED]', address_1 = '1
Anystreet Ave', address_2 = '', city = 'Tucson', state = 'AZ', zipcode
= '85730' 1062: Duplicate entry '21550' for key 2
The controller:
class OrdersController extends AppController
{
var $name = 'Orders';
var $uses = array('Order','OrderItem', 'Household', 'Student',
'Product');
function process_csv_file()
{
$this->pageTitle = 'Process CSV File';
$checkFileFields = Array ( "id", "Item", "Qty", "Household",
"HHID",
"FirstName", "LastName", "UserID", "Processed", "SchoolLocation",
"HouseholdEmail", "Address1", "Address2", "City", "State", "Zip",
"REFERENCE#2" );
if (!empty($this->params['form'])) {
$file_handle = fopen($this->params['form']['File']
['tmp_name'],"r");
$label_fields = fgetcsv ($file_handle);
$last_household_id = null;
if ($checkFileFields == $label_fields) {
$last_processed_student = null;
$current_order_id = null;
while (!feof($file_handle)) {
$csv_fields = fgetcsv ($file_handle);
/* Remember who we are processing - one
order per student */
$first_name = $csv_fields[5];
$last_name = $csv_fields[6];
$check_name = $first_name . ' ' .
$last_name;
/* Process the households db part. */
$this_userid = $csv_fields[7];
$match_userid = "Household.userid = '"
. $this_userid . "'";
$household_id =
$this->Household->field("id", $match_userid);
if ($household_id) {
/* We have the household.id
now, that's all we need. */
print_r($household_id);
} else {
$this->Household->createRecord(
$last_name, $csv_fields[4],
$this_userid, $csv_fields[10], $csv_fields[11], $csv_fields[12],
$csv_fields[13], $csv_fields[14], $csv_fields[15]);
