Displaying SQL query results in a 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
someone2088
Forum Commoner
Posts: 42
Joined: Thu Nov 17, 2011 1:09 pm

Displaying SQL query results in a table

Post by someone2088 »

Hey,

I'm using PHP to connect to a database, and pulling data from the database using SQL queries. I now want to be able to display the query results in HTML tables on my web page. Just wondering if someone could help me out with this?

The code I currently have is connecting to the database, and retrievning the information I want (it's not missing any info or getting me anything that I didn't ask for), but it's just displaying it in one very long paragraph with no formatting at all. I'd be grateful if someone could let me know how best to display the query results in an HTML table.

My code is below:

Code: Select all

<?php
	echo extension_loaded('pgsql');
	
	$conn_string = "host=***** port=**** dbname=***** user=***** password=*****";
	$dbconn = pg_connect($conn_string);
	
	if(!$dbconn){
		die('Could not connect: ' . pg_error());
		}
		
	// generate and execute a simple query
	$query = "SELECT * FROM CSGames";
	$result = pg_query($dbconn, $query) or die("Error in query: $query." . pg_last_error($dbconn));
	// Get the number of rows in the resultset
	$rows = pg_num_rows($result);
	echo "\n There are currently $rows records in the database.";
	
	// query to retrieve titles of all games from database, with their price and description
	$gameTitlesQuery = "SELECT title, price, description FROM CSGames";
	$gameTitlesQueryResult = pg_query($dbconn, $gameTitlesQuery) or die("Error in query: $gameTitlesQuery." . pg_last_error($dbconn));
	
	// now display query results in a table
	while($myRow = pg_fetch_assoc($gameTitlesQueryResult)){
		"INSERT INTO resultsTable (column1, column2, column3)
		VALUES(title, price, description)";
		
		printf("<tr><td>%s</td><td>%s</td<td>%s</td></tr>", $myRow['title'], htmlspecialchars ($myRow[price]), htmlspecialchars($myRow['description']));
		}
	
	// close database connection
	// pg_close($dbconn);	
?>
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: Displaying SQL query results in a table

Post by mikeashfield »

This should work, although I've never used PG.

Code: Select all

<table>
<tr><td>Title</td><td>Price</td><td>Description</td></tr>
<?php
    foreach ($myRow as $row) {
        echo "<tr>
        <td>".$row['title']."</td>
        <td>".htmlspecialchars($row['price'])."</td>
        <td>".htmlspecialchars($row['description'])."</td>
        </tr>";
    }
?>
</table>
someone2088
Forum Commoner
Posts: 42
Joined: Thu Nov 17, 2011 1:09 pm

Re: Displaying SQL query results in a table

Post by someone2088 »

Hey, thanks for your reply. I've tried adding in the code you suggested, but when I view the page, where the table should be displayed, I get a warning message: "Warning: Invalid argument supplied for foreach() in FILEPATH on line 50".

Line 50 is the start of the foreach loop.

Code: Select all

foreach($myRow as $row){
			echo "<tr>
			<td>".$row['title']."</td>
			<td>".htmlspecialchars($row['price'])."</td>
			<td>".htmlspecialchars($row['description'])."</td>
			</tr>";
		}
someone2088
Forum Commoner
Posts: 42
Joined: Thu Nov 17, 2011 1:09 pm

Re: Displaying SQL query results in a table

Post by someone2088 »

Would that be referring to the 'as $row' parameter?
maxx99
Forum Contributor
Posts: 142
Joined: Mon Nov 21, 2011 3:40 am

Re: Displaying SQL query results in a table

Post by maxx99 »

It means that $myRow its not something you can iterate over :(
Try:
var_dump($myRow); to see whats in there
Post some more of your new code, how do you get $myRow now?
someone2088
Forum Commoner
Posts: 42
Joined: Thu Nov 17, 2011 1:09 pm

Re: Displaying SQL query results in a table

Post by someone2088 »

Thanks for the help. I seem to have sorted it with this:

Code: Select all

<?php
	echo '<div id="Games">';
	echo '<table id="Games" border="1">';
	while ($a=pg_fetch_row($gameTitlesQueryResult)){
		echo '<tr>';
		for ($i=0; $i<pg_num_fields($gameTitlesQueryResult); $i++){
			echo '<td>'.htmlspecialchars($a[$i], ENT_QUOTES).'</ td>';
		}
	echo "<td><input type='checkbox' name='selectGame' value='{$a['refnumber']}' /></ td>
	</ tr>";
	}
	echo '</table></ div>';
?>
Post Reply