Get data from Recordset and INSERT into another database table.

Hi.

I want to get data from my recordset (see it attached) and insert some values in another database table. The other fields has different names.

For example, when this php file be executed the data from my recordset will be inserted in the other database table:

the other database name is Sql107121_9 and the table where I want to insert data is named "video"

the values should goes from:

rss_data_id ---> videocategoryid
date_added ---> dateline
num_views ---> views
title ---> title
link ---> videourl
description ---> description

How can I connect to the second database and then Insert the recordset values into the 2nd db fields?

Thanks.




<?php require_once('Connections/conCubagente.php'); ?>
<?php 
mysql_select_db($database_conCubagente, $conCubagente);
$query_rsMainData = "SELECT rss_data_id, date_added, num_views, title, link, `description` FROM items ORDER BY date_added DESC";
$rsMainData = mysql_query($query_rsMainData, $conCubagente) or die(mysql_error());
$row_rsMainData = mysql_fetch_assoc($rsMainData);
$totalRows_rsMainData = mysql_num_rows($rsMainData);
?>

Open in new window

FernanditosAsked:
Who is Participating?
 
Michael701Connect With a Mentor Commented:
note you're hard coding the videoid to 1, is this what you really want? is this an auto increment field?
<?php require_once('Connections/conCubagente.php'); ?>
<?php require_once('Connections/vb_videos_conn.php'); ?>
<?php
mysql_select_db($database_conCubagente, $conCubagente);
$query_rsMainData = "SELECT rss_data_id, date_added, num_views, title, link, `description` FROM items ORDER BY date_added DESC";
$rsMainData = mysql_query($query_rsMainData, $conCubagente) or die(mysql_error());
 
// TAKE OUT HIS LINE, it's part of the while loop  $row_rsMainData = mysql_fetch_assoc($rsMainData);
 
$totalRows_rsMainData = mysql_num_rows($rsMainData);
 
// are you getting more than one result in the record set?
echo "total records: ".$totalRows_rsMainData. "<br />\n";
 
?>
 
<?php   $link = $row_rsMainData['link'];
$codelink = substr($link,22,42);?>
 
<?php 
mysql_select_db($database_vb_videos_conn, $vb_videos_conn);
 
 
while ($row_rsMainData = mysql_fetch_assoc($rsMainData))
 
{
$sql_command = " INSERT INTO `video` (`videoid`, `title`, `description`, `videourl`, `videocategoryid`, `dateline`, `userid`, `videoservice`, `videoidservice`, `views`, `timelength`, `commentcount`, `ratingtotal`, `ratingnum`, `rating`, `reportthreadid`, `username`, `cachetags`) 
VALUES(1, '".$row_rsMainData['title']."', '".$row_rsMainData['description']."', '".$row_rsMainData['link']."', ".$row_rsMainData['rss_data_id'].", 1239699262, 1, 'YouTube', '$codelink', ".$row_rsMainData['num_views'].", 373, 0, 0, 0, 0, 0, 'Reportero', '<em>None...</em>')";
 
// you can always echo the commands and see how many are getting inserted
echo $sql_command. "<br />\n";
 
$ok= mysql_query($sql_command, $vb_videos_conn);
}
 
?>

Open in new window

0
 
Michael701Commented:
You can connect to as many databases as you'd like
each one will get a different resource link_identifier

you then pass this to the mysql_query just like you did with

mysql_query($query_rsMainData, $conCubagente)

0
 
FernanditosAuthor Commented:
Ok, but could you please tell me with code about how to insert the values into the other database. Please.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Michael701Commented:
when you open the new database you'll have to set
$conSql107121_9 (or change the name to whatever you use in the connect)

I also looped on the returned record set, I'm not sure if there will be only 1 record or multiple returned from the query.
mysql_select_db($database_conCubagente, $conCubagente); // you really don't need this because you specify the database in the mysql_query command
 
