Re: findCount SQL errors
Well, I got it to work. For all those who will run into this problem in the future, this is what I gathered. When you are using HABTM, it is very tricky to make manual saving of data successful. After you do it once, it seems to make sense With my data variables set for my classifieds, all that was left was to save the information into the join tables. Here is what I added: $this->data['Edition']['Edition']['id'] = (string)$ad->editions; $this->data['Category']['Category']['id'] = (string)$ad->class; I do not set these to the xmlID because, cake is smart enough to know that the id of the classified ad was already in the first array above these two. The way I have my join tables set up should be obvious but, here it is: classifieds_editions -classified_id -edition_id categories_classifieds -category_id -classified_id So, if you follow the naming convention to the "T", it will work out in the end. Cake says: I have a classified array and I have a classified table, insert this stuff but, I see that we have an edition array and it is Edition/ Edition so this is a join table. The join table I am looking for would be classifieds_editions since I already have a classified array. So, the id for editions is being set, so that would be editions_id, now let me grab the id for the array that I have already which would be classified_id. Oh, look, I have another array called category. It's naming convention is Category/Category so, I already have classified. Which comes first in the alphabet soup? Category does so, the table I am looking for is categories_classifieds. So, I have the id for classifieds so, now I just need an id for category. Oh, there it is in Category/Category/id. /insert /wash hands /repeat The array that is being passed looks like this: Array { [Classified] => Array { [id] => '1' [...] } [Edition] => Array { [Edition] => Array { [id] => 'B,E,I,N,Y' } } [Category] => Array { [Category] => Array { [id] => '0650' } } } Very neat stuff. I can't stress enough following the naming conventions. If you are having any problems, this would be the very first place I would start looking. My final code for this function looks like this: function classified_xml(){ // Perform these actions if a file was actually uploaded if (!empty($this->params['form']) && is_uploaded_file($this- >params['form']['File']['tmp_name'])){ move_uploaded_file($this->params['form']['File'] ['tmp_name'], "/var/www/localhost/htdocs/app/tmp/".$this- >params['form']['File']['name']); $this->xml_data = $this->AdminTool->parse_classified("/var/ www/localhost/htdocs/app/tmp/".$this->params['form']['File']['name'], $this->params['form']['File']['size']); $this->xml_data = $this->AdminTool->fix_editions($this- >xml_data); $this->xml_data = $this->AdminTool->fix_dates($this- >xml_data); $this->xml_data = $this->AdminTool->fix_pictures($this- >xml_data); $this->xml_data = $this->AdminTool->fix_text($this- >xml_data); $this->Classified->recursive = 0; $this->Classified->findAll(); $this->xmlID = $this->Classified->getNumRows(); ++$this->xmlID; $this->MrClean = new Sanitize(); foreach($this->xml_data as $ad){ $this->matchResult = $this->Classified- >findCount("Classified.text = '". addslashes($ad->text)."' AND Classified.editions = '$ad- >editions' AND Classified.category = '$ad->class'"); if($this->matchResult == 0){ $this->data['Classified']['id'] = (integer)$this->xmlID; $this->data['Classified']['itemno'] = (integer)$ad- >itemno; $this->data['Classified']['acctcode'] = (string)$ad- >acctcode; $this->data['Classified']['category'] = (string)$ad- >class; $this->data['Classified']['table'] = $ad->table; $this->data['Classified']['start'] = $ad->start; $this->data['Classified']['end'] = $ad->end; $this->data['Classified']['editions'] = (string)$ad- >editions; $this->data['Classified']['specials'] = (string)$ad- >specials; $this->data['Classified']['dispname'] = (string)$ad- >dispname; $this->data['Classified']['dispnote'] = (string)$ad- >dispnote; $this->data['Classified']['disptag'] = (string)$ad- >disptag; $this->data['Classified']['dispcols'] = (integer)$ad- >dispcols; $this->data['Classified']['text'] = $ad->text; $this->data['Edition']['Edition']['id'] = (string)$ad- >editions; $this->data['Category']['Category']['id'] = (s
Re: findCount SQL errors
Ok, as it turns out, the array that AD7six suggested did not work for me personally with or without addslashes(), magic quotes on or off or any variation therein. That is not to say that it doesn't work, it just did not make a correct comparison for my purposes. On the other hand, bernardo, your suggestion did work and it works flawlesly as I had wanted. I get 0 double entries out of 3 records. I went ahead and left magic_quotes on for now. I like not having to deal with it. Funny thing is, when I turned it off and used cake to do my save without sanitizing for SQL, MySQL took the data, apostrophes, ampersands and all without me having to specifically addslashes before the save. I am guessing cake does this automagically when I call save(). Thanks a million for your help (both of you!) Now for my second question that i am not too clear on. I have searched this group for information on saving HABTM. From what I have above, I have a classifieds_editions and a categories_classifieds. >From what I can gather from other posts, am I supposed to do? $this->data['Edition']['Edition']['classified_id'] = (integer)$this- >xmlID; $this->data['Edition']['Edition']['edition_id'] = (integer)$ad- >editions; I am pretty confused on this part. Like I said, I have read the manual, looked at the API checked this group and google. I am at a loss. On Feb 28, 3:28 am, "bernardo" <[EMAIL PROTECTED]> wrote: > On Feb 28, 7:50 am, "AD7six" <[EMAIL PROTECTED]> wrote: > > > On Feb 28, 10:15 am, "Christopher E. Franklin, Sr."<[EMAIL PROTECTED]> > > wrote: > > > I will try that as well. I thought that's what the sanitize->sql() > > > did > > > . Maybe I am mistaken. > > > If you use a string constraint, you are basically on your own from > > cake's perspective. If you use an array constraint, cake should take > > care of it for you. I bet this works, or at the very least does not > > generate errors: > > > $this->matchResult = $this->Classified->findCount(array( > > "Classified.text" = $ad->text, > > "Classified.editions" => $ad->editions) > > ); > > I don't think this works because it cuts the string at the first > newline. > > > > > HTH, > > > AD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
On Feb 28, 7:50 am, "AD7six" <[EMAIL PROTECTED]> wrote: > On Feb 28, 10:15 am, "Christopher E. Franklin, Sr."<[EMAIL PROTECTED]> wrote: > > I will try that as well. I thought that's what the sanitize->sql() > > did > > . Maybe I am mistaken. > > If you use a string constraint, you are basically on your own from > cake's perspective. If you use an array constraint, cake should take > care of it for you. I bet this works, or at the very least does not > generate errors: > > $this->matchResult = $this->Classified->findCount(array( > "Classified.text" = $ad->text, > "Classified.editions" => $ad->editions) > ); I don't think this works because it cuts the string at the first newline. > > HTH, > > AD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
On Feb 28, 6:15 am, "Christopher E. Franklin, Sr." <[EMAIL PROTECTED]> wrote: > I will try that as well. I thought that's what the sanitize->sql() > did If you look at the definitio of sanitize->sql(): function sql($string) { if (!ini_get('magic_quotes_gpc')) { $string = addslashes($string); } return $string; } So my guess is that you have magic_quotes_gpc enabled and addslashes is not being called. Note that in cake 1.2 sanitize->sql has been deprecated and has sanitize->escape instead, which in the case of mysql calls mysql_real_escape_string. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
On Feb 28, 10:15 am, "Christopher E. Franklin, Sr." <[EMAIL PROTECTED]> wrote: > I will try that as well. I thought that's what the sanitize->sql() > did > . Maybe I am mistaken. > If you use a string constraint, you are basically on your own from cake's perspective. If you use an array constraint, cake should take care of it for you. I bet this works, or at the very least does not generate errors: $this->matchResult = $this->Classified->findCount(array( "Classified.text" = $ad->text, "Classified.editions" => $ad->editions) ); HTH, AD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
I will try that as well. I thought that's what the sanitize->sql() did . Maybe I am mistaken. On Feb 27, 6:22 pm, "bernardo" <[EMAIL PROTECTED]> wrote: > You don't need to escape html characters. In fact doing that will give > you incorrect results because the escaped string will never match. > Html escaping will convert html special chars(< > & " ') into > entities, but what you want is adding backslashes to the characters > that are illegal in the query. > > So, I think you just need to use addslashes: > > $this->Classified->findCount("Classified.text = '" . addslashes($ad->text) . > "' AND > > Classified.editions = '$ad->editions'"); > > On Feb 27, 10:03 pm, "Christopher E. Franklin, Sr." > > <[EMAIL PROTECTED]> wrote: > > Hrmm, I seem to have fixed it. > > > The first time I tried this code, before I posted, it didn't work but, > > now it does. Maybe I had a typo. /shrug > > > Here is what I changed: > > [...] > > $this->xmlID = $this->Classified->getNumRows(); > > ++$this->xmlID; > > [...] > > $this->matchResult = $this->Classified->findCount("text = > > '".$this->MrClean->sql($this->MrClean->html($ad->text))."' AND editions = > > '". > > > $ad->editions."'"); > > [...] > > $this->data['Classified']['text'] = > > $this->MrClean->sql($this->MrClean->html($ad->text)); > > > [...] > > > My conclusion is that the sanitize->sql() really doesn't help if you > > have any html characters in your text. > > So, I convert the special characters using html() and the escape with > > sql(). > > I do the same thing on insert so, when I match the text, it should > > compare exactly. There a few snags. Some ads get by but, I can live > > with it. I will just use strip slashes and html_special_chars > > functions to convert the text back to my original HTML formatted. > > > Sorry for the bother and long posts --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
You don't need to escape html characters. In fact doing that will give you incorrect results because the escaped string will never match. Html escaping will convert html special chars(< > & " ') into entities, but what you want is adding backslashes to the characters that are illegal in the query. So, I think you just need to use addslashes: $this->Classified->findCount("Classified.text = '" . addslashes($ad- >text) . "' AND Classified.editions = '$ad->editions'"); On Feb 27, 10:03 pm, "Christopher E. Franklin, Sr." <[EMAIL PROTECTED]> wrote: > Hrmm, I seem to have fixed it. > > The first time I tried this code, before I posted, it didn't work but, > now it does. Maybe I had a typo. /shrug > > Here is what I changed: > [...] > $this->xmlID = $this->Classified->getNumRows(); > ++$this->xmlID; > [...] > $this->matchResult = $this->Classified->findCount("text = > '".$this->MrClean->sql($this->MrClean->html($ad->text))."' AND editions = '". > > $ad->editions."'"); > [...] > $this->data['Classified']['text'] = > $this->MrClean->sql($this->MrClean->html($ad->text)); > > [...] > > My conclusion is that the sanitize->sql() really doesn't help if you > have any html characters in your text. > So, I convert the special characters using html() and the escape with > sql(). > I do the same thing on insert so, when I match the text, it should > compare exactly. There a few snags. Some ads get by but, I can live > with it. I will just use strip slashes and html_special_chars > functions to convert the text back to my original HTML formatted. > > Sorry for the bother and long posts --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
Hrmm, I seem to have fixed it. The first time I tried this code, before I posted, it didn't work but, now it does. Maybe I had a typo. /shrug Here is what I changed: [...] $this->xmlID = $this->Classified->getNumRows(); ++$this->xmlID; [...] $this->matchResult = $this->Classified->findCount("text = '".$this- >MrClean->sql($this->MrClean->html($ad->text))."' AND editions = '". $ad->editions."'"); [...] $this->data['Classified']['text'] = $this->MrClean->sql($this->MrClean- >html($ad->text)); [...] My conclusion is that the sanitize->sql() really doesn't help if you have any html characters in your text. So, I convert the special characters using html() and the escape with sql(). I do the same thing on insert so, when I match the text, it should compare exactly. There a few snags. Some ads get by but, I can live with it. I will just use strip slashes and html_special_chars functions to convert the text back to my original HTML formatted. Sorry for the bother and long posts --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: findCount SQL errors
PS - I have tried every variation of the findCount I can think of, sanitizing with sql, using real_escape_string using just text instead of Classified.text --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
findCount SQL errors
Hello fellow Bakers, I am having a problem with some of my code, specifically the findCount function. I have an AdminToolController that I use to upload an XML file, parse it and add the resulting classified ads into the database. Because, the system we use is ancient, the XML files could contain an exact copy of a classified ad. I am trying to add code to compare what the actual text is and in which editions the ad will run in. So, I thought the findCount function would fit perfectly. If there is a match, do not insert the double entry. Only problem is, I get SQL errors. Without doing the check, all of the ads from each XML file insert flawlessly into the database. Here is a snippet of the error: SELECT COUNT(*) AS count FROM `classifieds` AS `Classified` WHERE ``.`` ``.`` ``.```Classified ``.```text ``.`` ``.`` ad for ``.`` `weeks ``.`` ``.``.$37. AND `Classified`.`editions` ``.`` ``.`` ``.`` ``.`` ``.`` ``.`` ``.`` error--->1064: 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 '`.`` ``.```Classified ``.```text ``.`` ``.`` I am wondering why I get all the extra ``.`` ``.``s in the SQL? Here is my controller: params['form']) && is_uploaded_file($this- >params['form']['File']['tmp_name'])){ move_uploaded_file($this->params['form']['File'] ['tmp_name'], "/var/www/localhost/htdocs/app/tmp/".$this- >params['form']['File']['name']); $this->xml_data = $this->AdminTool->parse_classified("/ var/www/localhost/htdocs/app/tmp/".$this->params['form']['File'] ['name'], $this->params['form']['File']['size']); $this->xml_data = $this->AdminTool->fix_editions($this- >xml_data); $this->xml_data = $this->AdminTool->fix_dates($this- >xml_data); $this->xml_data = $this->AdminTool->fix_pictures($this- >xml_data); $this->xml_data = $this->AdminTool->fix_text($this- >xml_data); $this->xmlID = $this->Classified->getLastInsertId(); ++$this->xmlID; $this->MrClean = new Sanitize(); foreach($this->xml_data as $ad){ TROUBLE MAKER-->$this->matchResult = $this->Classified->findCount("Classified.text = $ad->text AND Classified.editions = $ad->editions"); if(empty($this->matchResult) || $this->matchResults == 0){ $this->data['Classified']['id'] = (integer)$this- >xmlID; $this->data['Classified']['itemno'] = (integer)$ad- >itemno; $this->data['Classified']['acctcode'] = (string)$ad- >acctcode; $this->data['Classified']['class'] = (string)$ad- >class; $this->data['Classified']['table'] = $ad->table; $this->data['Classified']['start'] = $ad->start; $this->data['Classified']['end'] = $ad->end; $this->data['Classified']['editions'] = (string)$ad- >editions; $this->data['Classified']['specials'] = (string)$ad- >specials; $this->data['Classified']['dispname'] = (string)$ad- >dispname; $this->data['Classified']['dispnote'] = (string)$ad- >dispnote; $this->data['Classified']['disptag'] = (string)$ad- >disptag; $this->data['Classified']['dispcols'] = (integer)$ad- >dispcols; $this->data['Classified']['text'] = (string)$ad->text; $this->Classified->save($this->data); $this->xmlID++; $this->matchResult = 0; } } unset($this->xml_data, $this->xmlID); } } } ?> Here is some sample XML: 0 00-0 0001 01 12/27/05 12/25/07 BEINY Q 0 0 \BBRING A PICTURE\A Of any personal item you would like to sell and we'll run it with 15-20 words in a 2" column ad for 2 weeks..$37. Any ideas? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---