| Autor | Zpráva | ||
|---|---|---|---|
| branor Profil * |
#1 · Zasláno: 16. 7. 2013, 08:37:57
Ahojte, velmi prosim o pomoc, v databazach nie som az taky odbornik, ale obcas sa mi nieco podari.
Mam 2 tabulky. Jedna sa vola "people" a obsahuje zoznam ludi v stlpcoch "name" a "people_id" Druha sa vola "polozky" a obsahuje zaznamy v stlpcoch "people_id" a "kusy" teda obe tabulky maju zhodny jeden stlpec "people_id" v druhej tabulke su zaznamy, ktore obsahuju viacnasobne v stlpci "people_id" rovnake polozky Ulohou je vypisat zoznam ludi a pri kazdom vypisat sucet hodnot zo stlpca "kusy" len z riadkov ktore obsahuju jeho konkretne "people_id" Tabulka "people" name people_id Matus 1122 Tibor 6688 Tabulka "polozky" people_id kusy 1122______4 6688______2 1122______3 1122______2 1122______5 6688______2 Vysledok KTORY POTREBUJEM VRATIT Matus 14 Tibor 4 Dakujem pekne |
||
| Kajman Profil |
#2 · Zasláno: 16. 7. 2013, 09:22:52
Budete potřebovat spojení tabulek (join) se správnou podmínkou, seskupení záznamů (group by) a agregační funkci (sum)
SELECT pe.name,
Coalesce(Sum(po.kusy), 0) kusy
FROM people pe
LEFT JOIN polozky po
ON pe.people_id = po.people_id
GROUP BY pe.people_id,
pe.name |
||
| branor Profil * |
#3 · Zasláno: 16. 7. 2013, 13:52:21
Dakujem, upravil som si dany select na moje veci, ale priznam sa ze mam problem vysledok zaradit do vypisu k inym vypisom z tabuliek -selekt ako taky funguje vyborne to som skusal:
$query_vypocet = MySQL_Query("SELECT
Coalesce(Sum(po.pocet), 0) pocet
FROM peoples pe
LEFT JOIN cards_entries po
ON pe.people_id = po.people_id
GROUP BY pe.people_id");
$vypocet = mysql_fetch_array($query_vypocet);komplet vypis PHP v ktorom to potrebujem - jedna sa o polozku vypocet <table width="100%" border="0" cellspacing="1">
<tr>
<td class="thead">Priezvisko</td>
<td class="thead">Meno</td>
<td class="thead">Počet</td>
<td class="thead">Poznámka</td>
<td class="thead">Storno</td>
</tr>
<?php
$sql_where = "surname LIKE '%".$_REQUEST['surname']."%' ";
$sql_where .= " AND name LIKE '%".$_REQUEST['name']."%' ";
$sql_where .= " AND city LIKE '%".$_REQUEST['city']."%' ";
$sql_where .= " AND annotation LIKE '%".$_REQUEST['annotation']."%' ";
$sql_where .= " AND storno=".$_REQUEST['storno']." ";
if(empty($_REQUEST['actual_from'])) $_REQUEST['actual_from']="0";
if(empty($_REQUEST['rows_per_page'])) $_REQUEST['rows_per_page']="50";
if(empty($_REQUEST['pocet_listkov'])) $_REQUEST['pocet_listkov']="0";
$query = MySQL_Query("SELECT * FROM peoples WHERE $sql_where ORDER BY surname LIMIT ".$_REQUEST['actual_from'].",".$_REQUEST['rows_per_page']." ");
$query_vypocet = MySQL_Query("SELECT
Coalesce(Sum(po.pocet), 0) pocet
FROM peoples pe
LEFT JOIN cards_entries po
ON pe.people_id = po.people_id
GROUP BY pe.people_id");
$vypocet = mysql_fetch_array($query_vypocet);
$query_rows_count = MySQL_Query("SELECT * FROM peoples WHERE $sql_where ");
$rows_count = MySQL_Num_Rows($query_rows_count);
echo MySQL_Error();
$i=0;
while($result = mysql_fetch_array($query)){
if(($i%2)==1) {$bgclass="bg";}else{$bgclass="tvalue";};
?>
<tr>
<td class="<?php echo $bgclass?>"><?php echo $result['surname']?></td>
<td class="<?php echo $bgclass?>"><?php echo $result['name']?></td>
<td class="<?php echo $bgclass?>"><?php echo $result['vypocet']?></td>
<td class="<?php echo $bgclass?>"><?php echo $result['annotation']?></td>
<td class="<?php echo $bgclass?>"><?php echo select_yes("storno","view",$_REQUEST['storno'])?></td>
</tr>
<?php
$i++;
}
?>
</table> |
||
| branor Profil * |
#4 · Zasláno: 16. 7. 2013, 16:06:21
chyba je predpokladam v tom, ze sa mi biju selekty query a query_vypocet a neni spravne vytiahnuta vysledna hodnota, mohol by sa prosim na to niekto kuknut, ako tie 2 selekty zlucit? vobec netusim ako to spravne napisat...
DAKUJEM <?php
$sql_where = "surname LIKE '%".$_REQUEST['surname']."%' ";
$sql_where .= " AND name LIKE '%".$_REQUEST['name']."%' ";
$sql_where .= " AND city LIKE '%".$_REQUEST['city']."%' ";
$sql_where .= " AND annotation LIKE '%".$_REQUEST['annotation']."%' ";
$sql_where .= " AND storno=".$_REQUEST['storno']." ";
if(empty($_REQUEST['actual_from'])) $_REQUEST['actual_from']="0";
if(empty($_REQUEST['rows_per_page'])) $_REQUEST['rows_per_page']="50";
$query = MySQL_Query("SELECT * FROM peoples WHERE $sql_where ORDER BY surname LIMIT ".$_REQUEST['actual_from'].",".$_REQUEST['rows_per_page']." ");
$query_vypocet = MySQL_Query("SELECT Coalesce(Sum(po.pocet), 0) pocet FROM peoples pe LEFT JOIN cards_entries po ON pe.people_id = po.people_id GROUP BY pe.people_id");
$query_rows_count = MySQL_Query("SELECT * FROM peoples WHERE $sql_where ");
$rows_count = MySQL_Num_Rows($query_rows_count);
echo MySQL_Error();
$i=0;
while($result = mysql_fetch_array($query)){
if(($i%2)==1) {$bgclass="bg";}else{$bgclass="tvalue";};
?> |
||
|
Časová prodleva: 11 let
|
|||
0