Member Avatar for 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)

  • 3 Contributors
  • 9 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by trektrak

Dani AI

Generated

Both approaches in this thread are valid — row-by-row insertion (as suggested) works for small batches, while a single-set copy is far faster for bulk moves (as pointed out). The typical causes of the "Query was empty" / no-data symptoms seen here are easy to fix: the loop body not being enclosed in braces so only one statement runs, passing the wrong variable to the query function, and using backticks around values (backticks are for identifiers; string/date values must be quoted or parameterized). Always check the database error immediately after a failing call instead of later; that pinpoints the problem quickly.

Practical, safe workflow:

  • For bulk copies use a single INSERT...SELECT statement executed once on the server (much less overhead than dozens or thousands of INSERTs).
  • For row-by-row work use prepared statements plus a transaction to reduce parsing/locking cost and avoid SQL injection. Also make sure target table and column types match the source (datetime vs varchar, NULLability, indexes, unique keys).

Example (safe insert loop using PDO — this does not repeat the SELECT used earlier; assume you already have an array of source rows in $rows):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=log_time;charset=utf8mb4','user','pass',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO history_log (name, time) VALUES (:name, :time)');
foreach ($rows as $r) {
  $stmt->execute([':name'=>$r['name'], ':time'=>$r['time']]);
}
$pdo->commit();

Extra tips: use ON DUPLICATE KEY or INSERT IGNORE for duplicates, consider LOAD DATA INFILE for very large exports, and migrate away from deprecated mysql_* calls to mysqli or PDO for security and compatibility.

Recommended Answers

All 9 Replies

Member Avatar for 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

Edited by desup because: NOTE
Member Avatar for 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//

Member Avatar for 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

Edited by desup because: NOTE
Member Avatar for trektrak

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

Member Avatar for 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

Edited by desup because: n/a
Member Avatar for 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 ....

Member Avatar for desup

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

Edited by desup because: n/a
Member Avatar for 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
Edited by pritaeas because: n/a
Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.