[SOLVED] How can I improve this, need help with loop.

[m4x3vo]

I'm developing a site in which a person can keep track of their "achievements" from a RPG site called vpnwars. 

 

I have a database with all the possible achievements in the game, the database is named ``ach``.

This database contains the id of each achievement, its name, and description.

 

A person visits a page named add.php which allows them to check off each achievement they have.  When they are done checking off the ones they have, they click submit.  Insert.php then retrieves the achievements checked off and inserts the user's id and the first achievement's id that was checked off and being added into user_ach.  It then adds the second acheivement id and the user's id into the next row, and so on until it has added each ach id by that user into the database (one per row) (column names are user_id and ach_id and this table name is user_ach).

 

Then I am having trouble working on a page that displays the achievements checked off by each person.  What I would like it to do is when the user views the page, it will get their user id in their cookie.  It will use that id and go through each possible # id in the user_ach table (this table only stores the ach id and user id).  It basically tries to guess which achievements the user has checked off.  When the id exists, it goes to the achievements database using that id and finds the name of the achievement and displays it on the page showing what achievements youve checked.  This process is continually looped.  However there is one problem im getting that i need help with.  If the id does not exist in user_ach for that user when its bruting for the id, returns a mysql error since it doesnt exist.  I need something that will make the $counter go on if the number doesnt exist for the uid in the cookie.  Here is the source of the second page i explained that shows your achievements youve checked, I need a solution so it moved on if the number is not valid, and continues looping:

 

<?php
$cookie = $_COOKIE["uid"];
for ($counter = 1; $counter <= 139; $counter++)
{
$query = "SELECT * FROM user_ach WHERE user_id = $cookie AND ach_id = $counter";
$result1 = mysql_query($query);
$res = mysql_fetch_assoc($result1);
$ach_id = $res['ach_id'];
$query2 = "SELECT * FROM ach WHERE id = $ach_id";
$result2 = mysql_query($query2);
$res = mysql_fetch_assoc($result2);
echo $res['id']. " - ". $res['name'];
echo "<br>";
}
?>

[m4x3vo]

I'm developing a site in which a person can keep track of their "achievements" from a RPG site called vpnwars. 

 

I have a database with all the possible achievements in the game, the database is named ``ach``.

This database contains the id of each achievement, its name, and description.

 

A person visits a page named add.php which allows them to check off each achievement they have.  When they are done checking off the ones they have, they click submit.  Insert.php then retrieves the achievements checked off and inserts the user's id and the first achievement's id that was checked off and being added into user_ach.  It then adds the second acheivement id and the user's id into the next row, and so on until it has added each ach id by that user into the database (one per row) (column names are user_id and ach_id and this table name is user_ach).

 

Then I am having trouble working on a page that displays the achievements checked off by each person.  What I would like it to do is when the user views the page, it will get their user id in their cookie.  It will use that id and go through each possible # id in the user_ach table (this table only stores the ach id and user id).  It basically tries to guess which achievements the user has checked off.  When the id exists, it goes to the achievements database using that id and finds the name of the achievement and displays it on the page showing what achievements youve checked.  This process is continually looped.  However there is one problem im getting that i need help with.  If the id does not exist in user_ach for that user when its bruting for the id, returns a mysql_fetch_array error since it doesnt exist.  I need something that will make the $counter go on if the number doesnt exist for the uid in the cookie.  Here is the source of the second page i explained that shows your achievements youve checked, I need a solution so it moved on if the number is not valid, and continues looping:

 

<?php
$cookie = $_COOKIE["uid"];
for ($counter = 1; $counter <= 139; $counter++)
{
$query = "SELECT * FROM user_ach WHERE user_id = $cookie AND ach_id = $counter";
$result1 = mysql_query($query);
$res = mysql_fetch_assoc($result1);
$ach_id = $res['ach_id'];
$query2 = "SELECT * FROM ach WHERE id = $ach_id";
$result2 = mysql_query($query2);
$res = mysql_fetch_assoc($result2);
echo $res['id']. " - ". $res['name'];
echo "<br>";
}
?>

[kickstart]

Hi

 

Not 100% certain on what the fields id and name are, but a basic fix to your original idea.

 

<?php
$cookie = $_COOKIE["uid"];
for ($counter = 1; $counter <= 139; $counter++)
{
$query = "SELECT * FROM user_ach WHERE user_id = $cookie AND ach_id = $counter";
$result1 = mysql_query($query);
if ($res = mysql_fetch_assoc($result1))
{
	$ach_id = $res['ach_id'];
	$query2 = "SELECT * FROM ach WHERE id = $ach_id";
	$result2 = mysql_query($query2);
	if ($res2 = mysql_fetch_assoc($result2))
	{
		echo $res2['id']. " - ". $res2['name'];
		echo "<br>";
	}
}
}
?>

 

However, no real need to loop round all the possible achievments like that. It would appear that it would be better to do one SELECT joining the 2 tables together and loop through all the resulting records. Something like this:-

 

<?php
$cookie = $_COOKIE["uid"];
$query = "SELECT * FROM user_ach a JOIN ach b ON a.ach_id = b.ach_id WHERE user_id = $cookie ";
$result1 = mysql_query($query);
while ($res = mysql_fetch_assoc($result1))
{
echo $res['id']. " - ". $res['name'];
echo "<br>";
}
?>

 

All the best

 

Keith

[m4x3vo]

I was playing with the second one, im getting this error.

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/krawkne/public_html/vpnach/youhave.php on line 11

[Maq]

What table is 'user_id' from?

 

You will see the error if you temporarily change this line to:

 

$result1 = mysql_query($query) or die(mysql_error());

[m4x3vo]

That line doesnt have die in it.

[Maq]

That line doesnt have die in it.

 

I know that.  I'm telling you to add it in.

[m4x3vo]

Ah fixed it thanks.

[Maq]

Ah fixed it thanks.

 

Good, remember to take out the 'or die' clause, it should not be used on a live server.  It's just a quick way to diagnose your queries.