I am loading data from one table one row at a time and inserting that data into another table one row at a time.
How can I structure my queries to reduce the load?
php code:
<?php
include("conlocal.php");
$rs=mysql_select_db("local");
if(!$rs){echo "No DB";}
$sql="SELECT `name` FROM `memlog` WHERE `date`=NOW()";
$result=mysql_query($sql);
if(!$result){echo "Ooops!";}
$num=mysql_num_rows($result);
WHILE($row=mysql_fetch_array($result)){
$name=$row['name'];
$source="c";
$reported='0';
$sql="INSERT INTO lunch(date,name,source,reported)
VALUES (now(),'$name','$source','$reported')";
$rss=mysql_query($sql);
}
?>
table memlog:
CREATE TABLE `memlog` (
`log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`m_id` INT(11) UNSIGNED NOT NULL,
`name` VARCHAR(60) NOT NULL DEFAULT '\'\'',
`date` DATE NOT NULL DEFAULT '0000-00-00',
`time` TIME NOT NULL DEFAULT '00:00:00',
`status` VARCHAR(10) NOT NULL DEFAULT 'Verified',
`s_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=5254
table lunch:
CREATE TABLE `lunch` (
`date` DATE NOT NULL DEFAULT '0000-00-00',
`name` VARCHAR(50) NOT NULL DEFAULT 'unknown' COLLATE 'latin1_general_ci',
`source` VARCHAR(10) NOT NULL DEFAULT '""' COLLATE 'latin1_general_ci',
`reported` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`date`, `name`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
Thank you,
Andy
