2. PHPRunner General questions
  3. single quotes in the value field
This topic is locked

single quotes in the value field

 		10/9/2007 12:34:23 PM
PHPRunner General questions
L
 laonian author

I sent this message to support@xlinesoft.com last night. It was bounced back today because the recepient's mailbox is full.
I have a table with multiple fields like `Presenter`, `JC_Date`, `Journal`, `Title` etc. I want to backup the content real time and add an event at AfterAdd step. Everything works fine and the backup (insert) is completed perfectlyIf there is any such a charater in any fieldin the Add page, the following error will occur after clicking "save".(see below). After this error, I found that record in my original table is inserted, but there is no record entered in my backup table. Note the input value for field `Presenter` is changed to a\' in the SQL query. SOmetimes, I do need to have single quotes in my inputs. Could you please help me solve this problem? Is it illegal to use single quotes (') in mysql database? Why is the problem happening only on the backup (insert record in another table) table, but has no affects on the original table? I noticed that this problem is gone if I change the single quote (') to acute (`)- located on the same key as ~on my PC. Thanks a lot. I am a new PHPrunner 4.1 purchaser.
****error message***
PHP error happened
Technical information

Error type 256

Error description 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 '2007-10-05 00:00:00', 'Ecology', 'b', 'Cell communication', ''

URL .../journal_club_add.php?

Error file .../include/dbconnection.php

Error line 26

SQL query insert into `journal_club` (`Presenter`, `JC_Date`, `Journal`, `Title`, `Category`, `Signif_Authors`, `Abstract`, `Keywords`, `PubMed_URL`, `PDF_link`, `Comments`) values ([color=#0000FF]'a\'', '2007-10-05 00:00:00', 'Ecology', 'b', 'Cell communication', '', '

c', 'd', 'e', 'f', 'g')

Solution This is a general error. It occurs when thereis an error in event code or in SQL.

Send your SQL or event code along with full error message to support@xlinesoft.com.
J
 		jim9 10/9/2007

Don't know if it will help, but see the tip titled Apostrophe in sql.
L
 		laonian author 10/9/2007

Hi jim9,
Thanks for the reply.
I found that post and it clears up why it does not work in my backup event. Now I am running into problems. Either I input "O'Brian" in my field and will not allow backup to work or input "O\'Brian" in my field in order to have backup to work. But in the latter case, I will see "O\'Brian" in the PHPrunner updated database and "O'Brian" in the backup database. Is there a solution to have both of them displayed correctly? Do I need to re-configure MySQL or PHP?
Alexey admin 10/10/2007

You can post your event code here and I'll help you to fix it.
L
 		laonian author 10/10/2007

You can post your event code here and I'll help you to fix it.


Thanks a lot, Alexey. These are my codes (regular INSERT query):

[codebox]// After record added

function AfterAdd(&$values,&$keys,$inline)

{

global $conn;

$strSQLInsert = "insert into JC_backup (ID, Presenter, JC_Date, Journal, Title, Category, Signif_Authors, Abstract)

values ('".$keys["ID"]."', '".$values["Presenter"]."', '".$values["JC_Date"]."', '".$values["Journal"]."', '".$values["Title"]."',

'".$values["Category"]."', '".$values["Signif_Authors"]."', '".$values["Abstract"]."')";

db_exec($strSQLInsert,$conn);
} // function AfterAdd

[/codebox]
This is a publication database. Chances are high to have single quotes in fields like authors and abstract. My question is how to make PHP find those strings with single quotes and do conversions on them during the "background" backup (insert records into other table).
J
 		Jane 10/11/2007

Hi,
to convert single quotes in the field names use db_addslashes function:

global $conn;

$strSQLInsert = "insert into JC_backup (ID, Presenter, JC_Date, Journal, Title, Category, Signif_Authors, Abstract)

values ('".$keys["ID"]."', '".$values["Presenter"]."', '".$values["JC_Date"]."', '".$values["Journal"]."', '".$values["Title"]."',

'".$values["Category"]."', '".db_addslashes($values["Signif_Authors"])."', '".db_addslashes($values["Abstract"])."')";

db_exec($strSQLInsert,$conn);