Solved problem using database instead of local arrays

[richard]

$yr=1960;

while ($yr<'1970'){
echo "<table border='1'>";
echo "<tr><th colspan='10'>$yr</th></tr>";

if ($result = mysqli_query($link,"SELECT * from sixty
where peak='1' and year=$yr
ORDER BY sid "))
{

echo
"<tr><th>RANK</th><th>PEAK</th><th>ENTRY</th><th>TITLE</th><th>FLIP</th><th>ARTIST</th><th>LABEL</th><th>AUTHORA</th></tr>\n";
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>";
echo $row['rank'];
echo "</td>";
echo "<td>";
echo $row['peak'];
echo "</td>";
echo "<td>";
echo $row['entry'];
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['flip'];
echo "</td>";
echo "<td>";
echo $row['artist'];
echo "</td>";
echo "<td>";
echo $row['label'];
echo "</td>";
echo "<td>";
echo $row['authorA'];
echo "</td>";
echo "</tr>\n";

}
}

echo "</table>";

$yr++;

}

[Erwin Moller]

I barely dare to suggest a few improvements, BUT....

1) your usage of the string '1970' and the number 1960 is worrisome.
Please use a number for both.
So:
while ($yr<'1970')
should be
while ($yr < 1970)


2) You are performing multiple queries, that could easily be done in 1
query. This is a possible huge performance bottleneck if you need more
years.

You current code:

> if ($result = mysqli_query($link,"SELECT * from sixty
> where peak='1' and year=$yr
> ORDER BY sid "))
> {

will query the database for each year (from 1960 to 1970).
This takes time.

Consider using the query:
"SELECT * from sixty where (
(peak='1')
AND
(year>1960)
AND
(year<1970)
)ORDER BY year, sid "

That way you get the whole collection in at once.

Regards,
Erwin Moller

--
"That which can be asserted without evidence, can be dismissed without
evidence."
-- Christopher Hitchens

[richard]

thanks for the info.
I will check it out.

[Lew Pitcher]

On Wednesday April 1 2015 11:12, in comp.lang.php, "Erwin Moller"

<erwinmol...@xs4all.nl> wrote:

> On 4/1/2015 4:19 AM, richard wrote:
>>
>>
>> $yr=1960;
>>
>> while ($yr<'1970'){
>> echo "<table border='1'>";
>> echo "<tr><th colspan='10'>$yr</th></tr>";
>>
>> if ($result = mysqli_query($link,"SELECT * from sixty
>> where peak='1' and year=$yr
>> ORDER BY sid "))
>> {

[snip]

> Consider using the query:
> "SELECT * from sixty where (
> (peak='1')
> AND
> (year>1960)
> AND
> (year<1970)
> )ORDER BY year, sid "

If I understand richard's design correctly (from prior postings regarding
the mroldies website and php/mysql coding), this suggestion won't give him
much of a boost. You see, the table 'sixty' (IIRC) only contains rows with
a year between 1960 and 1969 inclusive. Your (year>1960) AND (year<1970)
will (in this case) be true for every row in the 'sixty' table.



--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

[richard]

On Wed, 01 Apr 2015 17:12:22 +0200, Erwin Moller wrote:

Code works just fine as given.
But wnen the year changes, a new table is NOT created.
Instead, it just continues on in the same table.


<?php


$result = mysqli_query($link,"SELECT * from sixty where (
(peak='1')
AND (year>1959)
AND (year<1970)
) ORDER BY year, sid ");


$yr=1960;

while ($yr<1970){

echo $yr;
echo "<br>";

echo "<table border='1'>";
echo "<tr><th colspan='10'>$yr</th></tr>";

?>

[richard]

On Wed, 01 Apr 2015 17:12:22 +0200, Erwin Moller wrote:

http://mroldies.net/test/numone2.php

[richard]

On Wed, 01 Apr 2015 17:12:22 +0200, Erwin Moller wrote:

Yet another FF hiccup with the damn caching.
After the cache was cleared, the page shows the results the way I intended.
With new tables for each year.

Thanks for the help.

[richard]

So I made the change to >1959.

[Erwin Moller]

That may be, but he still fetches each year with a seperate request to
the database.

It is like:
1) PHP asks database to fetch all that match 1960
2) Database fetches them and sends back.
3) PHP asks database to fetch all that match 1961
4) Database fetches them and sends back.
5) PHP asks database to fetch all that match 1962
6) Database fetches them and sends back.
etc. etc.

That takes time and in not needed, since Richard could fetch them all in
one go.