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
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
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, 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
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
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'] =
(string)$ad-
class;
$this-Classified-save($this-data);
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:
?php
class AdminToolsController extends AppController {
public $xml_data = NULL;
public $uses = array('Classified', 'AdminTool', 'sanitize');
public $xmlID = 1;
public $matchResult = 0;
/**
* This is the function to display the main index
*/
function index(){
}
/**
* This function provides control to upload an XML file, parse it
and save it
* into a MySQL table. We then set the result for use by the view
to tell us
* if the save was successful or not.
*/
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-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:
ad
itemno0/itemno
acctcode00-0/acctcode
class0001/class
table01/table
start12/27/05/start
end12/25/07/end
editionsBEINY/editions
specialsQ/specials
dispname/dispname
dispnote/dispnote
disptag/disptag
dispcols0/dispcols
dispdepth0/dispdepth
text
\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.
/text
/ad
Any ideas?
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 -~--~~~~--~~--~--~---
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
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
-~--~~~~--~~--~--~---
