fetching all items at once - how do you code to display them?

[richard]

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

Considering this code provided by Erwin Moller, how does one now display
the information?

The standard method of using $row=$result() winds up with only the first
item ending up in a displayed table while the other items are just bunched
up together one after the other.

If I simply fetch one item at a time, the table is displayed as wanted.

By this usage of the word "table" I am not speaking of the database table
stored on the server. But the html displayed table on the page.

[Jerry Stuckle]

Like you do any other time your search returns multiple results. You
fetch a row and display it. Repeat as necessary.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[Denis McMahon]

Note that the following is an example that assumes the reader has some
competencies and understanding of php, and the use of the ellipsis

<?php

// Start the table:

echo "<table>\n";

// print the table header row

echo "<tr> <th>header</th>... </tr>\n";

// use a loop that fetches every row from the mysqli result
// this example assumes the mysqli result is in $result
// $data is a local variable in the loop

while ($data = mysqli_fetch_assoc($result)) {

// in here you write out a single table row for each set of
// $data from the query result

echo "<tr> <td>{$data['fieldname']}</td>... </tr>\n";

}

// close the table

echo "</table>";

?>

--
Denis McMahon, denismf...@gmail.com

[richard]

Done that.
What I get is 11 duplicates of the first item.
So why does it stop at 11 when I have 200 records?

[Doug Miller]

richard <nor...@example.com> wrote in news:1lpvv6118algy.1pzxv594x426f$.dlg@
40tude.net:

[...]

>
> Done that.
> What I get is 11 duplicates of the first item.
> So why does it stop at 11 when I have 200 records?
>

Kind of hard to answer that question when we can't see your code....

[Denis McMahon]

Perhaps your query is not fetching the data you think it is. Without
running your query against your datatable at the mysql command line, it's
hard to tell.

I suspect but I can't be sure that the reason you're getting 11 values
might be linked to the "(year>1959) AND (year<1970)" part of the query.

There might be an issue if you have stored years as strings and are now
trying to compare those strings with a numeric value. I'm not sure,
because I don't usually try to compare strings with numbers in mysql.

--
Denis McMahon, denismf...@gmail.com

[richard]

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

$total=count($rows);
echo $total; // displays "11"
echo "<br>";
echo $rows['title']; //displays nothing.

[richard]

I tried using the "between" part and that resulted in displaying "0".

[Richard Yates]

On Sun, 5 Apr 2015 10:56:15 -0400, richard <nor...@example.com>
wrote:

In your code, $total does not give you the number of rows, it gives
you the size of the $row array (which is the number of columns in each
record).

You get the number of rows with $total=mysqli_num_rows($result);

[Lew Pitcher]

On Sunday April 5 2015 10:56, in comp.lang.php, "richard"

<nor...@example.com> wrote:

> On Sun, 5 Apr 2015 01:15:55 +0000 (UTC), Doug Miller wrote:
>
>> richard <nor...@example.com> wrote in
>> news:1lpvv6118algy.1pzxv594x426f$.dlg@ 40tude.net:
>>
>> [...]
>>>
>>> Done that.
>>> What I get is 11 duplicates of the first item.
>>> So why does it stop at 11 when I have 200 records?

Because you are doing it wrong. See the comments below.

>> Kind of hard to answer that question when we can't see your code....
>
> $result = mysqli_query($link,"SELECT * from sixty where (
> (peak='1')
> and (year>1959)
> and (year<1970)
> ) ORDER BY year, sid ");
> $rows = mysqli_fetch_assoc($result);

mysqli_fetch_assoc() populates an associative array with *a SINGLE row* of
the resultset.

> $total=count($rows);
> echo $total; // displays "11"

You have eleven columns in the fetched row.

> echo "<br>";
> echo $rows['title']; //displays nothing.

Do you have a table column named 'title'?


To get the entire table, you have to do something like

