asked on

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

Michael701

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)

Fernanditos

ASKER

Ok, but could you please tell me with code about how to insert the values into the other database. Please.

Michael701

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

Fernanditos

ASKER

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

ASKER CERTIFIED SOLUTION

Michael701

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernanditos

ASKER

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.

Michael701

In mysql define it as a unique key, duplicate inserts will fail.

Fernanditos

ASKER

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.

Michael701

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

Fernanditos

ASKER

Ok, I did it and It works, I don't get any error.

ALTER TABLE video ADD UNIQUE(videourl);

Thanks!