sql query result insert into new table

[trektrak]

I have two relation table and I've made a php sql query out of it ...
What I need is to insert the sql result into a new database table...

Anyone ?

this is my code

<?php




$con = mysql_connect("localhost","sample","sample");
if (!$con)
	{
	die('Could not connect: ' . mysql_error());
	}

mysql_select_db("logging", $con);


	
	$result = mysql_query("SELECT `members`.`name`,`time_attendance`.`time` FROM members\n"
    . "LEFT JOIN `log_time`.`time_attendance` ON `members`.`code` = `time_attendance`.`code` LIMIT 0, 30");

From this query, I would like to insert all the data inside a new database table... well let say..
"history_log"

column - (NAME),(TIME)

[desup]

Im not sure if I understand you, but:
if I do, try to make a while loop an start inserting.

$con = mysql_connect("localhost","sample","sample");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
 
mysql_select_db("logging", $con);
 
 
 
$result = mysql_query("SELECT `members`.`name`,`time_attendance`.`time` FROM members\n"
. "LEFT JOIN `log_time`.`time_attendance` ON `members`.`code` = `time_attendance`.`code` LIMIT 0, 30");
while($row=mysql_fetch_array($result)){
	$time = $row['time_attedance'];
	$name = $row['name'];
mysql_query("INSERT INTO history_log(name, time) VALUES(`$name`, `$time`)");
}

I think this will work. But I didn't test it.
NOTE: This may be really slow, because it will practically create 30 queries, there is one thing that can wok too:

Im thinking about the way how to make it quicker, I will tell you If I have found something

[trektrak]

I can sense this will work too...

But I still couldn't get the data inside the database...

this is what I did...

<?

$con = mysql_connect("localhost","sample","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
 
mysql_select_db("log_time", $con);
 
 
 
$result = mysql_query("SELECT `members`.`name`,`time_attendance`.`time` FROM members\n"
. "LEFT JOIN `log_time`.`time_attendance` ON `members`.`code` = `time_attendance`.`code` LIMIT 0, 30");
while($row=mysql_fetch_array($result))
	$time = $row['time'];
	$name = $row['name'];
mysql_query("INSERT INTO history_log(name, time) VALUES(`$name`, `$time`)");


mysql_close($con);

?>

within the error console, there is no error report .. So I assume all the variables are working properly ... I'm wondering why ..

Oh thanks by the way//

[desup]

You are Inserting wrong value, because you are selecting time_attedance, and inserting time
This should work

while($row=mysql_fetch_array($result)){
$time = $row['time_attedance'];
$name = $row['name'];
mysql_query("INSERT INTO history_log(name, time) VALUES(`$name`, `$time`)");
}
mysql_close($con);

try it like that

[trektrak]

Oh Yea, if I used that .. there will be an error... because the time_attendance is a table actually .. `time_attendance`.`time`

[desup]

Oh sorry, my bad, so, error can be in brackets.
Because, inside your code, you are not defining start and the end of loop, that means, you are closing mysql connection inside.
add curly brackets.
If this will not help, add

or die(mysql_query())

after each query.

EDIT: Im not sure, but my conscience is telling me, that first query is failing. :D

[trektrak]

I try to put this on the bottom line..

if (!mysql_query($row,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

and this is what I got

Error: Query was empty

So, there must be something missing ....

[desup]

Okay okay, so, can you send me your whole script? I will test it on my db :)

[pritaeas]

Not sure what you are after, but if you are just copying data you can use a single query:

INSERT INTO `history_log` (`name`, `time`)
  SELECT `members`.`name`, `time_attendance`.`time` 
  FROM members
    LEFT JOIN `log_time`.`time_attendance` 
    ON `members`.`code` = `time_attendance`.`code`
  LIMIT 0, 30

[trektrak]

Both code works fine now... where the last one copy the whole table data into the new table which very useful for backup purposes...

Tq