How Can Results From 1 Table To Order Those From Another?

[kjm7267]

Good morning from Palo Alto, California, where I’m stuck on a problem. My mySQL database has two tables: “names” and “descriptions”. In the names table, each record has three fields: id, which is auto-generated and is the key; star; and name. Star is binary – there’s either a y or a blank.

The second table, “descriptions,” also has three fields: id, which is auto-generated and is the key; link; and description. Link is simply the manually-entered id from the “names” table that connects a record from the description table with a a record from the names table. Some names have descriptions, but most don’t. A name with a description may or may not have a star. A name with a star always has a description.

I want to generate a list that orders the names as follows:

1. First, names with a star, in alphabetical order, each with star.jpg next to it.

2. Second, names with no star, but a description, in alphabetical order.

3. Third, names with neither a star nor a description, in alphabetical order.

I’m able to handle 1 and 3, but not 2. I just can’t figure out how to call the description table to order records from the names table. Here’s what I have so far:

<?
mysql_connect(“ipaddress”, “username”, “password”) or die(mysql_error());
@mysql_select_db(“databasename”) or die( “Unable to select database”);
$query=“SELECT * FROM name ORDER BY star DESC, name”;
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,“id”);
$star=mysql_result($result,$i,“star”);
$name=mysql_result($result,$i,“name”);
?>

<?
if ($star == ‘’) {
echo “”;?><? }
else {
?>
<img src=“star.jpg”>

<?
}
?>

<? echo “$name”;?><br><br>
<?
++$i;
}
?>

Please note that, for a variety of reasons, the two tables need to remain separate. Please, do you have any suggestions? Thank you!

[r937]

SELECT n.id
     , n.star
     , d.description
     , n.name 
  FROM names AS n
LEFT OUTER
  JOIN descriptions AS d
    ON d.link = n.id
ORDER 
    BY n.star DESC
     , CASE WHEN d.link IS NULL
            THEN 'last'
            ELSE 'first' END 
     , n.name

[kjm7267]

Thank you, r937, but for whatever reason, I seem to be unable to make that work. Perhaps I don’t know how to set it into the whole code? This is what I tried:

<?
mysql_connect(“ipaddress”, “username”, “password”) or die(mysql_error());
@mysql_select_db(“databasename”) or die( “Unable to select database”);
$query=“SELECT n.id
, n.star
, d.description
, n.name
FROM names AS n
LEFT OUTER
JOIN descriptions AS d
ON d.link = n.id
ORDER
BY n.star DESC
, CASE WHEN d.link IS NULL
THEN ‘last’
ELSE ‘first’ END
, n.name”;
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,“id”);
$star=mysql_result($result,$i,“star”);
$name=mysql_result($result,$i,“name”);
?>

<?
if ($star == ‘’) {
echo “”;?><? }
else {
?>
<img src=“star.jpg”>

<?
}
?>

<? echo “$name”;?><br><br>
<?
++$i;
}
?>

Also, I failed to mention one other fact: There can be multiple records in the description table linking to a single record in the names table. That is, one name can have multiple descriptions. For the purposes of generating the list, each name should appear only once, regardless of how many descriptions it has. Thanks, r937, you’re very kind to help me!

[r937]

sorry, i can only help you with the mysql, i don’t do php

as for multiple descriptions for each name, which description do you want? you can have the shortest one, or the longest one, or the one that sorts first, or the one that sorts last…

[kjm7267]

r937, thank you so much for thinking about this. The descriptions shouldn’t appear in the generated list. The list is of the names only. So various characteristics of a description don’t matter – the only thing that matters is whether there’s a description or not. If there’s a description of any sort tagged to a name, but no star, then that name goes in the second part of the list. Within that part, the only sort is by alphabet.

Second point: when you say that you only do SQL, I assume than that the code you provide is what goes in between
$query="
and
";

right?

r937, thank you so much again! And thanks also, of course, to anyone else who may be considering this problem.

[r937]

okay, slight change…

SELECT n.id
     , n.star
     , d.description
     , n.name 
  FROM names AS n
LEFT OUTER
  JOIN [COLOR="Blue"]( SELECT DISTINCT link
           FROM descriptions )[/COLOR] AS d
    ON d.link = n.id
ORDER 
    BY n.star DESC
     , CASE WHEN d.link IS NULL
            THEN 'last'
            ELSE 'first' END 
     , n.name

[kjm7267]

I’m grateful for your help, r937. But unfortunately, I’m still not sure how to plug your suggestion into my original code. My inexperience is certainly the cause. Perhaps someone else could chime in to bolster r937’s help?

[hash]

Not really sure what you’re having trouble with, what works? doesn’t?
Anyway, try this to display results

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {
  
  $id = $row['id'];
  $star = ('' === $row['star']) ? '' : '<img src="star.jpg">';
  $name = $row['name'];
  
  echo $star.$name.'<br>';
  
}

[kjm7267]

Our complete code for what’s now called page.php is now:

<?
mysql_connect(“ipaddress”, “username”, “password”) or die(mysql_error());
@mysql_select_db(“databasename”) or die( “Unable to select database”);
$query=“SELECT n.id
, n.star
, d.description
, n.name
FROM names AS n
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM descriptions ) AS d
ON d.link = n.id
ORDER
BY n.star DESC
, CASE WHEN d.link IS NULL
THEN ‘last’
ELSE ‘first’ END
, n.name”;
$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {

$id = $row[‘id’];
$star = (‘’ === $row[‘star’]) ? ‘’ : ‘<img src=“star.jpg”>’;
$name = $row[‘name’];

echo $star.$name.‘<br>’;

}
?>

We’re getting an error message that reads:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /web/page.php on line 23

Line 23 refers to:
while($row = mysql_fetch_array($result)) {

[deepM]

Hello kjm,

SELECT n.id
, n.star
, d.description
, n.name
FROM names AS n
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM descriptions ) AS d
ON d.link = n.id
ORDER
BY n.star DESC
, CASE WHEN d.link IS NULL
THEN 'last'
ELSE 'first' END
, n.name

try to run this query into mysql(phpmyadmin) first and check whether you are getting any result or not.

[kjm7267]

Yes, it is generating a list for me. But unfortunately, I’m still stuck.

[kjm7267]

Progress! Thank you, r937, deepson2, and hash. I’ve been working with your good suggestions and have the site almost doing what it needs to do now. I’m indebted to you for your help, very much. Thanks again!