Accessing database with loop inside of loop.

[donnan]

Hi guys,

I am really new to using database commands under joomla structure so I hope I am not annoying anyone.

I need to get the user id of 4 members to use later in the script.

Here is what I have tried. It works ok, but seems alot of code to get the job done. Is there a better way to access this information?

Code: Select all

	$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_1']));
$db->setQuery($query);
$results1 = $db->loadObjectList();

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

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_2']));
$db->setQuery($query);
$results2 = $db->loadObjectList();

foreach ($results2 AS $result2) {
echo "<br>".$result2->id." email ".$result2->username;
}

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_3']));
$db->setQuery($query);
$results3 = $db->loadObjectList();

foreach ($results3 AS $result3) {
echo "<br>".$result3->id." email ".$result3->username;
}

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_4']));
$db->setQuery($query);
$results4 = $db->loadObjectList();

foreach ($results4 AS $result4) {
echo "<br>".$result4->id." email ".$result4->username;
}


exit;

I am not sure if I have to have this each time I want to access the database. $db = JFactory::getDbo();

[Webdongle]

JApplication::getUserState https://docs.joomla.org/Using_User_State_Variables perhaps ?

[donnan]

Thanks for replying webdongle.

I don't think getUserState is the way to go as the users aren't online when I am trying to get their user id.

I am retreiving their user id from the database...just thought there must be a simple way to retrieve 4 user id's at one call.

[Per Yngve Berg]

Have you tried this?

Code: Select all

$query->where($db->quoteName('username') . ' IN '. $db->quote($row));

Should return all 4 users if $row contains 4 usernames.

WHERE `username` IN ('Paul', 'Peter', 'Jenny', 'Lisa')

[donnan]

Have you tried this?

Code: Select all

$query->where($db->quoteName('username') . ' IN '. $db->quote($row));

Should return all 4 users if $row contains 4 usernames.

WHERE `username` IN ('Paul', 'Peter', 'Jenny', 'Lisa')

I haven't tried that.

I am not sure how I can put that into the code I have already.

Thank you for replying.

[donnan]

I tried this:

Code: Select all

//get the userid of the 4 toplevel members.//
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
//$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_1']));
$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;
}

BUT....get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''donnan''wisesedi''getonid''Moneybee'' at line 3 .

But, the returned values are correct.

[Per Yngve Berg]

The parentheses is missing.

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'].')');
}

[donnan]

This is what I have now.

Code: Select all

//get the userid of the 4 toplevel members.//
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
//$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_1']));
$query->where($db->quoteName('username').' LIKE '.$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;
}

Now giving me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wisesedi','getonid','Moneybee'' at line 3

[Per Yngve Berg]

It's not the same line I posted. ) is not inside quoted string.

[donnan]

I have posted the line you suggested Per Ynge Berg. Code is below.

Code: Select all

foreach($rows AS $row) {

//get the userid of the 4 toplevel members.//
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
//$query->where($db->quoteName('username') . ' LIKE '. $db->quote($row['r_1']));
//$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']));
$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;
}

Returns this error: in the error log. PHP Parse error: syntax error, unexpected ';', expecting ',' or ')' in /home/donnan/public_html/pb31/ubuntu/getdata.php on line 154

[donnan]

I had it work once then changed something and now I just keeping this syntax error:
Parse error: syntax error, unexpected ';', expecting ',' or ')' in

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'].')))';

[Per Yngve Berg]

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']).')');

) missing from r_4.

[donnan]

I am just about ready to pull my hair out.

I have tried just about every combination of single and double quotes in all different places, changed variables to a simpler form and now it's saying the semicolon is the problem.

PHP Parse error: syntax error, unexpected ';', expecting ',' or ')'

Code: Select all

$r1 = $row['r_1'];
$r2 = $row['r_2'];
$r3 = $row['r_3'];
$r4 = $row['r_4'];


//get the userid of the 4 toplevel members.//
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'username')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName(username).' IN ('. $db->quote('$r1').','. $db->quote('$r2').','. $db->quote('$r3').','. $db->quote('$r4').'))';
$db->setQuery($query);
$results1 = $db->loadObjectList();

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

Thanks for your help

[Per Yngve Berg]

.'))'; at the end should be .')');

[donnan]

Finally!!!!

Thank you so much Per Yngve Berg......it was very frustrating.

This worked.

Code: Select all

$query->where($db->quoteName('username').' IN ('.$db->quote($r1).','.$db->quote($r2).','.$db->quote($r3).','.$db->quote($r4).')');

[SharkyKZ]

You have 3 closing brackets inside the string at the end when there should be only one. The other two should close $query->where() and $db->quote($row['r_4']):

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']).')');

[donnan]

thanks SharkyKZ...it's amazing how hard it is to see the simplest of errors at times.