Jump to content


Photo

Wierd MySQL Problem


  • Please log in to reply
6 replies to this topic

#1 johnl1479

johnl1479
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationSac-a-tomatoes, CA

Posted 08 March 2006 - 05:51 AM

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)

SELECT * FROM albums WHERE name = 'Away From the Sun'

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 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. 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:

<?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 />";
}

?>

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?

http://john.redhedinsanity.net
--------------------
OS X - Apache 1.3.33 :: PHP 5.0.1 :: MySQL 5
Linux - Apache 1.3.33 :: PHP 4.3.11 :: MySQL 4.1.11

#2 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 08 March 2006 - 02:08 PM

[!--quoteo(post=352768:date=Mar 8 2006, 12:51 AM:name=johnl1479)--][div class=\'quotetop\']QUOTE(johnl1479 @ Mar 8 2006, 12:51 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
SELECT * FROM albums WHERE name = 'Away From the Sun'
[/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 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. 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.
--
[a href=\"http://blog.godshell.com\" target=\"_blank\"]XenoPhage[/a]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Something mysterious is formed, born in the silent void. Waiting alone and unmoving, it is at once still and yet in constant motion. It is the source of all programs. I do not know its name, so I will call it the Tao of Programming.[/quote]

#3 johnl1479

johnl1479
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationSac-a-tomatoes, CA

Posted 08 March 2006 - 08:01 PM

[!--quoteo(post=352848:date=Mar 8 2006, 06:08 AM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 8 2006, 06:08 AM) View Post[/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) View Post[/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
http://john.redhedinsanity.net
--------------------
OS X - Apache 1.3.33 :: PHP 5.0.1 :: MySQL 5
Linux - Apache 1.3.33 :: PHP 4.3.11 :: MySQL 4.1.11

#4 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 08 March 2006 - 09:35 PM

[!--quoteo(post=352954:date=Mar 8 2006, 03:01 PM:name=johnl1479)--][div class=\'quotetop\']QUOTE(johnl1479 @ Mar 8 2006, 03:01 PM) View Post[/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\" /]
--
[a href=\"http://blog.godshell.com\" target=\"_blank\"]XenoPhage[/a]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Something mysterious is formed, born in the silent void. Waiting alone and unmoving, it is at once still and yet in constant motion. It is the source of all programs. I do not know its name, so I will call it the Tao of Programming.[/quote]

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 09 March 2006 - 05:41 AM

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.

#6 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 09 March 2006 - 07:13 AM

[!--quoteo(post=353158:date=Mar 9 2006, 06:41 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 9 2006, 06:41 AM) View Post[/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.)

#7 johnl1479

johnl1479
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationSac-a-tomatoes, CA

Posted 09 March 2006 - 03:32 PM

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
http://john.redhedinsanity.net
--------------------
OS X - Apache 1.3.33 :: PHP 5.0.1 :: MySQL 5
Linux - Apache 1.3.33 :: PHP 4.3.11 :: MySQL 4.1.11




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users