$result = mysqli_query($link,"SELECT * FROM sixty
WHERE ((peak='1') AND (year>1959) AND (year<1970))

ORDER BY year, sid ");

$total = mysqli_num_rows($result);
echo $total;

while ($rows = mysqli_fetch_assoc($result))
{
echo "<br/>"
echo $rows['title'];
}


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

[Jerry Stuckle]

Which goes back to Denis's original comment - how is the 'year" column
defined in your database?

[richard]

On Sun, 05 Apr 2015 08:14:54 -0700, Richard Yates wrote:

> $total=mysqli_num_rows($result);

thanks.
that now shows the correct total of records.

[richard]

Thank you sir. That worked like a charm.
<snicker> you forgot the ; after the br tag.

[richard]

I tried with and without quotes. Same result. Nothing.

[Lew Pitcher]

On Sunday April 5 2015 11:28, in comp.lang.php, "richard"
<nor...@example.com> wrote:

[snip]

> Thank you sir. That worked like a charm.

You are welcome.

> <snicker> you forgot the ; after the br tag.

Can you say "bite the hand that feeds you"?
Apparently, you aren't all that gracious when someone helps you.

[Jerry Stuckle]

Which doesn't answer the question at all. But you most probably don't
even understand the question.

[Doug Miller]

Lew Pitcher <lew.p...@digitalfreehold.ca> wrote in news:vTcUw.153157$Ek3.39252
@fx13.iad:

> On Sunday April 5 2015 11:28, in comp.lang.php, "richard"
> <nor...@example.com> wrote:
>
> [snip]
>
>> Thank you sir. That worked like a charm.
>
> You are welcome.
>
>> <snicker> you forgot the ; after the br tag.
>
> Can you say "bite the hand that feeds you"?
> Apparently, you aren't all that gracious when someone helps you.

You're *just now* noticing that, Lew?

[Doug Miller]

richard <nor...@example.com> wrote in news:1xbslhz79f19i.1tpyrouzu9o3f$.dlg@
40tude.net:

> On Sun, 5 Apr 2015 04:47:05 +0000 (UTC), Denis McMahon wrote:
>> I suspect but I can't be sure that the reason you're getting 11 values
>> might be linked to the "(year>1959) AND (year<1970)" part of the query.

[...]

>
> I tried using the "between" part and that resulted in displaying "0".
>

If so, then you did something wrong.

WHERE (year > 1959) AND (year < 1970)

and

WHERE (year BETWEEN 1960 AND 1969)

are equivalent -- assuming year has a numeric datatype.

[Doug Miller]

richard <nor...@example.com> wrote in news:13xqlknyrvdkn$.kascoyjwfeg8.dlg@
40tude.net:

> On Sun, 05 Apr 2015 11:21:04 -0400, Jerry Stuckle wrote:

[...]

>> Which goes back to Denis's original comment - how is the 'year" column
>> defined in your database?
>
> I tried with and without quotes. Same result. Nothing.
>

And you still haven't answered the question. How is the 'year' column defined in your
database? Numeric or string?

[Denis McMahon]

On Sun, 05 Apr 2015 10:56:15 -0400, richard wrote:

> On Sun, 5 Apr 2015 01:15:55 +0000 (UTC), Doug Miller wrote:
>
>> richard <nor...@example.com> wrote in
>> news:1lpvv6118algy.1pzxv594x426f$.dlg@
>> 40tude.net:
>>
>> [...]
>>>
>>> Done that.
>>> What I get is 11 duplicates of the first item.
>>> So why does it stop at 11 when I have 200 records?
>>>
>> Kind of hard to answer that question when we can't see your code....
>
> $result = mysqli_query($link,"SELECT * from sixty where (
> (peak='1')
> and (year>1959)
> and (year<1970)
> ) ORDER BY year, sid ");

> $rows = mysqli_fetch_assoc($result);

This thing that you're calling $rows is only getting a single record from
$result.

> $total=count($rows);
> echo $total; // displays "11"

That's the number of fields in the record you just pulled out of $result,
not the number of records retrieved.

All the other records are still in $result. This is why you loop through
$result as I posted earlier.

> echo "<br>";
> echo $rows['title']; //displays nothing.

That means you don't have a field called 'title' in your record.

try replacing:

> $total=count($rows);
> echo $total; // displays "11"
> echo "<br>";
> echo $rows['title']; //displays nothing.

with:

echo '<pre>\n';
echo print_r($rows, true);
echo '</pre>\n';

This will print out the array that corresponds to the record you got from
$result.

p.s. we're still waiting to hear what the data type of the years field is.

--
Denis McMahon, denismf...@gmail.com

[Denis McMahon]

On Sun, 05 Apr 2015 11:18:14 -0400, Lew Pitcher wrote:

> while ($rows = mysqli_fetch_assoc($result))
> {
> echo "<br/>"
> echo $rows['title'];
> }

See my post of 12:28 AM, I thought I'd given richard a nicely explained
example of using this loop to generate an HTML table from the results of
the database query, but he obviously didn't read it.

--
Denis McMahon, denismf...@gmail.com

[Lew Pitcher]

On Sunday April 5 2015 22:01, in comp.lang.php, "Doug Miller"

I know that "richard" is a d*ck.
I just didn't know how big a d*ck he was.

[richard]

On Wed, 08 Apr 2015 20:16:23 -0400, Lew Pitcher wrote:

> On Sunday April 5 2015 22:01, in comp.lang.php, "Doug Miller"
> <doug_at_mil...@example.com> wrote:
>
>> Lew Pitcher <lew.p...@digitalfreehold.ca> wrote in
>> news:vTcUw.153157$Ek3.39252 @fx13.iad:
>>
>>> On Sunday April 5 2015 11:28, in comp.lang.php, "richard"
>>> <nor...@example.com> wrote:
>>>
>>> [snip]
>>>
>>>> Thank you sir. That worked like a charm.
>>>
>>> You are welcome.
>>>
>>>> <snicker> you forgot the ; after the br tag.
>>>
>>> Can you say "bite the hand that feeds you"?
>>> Apparently, you aren't all that gracious when someone helps you.
>>
>> You're *just now* noticing that, Lew?
>
> I know that "richard" is a d*ck.
> I just didn't know how big a d*ck he was.

And you apparently have no sense of humer.
When I post code, somebody always jumps on my case for making a minor typo.
When I find they made a mistake, I get bashed for pointing it out.
Look up the definition of the word snicker.

[Richard Heathfield]

On 10/04/15 03:32, richard wrote:

<snip>

> Look up the definition of the word snicker.

Doesn't it mean Marathon? At least, for those of us old enough to
remember shillings and ounces and the like.

--
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[Doug Miller]

richard <nor...@example.com> wrote in news:1qry4qqazpwa4$.1r42kff51v2cc.dlg@
40tude.net:

> On Wed, 08 Apr 2015 20:16:23 -0400, Lew Pitcher wrote:
>
>> On Sunday April 5 2015 22:01, in comp.lang.php, "Doug Miller"
>> <doug_at_mil...@example.com> wrote:
>>
>>> Lew Pitcher <lew.p...@digitalfreehold.ca> wrote in
>>> news:vTcUw.153157$Ek3.39252 @fx13.iad:
>>>
>>>> On Sunday April 5 2015 11:28, in comp.lang.php, "richard"
>>>> <nor...@example.com> wrote:
>>>>
>>>> [snip]
>>>>
>>>>> Thank you sir. That worked like a charm.
>>>>
>>>> You are welcome.
>>>>
>>>>> <snicker> you forgot the ; after the br tag.
>>>>
>>>> Can you say "bite the hand that feeds you"?
>>>> Apparently, you aren't all that gracious when someone helps you.
>>>
>>> You're *just now* noticing that, Lew?
>>
>> I know that "richard" is a d*ck.
>> I just didn't know how big a d*ck he was.
>
> And you apparently have no sense of humer.
> When I post code, somebody always jumps on my case for making a minor typo.

No, Richard The Stupid, when you post code somebody always jumps on your case for
your longstanding, persistent inability to learn the difference between = and ==. That's *not*
a typo, that's just Stupid.