Creating a Query from results of previous Query

[tascam424]

Hi all, i'm a total newbie and i know very little about coding, but i'm beating my way through learning. I'm having a little problem working out how to do the following, as i probably don't know the correct terminology. So basically i have a database loaded with Karaoke Album Details : fields are Artist, Title, Description, DiscNo, TrackNo.
I have managed to create a query to display DiscNo in a simple table like below ..

What i would like to do is then have each of the DiscNo clickable to either expand on page or link to a popup to show all of the associated fields ie TrackNo, Artist etc .. Any help or direction would be greatly appreciated.

<?php


$dbhost = 'localhost';
$dbuser = '*******';
$dbpass = '*******';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = '*******';
mysql_select_db($dbname);

// Formulate Query
// This is the best way to perform a SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT DISTINCT DiscNo FROM CDG ORDER BY DiscNo ASC");

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

echo "<table border='1'>
<tr>
<th>Disc No</th>
</tr>";

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['DiscNo'] . "</td>";
echo "</tr>";
}

echo "</table>";

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);

?>

[swa66]

Before we go further, I'd like to correct one thing:

// This is the best way to perform a SQL query

This is very untrue.
For anything new I think it's terribly sad to still see the (should be) obsolete mysql interface used instead of mysqli and prepared statements.
The reason is that using it this way your next step is going to introduce a truckload of security issues if you're not extremely careful -and even then-.

Next comes database design ... a different story, but dumping all in one table usually means you're going to limit what a relational database can do severely.
Essentially you have artists, albums and tracks and relations between them

To link a result to another query, you have a number of options. The easiest is to link it to a query (another php script) that takes an argument and searches the database for that and displays the results.

You could also adapt your SQL query to return what you need and/or do multiple queries (but with just one table I don't see the use) and put it all in the html (potentialy with some hover effect to hide an overload of displayed information)

Another way could be to use ajax on the client to perform a async query and get more information from the server interactively (advanced stuff).

[tascam424]

Thank you for your reply.
If i'm totally honest i didn't fully understand all of it ..
So you gave me a few ideas on techniques to achieve what i want but i have no idea how to actually achieve it. Could you point me in the right direction with maybe a link to a tutorial or an example that i can try and relate to.
You also suggested that the query i am using isn't the best one. I found that in a tutorial with some demo, so the commented out text was part of that. As far as security goes, it's not really a huge concern at present as none of the data is private, but is there other reasons why i should not use this query ?

Thank you again for any help.

[swa66]

To switch you to mysqli on a simple select:

<?php
// server info
$server = '127.0.0.1';
$user = 'user';
$pass = 'pass';
$db = 'database';

// connect to the database
$mysqli = new mysqli($server, $user, $pass, $db);


if ($mysqli->connect_errno) {
 die('Error connecting to the database'); 
}

$sql="SELECT DISTINCT DiscNo FROM CDG ORDER BY DiscNo ASC";

if( $result = $mysqli->query($sql) ) {
 //print the header suppressed
 while($row = $result->fetch_object()) {
  print('  <tr>'."\n");
  print('   <td>'.htmlentities($row->DiscNo,ENT_COMPAT,'UTF-8').'</td>');
  print('  </tr>'."\n");
 } 
 //print the footer suppressed
} else {
 die ("database query failed\n");
}

Now you could use this to do other selects, but as son as you mix in user input it is *far* safer to switch to prepared statements.

I actually use htmlentities rarely lately: I like to just use the 5 xml allowed ones and leave all the rest in UTF-8 all the way. [This is required if you make polyglot xhtml5]. But that's another story.

[swa66]

To give an example of prepared statements using mysqli and soem user input.

You call this as http://www.example.com/test.php?id=1234

// parse id, numerical input only
if ( ( isset($_REQUEST['id']) ) && ( strlen($_REQUEST['id']) > 0 ) ) {
  $id= preg_replace('/[^a-zA-Z0-9]/m', '', $_REQUEST['id'] );
} else {
  die("bad query\n");
}

// rangecheck id
if(($id<0) || ($id>999999)){
  die("bad query\n");
}

// server info
$server = '127.0.0.1';
$user = 'user';
$pass = 'pass';
$db = 'database';

// connect to the database
$mysqli = new mysqli($server, $user, $pass, $db);

// note the ? instead of the replaced parameter
$sql = "SELECT TrackNo, Artist FROM CDG WHERE DiscNo =?";

if($stmt = $mysqli->prepare($sql)) {
  $stmt->bind_param("i", $id); 
       //this assumes the field is an integer, not a string
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($trackno,$artist);

  //output header here
  
  while ($stmt->fetch() ) {
    print('<tr>'."\n");
    print(' <td>'.htmlentities($trackno,ENT_COMPAT,'UTF-8').'</td>'."\n");
    print(' <td>'.htmlentities($artist,ENT_COMPAT,'UTF-8').'</td>'."\n");
    print('</tr>'."\n");
  }
  $stmt->close();

  // output footer here

} else {
  die("ERROR\n");
}