$query_rsMainData = "SELECT rss_data_id, date_added, num_views, title, link, `description` FROM items ORDER BY date_added DESC";
$rsMainData = mysql_query($query_rsMainData, $conCubagente) or die(mysql_error());
$totalRows_rsMainData = mysql_num_rows($rsMainData);
while ($row_rsMainData = mysql_fetch_assoc($rsMainData))
{
  $sql_command = "insert into video (`videocategoryid`, `dateline`, `views`, `title`, `videourl`, `description`) values ('".$row_rsMainData['rss_data_id']."', '".$row_rsMainData['date_added']."', '".$row_rsMainData['num_views']."', '".$row_rsMainData['title']."', '".$row_rsMainData['link']."', '".$row_rsMainData['description']."')";
  $ok=mysql_query($sql_command,$conSql107121_9);
}

Open in new window

0
 
FernanditosAuthor Commented:
Hi Michael,

Thank you. Your solution is working amazing. Just one thing: the loop is not working, could you please let me know why the loop is returning only 1 record ?

I attach all the code.

Thanks!
<?php require_once('Connections/conCubagente.php'); ?>
<?php require_once('Connections/vb_videos_conn.php'); ?>
<?php
mysql_select_db($database_conCubagente, $conCubagente);
$query_rsMainData = "SELECT rss_data_id, date_added, num_views, title, link, `description` FROM items ORDER BY date_added DESC";
$rsMainData = mysql_query($query_rsMainData, $conCubagente) or die(mysql_error());
$row_rsMainData = mysql_fetch_assoc($rsMainData);
$totalRows_rsMainData = mysql_num_rows($rsMainData);
?>
 
<?php 	$link = $row_rsMainData['link'];
$codelink = substr($link,22,42);?>
 
<?php 
mysql_select_db($database_vb_videos_conn, $vb_videos_conn);
 
 
while ($row_rsMainData = mysql_fetch_assoc($rsMainData))
 
{
$sql_command = " INSERT INTO `video` (`videoid`, `title`, `description`, `videourl`, `videocategoryid`, `dateline`, `userid`, `videoservice`, `videoidservice`, `views`, `timelength`, `commentcount`, `ratingtotal`, `ratingnum`, `rating`, `reportthreadid`, `username`, `cachetags`) 
VALUES(1, '".$row_rsMainData['title']."', '".$row_rsMainData['description']."', '".$row_rsMainData['link']."', ".$row_rsMainData['rss_data_id'].", 1239699262, 1, 'YouTube', '$codelink', ".$row_rsMainData['num_views'].", 373, 0, 0, 0, 0, 0, 'Reportero', '<em>None...</em>')";
$ok= mysql_query($sql_command, $vb_videos_conn);
}
 
?>

Open in new window

0
 
FernanditosAuthor Commented:
Great now it's perfect. I will accept your questions. Just one last thing:

 How can I avoid duplicate content by defining certain column as unique content.

For example to ignore the row insertion if the value of "videourl" already exist. This way I will not get duplicate videos.

Thanks, your help was brilliant.
0
 
Michael701Commented:
In mysql define it as a unique key, duplicate inserts will fail.
0
 
FernanditosAuthor Commented:
Yes, but I will get an error and I don't want that. It should be a way to place a condition to ignore or skip where duplicate field content is present.

thanks.
0
 
Michael701Commented:
by having the
$ok= mysql_query
it won't echo any error, $ok will be set to false

also it depends on what you want to do with the other fields.

You could use my_sql "insert ... on duplicate set" to update other fields.

if you really want you could read to see if the record already exists

$sql_command = "select * from video where videourl='".$row_rsMainData['link']."'";
$rs_check = mysql_query($sql_command, $vb_videos_conn);
if (mysql_num_rows($rs_check)==0)
{
  $sql_command = "insert .....
  $ok=mysql_query(...
}
0
 
FernanditosAuthor Commented:
Ok, I did it and It works, I don't get any error.

ALTER TABLE video ADD UNIQUE(videourl);

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.