Tricky Select
Posted: Mon Mar 24, 2008 3:45 pm
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:
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:
Im not sure how to do it thought, couold you help me please??
Thank You
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> </td>
<td align="center"> </td>
<td align="left"> </td>
</tr><tr><td> </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"> </td>
</tr></table>
<table width="362">
<tr>
<td></td>
<td align="center"> </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);
?>
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.idThank You