Jump to content
php.lv forumi

JOIN nedaudz nesanak...


reiniger

Recommended Posts

maja ID= 1 un ID_P = 0;

riga ID = 113 un ID_P = 1;

liela ID = 115 un ID_P 113;

 

raksta ID_P = 115

 

$m = 1;

 

SELECT post.*,category.* FROM post JOIN category ON (post.id_p=category.id AND category.id_p=$m) OR (post.id_p=category.id AND category.id=$m) 
 WHERE NOW() >= start_date AND NOW() <= end_date

 

Ja padod 113 vai 115 rezultāts rokā.

 

Kā lai atrod visus rakstus ja padota vērtība 1

 

mēģināju šādi bet nestrādā:

 

SELECT post.*,category.* FROM post JOIN category ON (post.id_p=category.id AND category.id_p=$m) OR (post.id_p=category.id AND category.id=$m) 
INNER JOIN category AS c1(post.id_p=category.id AND category.id_p=c1.id AND c1.id_p = $m) 
 WHERE NOW() >= start_date AND NOW() <= end_date

 

Kur esmu nošāvis nepareizi???

Link to comment
Share on other sites

brīvā veidā uzrakstīju prasīto

 

create category id, id_p, title
create post id, id_p, name

insert into category (id , id_p , title) value (1 , 0 , maja);
insert into category (id , id_p , title) value (113 , 1 , riga);
insert into category (id , id_p , title) value (115 , 113 , liela);

insert into post (id , id_p , name) value ( 1, 115 , kruta maja);

funtion count($m){
$result = mysql_query("SELECT post.*,category.* FROM post JOIN category ON (post.id_p=category.id AND category.id_p=$m) OR (post.id_p=category.id AND category.id=$m) WHERE NOW() >= start_date AND NOW() <= end_date");
$all_c = mysql_num_rows($result);

print_r($all_c);
}

...

count(115) rezultats ir 1

...

count(113) rezultats ir 1

...

count(1) ???

...

 

ka lai izveidoju select, lai nododot uz funkciju count vērtību 1 man rezultātā skaitītu ierakstus?

 

Ja vēl kas neskaidrs, tad jautājiet. Iespējams ka vajag vēlvienu JOIN, bet nevaru saprast ka to vajadzētu norealizēt.

Edited by reiniger
Link to comment
Share on other sites

Es rakstītu kaut kā tā.

"SELECT post.*,category.* FROM post JOIN category ON (post.id_p=category.id) WHERE NOW() >= start_date AND NOW() <= end_date AND (category.id={$m} OR category.id_p={$m})"

 

Šis strādā tā pat kā mans nedaudz sarežģītākais select. Paldies par opitmizētāku kodu, bet manu galveno problēmu tas neatrisināja

Izpildot :

...

count(115) rezultats ir 1

...

count(113) rezultats ir 1

...

bet count(1) rezultāts man parādās 0

 

Principā vajag ta, lai category.id = category.id_p = category.id = category.id_p = $m ! Kaut kā šādi, bet šis pieraksts nava pareizs!

Edited by reiniger
Link to comment
Share on other sites

Bet pareizi jau arī atgriež. :)

Šis insert into post (id , id_p , name) value ( 1, 115 , kruta maja); ir piesaistīts tikai šim insert into category (id , id_p , title) value (115 , 113 , liela); Līdz ar ko, gan 115, 113 atgriezīs 1 ierakstu.

 

t.i.

šos divus nav kam piejoinot.

insert into category (id , id_p , title) value (1 , 0 , maja);

insert into category (id , id_p , title) value (113 , 1 , riga);

Edited by xPtv45z
Link to comment
Share on other sites

Mans mēģinājums:

SELECT * FROM post JOIN category ON (post.id_p=category.id) RIGHT JOIN category AS cc ON (category.id=category.id) WHERE NOW() >= start_date AND NOW() <= end_date AND (category.id=1 OR category.id_p=1 OR (category.id_p = cc.id AND cc.id_p =1))

 

Šāds select ar funkciju count(1) man atgriež pareizu ierakstu skaitu!

 

BET tagad izpildot count(113) vai count(115) man tiek izdrukādts visu categoriju skaits nevis tikai manu vienu ierakstu!

 

Tad sanāk ka nava iespējams izveidot tādu select lai atlasītu pareizi ar count(1) !

Link to comment
Share on other sites

Atrisināju:

 

funtion count($m){
$result = mysql_query("SELECT * FROM post JOIN category ON (post.id_p=category.id) JOIN category AS cc ON (category.id_p=cc.id) WHERE NOW() >= start_date AND NOW() <= end_date AND (category.id=$m OR category.id_p=$m OR cc.id_p =$m)");
$all_c = mysql_num_rows($result);

print_r($all_c);
}

 

Paldies par palīdzību!

Edited by reiniger
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...