storing result in array

I am trying to store the result of an mysql query output in an array..
for testing purpose i wrote the following code:

$conn = mysql_connect("localhost","name","pwd");
mysql_select_db("media",$conn);
$sql = "select images from site";
//mysql_query($sql, $conn);
$result = mysql_query($sql, $conn);
$number_of_rows = mysql_num_rows($result);
$count = mysql_fetch_array($result,MYSQL_ASSOC);
print ("<br>");
echo "<b>Number of contacts = $number_of_rows<br></b>";
print_r($count);

but when i execute this query i get the following output:
Number of contacts = 6582
Array ( [images] => ----,3642 )

My question is why is the number of rows of indexed array more than
associative array ??

thecoolone wrote:
> I am trying to store the result of an mysql query output in an array..
> for testing purpose i wrote the following code:
>
> $conn = mysql_connect("localhost","name","pwd");
> mysql_select_db("media",$conn);
> $sql = "select images from site";
> //mysql_query($sql, $conn);
> $result = mysql_query($sql, $conn);
> $number_of_rows = mysql_num_rows($result);
> $count = mysql_fetch_array($result,MYSQL_ASSOC);
> print ("<br>");
> echo "<b>Number of contacts = $number_of_rows<br></b>";
> print_r($count);
>
> but when i execute this query i get the following output:
> Number of contacts = 6582
> Array ( [images] => ----,3642 )
>
> My question is why is the number of rows of indexed array more than
> associative array ??
>

Your code doesn't make any sense.

mysql_fetch_array() returns one row from the result set. From your
SELECT statement this would be one element of the column "image".

I don't see anywhere you are getting the entire result set into your
array, so there's no way to count the number of elements in it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Your code doesn't make any sense.
>
> mysql_fetch_array() returns one row from the result set. From your
> SELECT statement this would be one element of the column "image".
>
> I don't see anywhere you are getting the entire result set into your
> array, so there's no way to count the number of elements in it.
>

ok i will explain what i am trying to do here.
I have a table that contains a list of files(images) downloaded which
is stored as ids.
And another table that stores the name of the file.
Therefore i am first trying to store all the id's in an associative
array (say $count), and if the id is present in the first query(i.e.
from table that contain all ids) then increment the count of that id in
the array $count.

how would you go about doing this??
the reason i had used mysql_fetch_array is so that i can get an
associative array.

thecoolone wrote:
> Jerry Stuckle wrote:
>
>>Your code doesn't make any sense.
>>
>>mysql_fetch_array() returns one row from the result set. From your
>>SELECT statement this would be one element of the column "image".
>>
>>I don't see anywhere you are getting the entire result set into your
>>array, so there's no way to count the number of elements in it.
>>
>
>
> ok i will explain what i am trying to do here.
> I have a table that contains a list of files(images) downloaded which
> is stored as ids.
> And another table that stores the name of the file.
> Therefore i am first trying to store all the id's in an associative
> array (say $count), and if the id is present in the first query(i.e.
> from table that contain all ids) then increment the count of that id in
> the array $count.
>
> how would you go about doing this??
> the reason i had used mysql_fetch_array is so that i can get an
> associative array.
>

No problem using mysql_fetch_array - but that fetches ONE ROW into an
array. If you want to get all of the rows, you need to continue issuing
mysql_fetch_array calls until it returns false.

But if all you want is a count of id's in an array, let MySQL do it for
you. Check out the COUNT SQL function, GROUP BY clause and JOIN clauses.

I don't know your table structure or data, but something like:

SELECT id, COUNT(id) FROM table1
JOIN table2 ON table1.id = table2.id
GROUP BY id

Or something similar.

But without knowing exactly your table layout, a little sample data and
the results you want, it's hard to tell exactly what you need.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> No problem using mysql_fetch_array - but that fetches ONE ROW into an
> array. If you want to get all of the rows, you need to continue issuing
> mysql_fetch_array calls until it returns false.

i tried that as follows:
while ($count = mysql_fetch_array($result,MYSQL_ASSOC))
{
print_r($count);
print "<br>"; }

but i want to be able to store the all results of mysql_fetch_array in
one single array.
i thought of using a for loop like:
$number_of_rows = mysql_num_rows($result);
for ($i=0;$i<$number_of_rows;$i++)
{
print_r($count[$i]);
print "<br>";
global $num;
$num=array();
$num=$count;
}

but it isint working the way i expected.

