Jump to content


MySql Databse Row Question

Started by KLIMO, Nov 27 2004 10:23 PM

4 replies to this topic

#1 KLIMO

    Young Padawan

  • Members
  • Pip
  • 3 posts

Posted 27 November 2004 - 10:23 PM

Hello,

I am trying to do my sons race points using a mysql database.

I have the necessary rows added, but here is my dilemna:

I have Race1 Race 2 Race 3 etc. in my table however,

How do I add up the points totals from each row to make another row?

Meaning:
I want to add a row called Total to my existing table and have it add the Race 1 Race 2 points etc all together.

I have the databse setup and can insert data and pull data from the database, but I need help in this one last area.

BTW this is a killer site, it has helped me out tremendously. I am at best avaerage at writing PHP code, and I have a general understanding, but this one stumps me.

Here is the code that puts the info in the databse:

<?php
extract ( $_POST );
mysql_connect ( 'localhost', 'user', 'password' );
mysql_select_db( 'db_name');
mysql_query ( "INSERT INTO my_table VALUES ( '$f_name','$l_name','$age', '$city', '$state', '$class', '$race1', '$race2', '$race3', '$race4', '$race5' )" );
mysql_close ();
?>

But how do I add another row that will add Race 1 points , Race2 points, etc together?

#2 Jamie Huskisson

    Retired P2L Staff

  • Members
  • PipPipPipPip
  • 3,648 posts
  • Gender:Male
  • Location:Nottingham, UK

Posted 28 November 2004 - 07:15 AM 

SELECT sum(race1+race2+race3+race4+race5) AS "total_race"
FROM your_table WHERE id='$id'

this will add together all the variables for the current row (given that id is set), its cpu effective and you can just add it in the middle of your display code

so for example your sql would be "select name,city,state,sum(race1+race2+race3+race4+race5) as total_race, race1" etc. etc.

then it can be called using $row['total_race'] (if you've used $row as your mysql select variable

just noticed the question was about inserting - so you can put something like
$race_total = sum($race1+$race2+$race3+$race4+$race5);
mysql_query ( "INSERT INTO my_table VALUES ( '$f_name','$l_name','$age', '$city', '$state', '$class', '$race1', '$race2', '$race3', '$race4', '$race5','$race_total' )" );

hope this helps :)

#3 KLIMO

    Young Padawan

  • Members
  • Pip
  • 3 posts

Posted 28 November 2004 - 08:20 AM

Thanks for the help Jay. However, when I use a form to insert the data into the database it does not work. I deleted the $total out of it and I can add the 5 races.

I think the problem is when I submit the form there is no data to insert into the table in the row $total and thus it causes the problem.


Here is the form I am using (Basic, will make it look better down the road.)

FORM
<html>

<head>

<title>First Name</title>
</head>

<body>

<div align="center">
<center>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="237"

height="491">
<tr>
<form method="POST" action="input2.php">
<td width="73" height="64" align="right">First Name:</td>
<td width="10" height="64">
&nbsp;</td>
<td width="154" height="64">
<input type="text" name="f_name" size="20"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Last Name:</td>
<td width="10" height="65">
&nbsp;</td>
<td width="154" height="65">
<input type="text" name="l_name" size="20"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Age:</td>
<td width="10" height="65">
&nbsp;</td>
<td width="154" height="65">
<input type="text" name="age" size="20"></td>
</tr>
<tr>
<td width="73" height="65" align="right">City:</td>
<td width="10" height="65">
&nbsp;</td>
<td width="154" height="65">
<input type="text" name="city" size="20"></td>
</tr>
<tr>
<td width="73" height="65" align="right">State:</td>
<td width="10" height="65">
&nbsp;</td>
<td width="154" height="65">
<input type="text" name="state" size="20"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Class:</td>
<td width="10" height="65">
&nbsp;</td>
<td width="154" height="65">
<select size="1" name="class">
<option>Class One</option>
<option>Class Two</option>
</select></td>
</tr>
<tr>
<td width="73" height="65" align="right">Race #1</td>
<td width="10" height="65">&nbsp;</td>
<td width="154" height="65">
<input type="text" name="race1" size="5" value="0"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Race #2</td>
<td width="10" height="65">&nbsp;</td>
<td width="154" height="65">
<input type="text" name="race2" size="5" value="0"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Race #3</td>
<td width="10" height="65">&nbsp;</td>
<td width="154" height="65">
<input type="text" name="race3" size="5" value="0"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Race #4</td>
<td width="10" height="65">&nbsp;</td>
<td width="154" height="65">
<input type="text" name="race4" size="5" value="0"></td>
</tr>
<tr>
<td width="73" height="65" align="right">Race #5</td>
<td width="10" height="65">&nbsp;</td>
<td width="154" height="65">
<input type="text" name="race5" size="5" value="0"></td>
</tr>
<tr>
<td width="154" height="65">
</td>
</tr>
<tr>
<td colspan="3" width="237" height="26">
<p align="center">
<input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
</td>
</tr>
</table>
</center>
</div>

<p align="center">&nbsp;</p>
<p align="center">&nbsp;</p>
<p align="center">&nbsp;</p>


</body>

</html>


Here is the input2.php info:

<?php
extract ( $_POST );
mysql_connect ( 'localhost', 'username', 'password' );
mysql_select_db( 'my_database');
$total = sum($race1+$race2+$race3+$race4+$race5);
mysql_query ( "INSERT INTO my_table VALUES ( '$f_name','$l_name','$age', '$city', '$state', '$class', '$race1', '$race2',

'$race3', '$race4', '$race5','$total' )" );
mysql_close ();
?>

Putting thse two together, just doesnt work.

If I remove the $total out of there, it inserts all the other data into the database.

Any suggestions?

(I really appreciate the help)

#4 KLIMO

    Young Padawan

  • Members
  • Pip
  • 3 posts

Posted 28 November 2004 - 08:22 AM

Jay, on Nov 28 2004, 12:15 PM, said:

SELECT sum(race1+race2+race3+race4+race5) AS "total_race"
FROM your_table WHERE id='$id'

this will add together all the variables for the current row (given that id is set), its cpu effective and you can just add it in the middle of your display code
How do I set the 'id" like you say? I am confused on this one. :)

#5 Jamie Huskisson

    Retired P2L Staff

  • Members
  • PipPipPipPip
  • 3,648 posts
  • Gender:Male
  • Location:Nottingham, UK

Posted 28 November 2004 - 10:21 AM

first name sure u've got a field after race5 for the totals...

second most people put a field called "id" to label each row, as there first field for each row so that you can just use that reference number and know it won't change, i was just using that as an example, you can use another variable such as "where name='poo'" or whatever your field names are :)
Back to Programming and General Web Design · Next Unread Topic →


1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users

  1. Pixel2Life Forum
  2. → Help Section
  3. → Programming and General Web Design