User Extended Fields to filter external db data

40 Posts

bigben83 Reply #1, 11 years, 11 months ago

I have an external db that I need to filter the results according to a logged in users's extended field.
part of my snippet is as follows.

$con=mysqli_connect("database.com.au","user","password","DATA_RECEIVER");
$srt='[[!$user_field]]';
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM data_events WHERE customer='$srt' ORDER BY date ASC");

this uses the mysql WHERE command to filter the results,
$srt references the user extended field.

the

[[!$user_field]]

chunk is below

[[!Profile]]
[[+mac]]

This isnt working, I must be doing something wrong, as if I hard code the $srt with one of the extended user fields it works.
so it isnt bringing in the required information.

Someones help would be appreciated.

24,544 Posts

BobRay Reply #2, 11 years, 11 months ago

You can't use a MODX tag in PHP code. PHP doesn't know what to do with it.

You could pass the value as a property in the snippet tag, but it would be faster to get it directly in the snippet.

$profile = $modx->user->getOne('Profile');

if ($profile) {
    $extended = $profile->get('extended');
    $value = $extended['ExtendedFieldName'];
    
    /* Do database query here */

} else {
    /* user has no profile */
}

If speed is critical, there are some faster ways to do it, but the method above is usually fast enough.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

40 Posts

bigben83 Reply #3, 11 years, 11 months ago

Thanks for the speedy reply Bob,

so have I got your reply correct.

<?php
$profile = $modx->user->getOne('Profile');
 
if ($profile) {
    $extended = $profile->get('extended');
    $value = $extended['mac'];

$con=mysqli_connect("database.com.au","user","password","DATA_RECEIVER");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();

} else {
    /* user has no profile */
}

$result = mysqli_query($con,"SELECT * FROM data_events WHERE customer='$value' ORDER BY date ASC");

24,544 Posts

BobRay Reply #4, 11 years, 11 months ago

Quote from: bigben83 at Nov 10, 2014, 08:33 PM

Thanks for the speedy reply Bob,

so have I got your reply correct?

No quite. I would do it like this:

<!--?php
$profile = $modx--->user->getOne('Profile');
$result = null;
 
if ($profile) {
    $extended = $profile->get('extended');
    $value = $extended['mac'];

    $con=mysqli_connect("database.com.au","user","password","DATA_RECEIVER");
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    } else {
        $result = mysqli_query($con,"SELECT * FROM data_events WHERE customer='" . $value . "' ORDER BY date ASC");
    }

} else {
    /* user has no profile */

}

if ($result === null) {
   /* Something went wrong */
} else {
   /* process, return, or display $result */
}

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

40 Posts

bigben83 Reply #5, 11 years, 11 months ago

Great, thanks for that, It still isn't quite working, as you can see I am still very much a amateur

$profile = $modx--->user->getOne('Profile');
$result = null;
  
if ($profile) {
    $extended = $profile->get('extended');
    $value = $extended['mac'];
 
$con=mysqli_connect("database.com.au","user","password","DATA_RECEIVER");
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    } else {
        $result = mysqli_query($con,"SELECT * FROM data_events WHERE customer='" . $value . "' ORDER BY date ASC");
    }

} else {
    /* user has no profile */
}
 
if ($result === null) {
   /* Something went wrong */
} else {

echo "<table>
<tbody>
<tr class='upper-td'>
<th>date</th>
<th>customer</th>
<th>id</th>
<th>q</th>
<th>c</th>
<th>z</th>
<th>p</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr class='lower-td'>";
  echo "<td class='lower-td'>" . $row['date'] . "</td>";
  echo "<td class='lower-td'>" . $row['customer'] . "</td>";
  echo "<td class='lower-td'>" . $row['id'] . "</td>";
  echo "<td class='lower-td'>" . $row['q'] . "</td>";
  echo "<td class='lower-td'>" . $row['c'] . "</td>";
  echo "<td class='lower-td'>" . $row['z'] . "</td>";
  echo "<td class='lower-td'>" . $row['p'] . "</td>";
  echo "</tr>";
}

echo "</tbody>";
echo "</table>";
}
mysqli_close($con);

24,544 Posts

BobRay Reply #6, 11 years, 11 months ago

I think you're probably close.

Try adding this above the while loop and see what you get:

$row = mysquli_fetch_array($result);
var_dump ($row);[/code



BTW, If I'm understanding what your doing, you're only going to get one row (the one for the current user) so the while loop is unnecessary.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

40 Posts

bigben83 Reply #7, 11 years, 11 months ago

Thanks Bob, adding the above code still didn't work, it just shows a blank page.

the while loop shows all rows in the table with the specified customer id
so it shows several rows. and Im only showing a few of the rows of the table not all of them.

40 Posts

bigben83 Reply #8, 11 years, 11 months ago

I have found this error,
the following

$profile = $modx--->user->getOne('Profile');

needs to be

$profile =$modx->user->getOne(’Profile’);

The page now shows but is showing "user has no profileSomething went wrong"

so I just need to do some debugging.

24,544 Posts

BobRay Reply #9, 11 years, 11 months ago

First, can you go into PhpMyAdmin and enter the query on the SQL tab (hard-coding the user ID) and see if it works?

Try adding this after the if ($profile) line:

echo "<br/>Value: " . $value;
echo "<br><pre>" . print_r($extended, true) . "</pre><br/>";

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

40 Posts

bigben83 Reply #10, 11 years, 11 months ago

yes submitting this query worked.

SELECT * FROM `data_events` WHERE customer='001B110743DD' ORDER BY `date` DESC

adding that code returns Value:
with no data after it.