What is the indivual result variable?

[donnan]

I used the IN clause to get the id of 4 of my users from their usernames. The problem is the return is $results1. What would be the 4 individual variable names I would use to be able to access the returned data? eg; $results1->id(0), $results1->id(1)......

Code: Select all

$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('username').' IN ('.$db->quote($row['r_1']).','.$db->quote($row['r_2']).','.$db->quote($row['r_3']).','.$db->quote($row['r_4']).')');
$db->setQuery($query);
$results1 = $db->loadObjectList();

foreach ($results1 AS $result1) {
echo "<br>".$result1->id." username ".$result1->username;
}

I tried using this, but it is not correct.

Code: Select all

echo $result1->id[0]."is id and username is:".$result1->username[0].", ".$result1->id[1]." is 2nd id and their username is:".$result1->username[1];

[SharkyKZ]

Use keys on result array:

Code: Select all

echo $result1[0]->id."is id and username is:".$result1[0]->username.", ".$result1[1]->id." is 2nd id and their username is:".$result1[1]->username;

[donnan]

Use keys on result array:

Code: Select all

echo $result1[0]->id."is id and username is:".$result1[0]->username.", ".$result1[1]->id." is 2nd id and their username is:".$result1[1]->username;

Thanks for replying SharkyKZ.

Unfortunately I get this error using this code.

"Cannot use object of type stdClass as array"

[SharkyKZ]

$result1 should probably be $results1:

Code: Select all

echo $results1[0]->id."is id and username is:".$results1[0]->username.", ".$results1[1]->id." is 2nd id and their username is:".$results1[1]->username;

[donnan]

I am trying to get the user_id of 4 of my members using this line. Perhaps I need a different approach.

Code: Select all

$query->where($db->quoteName('username').' IN ('.$db->quote($row['r_1']).','.$db->quote($row['r_2']).','.$db->quote($row['r_3']).','.$db->quote($row['r_4']).')');

Can you suggest how else I could get the user id's of 4 members in 1 call to the database?

[donnan]

I am using this code in the middle of a foreach loop and I think that is also causing the problem.

I commented out the loop and used the variable $results1 and it gave me the id for only 1 member and left the other 3 blank.

[donnan]

I have 4 variables holding their usernames, I need to retreive their user-id for. $row[r_1},$row[r_2},$row[r_3},$row[r_4}

[SharkyKZ]

Your query should return correct results. But if $row contains only the usernames you want to lookup, you can avoid writing each item in the query by using implode():

Code: Select all

$db = JFactory::getDbo();

foreach($row as $key => $username)
{
	$row[$key] = $db->quote($username);
}

$query = $db->getQuery(true)
	->select(array('id', 'username'))
	->from('#__users')
	->where($db->quoteName('username') . ' IN (' . implode(',', $row) . ')');
	
$db->setQuery($query);

$results1 = $db->loadObjectList();

As for using the results, the foreach loop you have at the beginning should work fine as it is. The rest depends on what exactly you're trying to accomplish.

Finally, if you have doubts about results, do a var_dump(). If results are correct, it should output an array containing objects with id and username properties, e.g.:

Code: Select all

array(2) {
  [0]=>
  object(stdClass)#2032 (2) {
    ["id"]=>
    int(276)
    ["username"]=>
    string(6) "Admin"
  }
  [1]=>
  object(stdClass)#2033 (2) {
    ["id"]=>
    int(278)
    ["username"]=>
    string(4) "test"
  }
//...
}

[donnan]

I need to get the 4 userid's before it goes into the loop. I just thought there would have to be a better way than to do 4 calls to the database.

Thanks for your replies mate. Most appreciated.

[SharkyKZ]

If you're running this query to get IDs to be used in another query, select only the IDs and use $db->loadColumn() to load them as simple array:

Code: Select all

$db = JFactory::getDbo();

foreach($row as $key => $username)
{
   $row[$key] = $db->quote($username);
}

$query = $db->getQuery(true)
   ->select($db->quoteName('id'))
   ->from($db->quoteName('#__users'))
   ->where($db->quoteName('username') . ' IN (' . implode(',', $row) . ')');
$db->setQuery($query);
$results1 = $db->loadColumn();

Now $results1 is an array containing just the IDs. You can use it with implode() in your next query:

Code: Select all

$query->where($db->quoteName('some_user_id') . ' IN (' . implode(',', $results1) .')');

[donnan]

Thanks SharkyKZ......sorry mate...I have brain overload. Not sure how I can use that. I don't know what user id is, that is why I am trying to get it.

[SharkyKZ]

The code in bigger snippet above will get you an array of IDs ($results1) based on usernames ($row). This is assuming you need IDs of those 4 usernames to be used in another query.

[donnan]

This is what I have now. But I can't see where it knows it has to use the variables $row['r_1'], $row['r-2'], $row['r_3'], $ro['r_4'] to get their id numbers.

Code: Select all

$db = JFactory::getDbo();
foreach($row as $key => $username)
{
   $row[$key] = $db->quote($username);
}

$query = $db->getQuery(true)
   ->select($db->quoteName('id'))
   ->from($db->quoteName('#__users'))
   ->where($db->quoteName('username') . ' IN (' . implode(',', $row) . ')');
$db->setQuery($query);
$results1 = $db->loadColumn();

if (!$r1id){
$r1id = $results1['id'];
$r1u = $row['r_1'];
} elseif (!r2id){
$r2id = $result1s['id'];
$r2u = $row['r_2'];
} elseif (!r3id){
$r3id = $results1['id'];
$r3u = $row['r_3'];
} elseif (!r4id){
$r4id = $results1['id'];
$r4u = $row['r_4'];
} else {
$r1id = '';
$r2id = '';
$r3id = '';
$r4id = '';
$r1u = '';
$r2u = '';
$r3u = '';
$r4u = '';
}

echo $r1id." is id and username is: ".$r1u.", ".$r2id." is 2nd id and their username is: ".$r2u.", ".$r3id." is 3rd and username is: ".$r3u.", ".$r4id." is 4th id and their username is: ".$r4u;

[SharkyKZ]

It inputs the whole $row array. This is assuming that $row is an array that contains only usernames you want to lookup in the database:

Code: Select all

->where($db->quoteName('username') . ' IN (' . implode(',', $row) . ')');

The full query returns only IDs (no usernames). This is may or may not be correct. Again, it all depends on what you're trying to accomplish. What results do you need? Just the IDs or usernames too? And how are you planning to use them?

[donnan]

Looks like I have to do 4 calls to the database. It seems this is not going to do the job.

Thanks for trying though mate. Much appreciated.

I just tried to get a single user-id and it only returned the first character. I can't win!.....LOL

Two days I have been trying to work this out.