Problem with MODx username and mySQL query

288 Posts

galahad5 Reply #1, 14 years, 3 months ago

Hi, I'm trying to use a mySQL query to output a list of items that belong to a particular user inside a members area on a Revolution 2.2 site.

I'm setting the Profile snippet in the resource and I can call the username from the database successfully, but it's not working when I run a query to pull the data (even though if I echo the query it looks fine)

So I use this:

$query = "select * from items WHERE Username LIKE '[[+username]]' ORDER BY ItemName";

If I echo $query it displays the username correctly, but on the resource it seems to ignore that and doesn't output any results. Username is the correct name of the field in the database table, and the user has about 20 items to display.

I also tried making a variable from the MODx username like this:

$username = '[[+username]]';

And using $username in the query- but got the same result.

And yet if I echo $username on the resource it displays the user's actual username, correctly.

Definitely an issue with the username because I get all the results in the table if I remove the WHERE clause.

Why is mySQL ignoring this and not displaying the results? Any ideas?

MODX Staff
10,725 Posts

opengeek Reply #2, 14 years, 3 months ago

You would need to parse the tag before executing the query doing it like that. Try something like this, so the tag gets parsed before it gets sent to the Snippet.

[[!getMemberItems? &username=`[[!+username]]`]]

$query = "select * from items WHERE Username LIKE ?";
$stmt = $modx->prepare($query);
$stmt->execute(array($username));
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC)) {
    // collect your output here
}

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

288 Posts

galahad5 Reply #3, 14 years, 3 months ago

I tried using this:

[[!edititem? &username=`[[!+username]]`]]

]

This gives the same result- empty results basically.

But I'm guessing the problem is it isn't a module snippet, it's just some custom PHP that I made to pull the data from the table (and the table isn't in the same db as MODx - not sure if that matters?)

Basicall, when a user adds an item it's fine, I use a hidden input to add the username (got from Profile) into the items table- that all works.

I also tried adding the other lines into the snippet (I'm assuming they should work instead of my While loop for pulling the data) but they threw errors. [ed. note: galahad5 last edited this post 14 years, 3 months ago.]

288 Posts

galahad5 Reply #4, 14 years, 3 months ago

I've tried various ways of getting this set up and nothing seems to work- surely it ought to be easy enough just to get MODx to pull data from the db table and present it?

So far I've got as far as this in the snippet (I tried entering the MODX-specific code but as it isn't normal PHP syntax I can't really tell what's wrong with it and why it isn't working...)

The code below just generates a "Couldn't execute query" in the resource...

<?php
echo '<h2>Edit Your Items, [[+username]]</h2>';
    

include ('inc/dbconnect.php');

  echo '<div style="margin-left:10px;"><form action="editlistings_getedit.php" method="post" name="editlistings_getedit"><input name="_User" class="formfield" type="hidden" value="[[+username]]" /><table cellpadding="10" cellspacing="0" width="480" border="0" style="font-size:10px;">
  <tr>
  <td width="60" align="left" height="40"><strong>Name</strong></td>
  <td width="60" align="left" height="40"><strong>Category</strong></td>
  <td width="60" align="left" height="40"><strong>Value</strong></td>
  <td width="60" align="left" height="40"><strong>Description</strong></td>
  <td width="60" align="left" height="40"><strong>Telephone</strong></td>
  <td width="60" align="left" height="40"><strong>Email</strong></td>
  <td width="60" align="left" height="40"><strong>Published</strong></td>
  <td width="60" align="left" height="40"><strong>Action</strong></td></tr>';

$username = '[[+username]]';
// return $username;

// Build SQL Query  
$stmt = $modx->query("select * from items WHERE Username LIKE '$username' ORDER BY ItemName"); // specify the table and field names for the SQL query
//echo $query;

 $numresults=mysql_query($stmt);
 $numrows=mysql_num_rows($numresults);
 if ($numrows == 0)
  {
  echo "<p>You don't have any items listed for swapping at the moment. Why not <a href=\"members-add-item.html\"> add a new listing</a> now?</p>";
  }

// get results
  $result = mysql_query($stmt) or die("Couldn't execute query");
// display the results returned
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $itemname = $row["ItemName"];
  $category = $row["Category"];
  $estimatedvalue = $row["EstimatedValue"];
  $itemdescription = $row["ItemDescription"];
  $telephone = $row["ContactNumber"];
  $emailaddress = $row["EmailAddress"];
  $published = $row["Published"];
  $id = $row["ID"];

echo '
<tr>
<td align="left">' . $itemname . '</td>
<td align="left">' . $category. '</td>
<td align="left">£' . $estimatedvalue . '</td>
<td align="left">' . $itemdescription . '</td>
<td align="left">' . $telephone . '</td>
<td align="left">' . $emailaddress . '</td>
<td align="left">' . $published . '</td>

<td><a href="editlistings_getedit.php?ID=' . $id . '&category=' . $category . '">Edit</a>  <a href="editlistings_editdelete.php?ID=' . $id . '" onclick="return confirm(\'Are you sure you want to delete this item? Please note that the item cannot be restored once deleted!\');">Delete</a></td></tr>' ;
echo '<tr><td colspan="8"><hr></td></tr>';
$count++ ;
}
echo '</table></form></div>';

