We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

MySQL Displaying select data


Fullywired

Likes # 0

hi,

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?

Regards

Like this post
SimpleSimon1

Likes # 0

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

3)To sort returned results use the ORDER BY clause e.g. SELECT * FROM FRUIT WHERE FRUIT_TYPE = "TROPICAL" ORDER BY ORIGIN_COUNTRY

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

Like this post
Fullywired

Likes # 0

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

Like this post
SimpleSimon1

Likes # 0

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

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Windows 10 launch event as it happened: Read our Windows 10 launch live blog - find out first as...

IDG UK Sites

Windows 9 and the death of the OS as a must-have product

IDG UK Sites

Video trends: 4K is here – HDR video, VR and 3D audio is coming

IDG UK Sites

Best iPhone 6, iPhone 6 Plus deals: iPhone 6, iPhone 6 Plus tariffs, contracts and prices UK