Query Help - Not for the faint hearted!

yandoo · February 11, 2008

Hi there all,

Im having a right old nightmare with my php and mysql programming and was hoping for some help on it?? Please bare with me when im trying to explain my problem...i will try to be clear to illustrate whats going on...

Firstly to give u and imediate idea of what going on please look at attachment of what my page looks like..

On the page as you can see there are 3 areas that the user could input data - Please NOTE 1st 2 sections.....the animal details (Animal ID, Type, Breed, etc..) AND Animal Experience Check boxes are greyed out and not used on this page)

So we are ONLY dealing with the FearName Drop down! ALso Please NOTE nexto the drop down (on left) is query that echo's what fears the user has just inputed!

The tables that are being used to produce this:

Animal Table - AnimalID, Name, Sex, Experiences (check boxes bit)

AnimalFear Table - AnimalFearID, FearID & AnimalID

Fear Table - FearID, FearName

In order to get the records to be displayed after the user has updated them i used this query:

$query_animalFear2 = "SELECT FearID FROM animalfears WHERE AnimalID='" . $row_animalFear['AnimalID'] . "'";

NOTE: AnimalID is the connection in Animal and AnimalFear table, and this query displays what fears the user selects an updates on the drop down...this works fine.....

My problem lies with the drop down ONLY! For vallidation purposes i need to make it so that once the user entered a fear (e.g. DARK)after update it no longer appears as an option...to avoid the user making multiple entires of the same fear....

I initially tried to use a variation of the above query:

$query_fears = "SELECT FearID FROM fears WHERE FearID!= '" . $row_animalFear2['FearID'] . "'";

Please NOTE added ! to the query and changed appropriate query names, so that it selects the fearID from the table fears Where fearID is NOT = FearID of the displayed records that the user ha just inputted!

This query doesnt work at all! There is no change whatsoever to the drop down even after a record is updated, so the user could input the same fear twice!!!

Is this approach even dooable?

Please help me i am so desperate ???

Heres my full page of code:

<?php require_once('Connections/woodside.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$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 animalfears (AnimalFearID, AnimalID, FearID) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['animalFearid'], "text"),
                       GetSQLValueString($_POST['animalid'], "int"),
                       GetSQLValueString($_POST['fearid'], "text"));

  mysql_select_db($database_woodside, $woodside);
  $Result1 = mysql_query($insertSQL, $woodside) or die(mysql_error());

  $insertGoTo = "stage3.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_woodside, $woodside);
$query_animal = "SELECT * FROM animal ORDER BY AnimalID DESC";
$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_animalFear = "SELECT * FROM animalfears";
$animalFear = mysql_query($query_animalFear, $woodside) or die(mysql_error());
$row_animalFear = mysql_fetch_assoc($animalFear);
$totalRows_animalFear = mysql_num_rows($animalFear);

mysql_select_db($database_woodside, $woodside);
$query_fears = "SELECT FearID FROM fears WHERE FearID!= '" . $row_animalFear2['FearID'] . "'";
$fears = mysql_query($query_fears, $woodside) or die(mysql_error());
$row_fears = mysql_fetch_assoc($fears);
$totalRows_fears = mysql_num_rows($fears);

mysql_select_db($database_woodside, $woodside);
$query_animalFear2 = "SELECT FearID FROM animalfears WHERE AnimalID='" . $row_animalFear['AnimalID'] . "'";
$animalFear2 = mysql_query($query_animalFear2, $woodside) or die(mysql_error());
$row_animalFear2 = mysql_fetch_assoc($animalFear2);
$totalRows_animalFear2 = mysql_num_rows($animalFear2);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<link href="bordertop.css" rel="stylesheet" type="text/css" />
</head>

