homepage Welcome to WebmasterWorld Guest from 54.145.252.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Copy table data to another
Query help on this one
woldie

10+ Year Member



 
Msg#: 3127 posted 11:23 am on Mar 9, 2004 (gmt 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

 

carneddau

10+ Year Member



 
Msg#: 3127 posted 11:47 am on Mar 9, 2004 (gmt 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

woldie

10+ Year Member



 
Msg#: 3127 posted 3:45 pm on Mar 9, 2004 (gmt 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

Paul in South Africa

10+ Year Member



 
Msg#: 3127 posted 4:26 pm on Mar 9, 2004 (gmt 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]

woldie

10+ Year Member



 
Msg#: 3127 posted 4:42 pm on Mar 9, 2004 (gmt 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?

woldie

10+ Year Member



 
Msg#: 3127 posted 4:44 pm on Mar 9, 2004 (gmt 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 );
}

Paul in South Africa

10+ Year Member



 
Msg#: 3127 posted 4:50 pm on Mar 9, 2004 (gmt 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?

woldie

10+ Year Member



 
Msg#: 3127 posted 5:04 pm on Mar 9, 2004 (gmt 0)

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

10+ Year Member



 
Msg#: 3127 posted 5:07 pm on Mar 9, 2004 (gmt 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.

woldie

10+ Year Member



 
Msg#: 3127 posted 5:18 pm on Mar 9, 2004 (gmt 0)

Okay Paul,

Enjoy your dinner and get home quickly!

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

carneddau

10+ Year Member



 
Msg#: 3127 posted 5:22 pm on Mar 9, 2004 (gmt 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

coopster

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



 
Msg#: 3127 posted 11:52 pm on Mar 9, 2004 (gmt 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.
woldie

10+ Year Member



 
Msg#: 3127 posted 9:34 am on Mar 10, 2004 (gmt 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

Paul in South Africa

10+ Year Member



 
Msg#: 3127 posted 9:52 am on Mar 10, 2004 (gmt 0)

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

woldie

10+ Year Member



 
Msg#: 3127 posted 10:05 am on Mar 10, 2004 (gmt 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

Paul in South Africa

10+ Year Member



 
Msg#: 3127 posted 10:28 am on Mar 10, 2004 (gmt 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.

woldie

10+ Year Member



 
Msg#: 3127 posted 10:56 am on Mar 10, 2004 (gmt 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

Paul in South Africa

10+ Year Member



 
Msg#: 3127 posted 11:21 am on Mar 10, 2004 (gmt 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.

woldie

10+ Year Member



 
Msg#: 3127 posted 11:36 am on Mar 10, 2004 (gmt 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());
}

jatar_k

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



 
Msg#: 3127 posted 4:25 pm on Mar 10, 2004 (gmt 0)

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

since that is exactly what it is made for ;)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved