Database > MySQL database

Creating a Query from results of previous Query

(1/2) > >>

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 Code: ---<?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);

?> 
--- End code ---

wilson382:
Display the DiscNO like this

NOTE: you have to replace how you display your DiscNO with my code

--- PHP Code: ---
echo "<td><a href='?discno=".$row['DiscNo']."'>".$row['DiscNo']."</td>";

--- End code ---


the above link when clicked will attach the DishNo to the link for example

--- Code: ---http://example.com?discno=1457

--- End code ---


so what you do next is on the page where you gonna show a more detailed information get the discno and run a query fetching everything where the discno = discno


--- PHP Code: ---
if (isset($_GET['discno']))
{
//..... you run the query here and display everything according to the DiscNo
}

--- End code ---


good luck

tascam424:
Thank you for your reply .. but i am a little lost ..
I got the first bit and now have my list of linked DiscNo .. which all point towards   
--- Code: ---http://example.com?discno=1457
--- End code ---
   or similar ...

But obviously no page exists at that address do i need to physically create those pages as i have thousands of DiscNos to display .. not sure where to go next ..

Thank you again

wilson382:
yes you have to create the page where you are going displayed a full detailed information for instance your new file name is "detail.php"

you have to change

--- PHP Code: ---
echo "<td><a href='?discno=".$row['DiscNo']."'>".$row['DiscNo']."</td>";

--- End code ---

 to

--- PHP Code: ---
echo "<td><a href='detail.php?discno=".$row['DiscNo']."'>".$row['DiscNo']."</td>";

--- End code ---


and on that "detail.php" put the following code to get DiscNo sent from the previous page

--- PHP Code: ---
if (isset($_GET['discno']))
{
//..... you run the query here and display everything according to the DiscNo
}

--- End code ---

tascam424:
Thank you again, but i have not really got anywhere. My details.php looks like this


--- PHP Code: ---

<?php
if (isset($_GET['DiscNo']))
{

$query = sprintf("SELECT * FROM CDG WHERE DiscNo=DiscNo");


$result = mysql_query($query);

if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

echo "<table border='1'>
<tr>
<th>Artist</th>
<th>Title</th>
<th>Manufacturer</th>
<th>Disc No</th>
<th>Track No</th>
</tr>";

while ($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['Artist'] . "</td>";
echo "<td>" . $row['Title'] . "</td>";
echo "<td>" . $row['Description'] . "</td>";
echo "<td>" . $row['DiscNo'] . "</td>";
echo "<td>" . $row['TrackNo'] . "</td>";
echo "</tr>";
}

echo "</table>";

mysql_free_result($result);

?>
--- End code ---


But i am getting an internal server error ..due to a syntax error on line 42 .. As i said i really am a beginner.

Thank you again !

Navigation

[0] Message Index

[#] Next page