Professional Web Applications Themes

storing result in array - MySQL

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 ??...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: storing result in array

    thecoolone 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.

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

  3. #3

    Default Re: storing result in array


    Jerry Stuckle wrote: 

    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 Guest

  4. #4

    Default Re: storing result in array

    thecoolone wrote: 
    >
    >
    > 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.
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: storing result in array

    Jerry Stuckle wrote: 

    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.
     

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

    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 Guest

  6. #6

    Default Re: storing result in array

    thecoolone wrote: 
    >
    >
    > 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.
    >[/ref]

    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.
     
    >
    >
    > the problem here is that some rows have id's separated by commas.
    > and so doing a count(id) wont work here.
    >[/ref]

    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.
     
    >
    >
    > 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,
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: storing result in array

    Jerry Stuckle wrote: 
    > >
    > >
    > > 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.
    > >[/ref]
    >
    > 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.[/ref]

    Then how do i loop in such a way that it appends the value of
    mysql_fetch_array to $count
     [/ref][/ref]
     
    >
    > 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.[/ref]

    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 Guest

  8. #8

    Default Re: storing result in array

    thecoolone wrote: 
    >>
    >>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.[/ref]
    >
    >
    > Then how do i loop in such a way that it appends the value of
    > mysql_fetch_array to $count
    >[/ref]

    See the examples at
    http://www.php.net/manual/en/function.mysql-fetch-assoc.php and
    http://www.php.net/manual/en/function.mysql-fetch-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.
     [/ref]
    >

    >>
    >>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.[/ref]
    >
    >
    > 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.
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: storing result in array

    thecoolone wrote: 

    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.




    Paul Guest

Similar Threads

  1. Storing server side information into a javasript array
    By patb96 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 26th, 12:48 AM
  2. Problem with storing data in an array
    By Romain in forum PERL Beginners
    Replies: 3
    Last Post: April 7th, 10:08 PM
  3. storing the passthru() output as an array
    By Michael in forum PHP Development
    Replies: 3
    Last Post: October 7th, 02:18 AM
  4. storing an array in a db
    By Alexander Ross in forum PHP Development
    Replies: 3
    Last Post: August 7th, 08:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139