Welcome to WebmasterWorld Guest from 54.162.240.235

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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

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



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)

10+ Year Member



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)

10+ Year Member



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

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

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



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

since that is exactly what it is made for ;)

 

Featured Threads

Hot Threads This Week

Hot Threads This Month