• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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

0
Fernanditos
Asked:
Fernanditos
  • 5
  • 5
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
Michael701Commented:
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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now