simple query not working...

[turpentyne]

I've got to be missing something pretty basic here.. considering the query is pretty basic.

 

I'm trying to figure out how to pull a query as an array so I can compare it against another array (array_diff)

 

I'm doing a mysql_fetch_array, and I'm getting an error ( mysql_fetch_array(): supplied argument is not a valid MySQL result resource):

$checker = "SELECT ID FROM edible_uses";

 

$result2 = mysql_fetch_array($checker) or die(mysql_error());

//echoing to see if I'm getting what I need.

echo $row['ID'];

 

I've done a mysql_query and I get results. The table name and all that is correct. I'm stumped. This seems like a pretty simple query? I tried mysql_fetch_assoc as well. Same result? I tried it with an extra set of parenthesis around it. nope.

 

[KevinM1]

So... where's you're mysql_query() call, then?  And are you expecting to have many IDs?  You need to do something along the lines of:

 

$checker = "SELECT ID FROM edible_uses";

$result = mysql_query($result);

while($row = mysql_fetch_array($result))
{
   // do something with every $row['ID'] you get
}

[turpentyne]

ah! ok.. I got mixed up on that. I didn't realize I had to do mysql_query, then fetch_array. Now that that's good, any thoughts on how I compare the result with a different array and use the remainder I get to delete entries? I've got this now (the delete is toward the bottom - I just included the rest to help clarify what i'm doing a little):

 

if (isset($_POST['submit'])){

require ('db.php');

$id1 = $_POST['plant_id'];
$imploder=implode(',', $_POST['option2']); 
$exploder = explode(',', $imploder);

foreach($exploder as $value) {
$add = "INSERT INTO plant_edible_link(plant_id, edible_id) VALUES ($id1, $value) ON DUPLICATE KEY UPDATE plant_id = $id1, edible_id = $value";
$result2 = mysql_query($add) or die(mysql_error());


// NOW THE DELETES

$checker = "SELECT id FROM edible_uses";
$result2 = mysql_query($checker) or die(mysql_error());

while($row = mysql_fetch_array($result2))
{
// Here's what I'm trying to figure out..

$deletes = array_diff($row,$exploder); 

// $delete = "DELETE FROM plant_edible_link(plant_id, edible_id) VALUES ($id1, $value) WHERE plant_id = $id AND edible_id = $deletes";

}

[Psycho]

You should NEVER run queries in loops. It is horribly inefficient. In this case it probably wouldn't be an issue, but it is poor practice and, besides, there is a much, much easier solution.

 

Your logic above is flawed in that you are trying to run array_diff() on ONE record id instead of all the ids returned from the query. You would need to run through ALL the records THEN use array_diff(). But, as I said above, this is all completely unnecessary since you can run your deletes with ONE query using the IN clause. And, you should do all your INSERTS with one query as well.

 

Here is a complete rewrite that is much more efficient (and safer from SQL injection). Not tested:

if (isset($_POST['submit']))
{
    require ('db.php');
    $plant_id = intval($_POST['plant_id']);
    //Create an array of values that is DB safe
    //array_map applies intval() to each element and array_filter() removes empties or 0 elements
    $edible_ids = array_filter(array_map('intval',  $_POST['option2']));

    if(!count($edible_ids))
    {
        echo "No valid IDs passed.";
    }
    else
    {
        //Create array of INSERT values
        $insertAry = array();
        foreach($edible_ids as $edible_id)
        {
            $insertAry[] = "({$plant_id}, {$edible_id})";
        }

        //Create ONE insert query
        //Use INSERT IGNORE instead of ON DUPLICATE
        $query = "INSERT IGNORE INTO plant_edible_link
                      (plant_id, edible_id)
                  VALUES " . implode(', ', $insertAry);
        $result = mysql_query($query) or die(mysql_error());

        //Create ONE query to DELETE
        $query = "DELETE FROM plant_edible_link
                   WHERE plant_id = {$plant_id}
                     AND edible_id NOT IN (" . implode(', ', $edible_ids) . ")";
        $result = mysql_query($query) or die(mysql_error());
    }
}

 

[turpentyne]

oh wow! Thanks! I wasn't expecting that much help!

 

I'm testing now, and i got an error I've never seen before:

 

Access denied for user 'ODBC'@'localhost'

 

New to me. What causes this one?

[Psycho]

Looks like your connection to the DB is failing. If it worked before I see no reason it would not work with the modification I provided.

[turpentyne]

yep! I got it! There was just some extra coding in there that was screwing things up because it was no longer relevant. Thanks again!