DB Query help

103 Posts

sjh_vt Reply #1, 17 years, 1 month ago

I imagine this simply a php formatting issue, but I can’t seem to figure it out at the moment. In the db selections below, the first select statement works fine.

$result = $modx->db->select("unit_price","vtigercrm504.vtiger_products","productcode = \"$partnumber\" && unit_price > 0");

But I have several other queries that are are more complex, so I’d like to use an actual query. This works fine with $partumber hard coded

$result = $modx->db->query('SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = "123456" && unit_price > 0 ');

But when I try to use the variable $partnumber (passed correctly and verified), I get no results. $partnumber seems to not be getting evaulated correctly. I suspect this is a problem with the way $partumber is referenced, but I’ve tried several combinations of single, double quotes and parenthesis and can’t figure out how to get it to work.

$result = $modx->db->query('SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = "$partnumber" && unit_price > 0 ');

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 17 years, 1 month ago

$result = $modx->db->query(’SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = "$partnumber" && unit_price > 0 ’);

You have the whole query in single-quotes, which won’t resolve the variable. Try this one:

$result = $modx->db->select('unit_price', 'vtigercrm504.vtiger_products', 'productcode = ' . $partnumber . ' && unit_price > 0');

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

103 Posts

sjh_vt Reply #3, 17 years, 1 month ago

Thanks, Susan. The select works either the way that I posted it, or the way that you have it. However, I’d like to do the same with actual DBAPI query statement rather than a select

For the record, the double quotes actually follow the example in http://svn.modxcms.com/docs/display/MODx096/select

After getting back to this again, I figured it out:

$result = $modx->db->query("SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = '$partnumber' && unit_price > 0 ");

☆ A M B ☆
24,524 Posts

sottwell Reply #4, 17 years, 1 month ago

Double quotes are fine, but if they’re part of a string encapsulated in single quotes the variable still won’t be resolved. Single quotes are debatably faster than double quotes, but for all practical purposes it’s a personal choice. In any case, the way your original query was written, the single quotes surrounding the entire query would prevent the variable from being resolved. Either use double quotes, or break the variable out using . concatenation notation.

$result = $modx->db->query("SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = $partnumber && unit_price > 0 ");

Or

$result = $modx->db->query('SELECT unit_price FROM vtigercrm504.vtiger_products WHERE productcode = ' . $partnumber . ' && unit_price > 0 ');

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

103 Posts

sjh_vt Reply #5, 17 years, 1 month ago

Got it. Thanks for the help and info.

☆ A M B ☆
24,524 Posts

sottwell Reply #6, 17 years, 1 month ago

I have made it a habit to always break out variables, whether I’m using single quotes or double quotes; that way I never get caught with my variables not being evaluated!

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org