WHERE clause confusion
3 posts by 2 authors in: Forums > CMS Builder
Last Post: July 29, 2009 (RSS)
By MisterJim - July 28, 2009
I'm doing something that is normally, using straight SQL, fairly simple to do.
I've got a table that has a column called 'department'
I'm trying to capture the department name via the URL using a $_GET variable.
So here's the SQL at the top of my page:
It doesn't work.
YET, if I hard code the variable like so, it works and lists the blog posts within the department: Hair
Here's the URL: http://www.someURL.com/blog.php?dept=Hair
I've tried various ways to code the WHERE clause, such as:
'where' => 'department = ' . $dept,
returns: MySQL Error: Unknown column 'Hair' in 'where clause'
'where' => 'department = {$dept}',
return: MySQL Error: 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 '}) ORDER BY date DESC' at line 3
'where' => 'department = \'"{$dept}"\'',
returns: No blog posts were found
What am I doing wrong?
Thanks much.
Jim
I've got a table that has a column called 'department'
I'm trying to capture the department name via the URL using a $_GET variable.
So here's the SQL at the top of my page:
$dept = $_GET['dept'];
list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'allowSearch' => '0',
'where' => 'department = "$dept"',
'orderBy' => 'date DESC'
));
It doesn't work.
YET, if I hard code the variable like so, it works and lists the blog posts within the department: Hair
list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'allowSearch' => '0',
'where' => 'department = \'Hair\'',
'orderBy' => 'date DESC'
));
Here's the URL: http://www.someURL.com/blog.php?dept=Hair
I've tried various ways to code the WHERE clause, such as:
'where' => 'department = ' . $dept,
returns: MySQL Error: Unknown column 'Hair' in 'where clause'
'where' => 'department = {$dept}',
return: MySQL Error: 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 '}) ORDER BY date DESC' at line 3
'where' => 'department = \'"{$dept}"\'',
returns: No blog posts were found
What am I doing wrong?
Thanks much.
Jim
WebCamp One, LLC
Websites That Work
Websites That Work
Re: [Mr Jim] WHERE clause confusion
By Dave - July 29, 2009
Hi Jim,
If you have some comfort with SQL you can see the SQL code being generated by CMSB by adding this option:
'debugSql' => true,
It's really handy to see what's going on and where problems might be.
In this case there's three things to keep in mind:
1) PHP only replaces variables in double quoted strings (not single quoted strings)
2) MySQL requires values be in quoted strings (single or double)
3) You should escape any user form input to avoid SQL injection attacks
Try this:
$escapedDept = mysql_real_escape_string( @$_GET['dept'] );
This will escape quotes in the input (preventing SQL injection) and the @ supresses the "undefined index 'dept'" error that you'd get in the event the link didn't have ?dept= in it.
And this:
'where' => "department = '$escapedDept' ",
Which will replace the variable name with it's value and still have it quoted for MySQL.
Hope that helps! Let me know if that works for you.
If you have some comfort with SQL you can see the SQL code being generated by CMSB by adding this option:
'debugSql' => true,
It's really handy to see what's going on and where problems might be.
In this case there's three things to keep in mind:
1) PHP only replaces variables in double quoted strings (not single quoted strings)
2) MySQL requires values be in quoted strings (single or double)
3) You should escape any user form input to avoid SQL injection attacks
Try this:
$escapedDept = mysql_real_escape_string( @$_GET['dept'] );
This will escape quotes in the input (preventing SQL injection) and the @ supresses the "undefined index 'dept'" error that you'd get in the event the link didn't have ?dept= in it.
And this:
'where' => "department = '$escapedDept' ",
Which will replace the variable name with it's value and still have it quoted for MySQL.
Hope that helps! Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] WHERE clause confusion
By MisterJim - July 29, 2009
Dave,
I understand. Thank you for the lesson.
Your code worked perfectly. Much appreciated.
Jim
I understand. Thank you for the lesson.
Your code worked perfectly. Much appreciated.
Jim
WebCamp One, LLC
Websites That Work
Websites That Work