Thread: Calculations from MySQL

Hi folks,

I have managed to get information into tables in MySQL.

I know need to do certain thisgs and am in request of help.

1. I want to type a number into a cell and have it stored in a table (MySQL)
2. There are 18 such numbers inntiallly which have to be added together to give a total and this toal is to appear on the page that is being veiwed as well as stored in the table.

this might seem like a simple task but my experiance has shown that it is often the small things that take the most time.

I look forward to your replies and interaction

Hi,

I can probaly better explain this by looking at page www.gnjgf.co.za/score.html

This calculation might be also best done using a from rather than building it from scratch.

All advice would be welcome

You will need to write a specific Javascript for this. Javascript may be even more difficult than php as it is harder to debug.

Hi George,

As usual always first to answer.

It seems like one step forward ten steps back.

Do you have any suggestions where I can find any examples - I have googled till my eyes are now square in search.

Examples give me an idea wher eI can start playing and learning with it

Hi George,

I thought of another method -maybe this could work.

Create the blocks in a room to receive the inputs - then submit to a table.

In the table do something like this:

<? $db = mysql_connect('localhost', 'username', 'password');

mysql_select_db('database_name');

$query = 'select * from this_table';
$query2 = 'select SUM(titles) AS qsum from this_table';
$result = mysql_query($query);
$result2 = mysql_query($query2);
$num = mysql_num_rows($result);

$row = mysql_fetch_array($result2);

// echo '<strong>'.$num.'</strong>';

echo '<p>Here are your results: ';

echo '<strong>'.$row['qsum'].'</strong>'; ?>


Except that this php would be a little more involved in calculating the totals.

This information could then also be added to the same table.

Once this process is complete it can be displayed in html on another page showing the results retreived from the table.

I hope this makes sense to you and look forward to reply

There are different ways of doing it. If you don't mind having separate pages you can easily do it with PHP.
PHI is certainly easier.

Hi George,

My thoughts exactly.

I thought of splitting the screen.

One side showing the inputs and the opposite side reflecting the results.

You guys are the experts and would be able to best advise.

I am currently trying to have a look at doing it with ABVFP - to create the first form but cant remebr the login details.

I hope you can help in this regard as well.

Thanks again for all your help George - I appreciate it

Hi George,

I can post an example of where I want to go an dthis can be seen at http://uploads.saga.co.za/rankings/rateindx.htm

Is it possible to populate the combobox's with information from MySQl tables?

Sure you can populate the comboboxes with values taken from the database. Before i can give you any code i need to know if youuse BV for the visuals, or you hand code them.

Hi George,

Thanks for the replies.

Three things:

1. This will be my first BV forms as you did the previous two.
2. Suggestions in this regard would be welcome
3. Please give me some guides as to how I can populate the comboboxs form MySql tables?

