How to use next() and prev() on an array

[ApacheTech]

I take it your tblTitles table has fields as such:

title_id (AutoNumber)
artist_id (Lookup)
title (Text)

And is relationally joined to tblArtists so the artist_id matches up as a one-to-many relationship? (I'm assuming at least 3rd Normal Form)

Do you want your titles in alphabetical order, or in the order they appear in the database?

SELECT title, artist_id FROM tblTitles WHERE (artist_id = 1) ORDER BY title

Will return all titles by artist #1 in alphabetical order.

SELECT title_id, title, artist_id FROM tblTitles WHERE (artist_id = 1) ORDER BY title_id

Will return all titles by artist #1 in the order they appear in the database.

[kuau]

Dear ApacheTech: Yes, here is my query...

SELECT * FROM title WHERE artist_id=1 ORDER BY title

It is a very simple array. I do NOT want them ordered by title_id, which is what I have been stuck with for the past year. I am not very conversant with functions so I am trying to follow your code, which appears to be on the right track. Please explain what this means exactly...

function mysql_fetch_all($res) {
   while($row=mysql_fetch_array($res, MYSQL_BOTH)) {
       $return[] = $row;
   }
   return $return;
}

$art = mysql_fetch_all($res);

Thanks

[ApacheTech]

This example may explain it better. It's a much better way of achieving the same basic result.

While the mysql_fetch_all function creates a one dimensional array of each row as an object, this one deconstructs the whole (query) table into a multi-dimensional array.

I'm getting most of this from posts on the php.net site. I'm a .NET coder by trade, so little of this is second nature to me.

<?php

$query="select * from table_xyz";
$result = mysql_query($query) or die(mysql_error());
$arr_table_result=mysql_fetch_full_result_array($result);

function mysql_fetch_full_result_array($result)
{
    $table_result=array();
    $r=0;
    while($row = mysql_fetch_assoc($result)){
        $arr_row=array();
        $c=0;
        while ($c < mysql_num_fields($result)) {       
            $col = mysql_fetch_field($result, $c);   
            $arr_row[$col -> name] = $row[$col -> name];           
            $c++;
        }   
        $table_result[$r] = $arr_row;
        $r++;
    }   
    return $table_result;
}

echo $arr_table_result[2]['id'];

?>

With your problem of ordering by the correct type, that's an issue with your SQL SELECT statement. The only thing I can think with that, is that you may be encountering a problem with your naming.

SELECT * FROM title WHERE artist_id=1 ORDER BY title

This looks like you're trying to order the data by the table name rather than a field name.

Can you give us a field list of the tables in the database, with the relations between the tables?

[ApacheTech]

I would set the database up with the following tables:

tblArtists
artist_id (AutoNumber)
artist_name (Text)

tblTitles
title_id (Autonumber)
artist_id (Lookup = tblArtists.artist_id)
title (Text)
filename (Text)

With that set up, you can use:

SELECT T.artist_id, T.title, T.filename, A.artist_name 
FROM tblArtists AS A, tblTitles AS T 
WHERE (T.artist_id = "1") 
ORDER BY T.title

To select all the information you need.

Next, use mysql_fetch_full_result_array($result) on the query to construct a multi-dimensional array of all the selected artist's work.

[traq]

@kuau --
can you show us a sample of what your result set looks like? I think that would help clarify things.

About defining an array with only the rows you want, you certainly can do so. That's what Apache's first php block illustrates. As I mentioned, you can keep that array (instead of repeating the query on every request) by saving it to a session.

@ApacheTech --

right. so, say the pointer is currently at 0 (its starting position).
1. current( $art ) != reset( $art ) is FALSE.
2. skip to else{}, $prev = end( $art ). $prev will hold the last element of the array; that's what you wanted.

however, say the pointer is currently at 2 (maybe we used next() a few times).
1. current( $art ) != reset( $art ) is TRUE.
2. $prev = prev( $art ).
...*however*, reset() reset the pointer. when prev() is called, the pointer is at 0 again.
...$prev will hold FALSE, because you can't rewind the pointer past the beginning of the array.

to illustrate, try out these two variations:

$art = array( 1,2,3,4,5);
if(current($art) != reset($art)) {
  $prev = prev($art);
}else{
  $prev = end($art);
}
var_dump( $prev );
// int(5) 

$art = array( 1,2,3,4,5);
next( $art ); // advance the pointer!
if(current($art) != reset($art)) {
  $prev = prev($art);
}else{
  $prev = end($art);
}
var_dump( $prev );
// bool(false) 

Edit: boy, this thread is moving fast!

[ApacheTech]

Now you have your multi-dimensional array, you can traverse through it using next and prev.

<?php

$art = mysql_fetch_full_result_array($result);

$curr = current($art);

?>

<p><?php echo $curr['title']; ?> By <?php echo $curr['artist_name']; ?></p>
<img src="~/assets/images/<?php echo $curr['artist_name']; ?>/<?php echo $curr['filename']; ?>.jpg" title="<?php echo $curr['title']; ?>" alt="<?php echo $curr['title']; ?>" />

[ApacheTech]

Traq, thanks for the clear up.

I was mainly using end and reet as a shortcut for looping back around the array to make it seamless and solve any stack-overflows / index falloffs. There are obviously other ways to do this.

How about:

$art = array( 1,2,3,4,5);
if($key(current($art)) != 0) {
  $prev = prev($art);
}else{
  $prev = end($art);
}
var_dump( $prev );
// int(5)  

if($key(current($art)) != (count($art) - 1)) {
  $next= next($art);
}else{
  $next= reset($art);
}
var_dump( $next);
// int(1) 

[traq]

yup. Might do $last = count($art)-1 and save time in case you ever need to use it elsewhere.

[kuau]

Dear Traq & ApacheTech: Just so you know I tried to thank you guys but there is a bug in the forum software due to an upgrade. And then I was taken away for awhile. Now I am back. To answer some of your questions...

1. In the `title` table `title` is the name of a field as well. An example of a title would be 'A Quiet Moment.'
2. Your table structures are correctly like mine.
3. $art['filetag'] is a string, eg. a-quiet-moment (it is the filename of the image without the .jpg)
4. The way you have described prev(), next(), current(), end(), reset() is exactly how I want it to work advancing the array pointer each time.

In your code...

<a href="/php/art-detail.php?title_id=<?php
if(current($art) != reset($art)) {
  $prev = prev($art);
else
  $prev = end($art);
}
echo $prev['title'];
?>">Previous</a>

<!-- code to display current($art) image here -->

<a href="/php/art-detail.php?title_id=<?php
if(current($art) != end($art)) {
  $next= next($art);
else
  $next= reset($art);
}
echo $next['title'];
?>">Next</a>

I'm not sure what values $art has. It would have to contain a title_id (eg. 102) for the page to change, as that is how I have defined the query string, but the next() would find the next title in alphabetical order for that artist and then use the title_id that goes with that title. Not sure how to keep that straight which is why I tried using the file_tag with next and prev. Maybe it will help to see one of the actual pages... a picture is worth a thousand words and all that...

http://www.costgallery.com/php/art-d...php?title_id=2

[ApacheTech]

$art is a multidimensional array of all the fields from your query.

I don't know if it would make any difference, but you could prefix your table with tbl to distinguish the table names from the field names. I've always used a watered down version of the Hungarian Naming Method when coding to distinguish between types. so you title table would become tblTitles, and so on.