Search the Community

Hello. I'm trying to get this piece of code finished but it's not going my way, if anyone could help me out that would be great. Here whats I'm trying to do: Table1 Table2 ---------------- --------------------------------- ID | | PlayerID | PlayerName | ---------------- -------------------------------- 1393 | | 1393 | Player1 | 2097 | | 2097 | Player2 | 3888 | | 3888 | Player3 | 3888 | | 4017 | Player4 | 3888 | --------------------------------- 4017 | 4017 | 4017 | ---------------- I Want to Count the entries in Table1 (3888 has 3 entries so it will display like "3888: 3") Then I want to join Table1 and Table2 using the ID so I can get the players name (3888=Player3 so it would display like Player3 : 3) Here's the code I'm using: <?php //connect to db $query = "SELECT * FROM Table1 INNER JOIN Table2 WHERE Table1.PlayerID = Table2.ID"; $query2 = "SELECT ID, SUM(ID=0) AS n0, SUM(ID=1) AS n1, COUNT(*) AS total FROM Table1 GROUP BY ID"; $result = mysql_query($query) or die($query."<br/><br/>".mysql_error()); $result2 = mysql_query($query2) or die($query2."<br/><br/>".mysql_error()); echo "<table border='1'> <tr> <th>PlayerName</th> <th>Entries</th> </tr>"; while($row2 = mysql_fetch_array($result2)) while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['ID'] . "</td>"; echo "<td>" . $row['PlayerName'] . "</td>"; echo "<td>" . $row2['Total'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($link); ?> If I use $query on its own it joins perfectly, If I use $query2 on its own, it displays the results exactly how I want them listed but with the ID instead of the PlayerName, I tried putting them both together as shown above but I can't get them to work together. What I want my end Result to be Player1 1 Player2 1 Player3 3 Player4 3 How it keeps coming out; 1393 1 2097 1 3888 3 4017 3 Can anyone see where I'm going wrong? Thank you!!

Hi! I got this small irritating problem here, how should I use group by? I am joining a lot of tables here and this SQL is used in a webshop. Okey, the question: I want to get all images from customer_img connected to each product. in this example, I got one product with a image in customer_img connected to it AND I got one product with 2 images in customer_img connected to it. But when I run this query, I only get one image each to those products, when I am supposed to get 1 image to the first product and 2 images to the other product. I think the problem is that I use wrong column in group BY, but I don't know. SEE IMAGE SELECT products.id AS id, products.vat, products.outprice AS price, products.weight, products.imgupload, products_img.imgurl AS pimgurl, `customer_products`.`number`, `products_lang`.`title`, `customer_img`.`imgurl`, `customer_products`.`number`, count(customer_img.id) AS countimg, (`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150) AS total, round( ((`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150)) * (1 - products.vat / 100)) AS brutto, round( ((`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150)) * (products.vat / 100)) AS countvat FROM (`customer_products`) JOIN `products` ON `products`.`id` = `customer_products`.`product` LEFT JOIN `products_lang` ON `products_lang`.`product` = `customer_products`.`product` AND products_lang.language = ? LEFT JOIN products_img ON products_img.id = products.firstimg LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product` WHERE `customer_products`.`customer` = ? GROUP BY customer_products.product

I would like to solve this query. Query: $sql = 'SELECT DISTINCT *, c.ci as c_i FROM `GSM_cellule` c '. 'LEFT JOIN '.$table_freq.' f ON(c.nidtint=f.nidtint) '. 'WHERE c.NAP_'.$site->ur().' =\'1\' '. $this->_in.' and '.$this->_inetatbdespec.' and '.$this->_inindus.' GROUP BY c.nidtint ORDER BY c.nidtint '; Error: Thanks in advanced.

I have the query below which works great. Alls I want to do is all the projects from the query grouped by the description. $query_rs_installs = "SELECT Calls.Call_Ref, Calls.Link_to_Contract_Header, Calls.Order_No, Calls.Date_Received, Calls.Scheduled_Date_Time, Clients.Co_Name, Clients.Post_Code, LU_Call_Types.Call_Type_Description, LU_Call_Types.Type_Band, LU_Call_Status.Call_Status_Description, LU_Company_Types.Company_Type_DescriptionFROM { oj (((Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON Calls.Call_Type = LU_Call_Types.Call_Type_Code) INNER JOIN Siclops_Dispense.dbo.LU_Call_Status LU_Call_Status ON Calls.Last_Event_Status = LU_Call_Status.Call_Status_Code) INNER JOIN Siclops_Dispense.dbo.Clients Clients ON Calls.Link_to_Client = Clients.Client_Ref) LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON Clients.Company_Type = LU_Company_Types.Company_Type_Code}WHERE Calls.Link_to_Contract_Header = '".$row_rs_member['companyident']."' AND (LU_Call_Types.Type_Band = 'Project' OR LU_Call_Types.Type_Band = 'Project Complete' OR LU_Call_Types.Type_Band = 'Project Invoiced') AND (LU_Call_Status.Call_Status_Description = 'Reported Done' OR LU_Call_Status.Call_Status_Description = 'PTF Rep Done' OR LU_Call_Status.Call_Status_Description = 'Proforma Sent' OR LU_Call_Status.Call_Status_Description = 'Paperwork Recvd' OR LU_Call_Status.Call_Status_Description = 'In Query' OR LU_Call_Status.Call_Status_Description = 'Cryo PW Sent' OR LU_Call_Status.Call_Status_Description = 'Complete' OR LU_Call_Status.Call_Status_Description = 'Awaiting P/work' OR LU_Call_Status.Call_Status_Description = 'Awaiting Invoic' OR LU_Call_Status.Call_Status_Description = 'Await TB Return' OR LU_Call_Status.Call_Status_Description = 'ApplicationSent')GROUP BY LU_Call_Status.Call_Status_Description"; Now before the group by the query works fine. As soon as i write it in the query fails completely. I have also tried selecting distinct and unique on the same field but each time I write them in it fails. I believe this maybe because this is an MS SQL query and as such I may have the syntax wrong. Any ideas? Thanks guys