$query = mysql_query('query string');
echo '<select multiple="multiple" name="name">';
while($row = mysql_fetch_assoc($query)
{
echo '<option value="'.$row['column_val'].'">'.$row['column_label'].'</option>';
}
echo '</select>';

The green is the query string
The blue is what the submitted data value should be
The red is the label that will appear in the combobox

I don't know how to integrate this into BV as I don't have it install to do any testing, but all you should have to change is removing the echo before and after the while loop (remove the select tag), and insert it into the html of a combobox in BV.

To populate a BV combobox, right click it and select HTML, then paste this in the Inside Tag:


><?$query = mysql_query('query string');
while($row = mysql_fetch_assoc($query)
{
echo '<option value="'.$row['column_val'].'">'.$row['column_label'].'</option>';
}
?

Note the starting > and the missing last >

I usually prefer (unless it is a VERY long array) to perform all queries in the Start of Page, and store the values retrieved from the database in a local array (in example, if the dropdown is for names, i store the values in an array called "$names) and then echo the values only:

><?
for ($i=0 ; $i<count($names); $i++)
{
echo '<option value="'.stripslashes($name[$i]).'">'.stripslashes($name[$i]).'</option>
';
}
?

Please also note that if you want to make multiple choices, apart from checking the relevant checkbox (Watdaflip has added it using the word "multiple") you must also add square brackets [] after the checkbox name.

However, if you are using ABVFP to populate the database, it will receive the multiple values and combine them into a string like name1, name2, name3 etc.

Hi George,

thanks for the info re - populating the combo box.

I have inderted as requested but am not succesful.

It proably has something to do with the changing of names in the documentation sent

Watdafip instruction re: colour confuese me a bit as I do not know whta he saying -maybe you scan shed some light on this

Did you first create the connection ? Did you actually create the query string ? My instructions (as well as those by Watdaflip) don't take care of that part as we can't know details and what exactly your query should be. So we only took it from that point forward.

Hi George,

I have put the connection side in the html section ahead of you script.

If that makes sense?

$connect = mysql_connect("localhost", "******", "*********") or
die ("Hey loser, check your server connection.");
mysql_select_db("******a_Members");
$quey1="select * from _Form_Nr_3";
$result=mysql_query($quey1) or die(mysql_error());
?>
><?$query = mysql_query('query string');
while($row = mysql_fetch_assoc($query)
{
echo '<option value="'.$row['column_val'].'">'.$row['column_label'].'</option>';
}
?
>
</select>

Since the query string is $quey1, it should be


><?$query = mysql_query($quey1');
etc........

Hi George,

Thank you - made the change but no difference after publishing the page

Rob, you DON'T have "column_val" and "column_label" in your table!
This was just an example. You need to customize the query to the REAL column names that you have.
If, in example, the real column is "name", the code should be:

<?$query = mysql_query($quey1);
while($row = mysql_fetch_assoc($query))
{
echo '<option value="'.stripslashes($row['name']).'">'.stripslashes($row['name']).'</option>
';
}
?

So replace them with the actual fields of your table and try again.

Hi George,

Please excuse my ignorance - I dont know the language at all.

I do understand then that where I have the word column_label I must replace with the field name from my table.

As fro the Column_val I am at a loss.

I will persevere with a trial and error method - thanks fro you support

In many cases these are indeed separate columns in the database. In your case, it is the same. If the table column is "name" then you put "name" in both
If you have two columns (first_name and last_name, the code in the combobox html should become:

<?
$connect = mysql_connect("localhost", "******", "*********") or
die ("Hey loser, check your server connection.");
mysql_select_db("******a_Members", $connect);
$quey1="select * from _Form_Nr_3";
?>

in the Before Tag, and

><?
$query = mysql_query($quey1);
while($row = mysql_fetch_assoc($query))
{
echo '<option value="'.stripslashes($row['first_name']).' '.stripslashes($row['last_name']).'">'.stripslashes($row['first_name']).' '.stripslashes($row['last_name']).'</option>
';
}
?

In the Inside Tag

Hi George,

I dont know what I am doing wrong.

I have done what you asked but when trying to publish a Php page it comes out blank.

The result having done the other changes comes out like this page found at www.gnjgf.co.za/rank.html

There was a syntax error in the code, copy / paste again (I edited it)

Hi George,

Sorry to say this but it doesnt seem to help.
I have copied and pasted as requested but to no avail.

You may still veiw the cahnge at www.gnjgf.co.za/rank.php

Hi Rob,

obviously there is some syntax error. Since i don't see any in the code i posted, it must be somewhere else. Unfortunately php code is not visible in your pages, so i can't say what you might have done wrong.

Hi George,

I have made the changes to myt page which can be veiwed at www.gnjgf.co.za/rankings.html

I have tried to produce the same page with php extension but it gives me a blank page - dont know how that happens when I only change one button.

Be that as it may - maybe we can know see what needs to be changed in order for it to work

From what i see, your database username is wrong.
Also there is a syntax error (which causes the blank page: you have a line return after the final ? in the code i gave you. There should be nothing at all after the ?, so with the autogenerated ">" you should see in the page html:

?>


Now, instead, there is

?
>

Hi George,

Thanks for the reply.

I have made the changes you requested.

I have used the database you created *****_Members and am looking to utilise data from form Nr 2.

My concern is that when I publish the page for the php option it still comes up blank- however the prveiw looks fine just it doesnt preform the tasks we require to populate the combobox.

Hi Rob,

Ok, this is going too long both for you and me. Pls send me again your login details as well as your page, i will fix one dropdown for you, and you can then follow the same for the other dropdowns.

Hi George,

Thanks for the resque and your patience.

I will send the info you require to your email address if thats ok with you?

Page sent, pls read my comments.

Hi George,

Thanks for the work and time spent again - most appreciated.

With the "Combobox" now populating I can hopefully get it to the next phase of my plan which I will post to a new link for fear of creating confussion.

hi George,

You sent me a script once that allows a combo box to populate using the data in mysql.

Is it possible to resend?