Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: Seek rows upon array

    I have the PHP script with array of IDs.

    I have to make output to the screen of the table data based on the array IDs i have.

    I mean, mot only those IDs, but also the same order:
    If there is in table:
    ID data
    --------
    1. dog
    2. cat
    3. bird
    4. horse
    5. cow

    And array is:
    0:4
    1:2
    2:5

    there has to be the output:

    horse
    cat
    cow

    filtered and ordered.

    In MySQL there is no something like ORDER BY array.

    Also, no possibility to JOIN array to table (or I do not know for the possibility).

    I made while loop with something like:
    $i=0;
    $imax=mysql_num_rows("SELECT data FROM mytable");
    while ($i<$imax) {
    $toshow=mysql_fetch_row("SELECT data FROM mytable WHERE ID=$id[i]
    LIMIT 1");
    echo "$toshow<br>";
    $i++;
    }

    I am emphasizing that I simplified the code here.
    It works, but is there a way to do it better?
    Is the WHERE fast (at the moment I am testing this with only few rows)? Is there faster fetch possibility for just one row? Is HAVING faster?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on how many items you expect to be in you array you could consider using the In subclause of the where clause, in preference to issuing multiple selects
    eg
    ......where ID in (4,2,5) //that would only return the rows you want

    just thinking aloud.....
    -you could then copy those rows to an array or class, use some form of sort to extract the values
    -I don't think their is a pure SQL way of doing what you want, short of writing a temporary table
    -you could write those values to another array using the index / key from the original selection - that would automatially populate the display array in the order you want
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Thanks for response.

    Seems to be very simple solution, but it does not show records ordered:
    4,
    2,
    5,
    but, because it does check records one by one, they will be ordered:
    2,
    4,
    5.
    Is the WHERE the fastest way to find, to seek record based on the field content, indexed field content like ID? I saw in tutorials HAVING is slower. Is there any faster way?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can't order in SQL using an arbitary order. the where clause will return the rows you requested ie those with an id of 2,4,5

    I'd suggest you that you create a second array in PHP using the value from the MySQL recordset, but placed in the key order from the original array whch identified which itesm to retrieve from the db

    that way round you do one fetch from the db.
    for each result in the MySQL recordset find its matching value in the source array, retrieve the key for that value, write to a new array the key from the source array, with value form the recorsodset

    source array
    <Key/ID><animalType>
    0 : 4
    1 : 2
    2 : 5

    MySQL Resultset
    <ID><data>
    2 : cat
    4 : horse
    5 : cow

    new php array
    <Key><Value>
    0 : horse
    1 : cat
    2 : cow
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by computerforce
    .....
    Is the WHERE the fastest way to find, to seek record based on the field content, indexed field content like ID? I saw in tutorials HAVING is slower. Is there any faster way?....
    from my MySQL ref Koflers book: "MySQL"
    ......REMARK instead of formulating the conditionals with WHERE, you could instead use HAVING. The WHERE conditional are executed first, while HAVING conditionals are used on intermediate results (results returned by the WHERE). The advantage of HAVING is that the conditional can also be applied to calcualted / derived fields.
    HAVING conditionals are less easily optimised for MySQL than WHERE conditionals, and they should be avoided if an equivalenet where is possible.
    if your ID column is indexed then its doubtfull that there would any faster technique for retrieving the data.
    The WHERE IN(x,y....z) would almost certainly be the fastest (and most efficient) means of transferring data from the db to your app., certainly preferable to isuing n select statements to do the same job. However it will require more work in the application. The net effect may be very similar, the final implementation is up to you..
    Last edited by healdem; 01-23-06 at 07:54.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2004
    Posts
    84
    Thanks!

    The table I shall use with the few query rows is not so big. Means - the solution will be well using either WHERE or HAVING.

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by healdem
    you can't order in SQL using an arbitary order.
    That's what I thought too.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    How odd...
    I did try that on my server here and it didn't work.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2007
    Posts
    1
    Hmmm, the solution is related to IN. It's not something that is used every day....

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    IN is used everyday, it is a replacement for a list of OR clauses.

Posting Permissions

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