1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

sql query -> html table ?!

Discussion in 'PHP' started by falcondriver, Jul 7, 2006.

  1. #1
    hi,

    i need a small function that executes a mysql-query and returns the result in a html table. has anyone this done before and could send me the code please; because i only did this in asp a few years ago and dont wanna repeat all my errors and bugs...? :)
     
    falcondriver, Jul 7, 2006 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
  3. mad4

    mad4 Peon

    Messages:
    6,986
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #3
    <table>
    <?php
    $result = MYSQL_QUERY("SELECT * FROM tablename WHERE xxx='$xxxxxx'");
    if (!$row=mysql_fetch_array($result))
    exit();
    echo"<tr><td>$row[column1]</td><td>$row[column2]</td></tr>";
    ?>
    </table>
    PHP:
    or if you need to loop through the results:
    <table>
    <?php
    $SQLcommand = "SELECT * FROM tablename WHERE xxx='xxxxx'";
    $SQLresult = MYSQL_QUERY($SQLcommand);
    
    while($row=mysql_fetch_array($SQLresult)) 
    {
    echo"<tr><td>$row[column1]</td><td>$row[column2]</td></tr>";
    }
    ?>
    </table>
    PHP:
     
    mad4, Jul 7, 2006 IP
    falcondriver likes this.
  4. ip076

    ip076 Peon

    Messages:
    79
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #4
    If you insert the following code after line 4 in mad4's second example, you will get a header row with the column names. I'm fairly in-experienced, so, there may be an easier way, if someone has one, feel free to add it here.

    $NumFields = mysql_num_fields($SQLresult);
    echo "<tr>";
    for ($i=0; $i < $NumFields; $i++)
    { 
    	echo "<th>" . mysql_field_name($SQLresult, $i) . "</th>"; 
    }
    echo "</tr>";
    PHP:
     
    ip076, Jul 8, 2006 IP
  5. ip076

    ip076 Peon

    Messages:
    79
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Well...I got bored, and wrote this function for you. All you need to do is pass the function a query and it will return a formatted table.

    If anyone has any comments, or improvements, let me know.

    
    function SQLResultTable($Query)
    {
    	$link = mysql_connect(HOST, USER, PASS) or die('Could not connect: ' . mysql_error()); 		//build MySQL Link
    	mysql_select_db(DB) or die('Could not select database');		//select database
    	$Table = "";  //initialize table variable
    	
    	$Table.= "<table border='1' style=\"border-collapse: collapse;\">"; //Open HTML Table
    	
    	$Result = mysql_query($Query); //Execute the query
    	if(mysql_error())
    	{
    		$Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>";
    	}
    	else
    	{
    		//Header Row with Field Names
    		$NumFields = mysql_num_fields($Result);
    		$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\">";
    		for ($i=0; $i < $NumFields; $i++)
    		{     
    			$Table.= "<th>" . mysql_field_name($Result, $i) . "</th>"; 
    		}
    		$Table.= "</tr>";
    	
    		//Loop thru results
    		$RowCt = 0; //Row Counter
    		while($Row = mysql_fetch_assoc($Result))
    		{
    			//Alternate colors for rows
    			if($RowCt++ % 2 == 0) $Style = "background-color: #00CCCC;";
    			else $Style = "background-color: #0099CC;";
    			
    			$Table.= "<tr style=\"$Style\">";
    			//Loop thru each field
    			foreach($Row as $field => $value)
    			{
    				$Table.= "<td>$value</td>";
    			}
    			$Table.= "</tr>";
    		}
    		$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>";
    	}
    	$Table.= "</table>";
    	
    	return $Table;
    }
    
    //Call the function like this:
    echo SQLResultTable("SELECT * FROM TABLE");
    
    PHP:
     
    ip076, Jul 9, 2006 IP
    falcondriver likes this.
  6. nathfaceless

    nathfaceless Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    ip076, nice class - how would you add pagination to your class?
     
    nathfaceless, Jul 6, 2009 IP
  7. Social.Network

    Social.Network Member

    Messages:
    517
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    35
    #7
    I think you are three (3) years late. :rolleyes:
     
    Social.Network, Jul 6, 2009 IP