MySQL insert PHP Variables = errrrr

[zeroth404]

MySQL seems to not be able to handle PHP Variables properly. After I insert my variables into the `partsdb` they disappear! mysql_numrows returns zero.

if I put $db_data[$db_index+1] in the query string instead of copyign them into other variables, the brackets along with the +1 and +2 seems to confuse MySQL and it complains about finding a T_ENCAPSED_AND_WHITESPACE and expected an end-bracket.

the code completed jsut fine, no errors just the way I've typed it above, and the loop continues just like it should (I've tested it) but MySQL just doesn't like thoes variables and refuses to insert them.

[code]
    mysql_connect($host,$username,$password) or die(mysql_error());
    {
      mysql_select_db($database) or die(mysql_error());
      {
        $query = 'DROP TABLE partsdb';
        mysql_query($query);

        $query='CREATE TABLE partsdb (p_num varchar(16) NOT NULL, p_name varchar(100) NOT NULL, p_price varchar(10) NOT NULL)';
        mysql_query($query);

        $db_data=file('database.dat');                           // array of database contents
        $db_entries=count($db_data);                             // count of database array
        for ($db_index=0; $db_index < $db_entries; $db_index++)  // strip newlines from the end of each entry
          $db_data[$db_index] = trim($db_data[$db_index]);

        for ($db_index=0; $db_index < $db_entries; $db_index+=3)  //a part number is every 3 lines,
        {
//********THIS IS WHERE THINGS GET WHACKY.**************
          $p_num=$db_data[$db_index];
          $p_name=$db_data[$db_index+1];
          $p_price=$db_data[$db_index+2];
          $query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES ($p_num, $p_name, $p_price)";
          mysql_query($query);
        }
      }
      mysql_close();
    }
...
[/code]

what is going on here?

[lessthanthree]

add single quotes to your variables in the mysql query.

[zeroth404]

[!--quoteo(post=352996:date=Mar 8 2006, 04:18 PM:name=lessthanthree)--][div class=\'quotetop\']QUOTE(lessthanthree @ Mar 8 2006, 04:18 PM) [snapback]352996[/snapback][/div][div class=\'quotemain\'][!--quotec--]
add single quotes to your variables in the mysql query.
[/quote]

Thank you :-)

I considered that, but I figured that adding quotes around a variable would just make it a string. aye.


Ar, so why do I get this error:

PHP Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']'

With this code:

[code]
          $query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES ('$db_data[$db_index]', '$db_data[$db_index+1]', '$db_data[$db_index+2]')";
[/code]


and I get this error:

PHP Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in

with this code:

[code]
$query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES ('$db_data['$db_index']', '$db_data['$db_index'+1]', '$db_data['$db_index'+2]')";
[/code]

[lessthanthree]

You get the error because $db_data[$db_index+1] etc are syntactially incorrect. You can't have the +1 there.

That's also a different query than the one in your previous code posting.

in the second piece of code, you've used single quotes twice. (including single quoted data within single quotes)

try:

[code]

$query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES (".$db_data['$db_index'].", ".$db_data['$db_index'+1].", ".$db_data['$db_index'+2].")";
[/code]

[zeroth404]

[!--quoteo(post=353006:date=Mar 8 2006, 04:29 PM:name=lessthanthree)--][div class=\'quotetop\']QUOTE(lessthanthree @ Mar 8 2006, 04:29 PM) [snapback]353006[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You get the error because $db_data[$db_index+1] etc are syntactially incorrect. You can't have the +1 there.
[/quote]


but I need to increment that variable, otherwise I get the wrong array element. this is te only language I know of that doesn't let you do that.

edit: sure I could just create two more ariables and have it set, but it would take longer to execute the code, and considering that the script already takes longer than 30 seconds to read through the whole database and my script-execution time-limit is 30 seconds, every milisecond matters :-)

[zeroth404]

