0

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)

3
Contributors
9
Replies
11
Views
5 Years
Discussion Span
Last Post by trektrak
0

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

Edited by desup: NOTE

0

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//

0

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

Edited by desup: NOTE

0

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

0

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

Edited by desup: n/a

0

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 ....

0

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

Edited by desup: n/a

0

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

Edited by pritaeas: n/a

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.