Puzzling mysql_result problem since upgrading to MYSQL 4.1

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Puzzling mysql_result problem since upgrading to MYSQL 4.1

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
aladdinsane
Forum Commoner
Posts: 38
Joined: Fri Sep 05, 2003 2:15 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: Puzzling mysql_result problem since upgrading to

Post 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.
Post Reply