I am kind of new to MySQL and databases and I am having trouble getting the right information out of a database for a chart I am using. I have a database with three tables. The tables look like this:
Salesmen Table:
Salesman ID Salesman name Experience status
Products Table:
Product ID Product name Product description Product Cost
Orders:
Salesman ID Product ID Quantity Date
Foreign keys are salesman id and product id for orders.
I want to display the total product cost for each salesman and separate it out by experience.
The output should look like this:
Very Experience 34
New 12
Experience 45
I am able to figure out how to get the Salesman Id with the total product cost. But I am unable to figure out how to get it to show only the experience and product cost. This information is going into an graphic chart. If someone can help me I would appreciate it.
Thanks,
r.
Here is my code in the PHP page that queries the dabase .
This codes does work but it only gives the salesman id and the total product cost.
[php]
@ $db = new mysqli(‘localhost’,‘root’,‘newdata’,‘sales’);
$query = "select sm.salesmanid, sum(or.quantity) ";
$query .= "from salesmen as sm, orders as or ";
$query .= "where sm.salesmanid = or.salesmanid ";
$query .= "group by or.salesmanid ";
$result = $db->query( $query );
$numrecs = $result->num_rows; //how many records in result set
for ($i = 0; $i < $numrecs; $i++ ) {
$row = $result->fetch_array(); //each record is array
$Dataset->addPoint($row[0], $row[1], substr($row[0],0,1));
}
[/php]