7 Replies - 1576 Views - Last Post: 28 September 2009 - 12:26 PM

#1 BlissC   User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 27-September 09

Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 03:46 AM

What's the best way to extract a record from a MySQL database based on the current year where I have just a year in a column and not a full date? I'm trying to get at the value of 'target' in the database to use as a variable elsewhere on the site.

I've got a small table on a MySQL database (MySQL 5.0) that has the fields 'id', 'year' and 'target' that holds the data for fundraising targets for each year. The data's in the format:

id - int (auto-increment)
year - varchar(4)
target - varchar (7) (I've chosen varchar because I'm not going to be needing to do any calculations with this particular data)

The query I've been trying to use (at least the most recent one, which seems to be the closest I've got to doing what I want it to do is:

require_once('/path/to/file/connect.php');		
$result3 = mysql_query("SELECT * FROM annualtarget WHERE year LIKE YEAR(NOW());");
$goal_level = mysql_result($result3,2);	   
?>


...but this gives the error: "Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 6 in /path/to/file.php on line 83" and nothing appears on the page where I'm trying to use the variable $goal_level on the page. (With the current test data in the database, row 2 is the correct row for this year).

I've got a feeling that part of the problem is that the year isn't in a full date format, but I can't figure out another way of getting the current year, and I don't want to have to put a full date in (or rather the people who'll be keeping the site updated won't as they're not particularly technical, so I want to make it easy for them just to be able to type in a year and the fundraising target for that year.

Anyone any ideas where I'm going wrong with this?

Is This A Good Question/Topic? 0
  • +

Replies To: Problems extracting data from MySQL database based on year

#2 smacdav   User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 08:23 AM

Your query is fine. I believe it's a minor php error: try simply removing the semicolon inside the mysql_query string like so:

$result3 = mysql_query("SELECT * FROM annualtarget WHERE year LIKE YEAR(NOW())");


Was This Post Helpful? 1
  • +
  • -

#3 Hary   User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 10:06 AM

if you change your php code to:
$result = mysql_query("<....>") or die(mysql_error());


you can see the response of the MySQL server, which contains an error message your syntax was wrong.
Was This Post Helpful? 0
  • +
  • -

#4 BlissC   User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 27-September 09

Re: Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 11:37 AM

Thanks smacdav - good catch on the semi-colon (I always miss those! :lol: ). I've just tried removing that semi-solon though (and adding in the "or die(mysql_error()") and it's still no go, and I'm still getting exactly the same MySQL error coming up "Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 6 in /path/to/file/index.php on line 83" It's got me stumped!
Was This Post Helpful? 0
  • +
  • -

#5 BlissC   User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 27-September 09

Re: Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 04:31 PM

Okay, well I've been playing around with the query and I thought I might be getting somewhere with this with the following query (thinking that I need to convert the 'year' string to a date)...

require_once('/path/to/file/connect.php');	 
		  $result3 = mysql_query("SELECT *, STR_TO_DATE(`year`, '%Y') FROM annualtarget WHERE year(NOW())");		  
		  $goal_level = mysql_result($result3,2);


This does actually give me a result (yay!...or at least a figure for the $goal_level variable)....the problem is that it's not the right result! (not so good) :lol:

The result it's given me is '3'. This is the test data in the database though...

----------------------
ID | YEAR | TARGET |
----------------------
1 | 2010 | 9000 |
----------------------
2 | 2009 | 8000 |
----------------------
3 | 2008 | 6000 |
----------------------

It's been suggested to me that I should ditch the 'id' column and just go with the year and the target, using the year as the index (which I can partly see the idea of), but it's the query that's completely foxing me. It's one of those things that I know should be easy, but I just can't get it :crazy: Any ideas anyone before I go completely crazy trying to figure this out? :lol:
Was This Post Helpful? 0
  • +
  • -

#6 smacdav   User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: Problems extracting data from MySQL database based on year

Posted 27 September 2009 - 07:46 PM

I, too, have a database with tables with year columns that are stored as strings, and your originally posted query (with the table name changed, of course) works fine for me. How odd.

Regardless, looking at the documentation for mysql_result (I use PEAR DB, so I'm not 100% familiar with the mysql package), it looks to me like you should change goal_level as follows:

$goal_level = mysql_result($result3,0,2);



My reasoning is this: the $row argument of the function is the 0-index row of the result. Your query should only return one row, so you want the first one, thus, the 0. Your target value is in the third column, so the offset (from the first column) is 2, thus the 2 at the end.

The fact that you're getting a 3 right now makes it seem as though you are actually getting all three rows returned by the query, so what you're getting is the first entry in the third row (row 2, which is the one you were telling it to look for).

Come to think of it, this probably explains your original error: the query was returning one row and you were telling it to look at the third row (row 2). I suggest you go back to the query that you posted in the original post and change $goal_level to see what happens.

This post has been edited by smacdav: 27 September 2009 - 07:49 PM

Was This Post Helpful? 1
  • +
  • -

#7 BlissC   User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 27-September 09

Re: Problems extracting data from MySQL database based on year

Posted 28 September 2009 - 12:20 PM

Smacdav, you are wonderful! :D That works perfectly - thank you so much!

Your explanation for why it works makes perfect sense as well (and is clearer than the documentation) - thanks again!
Was This Post Helpful? 0
  • +
  • -

#8 smacdav   User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: Problems extracting data from MySQL database based on year

Posted 28 September 2009 - 12:26 PM

Always happy to help. :)

This post has been edited by smacdav: 28 September 2009 - 12:26 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1