Jump to content

SQL select SUM & For and while loop in a table

MrCostari

By MrCostari
in PHP Coding Help

 Share

Recommended Posts

MrCostari Newbie

MrCostari

Posted
Posted

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

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted

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.

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted

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.

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

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)

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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.");
	}


?>

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted

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.

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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.");
	}


?>

Link to comment
Share on other sites
MrCostari Newbie

MrCostari

Posted
  • Author
Posted

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 :(

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.   Restore formatting

  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.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.