SQL select SUM & For and while loop in a table

MrCostari · March 30, 2012

I have 2 question, which are related to each other.

They are about my current school project. Which I have been really stupid for, I took it too slow and thought I had enough time.

But no, I had to do almost my whole project in the last 3 weeks and sunday is the deadline.

I have a couple pages to make left, which one of them is the hardest one and I have the 2 questions about it.

Question 1.

I have to make a invoice with 4 different prices, divided over 3 tables in my mysql.

Let me sum up the tables.

Products

Productsid - productprice

Productrule

Productruleid - amount - productid - maintainanceid

Maintainance

Maintainanceid - price

I need to show the price

The price of each product

The amount * productprice

The amount * productprice + price

All in a table, it mustn't be edited, so it not in a form.

I know I have to use a SQL select sum for it, but I have never used it, and my querys always fail in the first place

I used a for and while loop to choose the amount of and the name of products for the mechanic page.

There was a max of 5 products to select, just to keep it easier.

If I would only pick 2, it wouldn send something empty to the database and it wasn't required.

My teacher helped me with that and it works.

So then we come to question 2, can I use that in a table, instead of a drop down menu, which I used at the mechanic page.

This is the PHP code:

for($i=1;$i<=5;$i++)
			{
				if(($_POST['aantal'.$i] != "") AND ($_POST['product'.$i] != ""))
				{
					$query3 = mysql_query ("INSERT INTO onderdeelregel SET
							aantal='".$_POST['aantal'.$i]."',
							onderdelenid='".$_POST['product'.$i]."',
							werkzaamhedenid='".$werkzaamhedenid."'");	
				}
			}

And the HTML:

<table border="0">
	<tr>
		<th>Aantal</th>
		<th>Product</th>
	<tr>

	<!-- START BLOCK : PRODUCT_REGEL -->
	<tr>
		<td>
			<input class="wijzigInput" name="aantal{NUMMER}" id="aantal" type="text" maxlength="2" size="2" /></td>
		<td>
			<select name="product{NUMMER}">
				<option selected="selected" class="wijzigOption" value=""></option>
			<!-- START BLOCK : PRODUCT_REGEL_OPTION -->
				<option class="wijzigOption" value="{PRODUCT_REGEL_WAARDE}">{PRODUCT_REGEL_NAAM}</option>
			<!-- END BLOCK : PRODUCT_REGEL_OPTION -->
			</select>
		</td>
	</tr>
	<!-- END BLOCK : PRODUCT_REGEL -->
</table>

Oh I didn't used a while loop there, but I know I have to use it in the customer page.

I know this isn't going to work on my own, and I haven't got much time left.

I would really appreciate it if anyone could give me some code, or atleast a start of the sql select sum and the for and while loop in a table.

I know I have add other stuff to the invoice, but that isn't hard, so with this I should be able to do it and finish my project in time.

I usually don't like that, I want to learn it myself, but time is very little right now

Oh and sorry for the Dutch in my code.

Please help me, I don't want to stress out and fail it.

School is very important to me and I hate myself for taking it so slow.

Thanks a lot

Chris

Jessica · March 30, 2012

If I do your homework do I get a gold star on my report card?

You need to post a specific problem and question, with specific code. Your request is too general IMO. If you're that concerned about it, go to your teacher and be honest that you need more time and help.

MrCostari · March 30, 2012

If I do your homework do I get a gold star on my report card?

You need to post a specific problem and question, with specific code. Your request is too general IMO. If you're that concerned about it, go to your teacher and be honest that you need more time and help.

Well, I can't get more time.

And this is my specific problem.

How can I make a SQL select sum with this data and can I put that into a table with a for and while loop?

Jessica · March 30, 2012

How can I make a SQL select sum with this data and can I put that into a table with a for and while loop?

Start with the first part, selecting the data. do you know how to do SQL queries?

MrCostari · March 30, 2012

How can I make a SQL select sum with this data and can I put that into a table with a for and while loop?

Start with the first part, selecting the data. do you know how to do SQL queries?

Yes, I know how to update and select.

I have to do multiple maths in this query, and I don't know how to put that in 1 query. I listed the maths.

Jessica · March 30, 2012

You said in your first post that your queries fail, elaborate.

What fails, what error do you get?

I don't see any select statements in your posted code, only an insert.

MrCostari · March 30, 2012

I don't know how to make it.

I talked about my previous querys.

The code is from another code.

Jessica · March 30, 2012

I just asked if you know how to do select and you said yes. Now you say you don't.

Maybe this is a language barrier issue, but you're being fairly unclear. You really should talk to your teacher, if you're giving us the true story.

MrCostari · March 30, 2012

Select sum is different than select.

And I don't have the time to ask my teacher. Ever heard of school in the weekend?

Have you ever learned to read?

Start helping me instead of b*tching me.

Muddy_Funster · March 30, 2012

What fields are you trying to sum?

from your OP it looks as though all you want is

SELECT (amount*productPrice) as totalPrice, (amount*(productPrice+price))as totalPricePlusMaintainance 
FROM products 
INNER JOIN productrule ON (products.productid = productrule.productid)
INNER JOIN maintainance ON (productrule.maintainanceid = maintainance.maintainanceid)

MrCostari · March 30, 2012

What fields are you trying to sum?

from your OP it looks as though all you want is

SELECT (amount*productPrice) as totalPrice, (amount*(productPrice+price))as totalPricePlusMaintainance 
FROM products 
INNER JOIN productrule ON (products.productid = productrule.productid)
INNER JOIN maintainance ON (productrule.maintainanceid = maintainance.maintainanceid)

Yeah thanks!

But how do I put this selected data in a table?

I use different variables here than in my database.

I have no total price in my database, so I have to do something to assign that.

And what about multiple products and different amounts?

I have to use a for and while loop for that, but I'm not very good with those, haven't used them much.

Thanks again

EDIT: I tried using var_dump to check if the query gives any info, but I got this: bool(false)

This is my code now, Im going for diner now

<?php

$content = new TemplatePower("klant_factuur/klant_factuur.tpl");
$content->prepare();

if($_SESSION['groepenid'] == 1 ) 
{

		$content->newBlock("TABEL_FACTUUR");

		$verkrijg_gegevens = mysql_query("SELECT personen.voornaam, personen.achternaam, accounts.accountsid, events.titel, events.eventsid, 
										events.begindatum, events.einddatum, events.eventstatusid, eventstatus.eventstatusid, 
										eventstatus.eventstatusnaam,
										werkzaamheden.werkzaamhedenid,
										werkzaamheden.werkzaamhedennaam, werkzaamheden.beschrijving
										FROM personen, accounts, events, eventstatus, werkzaamheden
										WHERE accounts.accounts=personen.accountsid
										AND events.eventstatusid=eventstatus.eventstatusid
										AND events.accountsid=accounts.accountsid
										AND events.eventsid=werkzaamheden.eventsid
										AND accounts.accountsid='".$_SESSION['accountsid']."'");

		$verkrijg_totaalprijs = mysql_query("SELECT (aantal*onderdelenprijs) as totaalonderdelenprijs, (aantal*(onderdelenprijs+starttarief))as totaalprijsPluswerkzaamheden 
													FROM onderdelen
													INNER JOIN onderdeelregel ON (onderdelen.onderdelenid = onderdeelregel onderdelenid)
													INNER JOIN werkzaamhedem ON (productrule.maintainanceid = maintainance.maintainanceid");




		if(mysql_num_rows ($verkrijg_gegevens) > 0)
		{
		// alleen de gegevens laten zien als de rijen groter zijn dan 0	
		// anders foutmelding

		}

		$content->newBlock("INFO_FACTUUR");
		$facturen = mysql_fetch_array($verkrijg_gegevens AND $verkrijg_totaalprijs);
		$content->assign(array(
				VOORNAAM => $facturen['voornaam'],
				ACHTERNAAM => $facturen['achternaam'],
				TITEL => $facturen['titel'],		
				BEGINDATUM => $facturen['begindatum'],	
				EINDDATUM => $facturen['einddatum'],
				WERKZAAMHEDENNAAM => $facturen['werkzaamhedennaam'],
				BESCHRIJVING => $facturen['beschrijving'],
				EVENTSTATUSID => $facturen['eventstatusid'],
				STARTTARIEF => $facturen['starttarief'],
				ONDERDELENPRIJS => $facturen['onderdelenprijs'],
				TOTAALONDERDELENPRIJS => $facturen['totaalonderdelenprijs'],
				TOTAALPRIJS => $facturen['totaalprijs']));

				var_dump ($facturen);



}
		else
	{
		$content->newBlock("ERROR_TOESTEMMING");
		$content->assign("ERROR_TOESTEMMING", "U heeft geen toestemming om deze pagina te bezoeken.");
	}


?>

Jessica · March 30, 2012

Select sum is different than select.

And I don't have the time to ask my teacher. Ever heard of school in the weekend?

Have you ever learned to read?

Start helping me instead of b*tching me.

Considering these forums are not supposed to be used to get your homework coded, perhaps you should take your own advice regarding reading. The forum rules also speak about asking for work to be done urgently.

Demanding help and calling names is probably the *least* effective way to actually get help.

MrCostari · March 30, 2012

Well, I'm sorry then.

I ask you polity to not read or reply to topic anymore, unless you can be of help.

Thank you for your understanding.

MrCostari · March 30, 2012

Well, I sure make too many typos in the evening.

Updated code:

<?php

$content = new TemplatePower("klant_factuur/klant_factuur.tpl");
$content->prepare();

if($_SESSION['groepenid'] == 1 AND (isset($_SESSION['accountsid'])))
{

		$content->newBlock("TABEL_FACTUUR");

		$verkrijg_gegevens = mysql_query("SELECT personen.voornaam, personen.achternaam, accounts.accountsid, events.titel, events.eventsid, 
										events.begindatum, events.einddatum, events.eventstatusid, eventstatus.eventstatusid, 
										eventstatus.eventstatusnaam,
										werkzaamheden.werkzaamhedenid,
										werkzaamheden.werkzaamhedennaam, werkzaamheden.beschrijving
										FROM personen, accounts, events, eventstatus, werkzaamheden
										WHERE accounts.accountsid=personen.accountsid
										AND events.eventstatusid=eventstatus.eventstatusid
										AND events.accountsid=accounts.accountsid
										AND events.eventsid=werkzaamheden.eventsid
										AND accounts.accountsid='".$_SESSION['accountsid']."'");

		for($i=1;$i<=5;$i++)
				{
						$content->assign("NUMMER", $i);

		$verkrijg_totaalprijs = mysql_query("SELECT (aantal*onderdelenprijs) as totaalonderdelenprijs, (aantal*(onderdelenprijs+starttarief))as totaalprijsPluswerkzaamheden 
													FROM onderdelen
													INNER JOIN onderdeelregel ON (onderdelen.onderdelenid = onderdeelregel onderdelenid)
													INNER JOIN werkzaamheden ON (onderdeelregelid.werkzaamhedenid = werkzaamheden.werkzaamhedenid");

					while($onderdelen = mysql_fetch_array($verkrijg_totaalprijs))
					{
						$content->assign("NUMMER", $i);
						$content->assign("ONDERDELENID", $onderdelen['onderdelenid']);
						$content->assign("ONDERDELENNAAM", $onderdelen['onderdelennaam']);
					}
				}


		if(mysql_num_rows ($verkrijg_gegevens) > 0)
		{
		// alleen de gegevens laten zien als de rijen groter zijn dan 0	
		// anders foutmelding

		}

		$content->newBlock("INFO_FACTUUR");
		$facturen = mysql_fetch_array($verkrijg_gegevens);
		$content->assign(array(
				VOORNAAM => $facturen['voornaam'],
				ACHTERNAAM => $facturen['achternaam'],
				TITEL => $facturen['titel'],		
				BEGINDATUM => $facturen['begindatum'],	
				EINDDATUM => $facturen['einddatum'],
				WERKZAAMHEDENNAAM => $facturen['werkzaamhedennaam'],
				BESCHRIJVING => $facturen['beschrijving'],
				EVENTSTATUSID => $facturen['eventstatusid'],
				STARTTARIEF => $facturen['starttarief'],
				ONDERDELENNAAM => $facturen['onderdelennaam'],
				ONDERDELENPRIJS => $facturen['onderdelenprijs'],
				TOTAALONDERDELENPRIJS => $facturen['totaalonderdelenprijs'],
				TOTAALPRIJS => $facturen['totaalprijs']));



}
		else
	{
		$content->newBlock("ERROR_TOESTEMMING");
		$content->assign("ERROR_TOESTEMMING", "U heeft geen toestemming om deze pagina te bezoeken.");
	}


?>

It fetches most of the info, but not the sum info.

I tried using AND $verkrijg_totaalprijs, but that didn't worked, so I removed it and then I got this.

MrCostari · March 31, 2012

Well, I still can't get my select sum to work. I have no idea how to do it and I would really appreciate some help.

This is my code so far:

<?php

$content = new TemplatePower("klant_factuur/klant_factuur.tpl");
$content->prepare();

if($_SESSION['groepenid'] == 1 AND (isset($_SESSION['accountsid'])))
{

		$content->newBlock("TABEL_FACTUUR");

		$verkrijg_gegevens = mysql_query("SELECT personen.voornaam, personen.achternaam, accounts.accountsid, events.titel, events.eventsid, 
										events.begindatum, events.einddatum, events.eventstatusid, eventstatus.eventstatusid, 
										eventstatus.eventstatusnaam, werkzaamheden.starttarief,
										werkzaamheden.werkzaamhedenid,
										werkzaamheden.werkzaamhedennaam, werkzaamheden.beschrijving
										FROM personen, accounts, events, eventstatus, werkzaamheden
										WHERE accounts.accountsid=personen.accountsid
										AND events.eventstatusid=eventstatus.eventstatusid
										AND events.accountsid=accounts.accountsid
										AND events.eventsid=werkzaamheden.eventsid
										AND accounts.accountsid='".$_SESSION['accountsid']."'");

		for($i=1;$i<=5;$i++)
				{

						$content->assign("NUMMER", $i);

		$verkrijg_totaalprijs = mysql_query("SELECT (aantal*onderdelenprijs) as totaalonderdelenprijs, (aantal*(onderdelenprijs+starttarief))as totaalprijsPluswerkzaamheden 
													FROM onderdelen
													INNER JOIN onderdeelregel ON (onderdelen.onderdelenid=onderdeelregel.onderdelenid)
													INNER JOIN werkzaamheden ON (onderdeelregelid.werkzaamhedenid=werkzaamheden.werkzaamhedenid");

					while($onderdelen = mysql_fetch_array($verkrijg_totaalprijs))
					{
						$content->assign("NUMMER", $i);
						$content->assign("ONDERDELENID", $onderdelen['onderdelenid']);
						$content->assign("ONDERDELENNAAM", $onderdelen['onderdelennaam']);
					}
				}


		if(mysql_num_rows ($verkrijg_gegevens) > 0)
		{
		// alleen de gegevens laten zien als de rijen groter zijn dan 0	
		// anders foutmelding

		}

		$content->newBlock("INFO_FACTUUR");
		$facturen = mysql_fetch_array($verkrijg_gegevens);
		$content->assign(array(
				VOORNAAM => $facturen['voornaam'],
				ACHTERNAAM => $facturen['achternaam'],
				TITEL => $facturen['titel'],		
				BEGINDATUM => $facturen['begindatum'],	
				EINDDATUM => $facturen['einddatum'],
				WERKZAAMHEDENNAAM => $facturen['werkzaamhedennaam'],
				BESCHRIJVING => $facturen['beschrijving'],
				EVENTSTATUSID => $facturen['eventstatusid'],
				STARTTARIEF => $facturen['starttarief'],
				NUMMER => $onderdelen['nummer'],
				ONDERDELENID => $onderdelen['onderdelenid'],
				ONDERDELENNAAM => $onderdelen['onderdelennaam'],
				ONDERDELENPRIJS => $facturen['onderdelenprijs'],
				TOTAALONDERDELENPRIJS => $facturen['totaalonderdelenprijs'],
				TOTAALPRIJS => $facturen['totaalprijs']));



}
		else
	{
		$content->newBlock("ERROR_TOESTEMMING");
		$content->assign("ERROR_TOESTEMMING", "U heeft geen toestemming om deze pagina te bezoeken.");
	}


?>

MrCostari · March 31, 2012

Well, I get info now, but it's not the right maths I want.

This is what I get on my page:

Onderdelennaam Onderdelenprijs Totaalonderdelenprijs Totaalprijs Aantal

9.99 29.98 1

49.95 169.95 1

19.98 27.98 1

5.98 57.96 1

2.99 22.98 1

17.94 161.94 1

11.96 107.96 1

8.97 20.97 1

37.98 55.96 1

18.99 44.98 1

Onderdelennaam Onderdelenprijs Totaalonderdelenprijs Totaalprijs Aantal

9.99 29.98 2

49.95 169.95 2

19.98 27.98 2

5.98 57.96 2

2.99 22.98 2

17.94 161.94 2

11.96 107.96 2

8.97 20.97 2

37.98 55.96 2

18.99 44.98 2

Onderdelennaam Onderdelenprijs Totaalonderdelenprijs Totaalprijs Aantal

9.99 29.98 3

49.95 169.95 3

19.98 27.98 3

5.98 57.96 3

2.99 22.98 3

17.94 161.94 3

11.96 107.96 3

8.97 20.97 3

37.98 55.96 3

18.99 44.98 3

Onderdelennaam Onderdelenprijs Totaalonderdelenprijs Totaalprijs Aantal

9.99 29.98 4

49.95 169.95 4

19.98 27.98 4

5.98 57.96 4

2.99 22.98 4

17.94 161.94 4

11.96 107.96 4

8.97 20.97 4

37.98 55.96 4

18.99 44.98 4

Copyright © Loodgieter B.V. 2011 - 2012

Onderdelennaam Onderdelenprijs Totaalonderdelenprijs Totaalprijs Aantal

9.99 29.98 5

49.95 169.95 5

19.98 27.98 5

5.98 57.96 5

2.99 22.98 5

17.94 161.94 5

11.96 107.96 5

8.97 20.97 5

37.98 55.96 5

18.99 44.98 5

With this PHP code:

<?php

$content = new TemplatePower("klant_factuur/klant_factuur.tpl");
$content->prepare();

if($_SESSION['groepenid'] == 1 AND (isset($_SESSION['accountsid'])))
{

		$content->newBlock("TABEL_FACTUUR");

		$verkrijg_gegevens = mysql_query("SELECT personen.voornaam, personen.achternaam, accounts.accountsid, events.titel, events.eventsid, 
										events.begindatum, events.einddatum, events.eventstatusid, eventstatus.eventstatusid, 
										eventstatus.eventstatusnaam, werkzaamheden.starttarief,
										werkzaamheden.werkzaamhedenid,
										werkzaamheden.werkzaamhedennaam, werkzaamheden.beschrijving
										FROM personen, accounts, events, eventstatus, werkzaamheden
										WHERE accounts.accountsid=personen.accountsid
										AND events.eventstatusid=eventstatus.eventstatusid
										AND events.accountsid=accounts.accountsid
										AND events.eventsid=werkzaamheden.eventsid
										AND accounts.accountsid='".$_SESSION['accountsid']."'");

		for($i=1;$i<=5;$i++)
				{
						$content->newBlock("TABEL_PRIJZEN");
						$content->assign("AANTAL", $i);

		$verkrijg_totaalprijs = mysql_query("SELECT (aantal*onderdelenprijs) as totaalonderdelenprijs, (aantal*(onderdelenprijs+starttarief))as totaalprijs
													FROM onderdelen, onderdeelregel, werkzaamheden, events, accounts
													WHERE onderdelen.onderdelenid=onderdeelregel.onderdelenid
													AND events.eventsid=werkzaamheden.eventsid
													AND events.accountsid=accounts.accountsid
													AND onderdeelregel.werkzaamhedenid=werkzaamheden.werkzaamhedenid");


					while($onderdelen = mysql_fetch_array($verkrijg_totaalprijs))
					{
						$content->newBlock("INFO_PRIJZEN");
						$content->assign("AANTAL", $i);
						$content->assign("ONDERDELENID", $onderdelen['onderdelenid']);
						$content->assign("ONDERDELENNAAM", $onderdelen['onderdelennaam']);
						$content->assign("ONDERDELENPRIJS", $onderdelen['onderdelenprijs']);
						$content->assign("TOTAALONDERDELENPRIJS", $onderdelen['totaalonderdelenprijs']);
						$content->assign("TOTAALPRIJS", $onderdelen['totaalprijs']);

					}
				}


		if(mysql_num_rows ($verkrijg_gegevens) > 0)
		{
		// alleen de gegevens laten zien als de rijen groter zijn dan 0	
		// anders foutmelding

		}

		$content->newBlock("INFO_FACTUUR");
		$facturen = mysql_fetch_array($verkrijg_gegevens);
		$content->assign(array(
				VOORNAAM => $facturen['voornaam'],
				ACHTERNAAM => $facturen['achternaam'],
				TITEL => $facturen['titel'],		
				BEGINDATUM => $facturen['begindatum'],	
				EINDDATUM => $facturen['einddatum'],
				WERKZAAMHEDENNAAM => $facturen['werkzaamhedennaam'],
				BESCHRIJVING => $facturen['beschrijving'],
				EVENTSTATUSID => $facturen['eventstatusid'],
				STARTTARIEF => $facturen['starttarief']));



}
		else
	{
		$content->newBlock("ERROR_TOESTEMMING");
		$content->assign("ERROR_TOESTEMMING", "U heeft geen toestemming om deze pagina te bezoeken.");
	}


?>

And this HTML code:

<div>

<!-- START BLOCK : TABEL_FACTUUR -->

<table>
<tr>
	<th>Voornaam</th>
	<th>Achternaam</th>
	<th>Titel</th>
	<th>Begindatum</th>
	<th>Einddatum</th>
	<th>Werkzaamheden naam</th>
	<th>Beschrijving</th>
	<th>Eventstatus</th>
	<th>Starttarief</th>
</tr>

<!-- START BLOCK : INFO_FACTUUR -->
<tr>
	<td>{VOORNAAM}</td>
	<td>{ACHTERNAAM}</td>
	<td>{TITEL}</td>
	<td>{BEGINDATUM}</td>
	<td>{EINDDATUM}</td>
	<td>{WERKZAAMHEDENNAAM}</td>
	<td>{BESCHRIJVING}</td>
	<td>{EVENTSTATUSID}</td>
	<td>{STARTTARIEF}</td>
</tr>
<!-- END BLOCK : INFO_FACTUUR -->
</table>
<!-- END BLOCK : TABEL_FACTUUR -->

<!-- START BLOCK : TABEL_PRIJZEN -->

<table>
<tr>
	<th>Onderdelennaam</th>
	<th>Onderdelenprijs</th>
	<th>Totaalonderdelenprijs</th>
	<th>Totaalprijs</th>
	<th>Aantal</th>
</tr>

<!-- START BLOCK : INFO_PRIJZEN -->

<tr>
	<td>{ONDERDELENNAAM}</td>
	<td>{ONDERDELENPRIJS}</td>
	<td>{TOTAALONDERDELENPRIJS}</td>
	<td>{TOTAALPRIJS}</td>
	<td>{AANTAL}</td>
</tr>

<!-- END BLOCK : INFO_PRIJZEN -->
</tabel>
<!-- END BLOCK : TABEL_PRIJZEN -->










</div>

Please help me, Im worried

Muddy_Funster · March 31, 2012

OK, break down your tables and info accurately and fully for me so we're both working off the same page. Also, an exact and verbose description of what you are looking for as an end result with as much detail as you can manage will help a lot.

Once I have a better idea of your starting info and your desired output we can better work together in getting this resolved.

Sign In

SQL select SUM & For and while loop in a table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information