Second pair of eyes, why is the query failing to execute.

[Failing_Solutions]

Think I'm missing something here. I can print/echo the $sql variable and paste it into phpMyadmin and it works just fine, but for some reason it will not work correctly here. Anybody see anything off?

 

Thank you

<?php
///Write Text File To Local Database
//declare variables
$i='1';
$handle= fopen('inventory.txt','r');
$sql='';
$find='"';
$replace='';
$first='';
$second='';
///read file
while(!feof($handle)) {
    $gets = fgets($handle,4096);
    list($first,$second)=explode(',',$gets);
    //create sql
    $sql.="INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`)
    VALUES ('".trim(str_replace($find,$replace,$first))."','".trim(str_replace($find,$replace,$second))."')
    ON DUPLICATE KEY UPDATE
    `facts_qty_on_hand` = '".trim(str_replace($find,$replace,$second))."';";
    $i++;
}
    if(mysql_query($sql)) {
        ///it Worked
        echo 'Updated '.$i.' fields.';
    } else {
        //Query Failed
    echo "<br>Failed at executing sql query. ";
    }
?>

[boompa]

What you should be doing to start the debugging process is printing the dynamically-generated query when it fails.

Edited June 13, 2013 by boompa

[mac_gyver]

you cannot run multi-queries in one mysql_query() statement. you must run each query separately edit: or make one multi-value insert query.

Edited June 13, 2013 by mac_gyver

[litebearer]

as an illustration to Mac's response... what does $sql contain on its second iteration?

[Failing_Solutions]

@boompa / litebearer

 

So I was echoing the sql statement what I ended up with is a multi-insert statement as mac_gyver pointed out like..

INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS002V14','1731.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '1731.000';INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS006N02','700.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '700.000';INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS006V14','6576.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '6576.000';INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS007E50','15878.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '15878.000';INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS007E59','1525.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '1525.000';INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('AS008N02','4900.000') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '4900.000';

 

 

@mac_gyver 2 things

1st Absolutely love your Avatar img! SG-1 is my fav.

2cd The issue then must be as you pointed out that I'm trying to run mutliple queries in one shot. I am to understand looping through them is not correct, I'll have to think on how to handle it now.

 

Thank you all for the input!

[Psycho]

Why don't you try echoing the actual error (e.g. mysql_erro())

 

But, there are a few problems. 1) You should not use multiple INSERT quries and should instead use one INSERT with multiple VALUES. 2) There is no need to do a str_replace() if you are properly escaping the data for use in a query. But, the query could also fail if there are single quotes in the data (you are only replacing double quotes).

 

Try this which has a good bit of error handling included

<?php
///Write Text File To Local Database
//declare variables

$file = 'inventory.txt';
$handle= @fopen($file, 'r');

if(!$handle)
{
    echo "Unable to open file '{$file}'";
}
else
{
    //read file into INSERT values
    $values = array()
    while(!feof($handle))
    {
        //Get a line of data and process into values
        $gets = fgets($handle, 4096);
        list($first, $second) = explode(',', $gets);
        $first = mysql_real_escape_string(trim($first));
        $second = mysql_real_escape_string(trim($second));

        //Add VALUE record to array
        $values[] = "('{$first}', '{$second}')";
    }

    if(!count($values))
    {
        echo "No values to insert";
    }
    else
    {
        //Prepare single query for ALL inserts
        $sql = "INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`)
                VALUES " .  implode(', ', $values) . "
                ON DUPLICATE KEY UPDATE
                `facts_qty_on_hand` = VALUES(`facts_qty_on_hand`)";
        
        //Execute query
        if(mysql_query($sql))
        {
            ///it Worked
            echo 'Updated ' . count($values) . ' fields.';
        }
        else
        {
            //Query Failed
            echo "<br>Failed at executing sql query.<br>Query: {$sql}<br>Error: " . mysql_error();
        }
    }
}

?>

Edited June 13, 2013 by Psycho

[Failing_Solutions]

Hi Psycho,

 

The query executed perfectly.

 

I do have 1 question. If I were to run into in issue where the table would be updated how does the sql actually accomplish that?

 $sql = "INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`)
                VALUES " .  implode(', ', $values) . "
                ON DUPLICATE KEY UPDATE
                `facts_qty_on_hand` = VALUES(`facts_qty_on_hand`)";

This seems ilogical to me we don't want to set teh facts_qty_on_hand to facts_qty_on_hand do we? Is that saying skip this field? Little confused?

 

Thank you

[Psycho]

Hi Psycho,

 

The query executed perfectly.

 

I do have 1 question. If I were to run into in issue where the table would be updated how does the sql actually accomplish that?

 $sql = "INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`)

                VALUES " .  implode(', ', $values) . "

                ON DUPLICATE KEY UPDATE

                `facts_qty_on_hand` = VALUES(`facts_qty_on_hand`)";

This seems ilogical to me we don't want to set teh facts_qty_on_hand to facts_qty_on_hand do we? Is that saying skip this field? Little confused?

 

Thank you

 

Not to be rude, but have you ever looked as the documentation for "ON DUPLICATE KEY UPDATE"? I don't know "everything" about the functions I use, but when I do use a new function I make an attempt to figure out what it does, how it works, and what it can do. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 

The third and fourth code blocks in the manual show exactly what that does, which is . . . if there are any records that would cause a duplicate then the record is updated. When using VALUES() with a field name it will update the record with the value associated with that field in the VALUES submitted. In your case, if there are any duplicates it will update the 'facts_qty_on_hand' field with the value for that field in the INSERT list.

[Failing_Solutions]

Thank you, don't consider it rude at all. If I have questions on something that seems odd, I just ask the person who told me. I'm a great pupil that way  

 

Thank you for your time, and your follow up response, the code was very helpful and I now fully understand exactly what is going on.