Page 1 of 1

HTML Table from MySQL Table

Posted: Sun Mar 04, 2007 2:57 pm
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>";

Re: HTML Table from MySQL Table

Posted: Sun Mar 04, 2007 3:04 pm
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>";

Posted: Sun Mar 04, 2007 3:19 pm
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.

Posted: Sun Mar 04, 2007 5:01 pm
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
    {

Posted: Sun Mar 04, 2007 6:08 pm
by aspekt9
Yup, that did it sure enough, thank you.