Welcome to WebmasterWorld Guest from 50.17.114.227

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Copy table data to another

Query help on this one

     
11:23 am on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

11:47 am on Mar 9, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 31, 2004
posts:86
votes: 0


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

3:45 pm on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

4:26 pm on Mar 9, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


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]

4:42 pm on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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?

4:44 pm on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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 );
}

4:50 pm on Mar 9, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


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?
5:04 pm on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

So it should insert into newsfeed_map table 218 rows.

5:07 pm on Mar 9, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


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.
5:18 pm on Mar 9, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


Okay Paul,

Enjoy your dinner and get home quickly!

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

5:22 pm on Mar 9, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 31, 2004
posts:86
votes: 0


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

11:52 pm on Mar 9, 2004 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.
9:34 am on Mar 10, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

9:52 am on Mar 10, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


Try $query_row[content]=addslashes($query_row[content])
10:05 am on Mar 10, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

10:28 am on Mar 10, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


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.

10:56 am on Mar 10, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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

11:21 am on Mar 10, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 9, 2003
posts:180
votes: 0


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.

11:36 am on Mar 10, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Feb 13, 2003
posts:220
votes: 0


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());
}

4:25 pm on Mar 10, 2004 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15755
votes: 0


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

since that is exactly what it is made for ;)