Page 1 of 2

Puzzling mysql_result problem since upgrading to MYSQL 4.1

Posted: Wed Dec 14, 2005 5:44 pm
by aladdinsane
Hi,

I wondered if anyone can help me with a problem I am having?

Since my host upgraded to MySQL 4.1 part of my site has stopped working - the code for this part is at the bottom of this post. Before this upgrade the code worked perfectly for many months. I believe the previous version of MySQL was 3.2x

The error I am getting is:

Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 85 in /home/clarik/public_html/shop/MXKart/myShippingRates.inc.php on line 102


This is line 102: $result = mysql_result($kgmstate, 0);

I have checked and the query is returning a result but I am still get this error.

Is it possible the table was corrupted in the MySQL upgrade?

It would be great if somebody could help me please.

Thanks Keith

Code: Select all

//New Shipping
 function MXKRate_newshipping($oldCost){
 global $HTTP_GET_VARS,$HTTP_POST_VARS,$HTTP_SESSION_VARS;
 $KartFV_RS = getKartRecordset();
 $total = $KartFV_RS->getTotalPrice(true);
 
 
 
// connect to MySQL

 mysql_connect("xxxx", "xxxx", "xxxx") 
  or die ("Unable to connect to database.");

  // select database on MySQL server

 mysql_select_db("clarik_mxshop") 
  or die ("Unable to select database.");

  // formulate query
  
  global $sqlwhere;
  
  $sqlwhere = $HTTP_SESSION_VARS['KT_kartOrderId'];
  
  $sql = "SELECT state_ord FROM order_ord WHERE id_ord = '$sqlwhere'";
  
  
  // run query
  
 $kgmstate = mysql_query($sql);
 
 $result = mysql_result($kgmstate, 0); 
 
  
  // close database connection
  
 mysql_close();
 
 
 if ($result == "UK Mainland" && $total<50) {return 7.05;}
 else if ($result == "UK Mainland" && $total<100 && $total>50 ) {return 3.53;}
 else if ($result == "UK Mainland" && $total>100) {return 0;} 
 
 else if ($result == "Channel Isles" && $total<400) {return 29.38;}
 else if ($result == "Channel Isles" && $total>400) {return 0;} 
 
 else if ($result == "Isle of Man" && $total<300) {return 17.63;}
 else if ($result == "Isle of Man" && $total>300) {return 0;} 
 
 else if ($result == "Isle of Wight" && $total<300) {return 17.63;}
 else if ($result == "Isle of Wight" && $total>300) {return 0;} 
 
 else if ($result == "Northern Ireland" && $total<300) {return 17.63;}
 else if ($result == "Northern Ireland" && $total>300) {return 0;} 
 
 else if ($result == "Scilly Isles" && $total<300) {return 17.63;}
 else if ($result == "Scilly Isles" && $total>300) {return 0;} 

 else if ($result == "Republic of Ireland" && $total<300) {return 23.50;}
 else if ($result == "Republic of Ireland" && $total>300) {return 0;} 
 
 else return 0;
 
 
}
//End New Shipping

Posted: Wed Dec 14, 2005 5:56 pm
by RobertGonzalez
This looks like a PHP error, not a MySQL error. Try throwing in an error trapper to see if MySQL is throwing an error:

Code: Select all

if (!$result = mysql_result($kgmstate, 0))
{
    die("Could not set result: " . mysql_error());
}
If something shows up there it is a MySQL error, which as I said, I don't think it is.

Posted: Wed Dec 14, 2005 6:25 pm
by aladdinsane
I did as you suggested and I just get this

Could not set result:

So I guess it must be a PHP error. Can you (or anyone else) see anything in my PHP code that could have started causing this problem. I'm not sure if they upgraded PHP too but the version in use now is 4.3.11

Its really bugging me because this code worked fine for months until they changed servers.

Thanks

Keith

Posted: Thu Dec 15, 2005 3:40 am
by twigletmac
Try some error trapping on the query itself, so instead of:

Code: Select all

$kgmstate = mysql_query($sql);
try

Code: Select all

$kgmstate = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
Mac

Posted: Thu Dec 15, 2005 7:45 am
by aladdinsane
Hi,

Thanks for the suggestions.

I tried yours twigletmac but it produced no error and "die" wasnt called so I got the same warning as originally posted.

This leads me to believe the query is executing but for some strange reason is returning no results.