<body>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <table height ="225" border="0" align="center">
    <tr valign="baseline">
      <td  height="20" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td>
    </tr>
    <tr valign="baseline">
      <td width="87"  height="20" align="right" valign="middle" nowrap="nowrap"><strong>Animal ID:</strong></td>
      <td width="6" align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="20" width="158"  valign="middle"><?php echo $row_animal['AnimalID']; ?></td>
    </tr>
    <tr valign="baseline">
      <td height="20" align="right" valign="middle" nowrap="nowrap"><strong>Animal Type:</strong></td>
      <td align="right" valign="middle"  nowrap="nowrap"> </td>
      <td height="20" valign="middle"><?php echo $row_animal['AnimalTypeID']; ?></td>
    </tr>
    <tr valign="baseline">
      <td height="20" align="right" valign="middle" nowrap="nowrap"><strong>Name:</strong></td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="20" valign="middle"><label></label>
          <label><?php echo $row_animal['Name']; ?></label></td>
    </tr>
    <tr valign="baseline">
      <td height="25" align="right" valign="middle" nowrap="nowrap"><strong>Breed:</strong></td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="25" valign="middle"><label><?php echo $row_animal['BreedID']; ?></label></td>
    </tr>
    <tr valign="baseline">
      <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Age:</strong></td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="25" valign="middle"><label><?php echo $row_animal['Age']; ?></label></td>
    </tr>
    <tr valign="baseline">
      <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Sex:</strong></td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="25" valign="middle"><label><?php echo $row_animal['Sex']; ?></label></td>
    </tr>
    <tr valign="baseline">
      <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Date:</strong></td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="25" valign="middle"><?php echo $row_animal['Date']; ?></td>
    </tr>
    <tr valign="baseline">
      <td height="25" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td>
    </tr>
  </table>
  <p> </p>
  <table width="471" border="0" align="center">
    <tr>
      <td width="461"> </td>
    </tr>
    <tr>
      <td> </td>
    </tr>
  </table>
  <table width="496" border="0" align="center">
    <tr valign="baseline">
      <td colspan="11" align="right" nowrap="nowrap"><div align="center">
          <p><strong>Animal Experience</strong></p>
        <p> </p>
      </div></td>
    </tr>
    <tr valign="baseline">
      <td width="97" height="30" align="right" valign="baseline" nowrap="nowrap">House Trained:</td>
      <td width="27" valign="baseline"><input <?php if (!(strcmp($row_animal['HouseTrained'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox2" value="checkbox" /></td>
      <td width="7" valign="baseline"> </td>
      <td width="62" valign="baseline">Cats:</td>
      <td width="36" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpCat'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox22" value="checkbox" /></td>
      <td width="15" valign="baseline"> </td>
      <td width="39"> Cars:</td>
      <td width="23" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpCar'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox24" value="checkbox" /></td>
      <td width="9" valign="baseline"> </td>
      <td width="63" valign="baseline">Children:</td>
      <td width="72" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpChild'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox25" value="checkbox" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">On lead:</td>
      <td><input <?php if (!(strcmp($row_animal['ExpOnlead'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox27" value="checkbox" /></td>
      <td> </td>
      <td> Off Lead:</td>
      <td><input <?php if (!(strcmp($row_animal['ExpOffLead'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox" value="checkbox" /></td>
      <td> </td>
      <td>dogs:</td>
      <td><input <?php if (!(strcmp($row_animal['ExpDog'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox23" value="checkbox" /></td>
      <td> </td>
      <td>alone:</td>
      <td><input <?php if (!(strcmp($row_animal['ExpAlone'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox26" value="checkbox" /></td>
    </tr>
    <tr valign="baseline">
      <td colspan="11" align="right" nowrap="nowrap"> </td>
    </tr>
  </table>
  <p> </p>
  <table width="200" border="0" align="center">
    <tr>
      <td align="center"><input name="submit" type="submit" value="Update record" /></td>
    </tr>
  </table>
  <p> </p>
  <table height ="117" border="0" align="center">
    <tr valign="baseline">
      <td  height="20" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td>
    </tr>
    <tr valign="baseline">
      <td width="87"  height="20" align="right" valign="middle" nowrap="nowrap">Fear Name: </td>
      <td width="6" align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="20" width="158"  valign="middle"><label>
        <select name="fearid" id="fearid">
          <?php
do {  
?>
          <option value="<?php echo $row_fears['FearID']?>"><?php echo $row_fears['FearID']?></option>
          <?php
} while ($row_fears = mysql_fetch_assoc($fears));
  $rows = mysql_num_rows($fears);
  if($rows > 0) {
      mysql_data_seek($fears, 0);
  $row_fears = mysql_fetch_assoc($fears);
  }
?>
        </select>
      </label></td>
    </tr>
    <tr valign="baseline">
      <td height="20" align="right" valign="middle" nowrap="nowrap"> </td>
      <td align="right" valign="middle"  nowrap="nowrap"> </td>
      <td height="20" valign="middle"></td>
    </tr>
    <tr valign="baseline">
      <td height="20" align="right" valign="middle" nowrap="nowrap"> </td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="20" valign="middle"><label></label>
          <label></label></td>
    </tr>
    <tr valign="baseline">
      <td height="25" align="right" valign="middle" nowrap="nowrap"> </td>
      <td align="right" valign="middle" nowrap="nowrap"> </td>
      <td height="25" valign="middle"><label></label></td>
    </tr>
  </table>
  <?php do { ?>
    <table width="310" border="1">
      <tr>
        <td width="101">Fear:</td>
        <td width="193"><?php echo $row_animalFear2['FearID']; ?></td>
      </tr>
          </table>
    <?php } while ($row_animalFear2 = mysql_fetch_assoc($animalFear2)); ?><p> </p>
  <p>
    <input name="animalFearid" type="hidden" id="animalFearid" value="<?php echo $totalRows_animalFear +1 ?>" />
    <input name="animalid" type="hidden" id="animalid" value="<?php echo $row_animal['AnimalID']; ?>" />
    <?php echo $totalRows_animalFear ?> </p>
  <p> </p>
    <input type="hidden" name="MM_insert" value="form1">
</form>
<p> </p>
</body>
</html>
<?php
mysql_free_result($animal);

mysql_free_result($animalFear);

mysql_free_result($fears);

mysql_free_result($animalFear2);
?>

[attachment deleted by admin]

rhodesa · February 11, 2008

The SQL would be the following:

$query_fears = "SELECT * FROM fears WHERE FearID NOT IN (SELECT FearID FROM animalfears WHERE AnimalID='" . $row_animalFear['AnimalID'] . "')";

yandoo · February 11, 2008

That is Absolutely FANTASTIC!!!!!!!!!!!

Thank you soo much, i have been trying to get my head around that for ages!!!!

I never new you could use "NOT IN" in sql to link it to another query!

You really have no idea how happy i am now.....delierious!!!

Thanks again!

Very please happy and thankful chap!

yandoo · February 11, 2008

I dont seem to be ableto find "Topic Solved" since forum update....just incase!! This topic has been solved!!!

Thank You

phpSensei · February 11, 2008

I dont seem to be ableto find "Topic Solved" since forum update....just incase!! This topic has been solved!!!

Thank You

Ya this has been a problem, i came here to help you and noticed you've gotten help already. I hope administrators can get this fixed, its getting annoying.

rhodesa · February 11, 2008

They are supposedly working on it:

http://www.phpfreaks.com/forums/index.php/topic,181556.0.html

yandoo · February 11, 2008

Yeah is bit frustrating...as lots of topics already solved!

Gota say this website and all the community here BRILLIANT tho!!

Sign In

Query Help - Not for the faint hearted!

Recommended Posts

yandoo

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

yandoo

Link to comment

Share on other sites

yandoo

Link to comment

Share on other sites

phpSensei

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

yandoo

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information