[RESOLVED] only displaying last bit of data in a table

[imad_imike]

Hi, I've just got a simple sql quesry with a loop, and I'm displaying the data in a table. heres the query

$SQL = "SELECT * FROM AccPeriod WHERE UserID = '$userID' ORDER BY AccID";
$result = mysql_query($SQL) or die(mysql_error());
$num_rows = mysql_num_rows($result);
	$year = 0;
	while ($row = mysql_fetch_array($result))
	{
		$AccID[$year] = $row['AccID'];
		$accOpen[$year]	= $row['AccOpen'];
		$accClose[$year]	= $row['AccClose'];

	$SQL1 ="SELECT * FROM Depreciation Where EntryID = '$id'";
	$result1 = mysql_query($SQL1) or die(mysql_error());
	if ($row1 = mysql_fetch_assoc($result1))

		$DepAccID[$year] = $row1['AccID'];
		$DepRate[$year] = $row1['DepRate']; 

		if ($AccID[$year] == $DepAccID[$year])
		{
		$DepRate[$year]== true;
		}		
		else 
		{
			$DepRate[$year] = 0;
		}


$year ++;
}

And heres the table

<?php 
		for ($year =0; $year < $num_rows; $year ++)
		{
	?>
		<tr style="background-color:#E8F4FF;text-align:center;">
	<td width="33%"><b><?php echo $accOpen[$year]?></td>
	<td width="33%"><b></td>
	<td width="33%"><b><?php echo $DepRate[$year]?></td>
		</tr>
	<?php 
		}
	?>

Theres 1 problem, The loop is working correct, Its showing 4 rows in the table, but the problem is displaying the '$DepRate[$year]', its not showing all the information, just the last entry.

Anyway, I'll attach a picture of the table and explain better.

In the table, it shows 15 dep rate for the year 2011, which is correct, but theres also supposed to be a 10 dep rate for the year 2010, which is not showing, and thats the part which I cant figure out.

Any help is appreciated.

[johanafm]

Well, either you execute one statement without any side effects (that is "nothing happens", except that you waste time not producing any results), or you assign 0 to DepRate for that year.

if ($AccID[$year] == $DepAccID[$year])
{
	# This can be either true or false, so you can comment this out and replace it with
#	$DepRate[$year] == true;
	# either
	true;
	# or
	false;

# And since neither of those statements have any side effects, you could also replace
# them with any other statement, or simply remove the statement completely.
}        

else
{
	# and here you assign the value 0
	$DepRate[$year] = 0;
}

# So the if-else block above can be rewritten as
if ($AccID[$year] != $DepAccID[$year])
{
	$DepRate[$year] = 0;
}

Other than that, I strongly suspect that you should be executing one query, not num_rows + 1 queries.

How does the data in the two tables relate to each other? What is $id? What is the purpose of if ($AccID[$year] == $DepAccID[$year]) ?

[imad_imike]

johanafm;10986974 wrote:

Well, either you execute one statement without any side effects (that is "nothing happens", except that you waste time not producing any results), or you assign 0 to DepRate for that year.

if ($AccID[$year] == $DepAccID[$year])
{
	# This can be either true or false, so you can comment this out and replace it with
#	$DepRate[$year] == true;
	# either
	true;
	# or
	false;

# And since neither of those statements have any side effects, you could also replace
# them with any other statement, or simply remove the statement completely.
}        

else
{
	# and here you assign the value 0
	$DepRate[$year] = 0;
}

# So the if-else block above can be rewritten as
if ($AccID[$year] != $DepAccID[$year])
{
	$DepRate[$year] = 0;
}

Other than that, I strongly suspect that you should be executing one query, not num_rows + 1 queries.

How does the data in the two tables relate to each other? What is $id? What is the purpose of if ($AccID[$year] == $DepAccID[$year]) ?

Id understand if it doesnt make sense, I only started using php mysql a few months back.

Anyway, theres 2 tables,

AccPeriod which holds the information for the years

and

Depreciation which holds the information for depreciation.

$AccID[$year] and $DepAccID[$year] are sort of the primary key.

Ive created the loop so It shows the 4 years in the table, but now im trying to get it to display the information from the depreciation table in the correct row.

so, if the $AccID[$year] from the Acc table is the same as the $DepAccID[$year] from the depreciation table (matching the yearly periods), I want it to show the $DepRate[$year] in that row,

if theres no matching periods, I want it to echo '0'.

Does that make sense??

If not please say, I'm rubbish at explaining things

[imad_imike]

Please, any ideas are appreciated

[johanafm]

You still did not answer what $id is.

$SQL1 ="SELECT * FROM Depreciation Where EntryID = '$id'";

Also whatever the variable is, I'd say it's a good bet EntryID is NOT some kind of string datatype (varchar or text), but very likely INT UNSIGNED, which means you should be passing it an integer, not a numeric string (i.e. drop the single quotes).

$id is not defined anywhere in the code you shown, but looking at the code you've written, I would go on to assume that EntryID really isn't a column set as primary key or unique index, even though conventionally you'd never name a column somethingID unless a uniqueness constraint is enforced on that column.

