Broken Query?

[Spunky]

Ok so I have a problem on my website and I can't seem to figure out the issue. On one web page there are links that when you click on them you send over the ID of it and then on the web page you go to depending on that ID it queries the correct data from the database.Go to: My WebsiteAnd click on the top link called "Armored Brown Bear". The code for that link (and all the links below it) is:

'<a href="achievement.php?aID=' . $row['ID'] . '">' . $row['Name'] . '</a>'

That way when you reach the next page it knows the ID number of the data that you wish to view and is able to view it. It runs this query:

$query = 'SELECT criteria.Info, achievements.Name, achievements.Image, achievements.Points, achievements.Description, pointsimage.Image AS tmp FROM ((achievements INNER JOIN criteria ON achievements.ID=criteria.ID) INNER JOIN pointsimage ON achievements.Points=pointsimage.iPoints) WHERE achievements.ID=' . $aID;

The important part being the: WHERE achievements.ID=' . $aIDAlso there is: $aID = $_GET['aID']; <<Which allows it to work with the link on the previous page.Problem is, if you now go to the third link down on that main page I linked, the nessessary stuff does not show. I put in additional code to try to understand what is going on.

$result = @mysql_query($query) or die(mysql_error());	  if ($result)	  {	  echo 'success';				if (mysql_num_rows($result) == 0) {			echo "No rows found, nothing to print so am exiting";			echo "<br/>" . $result;			exit;		}	  while ($row = mysql_fetch_assoc($result))	  {

Judging by this code and the result of whats shown on a page that doesn't work, we can see that part of the code works, becayse "echo 'success';" works. In that second if, what shows up in the web page is telling me that yes, no rows are being found. But why? And I do not know why "echo $result;" is showing "Resource id #2"You may note that there is no pattern as to which links work and which don't, at least none that I can see. Some do, some don't.I hope I posted the correct amount of code for anyone to look at to see a possible issue, if not, let me know what you need to see or know more about. Thanks!

[justsomeguy]

In that second if, what shows up in the web page is telling me that yes, no rows are being found. But why?

That should be obvious. No rows are matching the WHERE clause in the SQL statement.

And I do not know why "echo $result;" is showing "Resource id #2"

The mysql_query function returns a MySQL result resource, when you try to print any resource type it just prints the ID of it.

[Spunky]

That should be obvious. No rows are matching the WHERE clause in the SQL statement.

Yea I realize that, but why? It only doesn't find the row for some of them, not all.It was very obvious you right that's why I didn't state it, and I did say 'but why' at the end of the first thing you quoted.

[justsomeguy]

Right, the "why" is simply because the data in the database does not match what you're asking it for. It just doesn't match. That's the "why". If you're asking me why it doesn't match, I don't know, I didn't put the data there.

[Spunky]

Right, the "why" is simply because the data in the database does not match what you're asking it for. It just doesn't match. That's the "why". If you're asking me why it doesn't match, I don't know, I didn't put the data there.

Yes, yes I am asking why it doesn't match, you finally got that? Can I get someone helpful in here? I dont know if this guy is not being helpful on purpose or what.I KNOW that its telling me there are no rows because it is not finding rows because obviously there is something wrong with the query. I STATED THAT. Now WHY doesn't it match for some but for others it does. All the data was put in in the same manner and its all being queried the same. If you were to look in my database you would see in the tables each data DOES have an ID assigned to it. Infact, it IS finding that ID because if you went to the website I gave, clicked on the link I told you to, you would see in the url: http://www.wowtah.com/achievement.php?aID=16'16' being the ID. Its taking the ID of the data and putting that number in the variable 'aID' to bring it over to the next page to query. And for some its doing that, for others its not. Even though in my database, there IS an ID 16 and it DOES have data assigned to it, that data being the other data that the query is calling.

If you're asking me why it doesn't match, I don't know, I didn't put the data there.

ok, so that's rudely telling me that the query doesn't look like it has errors, which is what the title of this post is called, because I was worried about something being wrong with the query. However, if the database is fine, because I'm pretty sure it is, the data for the first entry and the data for the last entry are all put in the same way and nothing is empty so I cant see anything being wrong there. But, maybe there is, Im not the professional. Maybe you need to know more info or see more code in order to know more about the situation, if so, I did state before please let me know.So from me, the non-professional who needs help, to you, the supposed helpful professional, I ask if you, or someone, can please help me resolve this issue figure out what might be going wrong. Because me, the non-professional, cant figure it out and I was hoping someone who has more experience, the point of posting on this forum, could be helpful.

[dsonesuk]

looks like it could be an issue with joined tables, if any of these fail to find a related record in the joining, you might not retrieve all the results from achievements table you expected, even though you know that record exist in that table.try removing the joining, and add one join at a time, to see what results are returned.

[thescientist]

it might be worth echoing any variables before they go into the query to make they actually have a value. Everytime before you run that query, echo aID to make sure it is still what you expected from the previous page. That way you can check to see what its looking for, and to make sure that ID exists in the table. And dsonesuk also brings up a good point about the joins.

[Spunky]

Thank you both. I will try both your ideas and see what I come up with. Finally some answers I can work with.

[Spunky]

it might be worth echoing any variables before they go into the query to make they actually have a value. Everytime before you run that query, echo aID to make sure it is still what you expected from the previous page. That way you can check to see what its looking for, and to make sure that ID exists in the table.

Actually though, you will notice that up in the url when you go to the next page, the aID IS correctly shown up there like it is supposed to and as it does on even pages that do work, the only difference is is data actually shows in the ones that work.

[Spunky]

Alright I have it echoing the aID twice. Once in the first if and once in the second. Both times it is showing the correct aID number.