Can I do this in on query?

I'd love to know how I could do this in one query.. I've been teaching myself and would love to see how to make more complicated or better said, more eloquent queries... I was thinking that it should be possible to at the least take the SELECT queries together and the same with the inserts... but can this be done in one shorter query?

I really want to learn and any help here would be great.. thank you...

Jeremy

global $gCms;
	$db = &$gCms->db;
	$config =& $gCms->GetConfig();
	$result = array();	
	$uNameresult = array();		
	////up to this point I only have the user information so I need to get the groups they add join
	$query = "SELECT userid FROM ".
		cms_db_prefix()."module_feusers_belongs JOIN ".
		cms_db_prefix()."module_feusers_groups ON id=groupid where groupname='tester'";
	$dbresult = $db->Execute( $query );
	if($dbresult && $dbresult->RecordCount() > 0) {
		while ( $row = $dbresult->FetchRow() ) {
			array_push($result, $row['userid']);
		}
	}
 
	foreach($result as $uid){
		
		$query2 = "SELECT userid, username FROM ".
		cms_db_prefix()."module_feusers_belongs JOIN ".
		cms_db_prefix()."module_feusers_users ON id=userid where userid=".$uid."";
		$dbresult = $db->Execute( $query2 );
		if($dbresult && $dbresult->RecordCount() > 0) {
			while ( $row = $dbresult->FetchRow() ) {
			
			//$query = "INSERT INTO ".cms_db_prefix()."module_cgcalendar_categories (category_id, category_name, category_order) VALUES (".$uid.", ".$row['username'].", 50)
			$query = "INSERT INTO ".cms_db_prefix()."module_cgcalendar_categories (category_id, category_name, category_order) VALUES (".$uid.", '".$row['username']."', 50)
			ON DUPLICATE KEY UPDATE category_id=".$uid."";
			$result = $db->Execute($query);
			if(!$result) {
			  echo 'Error: '.mysql_error();
			}else{
			  echo "<h3>Update Complete".
			  //$uid.", ".$SetPart.", ".$timeBlocksIncro.", ".$startOfDay.", ".$hoursOpen.", ".$DayAhead.
			  "!</h3><br/><br/>";
			}
			}	
		}
		$query = "INSERT INTO ".cms_db_prefix()."module_feusers_booking (id, BKSet, TB, SD, HO, DA) VALUES (".$uid.", 0, 60, 7, 12, 7) 
		ON DUPLICATE KEY UPDATE id=".$uid."";
		$result = $db->Execute($query);
		if(!$result) {
		  echo 'Error: '.mysql_error();
		}else{
		  echo "<h3>Update Complete".
		  //$uid.", ".$SetPart.", ".$timeBlocksIncro.", ".$startOfDay.", ".$hoursOpen.", ".$DayAhead.
		  "!</h3><br/><br/>";
		}
	}

Open in new window

LVL 4
jeremyBass26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ivo StoykovCommented:
unless you do not connect from one another db you do not need cms_db_prefix()
creating any statement depends on table structure and data population according to the logic behing the db. All these are missing so the sample below is a mechanical combination of your selects and might produce wrong result.

Nevertheless you might give a try...
HTH
Ivo Stoykov

SELECT userid 
FROM module_feusers_belongs b, module_feusers_groups g, module_feusers_users u
where b.id=g.groupid and g.groupname='tester'
and b.id=u.userid and u.userid=".$uid.";

Open in new window

0
jeremyBass26Author Commented:
Um... I'm sorry I think you misunderstood ... it works,  what I put up... and cms_db_prefix() is required...

But here is the table, it was in the related post... I just thought people would read that too.. sorry...


module_feusers_booking
 ID       BKSet       TB       SD       HO       DA



module_cgcalendar_categories
 category_id       category_name       category_order



module_feusers_belongs
userid       groupid


module_feusers_groups
id       groupname       groupdesc



module_feusers_users
 id       username       password       createdate       expires


Really I want to make only one query, that is what I'm asking.. I know I can combine the SELECT queries... but how to do it all in one... that is what I want to learn here...

Thank you for the help...

Jeremy
Jeremy
0
webvogelCommented:
I can't test this, so try. Insert isn't possible in one query. You can put the query in the database directly, so you see better the results.
$query = "SELECT userid, username FROM ".
                cms_db_prefix()."module_feusers_belongs JOIN ".
                cms_db_prefix()."module_feusers_groups ON id=groupid 
          GROUP BY userid
	  HAVING groupname='tester'";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

webvogelCommented:
Sorry, JOIN is not the same, try:
But I'm not sure about the a., b. and c., maybe you have to change something.
$query = "SELECT a.userid, a.username FROM ".cms_db_prefix()."module_feusers_belongs a 
				JOIN ".cms_db_prefix()."module_feusers_groups b ON b.id=a.groupid 
				JOIN ".cms_db_prefix()."module_feusers_users c ON c.id=a.userid
				GROUP BY a.userid
				HAVING b.groupname='tester'";

Open in new window

0
Ivo StoykovCommented:
I do not know which database are you using but from your response I didn't see whether proposed select is useful for not and why.
If you need the prefix (which I doubt) just add it to the source and run it (my advice is to run query in the database, not through script).
Please send feedback
HTH
Ivo Stoykov
0
jeremyBass26Author Commented:
>>>>Insert isn't possible in one query
I can't seem to find anything to dispute this statment and this is what I asked about... cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.