PHP to scrub against existing rows in MySQL table

[soma56]

I'm racking my brain trying to figure out the most effective way (any way) to insert some information into a table if it hasn't been previously inserted. Here's a simplified sample that I'm working with:

--

 

Column 1      Column 2    Coloumn3

Sue              Cat              Adopted

Jack              Dog            Adopted

Cujo              Dog            Not Adopted

 

--

 

The column I'm trying to scrub my loop against is column 3. Here's what I have so far...

 

<?PHP 

$question = "ADOPTED";

$res = mysql_query("SELECT file FROM temp_data");

while($rw = mysql_fetch_array($res)){

$arr[] = $rw['file'];

if (in_array($question, $arr)){
 echo "ADOPTED";

//Leave it alone...

} else {
echo "NOT ADOPTED<br />";

//Insert My New Data

        }
?>

 

I think I'm on the right path by using the 'in_array' function to check if '$question' variable is within the the '$arr' array, however, logically (to me) it makes sense but it is not working. Suggestions?

 

I should also mention that I'm not receiving any error messages but rather, a completely inaccurate result instead.

[objnoob]

You are making this hard on yourself.

 

If you want to get pets have not been adopted, query the database for that

 

$query = "SELECT name, specie WHERE status != 'adopted'";

$result = mysql_query($query);

 

echo 'The pets that are still available:<br/>';

while ($row = mysql_fetch_assoc($result)){

echo $row['name'] . 'is an available' . $row['specie'] . '<br/>;

}

 

 

Remember with your current method... 'ADOPTED' will never match 'Adopted' using the in_array!

[soma56]

Thanks. I don't want to get a list of pets that were adopted. What I want to do is this:

 

Let's say I have a table that contains only adopted pets. My list contains adopted and unadpoted pets. I wish to add the unadpoted pets only to the list. 

[objnoob]

So you have 2 tables? Table 1 contains only adopted pets, and table 2 has both adopted (also found in table 1) and unadopted pets?  You can query an OUTER or RIGHT JOIN on Table 1 to Table 2, and only return only pets of Table 2 not found in table 1 by including a WHERE Table1.name IS NULL.

 

If you list is an ARRAY you can loop through that array and compare the adoption status member:

 

$AllThePetsEver[] = array('sue', 'dog','adopted');
$AllThePetsEver[] = array('jack', 'cat','adopted');
$AllThePetsEver[] = array('cujo', 'dog','not adopted');
foreach ($AllThePetsEver as $Pet){
  if ($Pet[2] == 'not adopted'){
//THE PET IS NOT ADOPTED
echo 'Not Adopted<br/>';
  }else{
//THE PET IS ADOPTED;
echo 'Has been Adopted<br/>';
  }
}

[hitman6003]

Warning ahead of time...I haven't tested any code........

 

$pet_names = array('bob', 'sue', 'larry', 'bill');

// find pets who are in the database as adopted and in your array of names
$result = mysql_query('SELECT * FROM pets WHERE name IN("' . implode("\",", $pet_names) . '")');

// put the adopted pets into their own array
while ($row = mysql_fetch_assoc($result)) {
  $adopted[] = $row['name'];
}

// determine the unadopted pets from the full list using array_diff
$not_adopted = array_diff($pet_names, $adopted);