Linking the results in a table via the ID

[stratusdodge]

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);

[Jessica]

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.

[stratusdodge]

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.

[Jessica]

What???

 

 

Post the structure of your table and explain what you want to echo on your page.

[stratusdodge]

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?

[Jessica]

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)

 

 

[stratusdodge]

Anyone?

 

Any help at all would be appreciated.

[lemmin]

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>";
}

[Jessica]

You'll still need to do an if() after the foreach to make sure you don't output the cell for the ID. This is a logic problem. If you can state it in english how it should work, you can then write the code.

[Christian F.]

Also, since you're using PHP_SELF you'll be wanting to use this snippet too!

[stratusdodge]

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);

[stratusdodge]

Oh, and I almost forgot---Thanks for your help lemmin and ChristianF!!