MODX Staff
10,725 Posts

opengeek Reply #5, 14 years, 3 months ago

You are mixing code for the mysql extension with MODX, which uses PDO via xPDO. This is not going to work. Pick one method or the other.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

288 Posts

galahad5 Reply #6, 14 years, 3 months ago

Ok, so would it work (or should it work) if I just run a standard mySQL query and PHP while loop as I would for a non-MODX site? I'd rather do that if possible, only when I tried before it wouldn't pull any data from the table because it didn't seem to recognise

[[+username]]

even though I could echo it in the snippet and it showed the user's username... huh

In the resource, I'm calling it as follows:

[[!Profile]]

[[!edititem? &username=`[[!+username]]`]]

And I've changed the edititems snippet so it's now as follows:

<?php
echo '<h2>Edit Your Items, [[+username]]</h2>';

include ('inc/dbconnect.php');

  echo '<div style="margin-left:10px;"><form action="editlistings_getedit.php" method="post" name="editlistings_getedit"><input name="_User" class="formfield" type="hidden" value="[[+username]]" /><table cellpadding="10" cellspacing="0" width="480" border="0" style="font-size:10px;">
  <tr>
  <td width="60" align="left" height="40"><strong>Name</strong></td>
  <td width="60" align="left" height="40"><strong>Category</strong></td>
  <td width="60" align="left" height="40"><strong>Value</strong></td>
  <td width="60" align="left" height="40"><strong>Description</strong></td>
  <td width="60" align="left" height="40"><strong>Telephone</strong></td>
  <td width="60" align="left" height="40"><strong>Email</strong></td>
  <td width="60" align="left" height="40"><strong>Published</strong></td>
  <td width="60" align="left" height="40"><strong>Action</strong></td></tr>';

$username = '[[+username]]';
// return $username;

// Build SQL Query  
$query = "select * from items WHERE Username LIKE '$username' ORDER BY ItemName"; // specify the table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);
 if ($numrows == 0)
  {
  echo "<p>You don't have any items listed for swapping at the moment. Why not <a href=\"members-add-item.html\"> add a new listing</a> now?</p>";
  }

// get results
while ($row= mysql_fetch_array($result)) {
  $itemname = $row["ItemName"];
  $category = $row["Category"];
  $estimatedvalue = $row["EstimatedValue"];
  $itemdescription = $row["ItemDescription"];
  $telephone = $row["ContactNumber"];
  $emailaddress = $row["EmailAddress"];
  $published = $row["Published"];
  $id = $row["ID"];

echo '
<tr>
<td align="left">' . $itemname . '</td>
<td align="left">' . $category. '</td>
<td align="left">£' . $estimatedvalue . '</td>
<td align="left">' . $itemdescription . '</td>
<td align="left">' . $telephone . '</td>
<td align="left">' . $emailaddress . '</td>
<td align="left">' . $published . '</td>

<td><a href="editlistings_getedit.php?ID=' . $id . '&category=' . $category . '">Edit</a>  <a href="editlistings_editdelete.php?ID=' . $id . '" onclick="return confirm(\'Are you sure you want to delete this item? Please note that the item cannot be restored once deleted!\');">Delete</a></td></tr>' ;
echo '<tr><td colspan="8"><hr></td></tr>';
$count++ ;
}
echo '</table></form></div>';
?>

If I substitute the $username for the actual value of the user's login, the query works, so as far as I can tell that's all fine. I just can't see why it doesn't allow the use of the variable generated from [[+username]]...??? [ed. note: galahad5 last edited this post 14 years, 3 months ago.]

288 Posts

galahad5 Reply #7, 14 years, 3 months ago

Anyone know what I need to do to get it to accept the $username variable? There's got to be a way?

MODX Staff
10,725 Posts

opengeek Reply #8, 14 years, 3 months ago

I explained how once; you are changing the value of $username to the tag string in your snippet. The tag is already setting the proper value of $username; you are losing the value at that point.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

288 Posts

galahad5 Reply #9, 14 years, 3 months ago

Ok, but if I try putting

 [[+username]]

in my query instead of $username, that doesn't work either. So what exactly do I need to put in my query?

24,544 Posts

BobRay Reply #10, 14 years, 3 months ago

You can't use MODX tags at all in PHP code. PHP doesn't know what to do with them.

Your echo statement makes it appear to work because you are echoing the tag, which MODX interprets when it renders the page.

This line:

$username = '[[+username]]';

is setting the $username to that literal string, which doesn't work in your query.

replace it with this:

$username = $scriptProperties['username'];

It will be set properly.

echo '<h2>Edit Your Items,' . $username . '</h2>';

BTW, you should really use an $output variable for all your output and put return $output at the end.

$output =  '<h2>Edit Your Items,' . $username . '</h2>';
$output .= 'More stuff';

return $output;

------------------------------------------------------------------------------------------
PLEASE, PLEASE specify the version of MODX you are using.
MODX info for everyone: http://bobsguides.com/modx.html

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