[SOLVED] MySQL on the fly

[tdors]

Good Morning,

 

I’ve been searching and toying with this solution for some time but can’t find that right answer.  Looking for solutions/suggestions to the following:

 

I created a program that will allow people to track sales and depending on what they’ve sold, they will either get a set bonus, or a bonus based on a percentage.  I have no problem creating a drop down box for them to select from, and I could probably create a select button that would do the action, but I’d rather have it in one step.  So what I’m trying to do is somehow when they select from the drop down (via a JavaScript onchange or something) I’d like it to pull data from the MySQL database and then update a locked text box that shows the $ amount.  I’d like it to pull the amount and then each or per based on the ID of what they’ve selected from the drop down.  If it was always based on each sale or based on a percentage, that would be easy as I could code that into the drop box, but I’d like to keep it based on the ID so that it can pull out of the database the name, and then when changed pull the info.  Any ideas?  Thanks in advance for the help.  If you have any questions or it seems like I left something out, please let me know.

 

[Barand]

Sounds like one for AJAX - see link in my sig

[Psycho]

I'm not 100% clear on everything you stated, but I do have a solution, well 2 to be exact.

 

I'm not sure how many options the user will have with respect to the select field and the value to be displayed, but if it is a small number, then I would do everything in javascript. So, if the select list will have 5 options and there will be 5 different values to be displayed, then query for all five values when the page loads and use javascript to change the value dynamically when the user changes the value of the select field.

 

Now, if the number of options is large, then the solution you are looking for would be AJAX which is a combination of Javascript and PHP in this case. There are plenty of tutorials out there to get you started. However, do realize that based upon server load and the user's bandwidth there can be a delay in this process - usually it is only negligible.

[tdors]

An example is here: http://www.toddspage.com/slex/tracking_csell.php?staff_id=1

 

Field Explanation:

 

Date (Date)

 

Member (Customer Number)

 

Walk In Product (Standard drop down, throwing in data from mysql with the id number (normalized database)), this is what the customer came in for, is just for reference, will have about 30 choices in it

 

X-Sell Product (What was cross sold to the customer, This will have the same data as the Walk In Product, but when the employee selects something from this list, I want it to grab data from mysql, The "Metric" and the "Each/Per", Metric being dollar amount, Each/Per being the definition of one time spiff, or per thousand spiff.)

 

New Money would be the field used to help calculate the per thousand spiff

 

Metric would be a locked field that gets data from mysql based on the X-Sell Drop down

 

EP Would show Each or Per (would be a single character text box)

 

Subtotal would be locked and calculate the total based on the metric and new money

 

Notes is just notes

 

I can figure out the JavaScript programming to calculate, so that's not a problem, just need help pulling the data from mysql and passing the variables.  I'll try asking in AJAX.  I've been reading a lot on AJAX but haven't found anything on passing variables. 

 

Open to any suggestions.  Thanks!

 

 

[per1os]

Using Ajax to pass variables requires POST or GET.

 

If you are using ajax to call a page, to pass a variable easily to that page you just make the url something like this:

 

yourScript.php?name1=value1&name2=value2&name3=value3

 

Than in the yourScript.php you would reference the values like echo $_GET['name1']; which should print value1

[Psycho]

I'm not knocking AJAX, but if the value to be dynamically displayed is based upon the option of 1 field with ~30 entries I would use Javascript only. When the page is called, just query for all 30 values and populate a javascript array within the page and call a javascript function to display the appropriate value on change of the select list.

[tdors]

I'm not totally familiar with an array (especially javascript) but catch on quickly.....  But yes, there are about 30 choices, and from each choice I need to get three or four values for each choice, off the top of my head, the list_id, Type (3 Letter Definition), Each/For Identifier (one Letter), and Metric (dollar amount, either per thousand or one-time)

[Psycho]

Now you confused me. I thought you had an amount that was tied to each option in the select list. Are the three values you mention needed to calculate the amount? As I stated, if you can query for all 30 amounts then you can just write them to a javascript array and create a function. Here is an example:

 

<html>
<head>

<script type="text/javascript">

  // Put the values in an array in the same
  // order as the items in the select list
  prices = new Array (10, 15, 20);

</script>

</head>
<body>

<select name="product" onchange="document.getElementById('cost').value=prices[this.selectedIndex];">
  <option value="apples">Apples</option>
  <option value="oranges">Oranges</option>
  <option value="pears">Pears</option>
</select>

<input type="text" name="cost" id="cost">

</body>
</html>

[tdors]

I don't have my sheets in front of me, but when the choice is selected, I'd like to have:

 

The "choice ID", which will let me keep the choice for future reporting, I made this Int(5)

The "choice Type" which is three letters (Char(3)) that define weather the option is "Not Selectable", "Pay Each", "Pay Per Thousand", or "Pay Each Specified".

The "Metric" which is the $ amount that is either Paid Each or Pay Per Thousand

The last one that I talked about "E" or "P", I can get with a formula based on the Choice Type

 

Hope this clears it up

[Psycho]

Um, not really. You need to "step back" a bit. When you get involved with a project what seems obvious to you makes no sense to someone else. I'm sure I can help you, but let's take one step at a time.

 

Ok, on the page you liked to above, there are several text fields that the user will enter information. Plus there are two select fields and a read-only field that you want to populate dynamically.

 

Can you give specify 1) some real examples of data that would go into those two select lists, 2) Which select field you want to control the value in the read-only field, and 3) How you determine that value? If you have the current query & code used to determine one of the values, that would be very helpful too.

[tdors]

I figured out some of the AJAX stuff and that makes sense, but I'm having trouble passing the variable through ajax.  I can call the ajax function, and it's coming back through the div just fine, but I'm having trouble knowing how to send the data to the ajax page based on what was picked in the drop down.  Code is in the thread linked below.

 

Thanks for the help, so I reposted in the AJAX forum

 

http://www.phpfreaks.com/forums/index.php/topic,142874.0.html