Wierd MySQL Problem

[johnl1479]

Ok, so here's my scenario:

I have a text file that contains data structered kind of like a database, each line is a different row. One element of the row, column #4, contains a string. I have a table in my database that associates the string with and id number. Using a foreach loop (every line of the file), i am trying to extract that string, run a mysql query, and retrieve the id number from the database. Here is the query from one of the lines of the file (using a php echo statement)

[code]
SELECT * FROM albums WHERE name = 'Away From the Sun'
[/code]

See the query? No syntax errors at all. Now, when I execute that query with a mysql_query() function, all goes well. No errors. Now, when i take the resultset from the query and put it into mysql_result, i get and error that says [b]Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/john/public_html/music/upload.php on line 17[/b]. But wait, it gets even better. When i take the above query, and run it through phpMyAdmin, i get the desired result. the id number is returned. What gives?

Here is the code of the file:

[code]
<?php

$file = "Music.txt";

$source = file($file);

mysql_connect("localhost","john","********************");
mysql_select_db("john_music");

for ($i = 1; $i < count($source); $i++) {

    $data = explode("    ",$source[$i]);
    
    $sql = "SELECT * FROM albums WHERE name = '".$data[3]."'";
    $resultset = mysql_query($sql);
    echo "> > $sql<br />";
    echo $resultset."<br />";
    echo "> > ".mysql_result($resultset,0,'id')."<br />";
    
    $sql = "INSERT INTO songs VALUES ('','".$data[0]."','".$data[3]."','".$data[10]."','','','')";
    echo $sql . ";<br /><br /><br />";
}

?>
[/code]

and here is the output that i get: [a href=\"http://johnluetke.net/music/upload.php\" target=\"_blank\"]Click For Script Output[/a]

What gives?

[XenoPhage]

[!--quoteo(post=352768:date=Mar 8 2006, 12:51 AM:name=johnl1479)--][div class=\'quotetop\']QUOTE(johnl1479 @ Mar 8 2006, 12:51 AM) [snapback]352768[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
SELECT * FROM albums WHERE name = 'Away From the Sun'
[/code]
[/quote]

When I hit that page, the query has all manner of wierd characters in the name... That may be the source of your problem..

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
See the query? No syntax errors at all. Now, when I execute that query with a mysql_query() function, all goes well. No errors. Now, when i take the resultset from the query and put it into mysql_result, i get and error that says [b]Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/john/public_html/music/upload.php on line 17[/b]. But wait, it gets even better. When i take the above query, and run it through phpMyAdmin, i get the desired result. the id number is returned. What gives?
[/quote]

That error means that no rows were returned. Basically, you queried the database and it returned nothing. You failed to check the number of return rows and assumed something came back. Because it didn't any further calls to work on the result (fetch row, etc) will fail.

You need to determine why the query has all those wierd characters in it first, and then put in code to handle non-existant data.

[johnl1479]

[!--quoteo(post=352848:date=Mar 8 2006, 06:08 AM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 8 2006, 06:08 AM) [snapback]352848[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That error means that no rows were returned. Basically, you queried the database and it returned nothing. You failed to check the number of return rows and assumed something came back. Because it didn't any further calls to work on the result (fetch row, etc) will fail.
[/quote]

Yes, I know this. An empty resultset is empty. (0 rows, I performed check with mysql_num_rows())

[!--quoteo(post=352848:date=Mar 7 2006, 09:51 PM:name=johnl1479)--][div class=\'quotetop\']QUOTE(johnl1479 @ Mar 7 2006, 09:51 PM) [snapback]352848[/snapback][/div][div class=\'quotemain\'][!--quotec--]
But wait, it gets even better. When i take the above query, and run it through phpMyAdmin, i get the desired result. the id number is returned. What gives?
[/quote]

As you can see what from i said, the data does exist in the database. The exact same query in phpMyAdmin returned the desired result.

--EDIT--
What do you mean by wierd characters? i have checked the page from multiple computers running different O/S's and different browsers, nothing shows wierd characters

[XenoPhage]

[!--quoteo(post=352954:date=Mar 8 2006, 03:01 PM:name=johnl1479)--][div class=\'quotetop\']QUOTE(johnl1479 @ Mar 8 2006, 03:01 PM) [snapback]352954[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yes, I know this. An empty resultset is empty. (0 rows, I performed check with mysql_num_rows())
As you can see what from i said, the data does exist in the database. The exact same query in phpMyAdmin returned the desired result.

--EDIT--
What do you mean by wierd characters? i have checked the page from multiple computers running different O/S's and different browsers, nothing shows wierd characters
[/quote]

It looks like this (Firefox 1.5) :

[img src=\"http://www.godshell.com/oss/broke.jpg\" border=\"0\" alt=\"IPB Image\" /]

[wickning1]

Same for me, also Firefox 1.5.

The warning you're getting about the result is because the query is erroring out. Echo mysql_error() right after mysql_query() to see what the problem is.

[sasa]

[!--quoteo(post=353158:date=Mar 9 2006, 06:41 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 9 2006, 06:41 AM) [snapback]353158[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Same for me, also Firefox 1.5.

The warning you're getting about the result is because the query is erroring out. Echo mysql_error() right after mysql_query() to see what the problem is.
[/quote]
try to change encoding of file Music.txt from 16-bit (UTF-16) to 8-bits (UTF-8). (You can do this with notepad.)

[johnl1479]

wow...changing the encoding of the file worked perfectly, thanks sasa...thats the last thing i would have tried to do.

ps. don't assume thats everyone uses windows (notepad) :P