Integrate SELECT

[yandoo]

Hi

 

Im have 2 different SELECT queries and need to integrate them..

 

$query_foods = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_test['AnimalTypeID'] . "'";
;

 

and..

 

$query_foods = "SELECT * FROM foods WHERE foodID NOT IN (SELECT foodID FROM favouritefoods WHERE AnimalID='" . $row_test['AnimalID'] . "')";

 

Is there a way i can integrate these together???

 

Thanks 

[BlueSkyIS]

use AND

 

$query_foods = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_test['AnimalTypeID'] . "'";
$query_foods .= " AND oodID NOT IN (SELECT foodID FROM favouritefoods WHERE AnimalID='" . $row_test['AnimalID'] . "')";

[fenway]

NOT IN() isn't great... a LEFT JOIN would be letter.

[yandoo]

Hi there,

 

Thanks forthe reply, ive been testing it out and cant get it of the SELECT statement is working. Nothing is displayed when echoed out. ??? What am i missing

 

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

[b]if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO favouritefoods (AnimalFoodID, AnimalID, FoodID) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['animalfoodid'], "int"),
                       GetSQLValueString($_POST['animalid'], "int"),
                       GetSQLValueString($_POST['select'], "text"));[/b]

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

  $insertGoTo = "add_animal_food.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_foods = "SELECT * FROM foods ORDER BY FoodID ASC";
$foods = mysql_query($query_foods, $woodside) or die(mysql_error());
$row_foods = mysql_fetch_assoc($foods);
$totalRows_foods = mysql_num_rows($foods);

$maxRows_animalfoods = 10;
$pageNum_animalfoods = 0;
if (isset($_GET['pageNum_animalfoods'])) {
  $pageNum_animalfoods = $_GET['pageNum_animalfoods'];
}
$startRow_animalfoods = $pageNum_animalfoods * $maxRows_animalfoods;

mysql_select_db($database_woodside, $woodside);
$query_animalfoods = "SELECT * FROM favouritefoods";
$query_limit_animalfoods = sprintf("%s LIMIT %d, %d", $query_animalfoods, $startRow_animalfoods, $maxRows_animalfoods);
$animalfoods = mysql_query($query_animalfoods, $woodside) or die(mysql_error());
$row_animalfoods = mysql_fetch_assoc($animalfoods);

if (isset($_GET['totalRows_animalfoods'])) {
  $totalRows_animalfoods = $_GET['totalRows_animalfoods'];
} else {
$all_animalfoods = mysql_query($query_animalfoods);
  $totalRows_animalfoods = mysql_num_rows($all_animalfoods);
}
$totalPages_animalfoods = ceil($totalRows_animalfoods/$maxRows_animalfoods)-1;

[b]mysql_select_db($database_woodside, $woodside);
$query_test = "SELECT * FROM favouritefoods 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]mysql_select_db($database_woodside, $woodside);
$query_foodlist = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_test['AnimalTypeID'] . "'";
$query_foodlist .= " AND FoodID NOT IN (SELECT FoodID FROM favouritefoods WHERE AnimalID='" . $row_test['AnimalID'] . "')";
$foodlist = mysql_query($query_foodlist, $woodside) or die(mysql_error());
$row_foodlist = mysql_fetch_assoc($foodlist);
$totalRows_foodlist = mysql_num_rows($foodlist);[/b]

[b]<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <table width="362" border="0">
        <tr>
          <td width="109"><span class="style11">Add New Food:</span></td>
          <td width="108" align="center"> <label>
  <select name="select">
    <?php
do {  
?>
    <option value="<?php echo $row_foodlist['FoodID']?>"><?php echo $row_foodlist['FoodName']?></option>
    <?php
} while ($row_foodlist = mysql_fetch_assoc($foodlist));
  $rows = mysql_num_rows($foodlist);
  if($rows > 0) {
      mysql_data_seek($foodlist, 0);
  $row_foodlist = mysql_fetch_assoc($foodlist);
  }
?>
  </select>
  </label></td>
          <td width="123" align="center"><input type="submit" name="Submit" value="Submit" /></td>
        </tr>
<tr><td align="center"><span class="style7 style13"><strong>This Animals Foods:</strong></span></td>
      </tr>
  <?php do { ?>
    <tr>        
        <td align="center"><span class="style12"><?php echo $row_test['FoodID']; ?></span></td>
        
      </tr><?php } while ($row_test = mysql_fetch_assoc($test)); ?>
          </table></td></tr>
<p>
    <input name="animalfoodid" type="hidden" id="animalfoodid" value="<?php echo $totalRows_animalfoods +1 ?>" />
    <input name="animalid" type="hidden" id="animalid" value="<?php echo $row_animal['AnimalID']; ?>" />
  </p>
  
<input type="hidden" name="MM_insert" value="form1">
</form></td> </tr> </table>
</body> </html>
<?php
mysql_free_result($user_conditional);
mysql_free_result($animal);
mysql_free_result($foods);
mysql_free_result($test);
mysql_free_result($animalfoods);[/b]
?>

 

Thanks

[fenway]

Don't post your entire code -- just the lines with the relevant query.

[yandoo]

Hi

 

Yeah ok sorry, i did ty and bold the relevant bits....

 

mysql_select_db($database_woodside, $woodside);
$query_foodlist = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_test['AnimalTypeID'] . "'";
$query_foodlist .= " AND FoodID NOT IN (SELECT FoodID FROM favouritefoods WHERE AnimalID='" . $row_test['AnimalID'] . "')";
$foodlist = mysql_query($query_foodlist, $woodside) or die(mysql_error());
$row_foodlist = mysql_fetch_assoc($foodlist);
$totalRows_foodlist = mysql_num_rows($foodlist);

 

<select name="select">
<?php do { ?>
    <option value="<?php echo $row_foodlist['FoodID']?>"><?php echo $row_foodlist['FoodName']?></option>
    <?php
} while ($row_foodlist = mysql_fetch_assoc($foodlist));
  $rows = mysql_num_rows($foodlist);
  if($rows > 0) {
      mysql_data_seek($foodlist, 0);
  $row_foodlist = mysql_fetch_assoc($foodlist);  }?>
  </select>

 

The drop down is suppose to list the results of the integrated SELECT...I have had both SELECT's working seperately jsut not together...

 

Please help

 

Thanks

[fenway]

Could you echo $query_foodlist?

[yandoo]

Hi,

 

Yes i can and the echo says: SELECT * FROM foods WHERE AnimalTypeID='' AND FoodID NOT IN (SELECT FoodID FROM favouritefoods WHERE AnimalID='52')

 

Thanks

[yandoo]

Hi all,

 

Think i fixed it!! need to change $test to $animal

 

 

line:

$query_foodlist = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_testl['AnimalTypeID'] . "'";

 

to:

$query_foodlist = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_animal['AnimalTypeID'] . "'";

 

 

Thanks all for your help