Copy table data to another

Query help on this one

[woldie]

Hi there,

I've been searching in the PHP forums for this answer, and I've found something similar, and have used to this to the problem I have.

Anyway, when I do run this script I get an error line 5 which is the 'while' statement.

$query_newsfeed = "SELECT itemid,heading,news_date,content FROM newsfeed";
$query1_exec = mysql_query ( $query_newsfeed );

// Error here!
while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )
{
$query2 = "INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( $query_row[$itemid],'$query_row[$heading]','$query_row[$news_date]','$query_row[$content]' )"
$query2_exec = mysql_query ( $query2 );

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Any ideas where I could be going wrong?

Thanks

[carneddau]

Hi,

Use mysql_error() to see what the problem is.

Add it here:

$query1_exec = mysql_query($query_newsfeed ) or die(mysql_error());

That should tell you what's going wrong with your query.

Cheers

[woldie]

Thanks Carneddau,

I've fixed the errors, however the scripts falls down because I've echoed $query2 and there's no data, so I'm doing something wrong here.

Any suggestions?

$query_newsfeed = "SELECT itemid,heading,news_date,content FROM newsfeed";
$query1_exec = mysql_query ( $query_newsfeed ) or die (mysql_error());


while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )
{
$query2 = "INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( $query_row[$itemid],'$query_row[$heading]','$query_row[$news_date]','$query_row[$content]' )";
echo $query2;
$query2_exec = mysql_query ( $query2 );
}

Thanks

[Paul in South Africa]

Is $query1_exec generating any results?

Try putting something like

if (mysql_num_rows($query1_exec) == 0) {
echo "No rows found, cannot do anything";
exit;
}

into your code.

Also the values for $query2 should be in the format $query_row[itemid] not $query_row[$itemid]

[woldie]

Thanks Paul,

That did work. I changed the $itemid just to itemid

However I know for a fact that there are 220 items in newsfeed table, but when it inserts in the newsfeed_map it has only inserted only 20 items.

I'm baffled...

Can you help?

[woldie]

This is the code as it stands....

$query_newsfeed = "SELECT itemid,heading,news_date,content FROM newsfeed";
$query1_exec = mysql_query ( $query_newsfeed ) or die (mysql_error());
if (mysql_num_rows($query1_exec) == 0)
{
echo "No rows found, cannot do anything";
exit;
}

while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )
{
$query2 = "INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( $query_row[itemid],'$query_row[heading]','$query_row[news_date]','$query_row[content]' )";

echo $query2;
$query2_exec = mysql_query ( $query2 );
}

[Paul in South Africa]

Have you tried echoing the value returned by mysql_num_rows($query1_exec) to see how many results are being found by the initial query?

[woldie]

Yes, I've just done, and it has returned 218 rows,

So it should insert into newsfeed_map table 218 rows.

[Paul in South Africa]

Now I'm confused as well. I've been at work for 14 hours and my girlfriend has just phoned to say that my dinner is going to be in the dog if I don't hurry up and get home. I'll try to think of an answer on the way.

[woldie]

Okay Paul,

Enjoy your dinner and get home quickly!

I'll try to think of a answer myself....

[carneddau]

Hi again,

See if something's going wrong with your INSERT use mysql_error() again:

$query2_exec = mysql_query($query2) or die (mysql_error());

Cheers

[coopster]

You may also want to make sure that you aren't attempting to

INSERT

duplicate rows based on a

UNIQUE

key. In MySQL, a

UNIQUE

key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.

[woldie]

Thanks Carneddau

You've hit the nail right on the head. What's happened its found a ' in the query, so how do you escape ' in a query?

I though MySQL escapes them automatically?

So much for debugging :-)

Cheers

[Paul in South Africa]

Try $query_row[content]=addslashes($query_row[content])

[woldie]

Hi Paul,

Thanks I've tried that, that does work to a certain extent, however I get this error...

INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( 1119820,'Search engine accused of 'typo-squatting'','26/09/2003','Domain registrar Verisign has come under criticism for directing \'page not found\' errors to its own search engine.

Critics have accused the portal of hijacking traffic, but laws regarding non-existent web domains are unclear.

Concerns have been raised over the fact that Verisign\'s service makes it impossible to check if names are valid or not.

\'The problem with that is that most anti-spam products conduct this simple test as their first move in determining whether an email is spam or not,\' explained one network operator, according to Web Rank.

\'That means spammers will have a free shot at getting their mail past one of the most basic weapons in the anti-spam armoury.\'' )

You have an error in your SQL syntax near 'typo-squatting'','26/09/2003','Domain registrar Verisign has come under criticis' at line 1

Code:

$query_newsfeed = "SELECT itemid,heading,news_date,content FROM newsfeed";
$query1_exec = mysql_query ( $query_newsfeed ) or die (mysql_error());
$num=mysql_num_rows($query1_exec);
echo '<b>'.$num.'</b><br>';
if (mysql_num_rows($query1_exec) == 0)
{
echo "No rows found, cannot do anything";
exit;
}

while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )
{
$query_row[content]=addslashes($query_row[content]);
$query2 = "INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( $query_row[itemid],'$query_row[heading]','$query_row[news_date]','$query_row[content]' )";

echo $query2;
$query2_exec = mysql_query($query2) or die (mysql_error());
}

As you can see I put ' round the $query_row[heading] because its a varchar.

Thanks

[Paul in South Africa]

The error seems to be in 'typo-squatting'','26/09/2003'

There is and extra ' in there. If it is present in the original data the the addition of
$query_row[heading]=addslashes($query_row[heading]);
to your code should solve the problem.

[woldie]

Yes you are right

However, take this line 'Search engine accused of 'typo-squatting'', I've put quotes round this because its a varchar (see code), but the word typo-squatting has already has ' round it, so you would expect this to work.

Any ideas?

Thanks

[Paul in South Africa]

Just add $query_row[heading]=addslashes($query_row[heading]); to your code before $query2

The ' before typo-sqatting is being interpreted as the end of the field and needs to be escaped.

[woldie]

Paul,

You've cracked it :-) It now copies the whole lot, thanks very much for help.

Here's the code...

$query_newsfeed = "SELECT itemid,heading,news_date,content FROM newsfeed";
$query1_exec = mysql_query ( $query_newsfeed ) or die (mysql_error());
$num=mysql_num_rows($query1_exec);
echo '<b>'.$num.'</b><br>';
if (mysql_num_rows($query1_exec) == 0)
{
echo "No rows found, cannot do anything";
exit;
}

while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )
{
$query_row[heading]=addslashes($query_row[heading]);
$query_row[content]=addslashes($query_row[content]);
$query2 = "INSERT INTO newsfeed_map ( itemid,heading,news_date,content ) VALUES ( $query_row[itemid],'$query_row[heading]',".$query_row[news_date].",'$query_row[content]' )";

echo $query2;
$query2_exec = mysql_query($query2) or die (mysql_error());
}

[jatar_k]

look at this one too
mysql_escape_string [ca.php.net]

since that is exactly what it is made for ;)