Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Display table via PHP using data from a MySQL database

Posted on 2008-10-01
8
Medium Priority
?
3,252 Views
Last Modified: 2013-12-12
Hi,

I am quite new to PHP and MySQL so I am a little stuck and I hope you can help.

I have created a database containing 1 table for bathroom products that we sell, the db is called bathrooms and the table is called products with following fields - ID, Group, Code, Width, Depth, Height, Other. I have also created a website that displays the information of all our current bathroom ranges located here: http://www.wbhltd.com/bathroom_css/ (I have only just started web design so its nothing special, please also not that it is not finished. The best place to use as an example is the accessories page)

Currently, all the tables on the site have been created using HTML, however I want to create them using PHP by extracting the data from the database. I have been following the tutorial here: http://devzone.zend.com/node/view/id/627 and I got to part 8 that talks about connecting to a database hoping it would tell me how to do what I want to do. As you can see there are several different tables on the site and I was hoping to extract the rows via the Group field which groups certain products together, however based on my logic I would have to create 37 separate queries to achieve this. I was thinking maybe I could extract the table data into an array of some kind and then just extract the data I need from that array, but here is where i get lost. I am also having trouble extracting only the rows from the group i define. For example the query I am using is "SELECT * FROM products WHERE Group='BP'" but I get a MySQL syntax error - "Error in query: .You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Group='BP'' at line 1"

The code I have attached will display all the products in the DB, but I only want to extract specific data such as Bath Panels so that I can create the table on the accessories page that displays Bath Panel information.

I hope this makes sense and that you can help.

Thank you
<?php
 
// define server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "bathrooms";
 
// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
 
// select database
mysql_select_db($db) or die ("Unable to select database!");
 
// create query
$query = "SELECT * FROM products";
 
// execute query
$result = mysql_query($query) or die ("Error in query: $query.".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
	// yes
	// print them one after another
	echo "<table cellpadding=10 border=1>";
	while(list($ID, $Group, $Code, $Width, $Depth, $Height, $Other) = mysql_fetch_row($result)) {
		echo "<tr>";
		echo "<td>$Code</td>";
		echo "<td>$Width</td>";
		echo "<td>$Depth</td>";
		echo "<td>$Height</td>";
		echo "<td>$Other</td>";
		echo "</tr>";
	}
	echo "</table>";
}
 
else {
	// no
	// print status message
	echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 
// close connection
mysql_close($connection);
 
?>

Open in new window

0
Comment
Question by:wbhltd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 1

Author Comment

by:wbhltd
ID: 22613016
Hi,

I managed to sort out the MySQL syntax error. I changed it from:

$query = "SELECT * FROM products WHERE Group='BP'"

to

$query = "SELECT * FROM products WHERE products.Group='BP'"

I don't know why I had to do that but that is what I was told to change it to by my boss and it now displays a table with only the Bath Panel information in it :)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613145
the syntax error is because GROUP is a reserved keyword,

this would also work:
$query = "SELECT * FROM products WHERE `Group? ='BP'"

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613147
sorry:
$query = "SELECT * FROM products WHERE `Group`='BP'"

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:wbhltd
ID: 22615921
Thanks angellll, I wondered why it wouldn't work

I think I should be more specific about what what I want to do. Basically, instead of assigning lots of queries to different variable I was wondering if it would be possible to change the query on the fly based on an input I selected.

Say I want to make 4 separate tables, one for Bath Panels, one for Mirrors, one for Glass Shelves and one for Infill Panels. All the products for each of these categories have been grouped with the codes BP, MI, GS & IP respectively. I would like to create a function that outputs the table structure that can be called upon every time I need to output a table and then just adjust the query to use the different criteria, e.g. the Bath Panels table would use the BP code, Mirrors would use MI etc.

I am hoping that this way I can cut the amount of code necessary significantly because I am not re-writing a different query and function to output the table every time I want to add a new table into the web page. This way I can call the function and say that I want it display, for example, Infill Panels.

I hope this makes more sense although I've probably made it more confusing lol.

Thanks
0
 
LVL 1

Author Comment

by:wbhltd
ID: 22622326
Hi,

I had an idea where every time I needed to create a table I would create a new query and then just call the function directly after it. But for some reason I get the error "Query was empty", which means to me that it is not calling in the query from outside the function. Can anyone see what I am doing wrong from the code below?
<html>
<head>
</head>
 
<body>
	
<?php
 
function displaytable() {
	
// create connection
$connect = mysql_connect("localhost", "test", "test", "bathrooms") or die ("unable to connect to database!");
 
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
	// yes
	// print them one after another
	echo "<table cellpadding=8 border=0>";
	echo "<tr><th>Code</th><th>Width</th><th>Depth</th><th>Height</th></tr>";
	while(list($ID, $Group, $Code, $Width, $Depth, $Height, $Other) = mysql_fetch_row($result)) {
		echo "<tr>";
		echo "<td>$Code</td>";
		echo "<td>$Width</td>";
		echo "<td>$Depth</td>";
		echo "<td>$Height</td>";
		echo "<td>$Other</td>";
		echo "</tr>";
	}
	echo "</table>";
}
 
else {
	// no
	// print status message
	echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 
// close connection
mysql_close($connection);
 
}
 
?>
 
<?php
$query = "SELECT * FROM products WHERE `Group`='BP'";
displaytable();
?>
 
</body>
</html>

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22622520
yes. the function does not know about the variable declared outside of it, unless either:
* you specify global
* you use parameter

the parameter method is like this:
<html>
<head>
</head>
 
<body>
        
<?php
 
function displaytable($query) {
        
// create connection
$connect = mysql_connect("localhost", "test", "test", "bathrooms") or die ("unable to connect to database!");
 
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
        echo "<table cellpadding=8 border=0>";
        echo "<tr><th>Code</th><th>Width</th><th>Depth</th><th>Height</th></tr>";
        while(list($ID, $Group, $Code, $Width, $Depth, $Height, $Other) = mysql_fetch_row($result)) {
                echo "<tr>";
                echo "<td>$Code</td>";
                echo "<td>$Width</td>";
                echo "<td>$Depth</td>";
                echo "<td>$Height</td>";
                echo "<td>$Other</td>";
                echo "</tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 
// close connection
mysql_close($connection);
 
}
 
?>
 
<?php
$query = "SELECT * FROM products WHERE `Group`='BP'";
displaytable($query);
?>
 
</body>
</html>

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22622523
the global method would be this (but note that you should prefer the parameter method):
<html>
<head>
</head>
 
<body>
        
<?php
 
function displaytable() {
global $query;        
// create connection
$connect = mysql_connect("localhost", "test", "test", "bathrooms") or die ("unable to connect to database!");
 
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
        echo "<table cellpadding=8 border=0>";
        echo "<tr><th>Code</th><th>Width</th><th>Depth</th><th>Height</th></tr>";
        while(list($ID, $Group, $Code, $Width, $Depth, $Height, $Other) = mysql_fetch_row($result)) {
                echo "<tr>";
                echo "<td>$Code</td>";
                echo "<td>$Width</td>";
                echo "<td>$Depth</td>";
                echo "<td>$Height</td>";
                echo "<td>$Other</td>";
                echo "</tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 
// close connection
mysql_close($connection);
 
}
 
?>
 
<?php
$query = "SELECT * FROM products WHERE `Group`='BP'";
displaytable();
?>
 
</body>
</html>

Open in new window

0
 
LVL 1

Author Comment

by:wbhltd
ID: 22622801
Excellent that's exactly what I wanted.

Thank a lot :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question