> But if all you want is a count of id's in an array, let MySQL do it for
> you. Check out the COUNT SQL function, GROUP BY clause and JOIN clauses.
>
> I don't know your table structure or data, but something like:
>
> SELECT id, COUNT(id) FROM table1
> JOIN table2 ON table1.id = table2.id
> GROUP BY id

the problem here is that some rows have id's separated by commas.
and so doing a count(id) wont work here.

> Or something similar.
>
> But without knowing exactly your table layout, a little sample data and
> the results you want, it's hard to tell exactly what you need.

i tried sending u a mail with details of what i need but it failed to
deliver.
if u cud email me then i can send the details to you.

many thanks,

thecoolone wrote:
> Jerry Stuckle wrote:
>
>>No problem using mysql_fetch_array - but that fetches ONE ROW into an
>>array. If you want to get all of the rows, you need to continue issuing
>>mysql_fetch_array calls until it returns false.
>
>
> i tried that as follows:
> while ($count = mysql_fetch_array($result,MYSQL_ASSOC))
> {
> print_r($count);
> print "<br>"; }
>
> but i want to be able to store the all results of mysql_fetch_array in
> one single array.
> i thought of using a for loop like:
> $number_of_rows = mysql_num_rows($result);
> for ($i=0;$i<$number_of_rows;$i++)
> {
> print_r($count[$i]);
> print "<br>";
> global $num;
> $num=array();
> $num=$count;
> }
>
> but it isint working the way i expected.
>

No, because you never put anything in $count, so printing it won't show
anything. You need to fetch the data from the mysql result before you
can us it.

Also every time through your loop you're reinitializing $num.

>
>>But if all you want is a count of id's in an array, let MySQL do it for
>>you. Check out the COUNT SQL function, GROUP BY clause and JOIN clauses.
>>
>>I don't know your table structure or data, but something like:
>>
>> SELECT id, COUNT(id) FROM table1
>> JOIN table2 ON table1.id = table2.id
>> GROUP BY id
>
>
> the problem here is that some rows have id's separated by commas.
> and so doing a count(id) wont work here.
>

That's a broken database design then. Do a search on "database
normalization". You should probably have a third table which links the
two current tables together.

>
>>Or something similar.
>>
>>But without knowing exactly your table layout, a little sample data and
>>the results you want, it's hard to tell exactly what you need.
>
>
> i tried sending u a mail with details of what i need but it failed to
> deliver.
> if u cud email me then i can send the details to you.
>
> many thanks,
>

Sorry, but I also don't discuss these things in email. It's better to
keep it in the newsgroup where others can make comments, also.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> thecoolone wrote:
> > Jerry Stuckle wrote:
> >
> >>No problem using mysql_fetch_array - but that fetches ONE ROW into an
> >>array. If you want to get all of the rows, you need to continue issuing
> >>mysql_fetch_array calls until it returns false.
> >
> >
> > i tried that as follows:
> > while ($count = mysql_fetch_array($result,MYSQL_ASSOC))
> > {
> > print_r($count);
> > print "<br>"; }
> >
> > but i want to be able to store the all results of mysql_fetch_array in
> > one single array.
> > i thought of using a for loop like:
> > $number_of_rows = mysql_num_rows($result);
> > for ($i=0;$i<$number_of_rows;$i++)
> > {
> > print_r($count[$i]);
> > print "<br>";
> > global $num;
> > $num=array();
> > $num=$count;
> > }
> >
> > but it isint working the way i expected.
> >
>
> No, because you never put anything in $count, so printing it won't show
> anything. You need to fetch the data from the mysql result before you
> can us it.
>
> Also every time through your loop you're reinitializing $num.

Then how do i loop in such a way that it appends the value of
mysql_fetch_array to $count

> >>But if all you want is a count of id's in an array, let MySQL do it for
> >>you. Check out the COUNT SQL function, GROUP BY clause and JOIN clauses.
> >>
> >>I don't know your table structure or data, but something like:
> >>
> >> SELECT id, COUNT(id) FROM table1
> >> JOIN table2 ON table1.id = table2.id
> >> GROUP BY id

> > the problem here is that some rows have id's separated by commas.
> > and so doing a count(id) wont work here.
> >
>
> That's a broken database design then. Do a search on "database
> normalization". You should probably have a third table which links the
> two current tables together.

