Solved

Display table via PHP using data from a MySQL database

Posted on 2008-10-01
8
3,136 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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Expert Comment

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

Open in new window

0
 
LVL 1

Author Comment

by:wbhltd
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:wbhltd
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
Excellent that's exactly what I wanted.

Thank a lot :)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now