My SQL query is only returning children I need the parent aswell
My sql query is only returning the children of the parent I need it to return the parent as well,
public function getNav($cat,$subcat){
//gets all sub categories for a specific category
if(!$this->checkValue($cat)) return false; //checks data
$query = false;
if($cat=='NULL'){
$sql = "SELECT itemID, title, parent, url, description, image
FROM p_cat
WHERE deleted = 0
AND parent is NULL
ORDER BY position;";
$query = $this->db->query($sql) or die($this->db->error);
开发者_StackOverflow社区 }else{
//die($cat);
$sql = "SET @parent = (SELECT c.itemID FROM p_cat c WHERE url = '".$this->sql($cat)."' AND deleted = 0);
SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image, (SELECT c2.url FROM p_cat c2 WHERE c2.itemID = c1.parent LIMIT 1) as parentUrl
FROM p_cat c1
WHERE c1.deleted = 0
AND c1.parent = @parent
ORDER BY c1.position;";
$query = $this->db->multi_query($sql) or die($this->db->error);
$this->db->store_result(); $this->db->next_result();
$query = $this->db->store_result();
}
return $query;
}
may be:
"SET @parent = (SELECT c.itemID FROM p_cat c WHERE url = '".$this->sql($cat)."' AND deleted = 0);
SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image, (SELECT c2.url FROM p_cat c2 WHERE c2.itemID = c1.parent LIMIT 1) as parentUrl
FROM p_cat c1
WHERE c1.deleted = 0
AND (c1.parent = @parent OR c1.itemID = @parent)
ORDER BY c1.position;"
Just join the p_cat table back to itself:
SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image
, parents.itemID, parents.parent, parents.url, parents.description, parents.image,
FROM p_cat c1
Inner Join p_cat parents
On parents.id = C1.parent
WHERE c1.deleted = 0
AND c1.parent = @parent
ORDER BY c1.position
精彩评论