Jump to content

Archived

This topic is now archived and is closed to further replies.

zeroth404

MySQL insert PHP Variables = errrrr

Recommended Posts

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?

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[!--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 :-)

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
[!--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!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.