Using placeholder in sql query? (evo)

12 Posts

smesple Reply #1, 15 years, 5 months ago

I’m using a modified version of loopDbChunk to pull events from an external database.
Works perfect however I need a way to use the logged in users email in the WHERE clause.

Is there a workaround to make this happen?
I was hoping to sub the email with placeholder like below.

<?php
include("assets/snippets/eventlister/config.php");

$query = "
SELECT *
FROM subscriptions
WHERE email = '[+user.email+]'
";

$paramra = array(
  'hostvar' =>$dbhost, 
  'uservar' => $dbuser, 
  'passvar' => $dbpass, 
  'databasevar'=> $dbname, 
  'sql' =>$query,
  'chunkName' =>'youreventsformat',
  'perPage' => '',
  'orderby' => '',
  'missingValTxt' => '--');

return $modx->runSnippet('loopDBChunk' , $paramra);
?>

could this maybe be done with a call to the mod database for the email using the internalKey or getLoginUser or something?
Thanks for any help.

2,310 Posts

bunk58 Reply #2, 15 years, 5 months ago

This might do it :

$_SESSION['webEmail']

12 Posts

smesple Reply #3, 15 years, 5 months ago

Man, so close I think.
Just not sure if my syntax is correct.

would it be:

WHERE email = $_SESSION['webEmail']

 or

WHERE email = '$_SESSION['webEmail']'

I guess I'm just too clueless.

12 Posts

smesple Reply #4, 15 years, 5 months ago

Closer.

I tried using:

<?php
include("assets/snippets/eventlister/config.php");

$email = $_SESSION['webEmail'];

$query = "
SELECT *
FROM subscriptions
WHERE email = '$email'
";

$paramra = array(
  'hostvar' =>$dbhost, 
  'uservar' => $dbuser, 
  'passvar' => $dbpass, 
  'databasevar'=> $dbname, 
  'sql' =>$query,
  'chunkName' =>'youreventsformat',
  'perPage' => '',
  'orderby' => '',
  'missingValTxt' => '--');

return $modx->runSnippet('loopDBChunk' , $paramra);
?>

and that sort of worked. It is returning data, but the data is not the same as if I just put the email in the WHERE

2,310 Posts

bunk58 Reply #5, 15 years, 5 months ago

Untested but maybe try :

<?php
include("assets/snippets/eventlister/config.php");

$email = $_SESSION['webEmail'];

$query = "
SELECT *
FROM subscriptions
WHERE email = \'{$email}\'
";

$paramra = array(
  'hostvar' =>$dbhost, 
  'uservar' => $dbuser, 
  'passvar' => $dbpass, 
  'databasevar'=> $dbname, 
  'sql' =>$query,
  'chunkName' =>'youreventsformat',
  'perPage' => '',
  'orderby' => '',
  'missingValTxt' => '--');

return $modx->runSnippet('loopDBChunk' , $paramra);
?>

12 Posts

smesple Reply #6, 15 years, 5 months ago

Thanks for the effort, but that tosses out a parse error.
so, so , so close.

I just cant figure out why it works, but data is bad.
duplicates some, and they are cut down to like 50 characters.

12 Posts

smesple Reply #7, 15 years, 5 months ago

Not sure what I did, but I got it working with:

<?php
include("assets/snippets/eventlister/config.php");

$email = $_SESSION['webEmail'];

$query = "
SELECT *
FROM subscriptions
WHERE email = '$email'
";

$paramra = array(
  'hostvar' =>$dbhost, 
  'uservar' => $dbuser, 
  'passvar' => $dbpass, 
  'databasevar'=> $dbname, 
  'sql' =>$query,
  'chunkName' =>'youreventsformat',
  'perPage' => '',
  'orderby' => '',
  'missingValTxt' => '#');

return $modx->runSnippet('loopDBChunk' , $paramra);
?>

Big thanks... can I buy you a beer or coffee or something?

2,310 Posts

bunk58 Reply #8, 15 years, 5 months ago

Thanks is all that’s required