Page 1 of 1

Displaying SQL query results in a table

Posted: Fri Nov 25, 2011 8:21 am
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);	
?>

Re: Displaying SQL query results in a table

Posted: Fri Nov 25, 2011 8:31 am
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>

Re: Displaying SQL query results in a table

Posted: Fri Nov 25, 2011 8:49 am
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>";
		}

Re: Displaying SQL query results in a table

Posted: Fri Nov 25, 2011 8:50 am
by someone2088
Would that be referring to the 'as $row' parameter?

Re: Displaying SQL query results in a table

Posted: Sun Nov 27, 2011 3:03 pm
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?

Re: Displaying SQL query results in a table

Posted: Mon Nov 28, 2011 10:00 am
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>';
?>