Also, if it was unique, there'd be no reason to loop through the result set, since it will have 0 or 1 rows

if ($row1 = mysql_fetch_assoc())

And if it was unique, you really should now what row you are talking about anyway, thus making

if ($AccID[$year] == $DepAccID[$year]) 

pointless.

Another thing that you should get into the habit of doing is to specify each and every single column you want to select rather than specifying *, even those times you want to select every column from a table. There are several reasons. If you look in half a year, there is a good chance you won't remember exactly what columns are in the table, and if you don't need all of them you are wasting the server's memory and also complicate things even more for yourself since you'd have to figure out what columns are actually used.

Since the two tables are related by the columns AccPeriod.AccID and Depreciation.AccID, you really should retrieve all this information with one query

SELECT a.AccID, AccOpen, AccClose, DepRate
FROM AccPeriod AS a
LEFT JOIN Depreciation AS d ON a.AccID = d.AccID

In my opinion you should also stick to lower case identifiers, especially if you are on a windows machine, since identifiers in MySQL on Windows are case insensitive. Should you have a case mismatch in your PHP strings and later switch to a non-windows OS, you will get errors.

Anyway, now that you have all data retrieved in one query, which is more efficient, it's also easier to handle it. Just loop over the result - each row contains data which is related, so there will be no further comparisons etc.

The LEFT JOIN means that if there is no related data in Depreciation for a given row in AccPeriod, this row from AccPeriod will still be retrieved and if any columns from the join table (Depreciation) are selected these will be set to null. Thus you may

echo ($row['DepRate'] == null ? 0 : $row['DepRate']);

[imad_imike]

Thanks again john, great that will work. But I think it may cause problems for me in the future. Just for the fact that it gets a hell of a lot more complicated, for example, next I have to work on acumulated depreciation, which will be a lot easier if the the results are in variables.

And also, if I do a join, the depreciation record may not exist for the year in question.

oh and about $id, this page which I'm creating now is just showing sperate assets, like a review screen for seperate assets, so the $id is set earlier on in the script

//Get EntryID from address bar
	$id = isset($_GET['id']) ? (int) $_GET['id'] : 0;

[johanafm]

imad imike;10987024 wrote:

Thanks again john, great that will work. But I think it may cause problems for me in the future.

No, I'd say quite the opposite. It will remain simpler to handle additional complexity in the future, not to mention keeping execution speed within tolerable limits.

imad imike;10987024 wrote:

Just for the fact that it gets a hell of a lot more complicated, for example, next I have to work on acumulated depreciation, which will be a lot easier if the the results are in variables.

Everything contained by the result set resource can be retrieved row by row into a variable, either an array or an object, which means that the separate column values will either be in an array element or an object instance variable. You never have to store each datum in a separate variable, and usually there is no point at all to doing so.
Moreover, accumulated depreciation sounds like another excellent job for SQL and your database. If I understood you correctly concerning EntryID, this refers to one single item, so that the Depreciation table will hold several rows (sometimes referred to as entries, which is why I'd not recommend EntryID for a column without uniqueness constraint. ItemID would be better, but it's a matter of opinion) for the same EntryID, but separate dates, which means that retrieving all the rows for one EntryID gives its entire history of depreciation.

Should this be correct, calculating the accumulated depr. is easy using GROUP BY and the aggregate function SUM()

$query =
"SELECT EntryID, SUM(DepRate) AS accumulated
FROM Depreciation
WHERE EntryID = $id
GROUP BY EntryID";

If you also want to know what time period this concerns

$query =
"SELECT EntryID, SUM(DepRate) AS accumulated, MIN(AccOpen) AS start, MAX(AccClose) as END
FROM Depreciation d
INNER JOIN AccPeriod a ON a.AccID = d.AccID
WHERE EntryID = $id
GROUP BY EntryID";

imad imike;10987024 wrote:

And also, if I do a join, the depreciation record may not exist for the year in question.

Well, in that case you don't query for AccPeriod's and try to find Depreciation values for each such period (i.e. FROM AccPeriod LEFT JOIN Depreciation). You query for Depreciation values and find their respective accounting periods (i.e. FROM Depreciation INNER JOIN AccPeriod), which I take it always must exist.

Besides, you could actually handle going FROM AccPeriod LEFT JOIN Depreciation anyway, using IFNULL(value, value_to_use_when_value_is_null).

SELECT SUM(IFNULL(DepRate, 0))
FROM AccPeriod
LEFT JOIN Depreciation ON ...

[imad_imike]

Thanks again, your a star, and you have brought light to me, by showing that variables are not actually needed.

Your a bit off on accumulated depreciation but you have gave me plenty to work with, huge thanks.

Well your not entirely off, theres just different methods for depreciation.

Straight line, which is basically what you done, just add up each year depreciation.

Then theres declining balance which is the method im using. Heres an example how this works.