[!--quoteo(post=353006:date=Mar 8 2006, 04:29 PM:name=lessthanthree)--][div class=\'quotetop\']QUOTE(lessthanthree @ Mar 8 2006, 04:29 PM) [snapback]353006[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You get the error because $db_data[$db_index+1] etc are syntactially incorrect. You can't have the +1 there.

That's also a different query than the one in your previous code posting.

in the second piece of code, you've used single quotes twice. (including single quoted data within single quotes)

try:

[code]

$query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES (".$db_data['$db_index'].", ".$db_data['$db_index'+1].", ".$db_data['$db_index'+2].")";
[/code]
[/quote]

ok, tried that. I get "Undefined index: $db_index" a bunch of times. strange.

[XenoPhage]

[!--quoteo(post=353017:date=Mar 8 2006, 04:41 PM:name=zeroth404)--][div class=\'quotetop\']QUOTE(zeroth404 @ Mar 8 2006, 04:41 PM) [snapback]353017[/snapback][/div][div class=\'quotemain\'][!--quotec--]
ok, tried that. I get "Undefined index: $db_index" a bunch of times. strange.
[/quote]

Here, this should help. (I'm assuming here that price is a float value)

[code]
$query = sprintf('INSERT INTO partsdb (p_num, p_name, p_price) VALUES (%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
[/code]

[zeroth404]

[!--quoteo(post=353027:date=Mar 8 2006, 04:51 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 8 2006, 04:51 PM) [snapback]353027[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Here, this should help. (I'm assuming here that price is a float value)

[code]
$query = sprintf('INSERT INTO partsdb (p_num, p_name, p_price) VALUES (%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
[/code]
[/quote]

thanks, that works. but its much slower than the other method of copying the contents into other variables. with your method I get about 100 lines copied in 30 seconds, while I get 700 lines with the former.

I may just have to request to my webhost that the 30 second execution limit be raised.

I'm still open for suggestions.

edit: by the way, they're all strings as of right now. I'll probably convert them later for efficiency.

[XenoPhage]

[!--quoteo(post=353029:date=Mar 8 2006, 04:55 PM:name=zeroth404)--][div class=\'quotetop\']QUOTE(zeroth404 @ Mar 8 2006, 04:55 PM) [snapback]353029[/snapback][/div][div class=\'quotemain\'][!--quotec--]
thanks, that works. but its much slower than the other method of copying the contents into other variables. with your method I get about 100 lines copied in 30 seconds, while I get 700 lines with the former.

I may just have to request to my webhost that the 30 second execution limit be raised.

I'm still open for suggestions.
[/quote]

Change the script to do one large insert instead of multiples.. something like this :

[code]
$query = 'INSERT INTO partsdb (p_num, p_name, p_price) VALUES ';
$first = true;
for ($db_index=0; $db_index < $db_entries; $db_index+=3)  //a part number is every 3 lines,
   {
      if ($first) {
         $query .= sprintf('%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
         $first = false;
      } else {
         $query .= sprintf(',(%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
      }
   }
mysql_query($query);
[/code]

[zeroth404]

[!--quoteo(post=353035:date=Mar 8 2006, 05:06 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 8 2006, 05:06 PM) [snapback]353035[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Change the script to do one large insert instead of multiples.. something like this :

[code]
$query = 'INSERT INTO partsdb (p_num, p_name, p_price) VALUES ';
$first = true;
for ($db_index=0; $db_index < $db_entries; $db_index+=3)  //a part number is every 3 lines,
   {
      if ($first) {
         $query .= sprintf('%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
         $first = false;
      } else {
         $query .= sprintf(',(%d, "%s", %f)', $db_data[$db_index], $db_data[$db_index+1], $db_data[$db_index+2]);
      }
   }
mysql_query($query);
[/code]
[/quote]


I think that solves the issue of speed, however it still refuses to insert the data.

heres what an an actual insert after varables have been dereferenced looks like:

INSERT INTO partsdb (p_num, p_name, p_price) VALUES ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234'), ('1234', 'woot (Stuff)', '1234')

only theres a crap load more, but its in the same exact format.

edit: heres the code as of current.

[code]
        $query = "INSERT INTO partsdb (p_num, p_name, p_price) VALUES ";

        for ($db_index=0; $db_index < $db_entries; $db_index+=3)  //3 lines per part: number, description, price.
        {
          $plusOne=$db_data[$db_index+1];
          $plusTwo=$db_data[$db_index+2];

          if ($db_index !== 0)
            $query .= ", ";

          $query .= "('" . $db_data[$db_index] . "', '" . $plusOne . "', '" . $plusTwo . "')";
        }

        mysql_query($query);
[/code]

[XenoPhage]

[!--quoteo(post=353260:date=Mar 9 2006, 09:57 AM:name=zeroth404)--][div class=\'quotetop\']QUOTE(zeroth404 @ Mar 9 2006, 09:57 AM) [snapback]353260[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I think that solves the issue of speed, however it still refuses to insert the data.
[/quote]

Umm... I must have missed that bit.. I wasn't aware that it wasn't adding the data..

Are you getting an error? Try changing the mysql_query line to this :

[code]
mysql_query($query) or die(mysql_error());
[/code]

[zeroth404]

[!--quoteo(post=353291:date=Mar 9 2006, 11:09 AM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 9 2006, 11:09 AM) [snapback]353291[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Umm... I must have missed that bit.. I wasn't aware that it wasn't adding the data..

Are you getting an error? Try changing the mysql_query line to this :

[code]
mysql_query($query) or die(mysql_error());
[/code]
[/quote]


Solved -- Everything works now. turns out that I had a stray single-appostrophe in my data that I was trying to insert.


Thank you all so much for your help!