Hi I have a below string. *PHP, HTML, SQL* Want to convert it to *'PHP', 'HTML', 'SQL'*
I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning.
Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140.
Looks it has extra forward slashes. How can I overcome this.
Thanks Austin
Austin,
The only time you need to escape a character in a string, is when it would otherwise be miss interpreted. When you begin a string with a ' or a ", PHP will identify the next ' or " as the end of the string, as long as it matches the first character. In other words beginning a string with a ' will cause PHP to expect the next ' to mark the end of the string (same matching on the " character, if that's what you started with). If you begin your string with a ' (single quote) you can code as many " (double quotes) as you want between the first and end string character without having to escape them (\ character), conversely if you begin your string with " (double quote) you can use as many single quotes as you want between them.
If you find yourself having to begin a string with a double quote and you absolutely need to imbed a double quote in the middle of the string and don't want it to be miss interpreted as the end of the string simply precede it with the backslash, to escape it from being miss interpreted as the end of string.
On another note, the SQL language used by databases is not PHP, so to place SQL syntax in your program so that you can pass it to the SQL server, you should place it in a string. In this case, as you would suspect, your string should begin and end with a single quote or a double quote.
hope this helps,
Warren Vail
On 4/10/2011 5:23 PM, Austin Einter wrote:
Hi I have a below string. *PHP, HTML, SQL* Want to convert it to *'PHP', 'HTML', 'SQL'* I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning. Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140. Looks it has extra forward slashes. How can I overcome this. Thanks Austin
<:avglsdata id=avglsdata> In Drupal code, you should never find a query with %s that is not surrounded by quotes. To do what you are looking for check the db_placeholders function in the API docs. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________ From: Austin Einter austin.einter@gmail.com To: support@drupal.org Sent: Sun, April 10, 2011 8:23:57 PM Subject: [support] String conversion
Hi I have a below string. PHP, HTML, SQL Want to convert it to 'PHP', 'HTML', 'SQL'
I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning.
Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140. Looks it has extra forward slashes. How can I overcome this.
Thanks Austin
Hi All I tried db_placeholder api, but getting 0 records from query. The below query works fine, gives me required records. $query = db_query("SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills}
WHERE resubmt_skills.skillname IN ('PHP', 'ITI')))");
Where as the query based on place holders giving 0 records.
$arrSkilll = array('PHP', 'HTML'); $query = db_query("SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills}
WHERE resubmt_skills.skillname IN (" . db_placeholders($arrSkilll, 'varchar') . ")))");
Not sure, whats the wrong here.
Regards, Austin
On Mon, Apr 11, 2011 at 7:00 AM, Anth malkouna@yahoo.com.au wrote:
Sometimes you can't control what you're getting for your IN statement...
Try something like:
$string = "PHP,HTML,SQL"; $pieces = explode(",", $string); $in_string = "'" . implode("','", $pieces) . "'";
On 6:59 AM, nan wich wrote:
In Drupal code, you should *never* find a query with %s that is not surrounded by quotes. To do what you are looking for check the db_placeholders function in the API docs.
*Nancy*
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
*From:* Austin Einter austin.einter@gmail.com *To:* support@drupal.org *Sent:* Sun, April 10, 2011 8:23:57 PM *Subject:* [support] String conversion
Hi I have a below string. *PHP, HTML, SQL* Want to convert it to *'PHP', 'HTML', 'SQL'*
I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning.
Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140.
Looks it has extra forward slashes. How can I overcome this.
Thanks Austin
-- [ Drupal support list | http://lists.drupal.org/ ]
Anth Thanks for help.
I tried this option, still get '' warning.
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill); $in_string = "'" . implode("','", $pieces) . "'";
$query = db_query("SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills}
WHERE resubmt_skills.skillname IN (%s)))", $in_string);
I get the warning
user warning: 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 ''PHP',' TML',' HTML')))' at line 4 query: SELECT name, email, phone, yrsofexp FROM resubmt_table WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM resubmt_users_skills WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('PHP',' TML',' HTML'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 126.
Thanks Austin On Mon, Apr 11, 2011 at 7:00 AM, Anth malkouna@yahoo.com.au wrote:
Sometimes you can't control what you're getting for your IN statement...
Try something like:
$string = "PHP,HTML,SQL"; $pieces = explode(",", $string); $in_string = "'" . implode("','", $pieces) . "'";
On 6:59 AM, nan wich wrote:
In Drupal code, you should *never* find a query with %s that is not surrounded by quotes. To do what you are looking for check the db_placeholders function in the API docs.
*Nancy*
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
*From:* Austin Einter austin.einter@gmail.com *To:* support@drupal.org *Sent:* Sun, April 10, 2011 8:23:57 PM *Subject:* [support] String conversion
Hi I have a below string. *PHP, HTML, SQL* Want to convert it to *'PHP', 'HTML', 'SQL'*
I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning.
Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140.
Looks it has extra forward slashes. How can I overcome this.
Thanks Austin
-- [ Drupal support list | http://lists.drupal.org/ ]
Hi All On further exploring..
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string);
*The output prints as *
SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ( '%s','%s','%s')))
But I do not see , pieces[0], pieces[1] and pieces[2] etc in query string. Is it fine or I am doing something wrong here.
Regards Austin
On Mon, Apr 11, 2011 at 7:30 AM, Austin Einter austin.einter@gmail.comwrote:
Anth Thanks for help.
I tried this option, still get '' warning.
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill); $in_string = "'" . implode("','", $pieces) . "'";
$query = db_query("SELECT name, email, phone, yrsofexp FROM {resubmt_table}
WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)))", $in_string);
I get the warning
user warning: 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 ''PHP',' TML',' HTML')))' at line 4 query: SELECT name, email, phone, yrsofexp FROM resubmt_table WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM resubmt_users_skills WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('PHP',' TML',' HTML'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 126.
Thanks Austin On Mon, Apr 11, 2011 at 7:00 AM, Anth malkouna@yahoo.com.au wrote:
Sometimes you can't control what you're getting for your IN statement...
Try something like:
$string = "PHP,HTML,SQL"; $pieces = explode(",", $string); $in_string = "'" . implode("','", $pieces) . "'";
On 6:59 AM, nan wich wrote:
In Drupal code, you should *never* find a query with %s that is not surrounded by quotes. To do what you are looking for check the db_placeholders function in the API docs.
*Nancy*
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
*From:* Austin Einter austin.einter@gmail.com *To:* support@drupal.org *Sent:* Sun, April 10, 2011 8:23:57 PM *Subject:* [support] String conversion
Hi I have a below string. *PHP, HTML, SQL* Want to convert it to *'PHP', 'HTML', 'SQL'*
I tried to write a function to do it as below. If I print the function output it prints properly. If same function output I use for SQL query as below I get warning.
Query: SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (%s)", $fSkill Warning: SELECT skillid FROM resubmt_skills WHERE resubmt_skills.skillname IN ('ITI',' PHP'))) in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_search\resume_search.module on line 140.
Looks it has extra forward slashes. How can I overcome this.
Thanks Austin
-- [ Drupal support list | http://lists.drupal.org/ ]
On Sun, Apr 10, 2011 at 8:35 PM, Austin Einter austin.einter@gmail.com wrote:
Hi All On further exploring..
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string);
The output prints as
SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ( '%s','%s','%s')))
But I do not see , pieces[0], pieces[1] and pieces[2] etc in query string. Is it fine or I am doing something wrong here.
You are close, but you need one last piece:
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string, $pieces); ^^^^^^^^^^^^^^^^^^
The $pieces as an argument to $db_query should get it done.
Also, for debugging sql I love using the Devel module and enabling query logging and printing the query log. It shows the exact query without having to do a drupal_set_message and after any argument substitution takes place.
Cheers, Greg
Thanks Greg. Inching closer. Got partial result.
$skill = "SQL, PHP"; $pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
$query = db_query($query_string, $pieces);
This gives records having skill set only SQL.
If I change $skill as below
$skill = "PHP, SQL";
I get records having PHP skill only.
So it is only taking the first element of array. I wanted it to take all the arguements in array.
Thanks Austin
On Mon, Apr 11, 2011 at 8:15 AM, Greg Knaddison < greg@growingventuresolutions.com> wrote:
On Sun, Apr 10, 2011 at 8:35 PM, Austin Einter austin.einter@gmail.com wrote:
Hi All On further exploring..
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table}
'
. 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string);
The output prints as
SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills}
WHERE
resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills}
WHERE
resubmt_skills.skillname IN ( '%s','%s','%s')))
But I do not see , pieces[0], pieces[1] and pieces[2] etc in query
string.
Is it fine or I am doing something wrong here.
You are close, but you need one last piece:
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string, $pieces); ^^^^^^^^^^^^^^^^^^
The $pieces as an argument to $db_query should get it done.
Also, for debugging sql I love using the Devel module and enabling query logging and printing the query log. It shows the exact query without having to do a drupal_set_message and after any argument substitution takes place.
Cheers, Greg
-- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
Thanks Greg. It was my mistake.
$skill = "SQL, PHP";
As there is a precceding space before PHP, so it was not matching. Now I need to see how do I trim the spaces. db_placeholders is really powerful,,, thanks everybody again.
Best Regards, Austin
On Mon, Apr 11, 2011 at 8:23 AM, Austin Einter austin.einter@gmail.comwrote:
Thanks Greg. Inching closer. Got partial result.
$skill = "SQL, PHP";
$pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
$query = db_query($query_string, $pieces);
This gives records having skill set only SQL.
If I change $skill as below
$skill = "PHP, SQL";
I get records having PHP skill only.
So it is only taking the first element of array. I wanted it to take all the arguements in array.
Thanks Austin
On Mon, Apr 11, 2011 at 8:15 AM, Greg Knaddison < greg@growingventuresolutions.com> wrote:
On Sun, Apr 10, 2011 at 8:35 PM, Austin Einter austin.einter@gmail.com wrote:
Hi All On further exploring..
$skill = "PHP, TML, HTML"; $pieces = explode(",", $skill);
$query_string = 'SELECT name, email, phone, yrsofexp FROM
{resubmt_table} '
. 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string);
The output prints as
SELECT name, email, phone, yrsofexp FROM {resubmt_table} WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills}
WHERE
resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills}
WHERE
resubmt_skills.skillname IN ( '%s','%s','%s')))
But I do not see , pieces[0], pieces[1] and pieces[2] etc in query
string.
Is it fine or I am doing something wrong here.
You are close, but you need one last piece:
$query_string = 'SELECT name, email, phone, yrsofexp FROM {resubmt_table} ' . 'WHERE resubmt_table.uid IN (SELECT DISTINCT uid FROM {resubmt_users_skills} ' . 'WHERE resubmt_users_skills.skillid IN(SELECT skillid FROM {resubmt_skills} ' . 'WHERE resubmt_skills.skillname IN ( ' . db_placeholders($pieces, 'varchar') . ')))';
drupal_set_message($query_string); $query = db_query($query_string, $pieces); ^^^^^^^^^^^^^^^^^^
The $pieces as an argument to $db_query should get it done.
Also, for debugging sql I love using the Devel module and enabling query logging and printing the query log. It shows the exact query without having to do a drupal_set_message and after any argument substitution takes place.
Cheers, Greg
-- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
-
Anth -
Austin Einter -
Greg Knaddison -
nan wich -
Warren Vail