1

This code is used to get a specific list of ID's from one table, then use those ID's to get the information from another table. Once I get all the information from the 2nd table, I am attempting to sort the data alphabetically based on a field in the 2nd table.

Example, I am getting the name based on a correlating ID and then want to display the entire result in alphabetical order by name (artist_name).

Here is the code I have. When I execute this without the sort(), it works fine but is not in alphabetical order. When I add the sort() in the 2nd while statement, the page looks the same but the name and other data do not display. The source code in the browser shows that the results are being accounted for but the sort must be preventing the variables or information from being displayed for some reason.

I haven't used a sort function before and I tried looking at some examples but couldn't really find something specific to my situation. Any and all help would be greatly appreciated. I have already looked at the PHP manual for sort so no need to send me a link to it ;-)

<?php $counter = 0;
    $artistInfo = mysql_query("SELECT DISTINCT event_url_tbl.artist_id FROM event_url_tbl WHERE (SELECT cat_id FROM artist_tbl WHERE artist_tbl.artist_id = event_url_tbl.artist_id) = 1");
    while ($aID = mysql_fetch_array($artistInfo))
        {
            $getArtistInfo = mysql_query("SELECT * FROM artist_tbl WHERE artist_id = '" . $aID['artist_id'] . "'");
            while($artist = mysql_fetch_array($getArtistInfo))
                { 
                    sort($artist);?>
                    <a class="navlink" href="<?=HOST?><?=$artist['page_slug']?>/index.html">
                        <?=$artist['artist_name']?>
                    </a><br />
                <?php }
        }
?>
Improve this question
6
  • 3
    Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. Commented Oct 25, 2012 at 14:14
  • 7
    Why not add an ORDER BY clause directly in your SQL? Commented Oct 25, 2012 at 14:14
  • $artist is a multi-dimensional array. You can't use the sort function directly on it. Commented Oct 25, 2012 at 14:18
  • You really shouldn't post-process data like that with PHP. It adds more overhead/work for the developer. SQL is designed to handle such requests at greater efficiency. @andrewsi has the correct idea. You can add ORDER BY artist_name on to the end of your query. Commented Oct 25, 2012 at 14:25
  • when you looked at the PHP manual for sort() did you also notice all the other sorting functions that PHP provides? Commented Oct 25, 2012 at 14:25

2 Answers 2

Reset to default
1

Your best bet, as a commenter mentioned, is to use an ORDER BY clause in SQL.

SELECT * 
FROM artist_tbl 
WHERE artist_id = XXX 
ORDER BY artist_name ASC

The other commenter who suggested using PDO or mysqli is also correct, but that's a different issue.

To answer your specific question about sorting, according to the manual,

Blockquote Note: This function assigns new keys to the elements in array. It will remove any existing keys that may have been assigned, rather than just reordering the keys.

This means all of your array keys ('page_slug', 'artist_name', etc) are wiped out. So when you try to refer to them later, there is no data there.

Were you to use this method, you would want to use asort to sort an associative array.

However, you don't want to use sort here. What you're sorting is the variables for one row of data (one individual artists), not all of your artists. So if you think of each artist row as an index card full of data (name, id#, page slug, etc) all you're doing is moving those items around on the card. You're not reorganizing your card catalog.

Using an order by clause in the SQL statement (and rewriting in PDO) is your best bet.

Here is how I would rewrite it. I have to take some guesses at the SQL because I'm not 100% sure of your database structure and what you're specifically trying to accomplish, but I think this would work.

$query_str = "
    SELECT 
        artist.name,
        artist.page_slug
    FROM 
        artist_tbl AS artist
        INNER JOIN event_tbl AS event
            ON event.artist_id = artist.artist_id
    WHERE
        artist.cat_id = 1
    ORDER BY
        artist.name ASC";
$db_obj = new PDO (/*Connection stuff*/);
$artists_sql = $db_obj->prepare ($query_str);
$artists_sql->execute ();
while ($artist = $artists_sql->fetch (PDO::FETCH_ASSOC)) {
    $return_str .= "<a class='navlink' href='{HOST}
                {$artist['page_slug']}/index.html'>
                {$artist['artist_name']}</a><br/>";
    }
echo $return_str;

In all honesty, I would probably create an artist class with a display_link method and use PDO's fetchObject method to instantiate the artists, but that's getting ahead of ourselves here.

For now I stuck with procedural code. I don't usually like to mix my HTML and PHP so I assign everything to a return string and echo it out at the end. But this is close to what you had, using one SQL query (in PDO - seriously worth starting to use if you're creating new code) that should give you a list of artists sorted by name and their associated page_slugs.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

ORDER BY artist_name ASC is not working in the query. I think because the query is inside a while statement... The asort allows the data to show up (unlike the sort) but how would I decipher which field to sort them by?
And also, yes, that makes perfect sense. Thank you for the explanation.
@NotJay - In my haste I grabbed the wrong SQL query to attach the ORDER BY to. I made the same mistake you did, ORDERing a query that's designed to only return one row anyway!
No problem. I tried adding a few types of sort and it seems like uasort would be the one I want to use? Here is what I attempted: uasort($artist, 'artist_name'); but the result was "Invalid comparison function".
@NotJay - unless I'm missing something (entirely possible, I'm doing like 20 things at work right now) I think you're still trying to sort a single row, which is not what you want. You would want to sort your entire array of ALL artists, not the data for a single artist. If I have time later I'll do a more in-depth rewrite of your code showing how I would approach it.
|
0

You could do all of this in one query:

SELECT * FROM event_url_tbl AS event
INNER JOIN artist_tbl AS artist ON event.artist_id = artist.id
ORDER BY artist.name DESC;

This cuts out a lot of the complexity/foreaches in your script. You'll end up with

Label1 (Label 1 details..) Artist1 (Artist1 details..)
Label1 (Label 1 details..) Artist2 (Artist1 details..)
Label2 (Label 2 details..) Artist3 (Artist1 details..)

Always good to bear in mind "one query is better than many". Not a concrete rule, just if it's possible to do, try to do it. Each query has overheads, and queries in loops are a warning sign.

Hopefully that helps

Improve this answer

4 Comments

I didn't even get into this in my answer, but it's right on. Especially the "one query is better than many".
SELECT DISTINCT artist_id FROM event_url_tbl AS event INNER JOIN artist_tbl AS artist ON event.artist_id = artist.artist_id ORDER BY artist.artist_name ASC I have tried this and I am getting a "supplied argument is not a valid MySQL result resource" message. I slightly modified your query to match field names and query conditions.
Give it a try without the DISTINCT, just to see if thats whats causing the problem. (It's been a long while since I last ran a query that wasn't in an ORM or PDO so the error message don't mean as much as they used to, sorry)
Also, to see if the query is erroring (hence feeding fetch_array something that is not a result resource) try using this: php.net/manual/en/function.mysql-error.php

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.