homepage Welcome to WebmasterWorld Guest from 54.226.43.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

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




msg:1305372
 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




msg:1305373
 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




msg:1305374
 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




msg:1305375
 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




msg:1305376
 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




msg:1305377
 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




msg:1305378
 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




msg:1305379
 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




msg:1305380
 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




msg:1305381
 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




msg:1305382
 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




msg:1305383
 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




msg:1305384
 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




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

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

woldie




msg:1305386
 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




msg:1305387
 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




msg:1305388
 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




msg:1305389
 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




msg:1305390
 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




msg:1305391
 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