Jump to content

Linking the results in a table via the ID


stratusdodge

Recommended Posts

Hello all,

 

I have a PHP table that populates from a MySQL database. I want to link each result in the table to their respective unique ID. The code below links each result to a unique ID, however the table now repeats the same name on every row.

 

Any ideas?

 

$result = mysql_query("SELECT concat(first_name, ' ', last_name), email, country, TimeStamp FROM {$table} order by {$sortkey} LIMIT 
$id, {$records_per_page}") or
die(mysql_error());

$result_id = mysql_query("SELECT concat(first_name, ' ', last_name), email, country, TimeStamp, id FROM {$table} order by {$sortkey} desc LIMIT 
$id, {$records_per_page}") or
die(mysql_error());


if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<table id='tabledata' cellpadding='5'><tr>";

//Insert table headers
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(first_name).'">Name</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(email).'">E-Mail</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(country).'">Country</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(id).'">Date Created</a></b></td>';

echo "<tr>\n";

// Display the table rows
while($row = mysql_fetch_row($result))
while($row_id = mysql_fetch_row($result_id))

    foreach($row as $cell)

       echo "<td><a href=../ui/edit_customer.php&id=$row_id[4]>$cell</a></td>";

    echo "</tr>\n";

}
mysql_free_result($result);

Link to comment
Share on other sites

Why are you doing two queries?

 

You need to turn on error reporting because it would show you a ton of notices and errors.

Your HTML table is also going to be way messed up, you have only one <tr> and you put the </tr> inside the loop.

Link to comment
Share on other sites

Why are you doing two queries?

 

You need to turn on error reporting because it would show you a ton of notices and errors.

Your HTML table is also going to be way messed up, you have only one <tr> and you put the </tr> inside the loop.

 

The first query gets the rows that are being displayed on the table. The second query gets the ID (i've since cleaned this up to only get the ID).

 

If I use just the first query then the ID is being displayed in the table. I don't want this.

Link to comment
Share on other sites

OK. Let's start over. The following code displays a table on my page:

 

$result = mysql_query("SELECT concat(first_name, ' ', last_name), email, country, TimeStamp FROM {$table} order by {$sortkey} LIMIT 
$id, {$records_per_page}") or
die(mysql_error());
$fields_num = mysql_num_fields($result);

echo "<table id='tabledata' cellpadding='5'><tr>";

//Table Headers
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(first_name).'">Name</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(email).'">E-Mail</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(country).'">Country</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(id).'">Date Created</a></b></td>';

echo "<tr>\n";


while($row = mysql_fetch_row($result))
{
if ($c&1) {
    echo "<tr class=odd>";
}
else {
    echo "<tr class=even>";
}
    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable

    foreach($row as $cell)
       
       echo "<td><a href=../ui/edit_customer.php&id=[???????]>$cell</a></td>";

    echo "</tr>\n";
$c++;

}

 

The "ID" is a column in my MySQL database but I haven't selected it. I want each value in the table to link (example http://www.google.ca/ref=[iNSERT ID HERE]). If I select the ID, it gets displayed in the table.

 

So the question is, how do I extract the ID and assign it as a link to each row?

Link to comment
Share on other sites

If you want the ID, you need to select it. If you don't want it output in your table, don't output it!  You'll have to add something after foreach($row as $cell) to check which cell you are outputting before doing it. You will likely want to change it to foreach($row as $key=>$cell)

 

 

Link to comment
Share on other sites

Jesirose is right, this code is a mess! I suggest doing some cleanup before continuing.

 

Try using mysql_fetch_assoc:

 

while ($row = mysql_fetch_assoc($result_id))
{
foreach ($row as $cell)
	echo "<td><a href=\"../ui/edit_customer.php&id=".$row['id']."\">$cell</a></td>";
}

Link to comment
Share on other sites

OK, so I fixed it myself by changing the tables:

 

$result = mysql_query("SELECT concat(first_name, ' ', last_name), email, country, TimeStamp FROM {$table} order by {$sortkey} LIMIT 
$id, {$records_per_page}") or
die(mysql_error());

$result_id = mysql_query("SELECT concat(first_name, ' ', last_name), email, country, TimeStamp, id FROM {$table} order by {$sortkey} desc LIMIT 
$id, {$records_per_page}") or
die(mysql_error());


if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<table id='tabledata' cellpadding='5'><tr>";

//Insert table headers
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(first_name).'">Name</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(email).'">E-Mail</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(country).'">Country</a></b></td>';
echo '<td><b><a href="'.$_SERVER['PHP_SELF'].'?sortkey='.(id).'">Date Created</a></b></td>';

echo "<tr>\n";

// Display the table rows
while($row = mysql_fetch_row($result))
while($row_id = mysql_fetch_row($result_id))

    foreach($row as $cell)

       echo "<td><a href=../ui/edit_customer.php&id=$row_id[4]>$cell</a></td>";

    echo "</tr>\n";

}
mysql_free_result($result);

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.