View detailed MySQL Result when Hyperlink is Clicked

Question

I'm trying to achieve the following. On the homepage a MySQL query returns results from table "parcid"(but limited results). The next step is, I would like to hyperlink a field (preferably "ID") to open a new page using the same ID but returning more detailed results. The code below works but obviously I'm missing something somewhere as it is returning all results rather than only results for the clicked on ID. Any help will be greatly appreciated.

Home url: "http://localhost/"

Pdetail url: "http://localhost/pdetail/"

Query on "Home" page

<?php

// set database server access variables:
$host = "localhost";
$user = "root";
$pass = "";
$db = "wordpress";

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// create query
$query =  "SELECT * FROM parcid";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table cellpadding=1 >";
        echo "<tr>";
        echo "<td>"."ID"."</td>"; 
        echo "<td>"."City"."</td>";          
        echo "<td>"."Destination City"."</td>";
        echo "<td>"."Weight"."</td>";
        echo "<td>"."Length"."</td>";
        echo "<td>"."Width"."</td>";
        echo "<td>"."Height"."</td>";
        echo "<td>"."Type"."</td>";
        echo "<td>"."Courier Option"."</td>";
        echo "</tr>";
    while($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo '<td><a href="http://localhost/pdetail/'.$row[0].'">'.$row[0].'</a></td>';
        echo "<td>" . $row[6]."</td>";      
        echo "<td>" . $row[12]."</td>";
        echo "<td>" . $row[14]."</td>";
        echo "<td>" . $row[15]."</td>";
        echo "<td>" . $row[16]."</td>";
        echo "<td>" . $row[17]."</td>";
        echo "<td>" . $row[18]."</td>";
        echo "<td>" . $row[19]."</td>";
        echo "<td>" . $row[20]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

Query on"pdetailed" page.

<?php

// set database server access variables:
$host = "localhost";
$user = "root";
$pass = "";
$db = "wordpress";

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

$ID = $_GET['ID'];

// create query
$query =   "SELECT * FROM parcid WHERE ID LIKE '%$ID%'";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table cellpadding=1 >";
        echo "<tr>";
        echo "<td>"."ID"."</td>";
        echo "<td>"."CID"."</td>";
        echo "<td>"."From"."</td>";
        echo "<td>"."Street"."</td>";
        echo "<td>"."Suburb"."</td>";
        echo "<td>"."Post Code"."</td>";
        echo "<td>"."City"."</td>";
        echo "<td>"."Province"."</td>";
        echo "<td>"."Receiver"."</td>";
        echo "<td>"."Destination Street"."</td>";
        echo "<td>"."Destination Suburb"."</td>";
        echo "<td>"."Destination Postcode"."</td>";
        echo "<td>"."Destination City"."</td>";
        echo "<td>"."Destination Province"."</td>";
        echo "<td>"."Weight"."</td>";
        echo "<td>"."Length"."</td>";
        echo "<td>"."Width"."</td>";
        echo "<td>"."Height"."</td>";
        echo "<td>"."Type"."</td>";
        echo "<td>"."Courier Option"."</td>";
        echo "</tr>";
    while($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo "<td>" . $row[0]."</td>";
        echo "<td>" . $row[1]."</td>";
        echo "<td>" . $row[2]."</td>";
        echo "<td>" . $row[3]."</td>";
        echo "<td>" . $row[4]."</td>";
        echo "<td>" . $row[5]."</td>";
        echo "<td>" . $row[6]."</td>";
        echo "<td>" . $row[7]."</td>";
        echo "<td>" . $row[8]."</td>";
        echo "<td>" . $row[9]."</td>";
        echo "<td>" . $row[10]."</td>";
        echo "<td>" . $row[11]."</td>";
        echo "<td>" . $row[12]."</td>";
        echo "<td>" . $row[13]."</td>";
        echo "<td>" . $row[14]."</td>";
        echo "<td>" . $row[15]."</td>";
        echo "<td>" . $row[16]."</td>";
        echo "<td>" . $row[17]."</td>";
        echo "<td>" . $row[18]."</td>";
        echo "<td>" . $row[19]."</td>";
        echo "<td>" . $row[20]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

**Stop** using deprecated `mysql_*` API. use `mysqli_*` or `PDO` with prepared statements — Jens, Mar 09 '16 at 12:05

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

If you want just the single row identified by $ID then the query is

$query = "SELECT * FROM parcid WHERE ID = '$ID'";

Also when you know you should only receive a single result row from a query, you do not need to run the mysql_fetch_row($result) in a while loop.

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use

Unless you are using a framework this statement might be wrong

echo '<td><a href="http://localhost/pdetail/'.$row[0].'">'.$row[0].'</a></td>';

Maybe it should be

echo '<td><a href="http://localhost/pdetail?ID='.$row[0].'">'.$row[0].'</a></td>';

edited May 23 '17 at 12:23

Community

1
1

answered Mar 09 '16 at 12:06

RiggsFolly

93,638
21
103
149

Thank you for the advice RiggsFolly. Hitting a snag, it doesn't return rows on the page when using that query. When clicking on the hyperlinked id it looks like this: "http://localhost/pdetail/2/" – Venom791 Mar 09 '16 at 12:19
Nobody but you can use the url `localhost` it by definition specifies `this machine` and we are not using your machine – RiggsFolly Mar 09 '16 at 12:21
nah, just a sql error: "No rows found!", regarding the localhost url, I just wanted to show what the URL looks like when clicking on a certain ID – Venom791 Mar 09 '16 at 12:27
See additional suggestion – RiggsFolly Mar 09 '16 at 12:29
HAHAA!!!! RiggsFolly you beaut! Working nicely. Thank you very much. I will definitely take your advice and invest my time into PDO, mysql_ – Venom791 Mar 09 '16 at 12:41

View detailed MySQL Result when Hyperlink is Clicked

1 Answers1