its not a broken db design. There is a reason for that, which i had
wrote in a mail i had sent you.
the reason being each id's indicate the files that the user has choose
to download.
i want to be able to scan through each row of $count array and if the
id is present in that row
then increment the count for that id a $counter array (which is
associative). If my thinking is correct
i will have to do an in_array comparision for each id in the first
table against the $count array and then
increment $counter right??

thank you.

thecoolone wrote:
> Jerry Stuckle wrote:
>
>>thecoolone wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>No problem using mysql_fetch_array - but that fetches ONE ROW into an
>>>>array. If you want to get all of the rows, you need to continue issuing
>>>>mysql_fetch_array calls until it returns false.
>>>
>>>
>>>i tried that as follows:
>>>while ($count = mysql_fetch_array($result,MYSQL_ASSOC))
>>>{
>>>print_r($count);
>>>print "<br>"; }
>>>
>>>but i want to be able to store the all results of mysql_fetch_array in
>>>one single array.
>>>i thought of using a for loop like:
>>>$number_of_rows = mysql_num_rows($result);
>>>for ($i=0;$i<$number_of_rows;$i++)
>>>{
>>>print_r($count[$i]);
>>>print "<br>";
>>>global $num;
>>>$num=array();
>>>$num=$count;
>>>}
>>>
>>>but it isint working the way i expected.
>>>
>>
>>No, because you never put anything in $count, so printing it won't show
>>anything. You need to fetch the data from the mysql result before you
>>can us it.
>>
>>Also every time through your loop you're reinitializing $num.
>
>
> Then how do i loop in such a way that it appends the value of
> mysql_fetch_array to $count
>

See the examples at
http://www.php.net/manual/en/functio...etch-assoc.php and
http://www.php.net/manual/en/functio...etch-array.php. They have
some good examples.

Your first try has the right idea - but you overwrite $count each time
when you fetch the new row. Additionally, the array in $count only
represents a single row. If you want multiple elements, you need an
array of arrays, i.e.

$count = array();
while ($temp = mysql_fetch_array($result), MYSQL_ASSSOC) {
$count [] = $temp;
....

This creates the $count array then appends each row read into it.

>
>>>>But if all you want is a count of id's in an array, let MySQL do it for
>>>>you. Check out the COUNT SQL function, GROUP BY clause and JOIN clauses.
>>>>
>>>>I don't know your table structure or data, but something like:
>>>>
>>>> SELECT id, COUNT(id) FROM table1
>>>> JOIN table2 ON table1.id = table2.id
>>>> GROUP BY id
>
>
>>>the problem here is that some rows have id's separated by commas.
>>>and so doing a count(id) wont work here.
>>>
>>
>>That's a broken database design then. Do a search on "database
>>normalization". You should probably have a third table which links the
>>two current tables together.
>
>
> its not a broken db design. There is a reason for that, which i had
> wrote in a mail i had sent you.
> the reason being each id's indicate the files that the user has choose
> to download.
> i want to be able to scan through each row of $count array and if the
> id is present in that row
> then increment the count for that id a $counter array (which is
> associative). If my thinking is correct
> i will have to do an in_array comparision for each id in the first
> table against the $count array and then
> increment $counter right??
>
> thank you.
>

To start, it is a violation of first normal form (having more than one
value in a column). I have *never* found a reason this is "better" than
the correct way of doing things - with a link table. And I've been
doing RDB design for over 20 years, starting with DB2 on mainframes.
And yes, when I say never, I do mean *never*.

Read up on database normalization. You will find a correct database
design will make this job a lot easier (plus give you more options). In
fact, you'll be able to get your count entirely in SQL without the need
for PHP or any other language.

Also, as I said before - IMHO newsgroup threads should remain in the
newsgroup. You can explain why you have such a design here. I tend to
delete email related to newsgroup messages.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

thecoolone wrote:
> its not a broken db design. There is a reason for that, which i had
> wrote in a mail i had sent you.
> the reason being each id's indicate the files that the user has choose
> to download.
> i want to be able to scan through each row of $count array and if the
> id is present in that row
> then increment the count for that id a $counter array (which is
> associative). If my thinking is correct
> i will have to do an in_array comparision for each id in the first
> table against the $count array and then
> increment $counter right??
>
> thank you.

It IS a broken DB design. The reason you gave is not a reason for having the
design that you have. The reason why you have the broken design is because
you do not know how to design a database. Read up on normalisation.

You don't even need any programming to get the answer that you want. With a
properly designed database a single SQL query will give the required result.