When I run the query using phpmyadmin it works as expected.

I will try hard coding a session ID into the query for an entry that already exists in the database. Hopefully this will narrow down my hunt for the problem.

Thanks

Keith

Posted: Thu Dec 15, 2005 8:15 am
by RobertGonzalez
OK, immediately after:

Code: Select all

$result = mysql_result($kgmstate, 0)
put:

Code: Select all

echo $result;
What is that showing you? Now that I look at the error it seems as though PHP can't return to the beginning of an array it already walked. It would be interesting to see where this happening.

Posted: Thu Dec 15, 2005 8:23 am
by aladdinsane
Hi,

I tried hard coding an existing session id into the query and this returned this worked and returned the expected result.

I add the code you suggested Everah (with a small change to make it easier to see) - echo "the result is".$result."<br><br>";

All I get back is this:

the result is

This is so strange because it worked before for months.

Thanks

Keith

Posted: Thu Dec 15, 2005 9:04 am
by RobertGonzalez
This tells me that your session var is not getting passed to the page. I would imagine the reason you are not getting a result is because if the session var is empty the query is searching for all records where id_ord is empty. If there are none then MySQL would return an empty result set.

Have you tried echo'ing out your session vars at the beginning of the script to see that they are actually set to something? Try that and see what comes out of it.

Posted: Thu Dec 15, 2005 9:05 am
by aladdinsane
My session var is there I have echoed it to the page and it is displayed properly and the same as in the database.

On further testing I have noticed if I use a session id from when the whole thing worked (i.e. before the host upgraded mysql/the server itself) my query works fine.

However if I use a session id thats only been in the database since this problem it wont work at all and I get the usual error warning.

Could this somehow be related to how the data is now stored in MySQL?

The Session ID is stored in a field that is a primary key with a type and length of varchar (255). Has MySQL 4.1 changed how varchar is used, maybe by adding trailing or following info to pad out to 255 length?

Thanks

Keith

Posted: Thu Dec 15, 2005 9:11 am
by RobertGonzalez
And you said that when you run the hard query through phpMyAdmin and in your script, it works, but trying to assign a var the same value as your session id fails? That is peculiar indeed. There are no naming conflicts in your script anywhere, are there?

I'm off to work right now. I will try to pick up again in a few hours.

Posted: Thu Dec 15, 2005 9:28 am
by aladdinsane
Thats correct Everah. There are no naming conflicts - the only change thats been made is to the MySQL version and server set-up.

Interestingly when I run the query using phpmyadmin with a hard coded session id the same strange thing happens - if I use a session id from when the whole thing worked (i.e. before the host upgraded mysql/the server itself) my query works fine returning 1 row.

However if I use a session id thats only been in the database since this problem started the query returns 0 rows.

I'm not sure why but something tells me this problem is somehow related to how the data is now stored in MySQL and possibly a corruption of the table during the upgrade.

Thanks for your help Everah. Any further ideas would be much appreciated.

Keith

Posted: Thu Dec 15, 2005 10:37 am
by RobertGonzalez
Are your session ids still the same length as before when the servers were upgraded? If you search the entire table and display all of the session ids that are in the table, what does it show? It seems weird that MySQL would return an empty set even when the requested data is in the table.

Posted: Thu Dec 15, 2005 10:44 am
by aladdinsane
Hi Everah,

The session id's have never been the same length every time. I can see no pattern of change between the old configuration and the new.

Thanks

Keith

Posted: Thu Dec 15, 2005 11:50 am
by RobertGonzalez
Ok, my next question is are you sure that your session id's are getting added to your database? If the session id is available as a var but MySQL can't find it, maybe the session id is not getting stored or is getting modified during INSERT.

The field length for varchar is a reserved memory figure (I think) so I don't think that MySQL will change your data from one db version to the next. So if your field length is a 255 and your field value is 14 character long, the db reserves 255 characters worth of memory for the data. Maybe the problem is with the INSERT code in PHP?

Re: Puzzling mysql_result problem since upgrading to

Posted: Thu Dec 15, 2005 5:16 pm
by AKA Panama Jack
aladdinsane wrote:

Code: Select all

$kgmstate = mysql_query($sql);
 
 $result = mysql_result($kgmstate, 0);
You might want to place this after the query...

Code: Select all

echo mysql_num_rows($kgmstate);
I bet you are returning 0 results and that WILL cause your error.