Php Query Dont Work

[madness69]

hello there, i need some help to display some items from my articles table, the articles table is related whit the other table of mine called category, and in the table categorys there are on column that is called category, inside there are 3 type, and one of them that cals "profile" is the one i need, i need the display all of my articles that is related whit the table categorys and column named "profile", above is the original query

 

Original query:

$sql = "SELECT * FROM articles order by articles.id DESC LIMIT ".$from.", 20";

 

Query i tryed to change:

$sql = "SELECT * FROM articles WHERE category = profile and order by articles.id DESC LIMIT ".$from.", 20";

 

I cant put this work i tryed many thign but no suscess, could someone help me?

 

Could someone help me

[Zane]

PHP does not make queries, MySQL does.

 

To achieve what you want you will need to use a join.

"SELECT * FROM articles a
JOIN categorys c ON a.profile = c.id
ORDER BY a.id DESC LIMIT ".$from.", 20"

[madness69]

It doesnt work, gives me this error:

 

Query Failed: Unknown column 'profile' in 'on clause'SELECT * FROM articles a JOIN categorys c ON profile = c.id ORDER BY a.id DESC LIMIT 0, 20

[Barand]

Show us your table structures. As you insist on using "SELECT * " we have no idea what your column names are.

[madness69]

CREATE TABLE IF NOT EXISTS `categorys` (

`category` varchar(200) NOT NULL,

PRIMARY KEY (`category`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `categorys`

--

 

INSERT INTO `categorys` (`category`) VALUES

('profiles'),

('components');

 

---------------------------------------------------------

 

CREATE TABLE IF NOT EXISTS `articles` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(200) NOT NULL,

`category` varchar(200) NOT NULL,

PRIMARY KEY (`id`,`title`,`category`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

 

--

-- Dumping data for table `articles`

--

 

INSERT INTO `articles` (`id`, `title`,`category`) VALUES

(60, '63101WMH20x20','profiles'),

[Barand]

As it stands the category table is a waste of space, it just duplicates the values held in the articles table

 

Try this, and note the quotes around profile to show it's a string value.

$sql = "SELECT * FROM articles
WHERE category = 'profile'
order by articles.id DESC
LIMIT $from, 20";

 

The correct way would be

 

articles -> id, title, cat_id

categorys -> id, category

 

Then

SELECT a.id, a.title, c.category
FROM articles a
INNER JOIN categorys c ON a.cat_id = c.id
WHERE c.category = 'profiles'
ORDER BY articles.id DESC
LIMIT $from, 20

Edited October 5, 2012 by Barand