Strange CREATE TABLE Issue

[rseigel]

The following query works fine when I do it phpmyadmin.

 

DROP TABLE IF EXISTS tmp_price_compare;
			CREATE TABLE IF NOT EXISTS tmp_price_compare (
			supplier_reference varchar(32) DEFAULT NULL,
			quantity int(10) NOT NULL DEFAULT '0',
			price decimal(20,6) NOT NULL DEFAULT '0.000000',
			wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000'
		) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

 

When I try to put it in my code though like so:

 

mysql_query("DROP TABLE IF EXISTS tmp_price_compare;
			CREATE TABLE IF NOT EXISTS tmp_price_compare (
			supplier_reference varchar(32) DEFAULT NULL,
			quantity int(10) NOT NULL DEFAULT '0',
			price decimal(20,6) NOT NULL DEFAULT '0.000000',
			wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000'
		) ENGINE=InnoDB  DEFAULT CHARSET=utf8;")
			or die(mysql_error());

 

it gives me the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32' at line 2

 

Any thoughts? No doubt it's something simple. I'm still learning.

 

Thanks,

 

Ron

[PFMaBiSmAd]

mysql_query doesn't support multiple sql statements separated with ;, because too many programmers don't validate external input being put into query statements and php wanted to prevent injected sql from doing things like dropping tables.

 

You need to execute each query statement separately.

[rseigel]

This I did not know. Thanks.

[rseigel]

ACK!! One more issue related to this.

 

$handle = fopen("GeneratedList.csv", "r+");
 $contents = file('GeneratedList.csv');
$header = true;
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
if ($header == true) {
 $header = false;
 continue;
}
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
 if ($c == 1) {
  $supplier_reference = $data[($c-1)];
 }
    if ($c == 2) {
  $quantity = $data[($c-1)];
  mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
  mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
    }
 if ($c == 3) {
  $price = $data[($c-1)];
 }
 if ($c == 4) {
  $wholesale_price = $data[($c-1)];
 }
 mysql_query("INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price)
  VALUES($supplier_reference,$quantity,$price,$wholesale_price");
   }
}
fclose($handle);

 

The INSERT doesn't give any errors but it also doesn't insert any values into the table.

 

Any ideas?

[rseigel]

Of course I didn't get an error since I forgot the die code.

 

Here's the error I get:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,' at line 2

[Christian F.]

That tells me that one, or more, of your variables are empty. Echo out the completed query, to find out which.

[PFMaBiSmAd]

I was going to mention this in your previous thread, but why do you have the for(){} loop in your code?

 

You can reference all the elements of the $data array at one time and since the array indexes are 0-3 (for a count of 4 elements), your code will never have $c == 4

[rseigel]

That tells me that one, or more, of your variables are empty. Echo out the completed query, to find out which.

 

I feel silly for having to ask but what do you mean by this? I know what echo is - just not sure what to do with it code wise here.

[rseigel]

I was going to mention this in your previous thread, but why do you have the for(){} loop in your code?

 

You can reference all the elements of the $data array at one time and since the array indexes are 0-3 (for a count of 4 elements), your code will never have $c == 4

 

Easy answer - cause it works and I don't know how to do what you're suggesting.

 

Any hints (or flat out solutions) on how to improve my code is always appreciated.

[Psycho]

I find it extremely helpful to echo the query to the page when I encounter an error. Create your queries as a string variable THEN use that for your query. You will be amazed at how much easier it is find and fix these errors. Of course, you shouldn't be exposing any of these errors in a production environment.

 

$query = "INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price)
         VALUES($supplier_reference,$quantity,$price,$wholesale_price";
mysql_query($query) or die("Query: $query<br>Error: " . mysql_error());

[PFMaBiSmAd]

Here's another question - if you are looping over the data using fgetcsv, why do you also have a file statement in your code to read it all at once into an array?

[rseigel]

I find it extremely helpful to echo the query to the page when I encounter an error. Create your queries as a string variable THEN use that for your query. You will be amazed at how much easier it is find and fix these errors. Of course, you shouldn't be exposing any of these errors in a production environment.

 

$query = "INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price)
VALUES($supplier_reference,$quantity,$price,$wholesale_price";
mysql_query($query) or die("Query: $query<br>Error: " . mysql_error());

 

AHA!!!

 

Now I see what you mean.

 

Query: INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES(,,,
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,' at line 2

 

I'm assuming this means that all values are empty?

[rseigel]

Here's another question - if you are looping over the data using fgetcsv, why do you also have a file statement in your code to read it all at once into an array?

 

Another good question.

 

No good answer.

 

This is a mismash of code that I've pieced together searching and asking questions.

[PFMaBiSmAd]

The reason the data in the query is empty is because of the for(){} loop. When $c is zero (the first pass through the for loop), none of that code sets any values, but your INSERT query is executed anyway. See the following on how you could be accessing and using the data -

 

<?php
ini_set('auto_detect_line_endings', true);
$handle = fopen("GeneratedList.csv", "r+");
fgetcsv($handle, 100000, ","); // get and discard the header row
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
   $supplier_reference = $data[0];
   $quantity = $data[1];
   $price = $data[2];
   $wholesale_price = $data[3];

   // your code that uses the data goes here...

}
fclose($handle);

Edited January 24, 2013 by PFMaBiSmAd

[rseigel]

Thanks a million for the help fixing my code.

 

Here's what I have now:

 

ini_set('auto_detect_line_endings', true);
$handle = fopen("GeneratedList.csv", "r+");
 $contents = file('GeneratedList.csv');
$header = true;
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
if ($header == true) {
 $header = false;
 continue;
}
$supplier_reference = $data[0];
    $quantity = $data[1];
    $price = $data[2];
    $wholesale_price = $data[3];

mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
  mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
   or die(mysql_error());

$query = "INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price)
			 VALUES($supplier_reference,$quantity,$price,$wholesale_price)";
mysql_query($query) or die("Query: $query<br>Error: " . mysql_error());
}
fclose($handle);

 

The ewrror I'm getting now is:

 

Query: INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES(1600SLIM,64,15.95,8.33)
Error: Unknown column '1600SLIM' in 'field list'

 

Any ideas? The values are there. Weird.

[Christian F.]

You also should be moving the query out of the loop, and build it up as a UPDATE-CASE query instead. That way you'll save a lot of processing time, and your code will be a bit cleaner and easier to read.

 

As for your error message: "1600SLIM" isn't a number.

Edited January 25, 2013 by Christian F.

[rseigel]

For the record '1600SLIM' is the first supplier_reference that contains letters instead of all numbers.

[PFMaBiSmAd]

String data values must be enclosed by single-quotes in the query.

 

And for some reason you still have an unused file statement in your code. You must know what each statement does and why it is in your code and what it contributes to the overall goal. Otherwise it shouldn't be in your code.

[rseigel]

String data values must be enclosed by single-quotes in the query.

 

And for some reason you still have an unused file statement in your code. You must know what each statement does and why it is in your code and what it contributes to the overall goal. Otherwise it shouldn't be in your code.

 

Thanks for your help and your patience. I'm stoked that this is working now.

 

Now off to the next part....

[rseigel]

You also should be moving the query out of the loop, and build it up as a UPDATE-CASE query instead. That way you'll save a lot of processing time, and your code will be a bit cleaner and easier to read.

 

Yikes! I've done some digging on this one and I'm confused.

 

I found this:

 

http://redbonzai.com/update-multiple-rows-in-a-single-query/

 

Is that a good example to go by for what you're talking about?

[Christian F.]

Yes, that's a pretty good example.