Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Display table via PHP using data from a MySQL database

Posted on 2008-10-01
8
Medium Priority
?
3,262 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

916 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