MySQL Displaying select data

  Fullywired 12:26 22 Apr 2009


i am currently doing a small web project that uses a MySQL database and PHP.

i have a table set up with a list of products that has the main headings:
ID, Name, Brand, Category, Spec, Description and Price.

my current problem is that i want a seperate part of the site for each category.

i have figured out how to display the whole table of products in one big list using

$data = mysql_query("SELECT * FROM product_db") or die(mysql_error());

and then printing it into a table but cannot work out how to display a select category of data or even sort the long table by category , name or price.

any ideas?


  SimpleSimon1 09:33 23 Apr 2009

OK, teach yourself SQL is to big a topic for any post :-)However, although I don't know MySQL and PHP, I can probably give you some general SQL pointers.

1) To return a subset from a table, you need to USE a WHERE clause e.g SELECT * FROM FRUIT WHERE FRUIT_TYPE = "TROPICAL"

The above SQL will return all records from the table "FRUIT" where the column FRUIT_TYPE = "TROPICAL" (not "tropical" or "Tropical" since SQL is case-sensitive)

2) WHERE clauses can be concatenated and/or wild carded e.g SELECT * FROM FRUIT WHERE FRUIT_TYPE = "TROPICAL" AND FRUIT_COLOUR = "GR*"

Check what the wild card character is in MySQL because it can vary between different types of SQL


The above SQL will cause the returned results to be sorted by the contents of the ORIGIN_COUNTRY column in the FRUIT table.

These are the simplest forms of these functions and much more sophisticated constructs are available. I suggest you google for "basic SQL" which returns a number of good intros to SQL.

Like I said, the above a general SQL principles and I normally work with Oracle not MySQL. However, they should give you a hint as to where you need to look in the MySQL docs

Good luck

  Fullywired 10:19 23 Apr 2009

thanks very much for the help, that has cleared alot up for me.

the synax works perfectly in phpMyAdmin however when i put the same code into my php script then i just get a blank page.

This works and displays the database:

$data = mysql_query("SELECT * FROM product_db") or die(mysql_error());

however this just displays a blank page

$data = mysql_query("SELECT * FROM product_db WHERE category = "Processor" ORDER BY title") or die(mysql_error());

so i am a little confused

  SimpleSimon1 10:41 23 Apr 2009

It might just be a typo in your mails but in your first mail you said you had a column called "Category" and in your second mail, your statement references the column "category" i.e. the first character is lower case not upper case.

If this is NOT a typo in your mail, the column headings in your statement must be identical to the column defintions i.e. if your column name is "Category", you must always reference it as "Category" in your SQL. Because SQL is case-sensitive, "category" would be an unknown column heading.

BTW, because of this case-sensitivity, as a convention, database, table and column names are normally defined using upper-case only.

Until you get this working, I would suggest dropping the ORDER BY statement to make the fault finding simpler.

If the problem can't be explained by a typo, I'm at a bit of a loss. Hopefully, one of the forum php experts can see what the problem is. Typos apart, your SQL looks OK and the fact that it works in the Admin screen would suggest that whatever the problem is, it doesn't lie within the SQL.

Once again, good luck

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone