PHP and MYSQL - Please Help

  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

Hello!

I have a deadline till friday and i've stumbled into a problem.

I need to do one thing that it gonna take me days to achieve and i know it is possible to do it in 10 minutes...!

PART 1:

I want to create a cycle using mysql to insert products. Like:

$i = 0
while $i!=300 {
execute this QUERY
$i++;
}

This will create 300 product rows exactly the same! thats what i want! (attention: i don't know if this is correct, that what i'm trying to know! including the way to insert that mysql query.. just need to know the syntax, not the command itself.)

PART 2:

in the mysql query above, there will be a field that will be diferent for this 300 products...

so i will have to select those 300 again by querying "select vm_product where product_parent_id = X" and in each of these do this:

pick this file: output.txt


    (this file look like this)
    86
    1
    2
    215
    226
    5882
    6154

and for each of those "select vm_product where product_parent_id = X" insert the values of the file (each line in each row). So the first one of the 300 will have product_id 86, the second will be 1, the third will have product_id 2, and so on... This will be a headache with hundreds of products :(

I know this isn't too hard but i don't have the knowledge to do it.

Any help would be most appreciatted!

Thanks
Peter
  • Flanders
  • Beginner
  • Beginner
  • User avatar
  • Posts: 48
  • Loc: Reno, Nevada US

Post 3+ Months Ago

I would load the txt file values into an array, say call it $productnumber or whatever, and then you have:

$productnumber[0]=86;
$productnumber[1]=1;
$productnumber[2]=2;
$productnumber[3]=215;
and so on.

Then write your loop to insert the 300 products with the id numbers already in the query...

for($i=0;$i<300;$i++){
$query="INSERT INTO producttable (field,field,productnumber) VALUES ('value','value','$productnumber[$i]'";
$result=mysql_query($query);
}
  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

Thank you for your reply Flanders.

I know that threw phpmyadmin i can execute php code. So i can insert this in phpmyadmin and it will do the job?

$productnumber[0]=86;
$productnumber[1]=1;
$productnumber[2]=2;
$productnumber[3]=215;
etc..etc..

for($i=0;$i<300;$i++){
$query="INSERT INTO producttable (field,field,productnumber) VALUES ('value','value','$productnumber[$i]'";
$result=mysql_query($query);
}

threw phpmyadmin i don't need the $result=mysql_query($query); right? just need to do this:

mysql_query($query);

and he will return the result. correct?
  • Flanders
  • Beginner
  • Beginner
  • User avatar
  • Posts: 48
  • Loc: Reno, Nevada US

Post 3+ Months Ago

Actually, no. My answer was assuming you were running a php script. You can do your insert throught PHPMyAdmin, but I don't think you can define arrays and such with that interface. I would just do it using a script like I mentioned in the earlier post. You could even load the array using your script if you wanted. Ideally, you would have a delimited (tab,comma,or whatever) list of your product numbers.

Let's say your product list is in comma delimited format:

86,1,2,215,226,5882,6154

you can load this in externally, or just put it in the script:

Code: [ Select ]
$productlist="86,1,2,215,226,5882,6154";


Then, run the following command to place these numbers in an array:

Code: [ Select ]
$productarray=explode(",",$productlist);


This will output an array called $productarray which can be accessed like so:

Code: [ Select ]
echo $productarray[0];  //Prints 86
echo $productarray[1]; //Prints 1
  1. echo $productarray[0];  //Prints 86
  2. echo $productarray[1]; //Prints 1


So, now you have an array with all of your product numbers in order. All that's left is to execute your queries to write into the database. Lets say your database layout is as follows:
id (auto-increment)
productnumber (int)

Code: [ Select ]
for($i=0;$i<300;$i++){
$query="INSERT INTO tablename (id,productnumber) VALUES ('','$productarray[$i]')";
$result=mysql_query($query);
}
  1. for($i=0;$i<300;$i++){
  2. $query="INSERT INTO tablename (id,productnumber) VALUES ('','$productarray[$i]')";
  3. $result=mysql_query($query);
  4. }


you would end up with a table like:

id | product number
0 | 86
1 | 1
2 | 2
3 | 215
4 | 226
5 | 5882
6 | 6154
etc. all the way through the 300 entries.

I hope that's a bit clearer than my last post.
  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

Thanks for your help! I've understood that your code is much simple in the second reply. However i've already started to work on this so i went threw the first code. But i found a problem... first i wasn't introducing the correct database name so the page were issuing errors with mysql connecting, then i fixed and the page appeared blank.

So i presumed the script had it inserted. but it didn't. There are no errors in the page when i access this php file on the server, but can't figure why they were not inserted. I exported the database to a mysql file so that i could get a line of an "insert into" correctly. The array is all ok because i inserted an

Quote:
echo $productnumber[$i];


inside the FOR and prints all the data correctly. I used high numbers in $i because when i insert every row i need to insert the product_id which is $i and its value is the product_parent_id.

So the only thing here that its not working is the insert... do you have any idea?

Code: [ Select ]
<?php
$productnumber[10915]=86;
$productnumber[10916]=1;
$productnumber[10917]=2;
...
$productnumber[11209]=10112;
$productnumber[11210]=10456;
$productnumber[11211]=10728;
 
$dbhost = 'localhost';
$dbuser = 'my_db_user';
$dbpass = 'my_db_pass';
 
mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db("my_db");
 
for($i=10915;$i<11212;$i++) {
echo $productnumber[$i];
$query="INSERT INTO 'product' ('product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc', 'product_desc', 'product_thumb_image', 'product_full_image', 'product_publish', 'product_weight', 'product_weight_uom', 'product_length', 'product_width', 'product_height', 'product_lwh_uom', 'product_url', 'product_in_stock', 'product_available_date', 'product_availability', 'product_special', 'product_discount_id', 'ship_code_id', 'cdate', 'mdate', 'product_name', 'product_sales', 'attribute', 'custom_attribute', 'product_tax_id', 'product_unit', 'product_packaging', 'child_options', 'quantity_options', 'child_option_ids', 'product_order_levels') VALUES
('$i', 1, '$productnumber[$i]', '', '', '', '', '', 'Y', 17.0000, 'pounds', 0.0000, 0.0000, 0.0000, 'inches', '', 0, 1211583600, '', 'N', 0, 0, 1215129078, 1220372925, 'Quadro em Tela - Duplo - Quadrados', 0, 'Tamanho,30x30[+ 35.00],50x50[+ 70.00],60x60[+ 109.00],70x70[+ 138.00],80x80[+ 140.00],90x90[+ 205.00],100x100[+ 240.000],110x110[+ 285.00],120x120[+ 330.00],130x130[+ 375.00]', 'Comentários Adicionais(textarea)', 5, 'piece', 0, '', 'none,0,0,1', '', '0,0')";
$result=mysql_query($query);
}
echo $result;
?>
  1. <?php
  2. $productnumber[10915]=86;
  3. $productnumber[10916]=1;
  4. $productnumber[10917]=2;
  5. ...
  6. $productnumber[11209]=10112;
  7. $productnumber[11210]=10456;
  8. $productnumber[11211]=10728;
  9.  
  10. $dbhost = 'localhost';
  11. $dbuser = 'my_db_user';
  12. $dbpass = 'my_db_pass';
  13.  
  14. mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
  15. mysql_select_db("my_db");
  16.  
  17. for($i=10915;$i<11212;$i++) {
  18. echo $productnumber[$i];
  19. $query="INSERT INTO 'product' ('product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc', 'product_desc', 'product_thumb_image', 'product_full_image', 'product_publish', 'product_weight', 'product_weight_uom', 'product_length', 'product_width', 'product_height', 'product_lwh_uom', 'product_url', 'product_in_stock', 'product_available_date', 'product_availability', 'product_special', 'product_discount_id', 'ship_code_id', 'cdate', 'mdate', 'product_name', 'product_sales', 'attribute', 'custom_attribute', 'product_tax_id', 'product_unit', 'product_packaging', 'child_options', 'quantity_options', 'child_option_ids', 'product_order_levels') VALUES
  20. ('$i', 1, '$productnumber[$i]', '', '', '', '', '', 'Y', 17.0000, 'pounds', 0.0000, 0.0000, 0.0000, 'inches', '', 0, 1211583600, '', 'N', 0, 0, 1215129078, 1220372925, 'Quadro em Tela - Duplo - Quadrados', 0, 'Tamanho,30x30[+ 35.00],50x50[+ 70.00],60x60[+ 109.00],70x70[+ 138.00],80x80[+ 140.00],90x90[+ 205.00],100x100[+ 240.000],110x110[+ 285.00],120x120[+ 330.00],130x130[+ 375.00]', 'Comentários Adicionais(textarea)', 5, 'piece', 0, '', 'none,0,0,1', '', '0,0')";
  21. $result=mysql_query($query);
  22. }
  23. echo $result;
  24. ?>


By the way.. $result displays nothing.. even with the echo.. which is strange?

Thanks
  • Flanders
  • Beginner
  • Beginner
  • User avatar
  • Posts: 48
  • Loc: Reno, Nevada US

Post 3+ Months Ago

Try unquoting the tablename in the query:

Code: [ Select ]
$query="INSERT INTO product ('product_id',...";


I assume you're not getting any error messages when you run this script?
  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

No, no error messages. I will try unquoting and get back in 1 minute.
  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

I unquoted and nothing.

I picked this and went to phpmyadmin and copy pasted. it issued an error dispite the php file did not.

what i inserted:
Code: [ Select ]
INSERT INTO product ('product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc', 'product_desc', 'product_thumb_image', 'product_full_image', 'product_publish', 'product_weight', 'product_weight_uom', 'product_length', 'product_width', 'product_height', 'product_lwh_uom', 'product_url', 'product_in_stock', 'product_available_date', 'product_availability', 'product_special', 'product_discount_id', 'ship_code_id', 'cdate', 'mdate', 'product_name', 'product_sales', 'attribute', 'custom_attribute', 'product_tax_id', 'product_unit', 'product_packaging', 'child_options', 'quantity_options', 'child_option_ids', 'product_order_levels') VALUES ('11000', 1, '11000', '', '', '', '', '', 'Y', 17.0000, 'pounds', 0.0000, 0.0000, 0.0000, 'inches', '', 0, 1211583600, '', 'N', 0, 0, 1215129078, 1220372925, 'Quadro em Tela - Duplo - Quadrados', 0, 'Tamanho,30x30[+ 35.00],50x50[+ 70.00],60x60[+ 109.00],70x70[+ 138.00],80x80[+ 140.00],90x90[+ 205.00],100x100[+ 240.000],110x110[+ 285.00],120x120[+ 330.00],130x130[+ 375.00]', 'Comentários Adicionais(textarea)', 5, 'piece', 0, '', 'none,0,0,1', '', '0,0');


the error:
Code: [ Select ]
Comando SQL:

INSERT INTO product ('product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc', 'product_desc', 'product_thumb_image', 'product_full_image', 'product_publish', 'product_weight', 'product_weight_uom', 'product_length', 'product_width', 'product_height', 'product_lwh_uom', 'product_url', 'product_in_stock', 'product_available_date', 'product_availability', 'product_special', 'product_discount_id', 'ship_code_id', 'cdate', 'mdate', 'product_name', 'product_sales', 'attribute', 'custom_attribute', 'product_tax_id', 'product_unit', 'product_packaging', 'child_options', 'quantity_options', 'child_option_ids', 'product_order_levels') VALUES ('11000', 1, '11000', '', '', '', '', '', 'Y', 17.0000, 'pounds', 0.0000, 0.0000, 0.0000, 'inches', '', 0, 1211583600, '', 'N', 0, 0, 1215129078, 1220372925, 'Quadro em Tela - Duplo - Quadrados', 0, 'Tamanho,30x30[+ 35.00],50x50[+ 70.00],60x60[+ 109.00],70x70[+ 138.00],80x80[+ 140.00],90x90[+ 205.00],100x100[+ 240.000],110x110[[...]

Mensagens do MySQL : Documentação
#1064 - 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 ''product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc',' at line 1
  1. Comando SQL:
  2. INSERT INTO product ('product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc', 'product_desc', 'product_thumb_image', 'product_full_image', 'product_publish', 'product_weight', 'product_weight_uom', 'product_length', 'product_width', 'product_height', 'product_lwh_uom', 'product_url', 'product_in_stock', 'product_available_date', 'product_availability', 'product_special', 'product_discount_id', 'ship_code_id', 'cdate', 'mdate', 'product_name', 'product_sales', 'attribute', 'custom_attribute', 'product_tax_id', 'product_unit', 'product_packaging', 'child_options', 'quantity_options', 'child_option_ids', 'product_order_levels') VALUES ('11000', 1, '11000', '', '', '', '', '', 'Y', 17.0000, 'pounds', 0.0000, 0.0000, 0.0000, 'inches', '', 0, 1211583600, '', 'N', 0, 0, 1215129078, 1220372925, 'Quadro em Tela - Duplo - Quadrados', 0, 'Tamanho,30x30[+ 35.00],50x50[+ 70.00],60x60[+ 109.00],70x70[+ 138.00],80x80[+ 140.00],90x90[+ 205.00],100x100[+ 240.000],110x110[[...]
  3. Mensagens do MySQL : Documentação
  4. #1064 - 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 ''product_id', 'vendor_id', 'product_parent_id', 'product_sku', 'product_s_desc',' at line 1
  • Flanders
  • Beginner
  • Beginner
  • User avatar
  • Posts: 48
  • Loc: Reno, Nevada US

Post 3+ Months Ago

unquote your field names. Only quote the actual values.
  • stblink
  • Novice
  • Novice
  • stblink
  • Posts: 22

Post 3+ Months Ago

YOU'RE THE MAN! thanks Flanders for all your help. It works now, it saved me a bunch of time ;)

Cheers
  • Flanders
  • Beginner
  • Beginner
  • User avatar
  • Posts: 48
  • Loc: Reno, Nevada US

Post 3+ Months Ago

You're welcome. Good luck with your project.

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 66 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.