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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
svenarildConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mankisAuthor Commented:
:) no problem
0
 
Avinash ZalaWeb ExpertCommented:
Thanks
Addy
0
 
mankisAuthor Commented:
This is great, thank you. I just needed to change  USING (PrizeGameID) into  ON (winner.PrizeID=prize.ID)
0
All Courses

From novice to tech pro — start learning today.