Value = 1000
Year 1 depreciation = 20% value = 800
year 2 depreciation = 20% value = 640
year 3 depreciation = 20% value = 512

acumulated depreciation value is still 60%, but the ammount of value which is depreciated is less than that of the straight line method, which would of took the value to 400 after the 3 years in the example above.

Anyway, you have gave me a lot to work with, thanks again

Michael

ps, ill change thread to resolved

[johanafm]

imad imike;10987034 wrote:

Well your not entirely off, theres just different methods for depreciation.

Straight line, which is basically what you done, just add up each year depreciation.

Hehe, I was afraid of that. I had to take some basic courses in accounting during the first year at the uni and suspected it might be as you describe it here.

imad imike;10987034 wrote:

Value = 1000
Year 1 depreciation = 20% value = 800
year 2 depreciation = 20% value = 640
year 3 depreciation = 20% value = 512

acumulated depreciation value is still 60%,

Actually, it isn't still 60%. You are now calculating with a depreciation of 20% from last years value, and since last years value is < 100% except for the first year, you will not have a 60% depreciation from the initial value. You have

0.8 * 0.8 * 0.8 = 0.512

imad imike;10987034 wrote:

Anyway, you have gave me a lot to work with, thanks again

No problem, but there is one more thing which might not be obvious and actually solves this issue as well.

log (a*b* ... *n) = log(a) + log(b) + ... + log(n)

=>

a*b*...*n = exp(log(a) + log(b) + ... + log(n))

Which means that you now have a sum of a column over n rows on the right hand side!

SELECT MIN(AccStart), MAX(AccEnd), EXP(SUM(LOG( (100 - DepRate) / 100 ))) AS total_dep,
(1 -  EXP(SUM(LOG(1-dep_rate)))  ) * 100 AS total_dep_rate
FROM Depreciation d
INNER JOIN AccPeriod a ON a.AccID = d.AccID
WHERE d.entryID = $id

[imad_imike]

Thanks again and again and again,

Just out of curiosity, did you learn all your web designing knowlege at uni??

I'd love to go to get back into education, now I know which road I want to take in life. Would love to do some sort of web designing course.

Regards

Michael

[johanafm]

imad imike;10987058 wrote:

Thanks again and again and again,

My pleasure 🙂

imad imike;10987058 wrote:

Just out of curiosity, did you learn all your web designing knowlege at uni??

No. I dropped out of Comp. Science. after 1-2 years and started working as a bartender. So the only comp. scienc stuff I learnt was about database (SQL, database normalization, locking techniques, indexing), some stuff about data structures, their algorithms and time comlexity, and automata theory. Aside from that a little Java and LISP along with discrete mathematics, calc, algebra.

Several years later, I got an offer to build a web based system for a company partly owned by a friend of mine, and not so partly for just that reason 😉
At that point, I knew nothing about the web: how browse-server communication works, sessions, html, php, javascript...
But I was forced to learn, and I'm damn happy I had the DB stuff to begin with since it gave me a little time to start learning the rest while getting info about what they wanted and designing the DB schema.

imad imike;10987058 wrote:

I'd love to go to get back into education, now I know which road I want to take in life. Would love to do some sort of web designing course.

Well, computer science should enable you to do whatever you want, assuming you follow through instead of tend bars 😉
It may also be overkill depending on what skill level you want to achieve and how deep and wide you want your knowledge to be.

However, as long as you have a project real or imaginary, and follow through on that, you can still learn how to do this on your own. Sure, I had some help from what little knowledge I had gained before, at the uni and at home, but the key point is that if you can learn at the uni, you can learn on your own, and later at work. What you don't get at home is a uni degree. It might be nice to have, but you don't need it, as long as you can show things you've done that match skill expectations, are able to answer questions at interviews.

It's nice to have teachers to ask, but now you have the web instead. Not just for asking forum questions, but there are also tutorials, examples discussions etc to read. And once you get to a certain skill level, you will be able to find work which means collegues to learn from.

Also, I have learnt things by answering questions here. Whenever someone asked a question I couldn't answer, I started reading up. The documentation at php.net is often a good start, but to answer certain questions I've had to read up on other stuff as well. And every now and again I make an error or miss something of importance and someone more experienced comes along and corrects me or add more information.

Not to mention how much I've learnt by reading other people's answers.

Good luck 🙂

[imad_imike]

Hehe, nice, I'm think I'm already heading down the same road as you did.

I've already dropped out of college twice, and then I seen a job advertised a few months back, asking for people with knowlege of php mysql, at that point I had none, but I was reading about it on the net, seen the possibilites of it, and became hooked. Straight away I ordered 2 books to read, one for dummies and one advanced php mysql books.

I emailed about the job, showing a huge interrest but saying I lack knowlege, but I'm learning, and 2 weeks later, I cantact them again after reading these books and now I'm working in a team of 3 creating this website.

I seem to hit bumps in the road everyday, but I enjoy this type of work so much, and I'm learning new things everyday.

Hopefully I can make a lifelong career out of it.

I'll be back soon with more questions more than likely 😃

Regards

Michael