Jump to content

MySQL insert PHP Variables = errrrr


zeroth404

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?
Link to comment
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]
Link to comment
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]
Link to comment
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 :-)
Link to comment
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.
Link to comment
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]
Link to comment
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.
Link to comment
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]
Link to comment
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]
Link to comment
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]
Link to comment
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.