Help me in a MySql problem

<?php
$link=mysql_connect('localhost','bari','bari');
mysql_select_db('gosta');

$query="select * from items where site='jsm'";
$result=mysql_query($query);

while ($row=mysql_fetch_row($result))
{	
	
	$q="insert into jsm values(".$row[0].",'".$row[1]."','".$row[2]."','".$row[3]."','".$row[4]."','".$row[5]."','".$row[6]."','".$row[7]."','".$row[8]."','".$row[9]."','".$row[10]."','".$row[11]."','".$row[12]."','".$row[13]."','".$row[14]."','".$row[15]."','".$row[16]."','','')";	
	mysql_query($q);
	
}

mysql_close($link);
?>

PHP:

I used this code to copy all the members of items tables into jsm whose site=jsm.
and there are 12808 members in items table whose site=jsm
but this code copying only 2800 members!!!!
i tried in another way using limit for taking 1000 members each time,but then only 144 members are copied.
i dont know why this strange thing happening.when i use mysql_num_row($result) it shows 12808 but this code not inserting all of them.plz give me some help.i am frustrated with this problem.

 

Well, first I think you should look at doing an inner join - a single SQL statement can do this task for you.

However if that's not your cup of tea, add a counter within your loop to see if the problem is in the loop, earlier or within the update itself (i.e. you have unique key constraints).

I also don't see you checking for errors on the mysql_query. This operation isn't guaranteed to work. If you are hitting errors, you should be outputting the error and the record.

 

i'd look at the design of the new table, does your table have a primary id? is it set to auto increment? is it limited?

 

What about this?...

CREATE TABLE jsm SELECT * FROM items WHERE site='jsm'

Code (markup):

 

well,the jsm table doesn't have all the fields of items.so i can't just select all the members from items table and insert them into jsm table.the new table has a primary key itemID which is similar to items table primary key.and it is not auto_increment.i have to maintain the itemID from items table to jsm table.

 

insert into jsm
select [put fields here which are in jsm] from items where site='jsm'

Execute a query like above. In your select fields just write the fields exists on jsm.

 

coderbari,

Your problem may actually be caused by PHP timing out because it takes a lot of time to insert the records.

Try to select only a range of records at a time, then create a query that will insert multiple records at a time, to keep the php execution time small.

You could also try to use the php function ini_set to set the execution time to 0 (infinite) for that script, right before starting to insert all those records in the other table.

Your query would be:

INSERT INTO `table` ( `field1` , `field2` , `field3`, .... , `fieldn` )
VALUES 
( 'record1_value1' , 'record1_value2', 'record1_value3', .... , 'record1_valuen'), 
( 'record2_value1' , 'record2_value2', 'record2_value3', .... , 'record2_valuen'), 
..... ,
( 'record50_value1' , 'record50_value2', 'record50_value3', .... , 'record50_valuen'), 
....,
( 'lastrecord_value1' , 'lastrecord_value2', 'lastrecord_value3', .... , 'lastrecord_valuen') ;

Code (markup):

MySQL will usually accept queries up to 1MB of text, about 1 million characters, though I don't recommend you insert more than 1-2000 records at a time.

You could try extending the execution time or setting the time to 0 by using this:

ini_set("max_execution_time","100");

PHP:

to extend the execution time to 100s, you need to write this before the moment you start inserting records.

You can get a subset of records by using this

SELECT * FROM `table` LIMIT 0 , 30

PHP:

where 0 is the number from where to start reading records and 30 is how many records to read from that point.
You should have an index on the first table if you're using ranges because there's a very small possibility to get a record twice if you're getting a range, than another one. The order of the returned results is not guaranteed of you don't have an index.

 

Thanks pal,thank you all for your helps.special thanks to Clark Kent-his query was the simplest.this forum is a great help for my learning.here all the members are so helpful.i like you all

 

or just put this at the top of your script

it means the script will be executed for about 1000 seconds

 

yes.i think in the end i will have to make queries from PHP script.not all the time query from PHPMYADMIN will work,bcoz there may be complex work which work with more than 1 big queries.i think time limit setting for the scripts working will also be helpful for me.