asked on

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

Avinash Zala

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.

Avinash Zala

Sorry its not for you, really sorry.

Thanks
Addy

mankis

ASKER

:) no problem

Avinash Zala

Thanks
Addy

ASKER CERTIFIED SOLUTION