Tricky Select

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kananga
Forum Newbie
Posts: 6
Joined: Thu Mar 13, 2008 9:37 am

Tricky Select

Post by kananga »

Hi there,

I was hoping for a little help please.

I have 3 tables in my database:

Animal (stores info on animals)
AnimalID
Name
Age

Experience(stores info experiences, i.e. house trained, on lead, off lead, cars )
ExpID
Experience name

AnimalExperience(combines primary keys Animal & Experience)
AnimalExpID
AnimalID*
ExipID"

NOTE: (* = foregin key, underline = primary key)

The bit i am stuck on is regarding adding AnimalExperiences. I have a page (hyperlink from pervious page makes sure that AnimalID number is appropriate) that a user inserts AnimalExperiences for a specifc animal. After the user inserts the record the a query searchs AnimalExperiences table for all ExpID's and dislpays them.

This works fine......(Please see attachment: SCREEN.GIFF to see what it looks like) but..

It displays the ExpID and i need it to display Experience Name

So im thinking that will involve the ExpID's that relate to an AnimalID are then queried against the Experience table to find the Experience Names.

I have tried to do this so far:

Code: Select all

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
 
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO animalexperience (AnimalExpID, AnimalID, ExpID) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['animalexperienceid'], "int"),
                       GetSQLValueString($_POST['animalid'], "int"),
                       GetSQLValueString($_POST['select'], "text"));
                       
 
                       
 
  mysql_select_db($database_woodside, $woodside);
  $Result1 = mysql_query($insertSQL, $woodside) or die(mysql_error());
 
  $insertGoTo = "add_animal_exp.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
 
$colname_animal = "-1";
if (isset($_GET['recordID'])) {
  $colname_animal = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_woodside, $woodside);
$query_animal = sprintf("SELECT * FROM animal WHERE AnimalID = %s", $colname_animal);
$animal = mysql_query($query_animal, $woodside) or die(mysql_error());
$row_animal = mysql_fetch_assoc($animal);
$totalRows_animal = mysql_num_rows($animal);
 
mysql_select_db($database_woodside, $woodside);
$query_experience = "SELECT * FROM experience ORDER BY ExpID ASC";
$experience = mysql_query($query_experience, $woodside) or die(mysql_error());
$row_experience = mysql_fetch_assoc($experience);
$totalRows_experience = mysql_num_rows($experience);
 
$maxRows_animalexperience = 10;
$pageNum_animalexperience = 0;
if (isset($_GET['pageNum_animalexperience'])) {
  $pageNum_animalexperience = $_GET['pageNum_animalexperience'];
}
$startRow_animalexperience = $pageNum_animalexperience * $maxRows_animalexperience;
 
mysql_select_db($database_woodside, $woodside);
$query_animalexperience = "SELECT * FROM animalexperience";
$query_limit_animalexperience = sprintf("%s LIMIT %d, %d", $query_animalexperience, $startRow_animalexperience, $maxRows_animalexperience);
$animalexperience = mysql_query($query_limit_animalexperience, $woodside) or die(mysql_error());
$row_animalexperience = mysql_fetch_assoc($animalexperience);
 
if (isset($_GET['totalRows_animalexperience'])) {
  $totalRows_animalexperience = $_GET['totalRows_animalexperience'];
} else {
  $all_animalexperience = mysql_query($query_animalexperience);
  $totalRows_animalexperience = mysql_num_rows($all_animalexperience);
}
$totalPages_animalexperience = ceil($totalRows_animalexperience/$maxRows_animalexperience)-1;
 
[color=#00FF00][b]//BELOW IS QUERY THAT SEARCHES DB FOR AnimalExperiences WITH AnimalID & DISPLAYS ExpID [/b]
 
[b]mysql_select_db($database_woodside, $woodside);
$query_test = "SELECT * FROM animalexperience WHERE AnimalID='" . $row_animal['AnimalID'] . "'";
$test = mysql_query($query_test, $woodside) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);
$totalRows_test = mysql_num_rows($test);[/b]
 
[b]// I NEED FOR IT TO DISPLAY THE Experience (which is the name of the exeperience)[/b][/color]
 
mysql_select_db($database_woodside, $woodside);
$query_experiencelist = "SELECT * FROM experience WHERE ExpID NOT IN (SELECT ExpID FROM animalexperience WHERE AnimalID='" . $row_test['AnimalID'] . "')";
$experiencelist = mysql_query($query_experiencelist, $woodside) or die(mysql_error());
$row_experiencelist = mysql_fetch_assoc($experiencelist);
$totalRows_experiencelist = mysql_num_rows($experiencelist);
?>
 
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <label>
  
 
   <table width="362" border="0">
 <tr>
   <td width="109"><span class="style11">Add Experience: </span></td>
   <td width="108" align="center"><select name="select">
    <?php
do {  
?>
    <option value="<?php echo $row_experiencelist['ExpID']?>"><?php echo $row_experiencelist['Experience']?></option>
    <?php
} while ($row_experiencelist = mysql_fetch_assoc($experiencelist));
  $rows = mysql_num_rows($experiencelist);
  if($rows > 0) {
      mysql_data_seek($experiencelist, 0);
      $row_experiencelist = mysql_fetch_assoc($experiencelist);
  }
?>
  </select>
  </label></td>
   <td width="131" align="center"><input type="submit" name="Submit" value="Submit" /> </td>
 </tr><tr><td>&nbsp;</td>
   <td align="center">&nbsp;</td>
   <td align="left">&nbsp;</td>
 </tr><tr><td>&nbsp;</td>
   <td align="center"><table width ="108" class="newbord"> 
  <tr><td width="108" align="center"><span class="style11 style15">This Animal's Experience:</span></td>
</tr>
  <?php do { ?>
   <tr><td align="center"><span class="style12"><?php echo $row_test['ExpID']?></span></td>
     </tr>
          
    <?php } while ($row_test = mysql_fetch_assoc($test)); ?>
    </table></td>
   <td align="left">&nbsp;</td>
 </tr></table>
 
<table width="362">
  <tr>
    <td></td>
    <td align="center">&nbsp;</td>
    <td></td>
  </tr>
  <tr><td width="109">
   </td><td align="center"><a href="add_animal2.php?recordID=<?php echo $row_animal['AnimalID']; ?>" class="style7 style16">CONTINUE</a></td>
   <td width="131"></td> </tr></table>
   
   <input name="animalexperienceid" type="hidden" id="animalexperienceid" value="<?php echo $totalRows_animalexperience +1 ?>" />
    <input name="animalid" type="hidden" id="animalid" value="<?php echo $row_animal['AnimalID']; ?>" />
    
      <input type="hidden" name="MM_insert" value="form1">
 
</form>
<?php
mysql_free_result($animal);
mysql_free_result($experience);
mysql_free_result($animalexperience);
?>
 
I have bolded the relevant bit code and commented above and below it.

Was thinking I would need to change the $test query to something like:

Code: Select all

SELECT A.*, B.* FROM some_table A, other_table B WHERE A.user = '$user_criteria' AND A.id = B.id
Im not sure how to do it thought, couold you help me please??

Thank You :)
Attachments
screen.GIF
screen.GIF (48.81 KiB) Viewed 63 times
Post Reply