MySql, PHP - can this be done with one query

I have table with prizes (TBL_PRIZE),  table with players (TBL_PRIZEGAMECONTACT) , and table winners (TBL_WINNER)  that connects ID of prizes with ID of players (not all players because not all players are winners).

User should see this:
1.prize: 1 x some prize
1. name of winner
2. prize: 3 x some prize
1. name of winner
2. name of winner
3. name of winner

I have solution for this, you can see my code, but I was wondering could this be done with just one query?

function displayLot($pgameID) {
	global $database;	
      
	  $q = "SELECT * FROM ".TBL_PRIZE." WHERE PrizeGameID=$pgameID ORDER BY ID ";

      $result = $database->query($q);
	   
	   /* Error occurred, return given name by default */
	   $num_rows = mysql_numrows($result);
	   if(!$result || ($num_rows < 0)){
		  echo "Error displaying info";
		  return;
	   }
	   if($num_rows == 0){
		  echo "Database is empty";
		  return;
	   }
	   
	    $prizerows=''; 
	    for($i=0; $i<$num_rows; $i++){
		  $id = mysql_result($result,$i,"ID");
		  $item = mysql_result($result,$i,"item");
		  $quantity = mysql_result($result,$i,"quantity");
	      $prizerows.= '<tr><td colspan="7"><p class="bigbluetext">'.$quantity.' x '.$item.'</p></td> </tr>';
          $prizerows.=displayWinners($pgameID,$id);
		}
	   echo $prizerows;
  }
	
	function displayWinners($pgameID,$prizeid) {
	global $database;	
  
  
       $q = "SELECT ".TBL_PRIZEGAMECONTACT.".personName AS personName, ".TBL_PRIZEGAMECONTACT.".address AS address, ".TBL_PRIZEGAMECONTACT.".city AS city, ".TBL_PRIZEGAMECONTACT.".phone AS phone, ".TBL_PRIZEGAMECONTACT.".email AS email, ".TBL_PRIZEGAMECONTACT.".date AS date FROM ".TBL_PRIZEGAMECONTACT.", ".TBL_WINNER." WHERE ".TBL_PRIZEGAMECONTACT.".ID=".TBL_WINNER.".ContactID AND ".TBL_WINNER.".PrizeGameID=$pgameID AND ".TBL_WINNER.".PrizeID='$prizeid' ORDER BY ".TBL_WINNER.".ID ASC ";

      $result = $database->query($q);
	   
	   /* Error occurred, return given name by default */
	   $num_rows = mysql_numrows($result);
	   if(!$result || ($num_rows < 0)){
		  echo "Error displaying info";
		  return;
	   }
	   if($num_rows == 0){
		 // echo "Winners Database is empty";
		  return;
	   }
	   /* Display table contents */
	  $contactrows=''; 
	   for($i=0; $i<$num_rows; $i++){
		  //$id = mysql_result($result,$i,"PrizeContactID");
		 // $highscoreid  = mysql_result($result,$i,"HighScoreID");
		  $personName = mysql_result($result,$i,"personName");
		  $address = mysql_result($result,$i,"address");
		  $city = mysql_result($result,$i,"city");
		  $phone = mysql_result($result,$i,"phone");
		  $email = mysql_result($result,$i,"email");
		  $date = mysql_result($result,$i,"date");
		   
		   $type="H";
	       $date = convertDate($date, $type);
		   
		   $num = $i+1;
		  if(($i % 2)==0) {
              $yellowclass = ' class="yell"';
          }else{
              $yellowclass = '';
          }
		
		  $contactrows.= '<tr '.$yellowclass .'>
      <td><a class="remove" href="#">remove </a></td>
      <td>'.($i+1).'. <strong>'.$personName.'</strong></td>
      <td>'.$address.'<span class="smalltxt"></span></td>
      <td>'.$city.'<span class="smalltxtd"></span></td>
      <td>'.$phone.'</td>
      <td><a href="mailto:'.$email.'">'.$email.'</a></td>
      <td><span>'.$date.'</span> </td>
      
    </tr>';
				  
	   }
	   return $contactrows;
    
    }

Open in new window

mankisAsked:
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.

Avinash ZalaWeb ExpertCommented:
Try this::

Add below script to head of your page..

// Create a new google map key for your website

<script src="http://maps.google.com/maps?file=api&v=2&key=Your google map key"
       type="text/javascript"></script>
<script src="http://gmaps-utility-library.googlecode.com/svn/trunk/mapiconmaker/1.0/src/mapiconmaker.js" type="text/javascript"></script>
<script type="text/javascript"
src="http://www.google.com/jsapi?key=Google map key"></script>

// Now add below script after adding the above scripts.

<script type="text/javascript">
      google.load("maps", "2");
      google.load("search", "1");
      var rlat = google.loader.ClientLocation.latitude
      var rlang = google.loader.ClientLocation.longitude;
     
</script>

// rlat you will get latitude of the location from the IP address
// rlang you will get longitude of the location from the IP address

// From this latitude and longitude you can easily get the address.
// Check below URL for that

http://www.google.com/search?source=ig&hl=en&rlz=1G1GGLQ_ENIN362&=&q=get+address+using+latitude+and+longitude&aq=1&aqi=g2&aql=&oq=get+address+using+&gs_rfai=


Hope this helps
Addy.
0
Avinash ZalaWeb ExpertCommented:
Sorry its not for you, really sorry.

Thanks
Addy
0
mankisAuthor Commented:
:) no problem
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Avinash ZalaWeb ExpertCommented:
Thanks
Addy
0
svenarildCommented:
The short answer is yes.

Give this a try, you might need to do a few minor changes in the event I misunderstood your system, but this should work.
$result = mysql_query("SELECT 
												prize.ID
												, prize.item
												, prize.quantity
												, contact.personName
												, contact.address
												, contact.city
												, contact.phone
												, contact.email
												, contact.date
											 FROM
												".TBL_PRIZE." as prize
												INNER JOIN ".TBL_WINNER." as winner
													USING (PrizeGameID)
												INNER JOIN ".TBL_PRIZEGAMECONTACT." as contact
													ON (winner.ContactID=contact.ID)
											 WHERE
											  prize.PrizeGameID=".(int) $pgameID."
											 ORDER BY
												ID ASC") or die(mysql_error());

$game_id = null;

while ($buffer = mysql_fetch_assoc($result)) {
	if (empty($game_id) || $game_id != $buffer['ID']) {
		$game_id = $buffer['ID'];
		$num = 0;

		echo '<tr><td colspan="7"><p class="bigbluetext">'.$buffer['ID'].' '.$buffer['quantity'].' x '.$buffer['item'].'</p></td> </tr>';
		}

	echo '<tr '.(($num++ % 2 == 0)?' class="yell"':'').'>
					<td><a class="remove" href="#">remove </a></td>
					<td>'.$num.'. <strong>'.$buffer['personName'].'</strong></td>
					<td>'.$buffer['address'].'<span class="smalltxt"></span></td>
					<td>'.$buffer['city'].'<span class="smalltxtd"></span></td>
					<td>'.$buffer['phone'].'</td>
					<td><a href="mailto:'.$buffer['email'].'">'.$buffer['email'].'</a></td>
					<td><span>'.convertDate($buffer['date'], "H").'</span> </td>
         </tr>';
	}

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
mankisAuthor Commented:
This is great, thank you. I just needed to change  USING (PrizeGameID) into  ON (winner.PrizeID=prize.ID)
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
MySQL Server

From novice to tech pro — start learning today.