HTML Table from MySQL Table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aspekt9
Forum Commoner
Posts: 43
Joined: Wed Dec 06, 2006 5:03 pm

HTML Table from MySQL Table

Post by aspekt9 »

I have this function that retrieves events from a database and displays them nicely in an html table and allows the user to order them by their respected columns. the problem is I create a row to allow for a link called edit that a user could click to edit the event and I have it linked to the event id but it only displays the top most id and just repeats it for the rest of the results. Here's the function:

Code: Select all

function show_table($sqlQuery)
{
    // Validate SQL Statement
    $array = explode(" ORDER",$sqlQuery);
    $sqlQuery = $array[0];
    if(!strstr($sqlQuery,"SELECT"))
        die("Invalid Query : SQL statement should be a SELECT statement.");
    // ORDER records by requested column    
    if($_GET['order']) 
        $sqlQuery = $sqlQuery." ORDER BY ".$_GET['order'];
    $result = mysql_query($sqlQuery) or die("Invalid Query : ".mysql_error());
    $row = mysql_fetch_array($result);
    // Check whether NULL records found
    if(!mysql_num_rows($result))
        die("No records found.");

    echo "<table border=0 cellspacing=5><tr>";
    // Make the row for table column names
    while (list($key, $value) = each($row))
    {
        $i++;
        if(!($i%2))
           echo "<td><b><a href='?order=$key'>$key</a></td>";
    }
    echo "</tr>";
    $result = mysql_query($sqlQuery);
    // Make rows for records
    while($rec = mysql_fetch_array($result))
    {
        echo "<tr>";
        for($i=0;$i<count($rec);$i++)
        {
            if($rec[$i]) 
			{
                if(strlen($rec[$i]) >= 40) 
				{
					$string = substr($rec[$i], 0, 40)."...";
				} else {
					$string = $rec[$i];
				}
				echo "<td>$string</td>"; 
			}
		}
		echo "<td><a href=\"editevent.php?id=$row[id]\">edit</a></td>";
        echo "</tr>";
    }
    echo "</table>";
}
show_table("SELECT id,title,content,DATE_FORMAT(date, '%M %d, %Y') as
            date FROM calevents");
And this is where I'm having problems:

Code: Select all

echo "<td><a href=\"editevent.php?id=$row[id]\">edit</a></td>";
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: HTML Table from MySQL Table

Post by Christopher »

I am not sure I actually understand the problem (or that you even asked a question), but maybe:

Code: Select all

echo "<td><a href=\"editevent.php?id={$row['id']}\">edit</a></td>";
(#10850)
aspekt9
Forum Commoner
Posts: 43
Joined: Wed Dec 06, 2006 5:03 pm

Post by aspekt9 »

Sorry, perhaps I wasn't very clear. When I have the function as shown, when it outputs the table the last column is called edit. It adds the edit url to the end of all the rows retrieved from the database and links them to a page. However when I use $row[id] it only shows the correct id for the first displayed row. All the other rows are the same as the first. It doesn't update each individual row to reflect the correct id.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

aspekt9 wrote:Sorry, perhaps I wasn't very clear. When I have the function as shown, when it outputs the table the last column is called edit. It adds the edit url to the end of all the rows retrieved from the database and links them to a page. However when I use $row[id] it only shows the correct id for the first displayed row. All the other rows are the same as the first. It doesn't update each individual row to reflect the correct id.
Could it be your not using $row for anyother of the variables from the Query....???

Try replacing $row with $rec...

Code: Select all

// Make rows for records 
    while($rec = mysql_fetch_array($result))  ///<<<<----- $rec not row
    {
aspekt9
Forum Commoner
Posts: 43
Joined: Wed Dec 06, 2006 5:03 pm

Post by aspekt9 »

Yup, that did it sure enough, thank you.
Post Reply