PHP, MySql and Yahoo Flash Charts, yahoo dev network

Today we will do a chart using PHP, MySql and Yahoo Astra API (charts).

The list of books that can be used as reference depending on level of comfort can be found below.

Our poll will be using PHP as scripting language, a MySql data base and the Yahoo Dev Network Flash API.

Some comfort level with javascript would help, but is not necessary.

Using your MySql console or however you create your databases and tables, you can run this simple query:

CREATE TABLE chartTbl (
'chartId' INT NOT NULL AUTO_INCREMENT ,
'chartVal' INT NOT NULL ,
'chartStamp' DATETIME NOT NULL ,
PRIMARY KEY ( `chartId` )
) ENGINE = MYISAM COMMENT = 'Sample Chart Table'

Afterwards creating the table run this:

INSERT INTO `chartTbl` (`chartId`, `chartVal`, `chartStamp`) VALUES
(1, 1, '2009-04-02 16:14:03'),
(2, 2, '2009-04-02 16:14:07'),
(3, 2, '2009-04-02 16:14:21'),
(4, 1, '2009-04-02 16:14:28'),
(5, 1, '2009-04-02 16:14:32'),
(6, 3, '2009-04-02 16:14:39'),
(7, 1, '2009-04-02 16:14:44');

Now what we've done above is to create a db and a table that will hold our poll results.

Now we can connect to it and display the information, but before we do so, we need to create a small form that will insert the poll results into the database (db).

Form:

<form action="<?=$_SERVER['PHP_SELF'];?>" method="post"/>
<ul>
<li><input type="radio" id="like_1" name="pbj" value="1"/> Oh Yeah</li>
<li><input type="radio" id="like_2" name="pbj" value="2"/> No Way</li>
<li><input type="radio" id="like_3" name="pbj" value="3"/> Its Cool...</li>
</ul>
<div align="center">
<input type="submit" name="Submit" value="Show what ya Like!" />
</div>
</form

Short, simple and sweet, you can style the form and the holding contents however you want, and there are the styles that are included in the source (src) file.

Now the first thing you want is some kind of validation, No EMPTY form values in the db.

This how we do that:

if(empty($_POST['pbj'])){

Notice in bold the $_POST is the same name as the radio button, the reason being is so we can relate that value to another when we process the form.

Not if all goes as plan for the above snippet, the form will show again, with some feedback, now you can do alot of things with javascript, CSS, but in this particular sample we are using the PHP to check things out before we insert the values.

If the value that we are looking for is filled in (radio button), we can begin insertion.

$sql = "insert into chartTbl (chartVal,chartStamp) values('" . $_POST['pbj'] . "',now())";

Look at the things in bold starting from left to right:

1. The name of the table chartTbl
2. There is the name of the radio button
3. NOW(), this is just a function that will give the record a stamp, this can be used to show when the record was created, and if you track users to show the stamp etc.

Now in our example if all goes well with our script, a new message will be on the screen,"I guess you like it!", do whatever you want with this, it is the logic of this that is important.

Along with message the form will be shown, along with a link to show the poll results.

With our example the poll results are shown by using a querystring the value is: $_GET['showMePoll'].

Look at the address bar after you submit the form.

You will see something like this:

http://diadde.com/test/charts/chartTest.php?showMePoll=true

And this is all we look for to show it.

<?php if(isset($_GET['showMePoll'])){?>

It reads; if this is set do whatever is below and in between the code, which is just this.

<div id="chart"></div>

That is the tag of the div element that holds all the code for the yahoo dev api:

Here it is:

<!--insert yahoo chart stuff here-->
<script type="text/javascript">
YAHOO.widget.Chart.SWFURL = "http://yui.yahooapis.com/2.7.0/build/charts/assets/charts.swf";
YAHOO.example.pbj =
[
{ response: "Yes", count: <?=$row['yes'];?>},
{ response: "No", count: <?=$row['no'];?>},
{ response: "Maybe", count: <?=$row['maybe'];?>}
]

var subData = new YAHOO.util.DataSource( YAHOO.example.pbj );
subData.responseType = YAHOO.util.DataSource.TYPE_JSARRAY;
subData.responseSchema = { fields: [ "response", "count" ] };
var mychart = new YAHOO.widget.PieChart( "chart", subData,
{
dataField: "count",
categoryField: "response",
style:
{
padding: 20,
legend:
{
display: "right",
padding: 10,
spacing: 5,
font:
{
family: "Arial",
size: 13
}
}
},
//only needed for flash player express install
expressInstall: "assets/expressinstall.swf"
});
</script>

This is the same code from the yahoo library that is free to everyone, now in the code above notice this:

{ response: "Yes", count: <?=$row['yes'];?>},
{ response: "No", count: <?=$row['no'];?>},
{ response: "Maybe", count: <?=$row['maybe'];?>}

In bold are the results of a query at the top of our page that is giving the values to the script text, here is the query:

if(isset($_GET['showMePoll'])){
###########################
//this is for the chart
$sql="select distinct (select count(chartVal) from chartTbl where chartVal = 1) as yes,";
$sql.="(select count(chartVal) from chartTbl where chartVal = 2) as no,";
$sql.="(select count(chartVal) from chartTbl where chartVal = 3) as maybe";
$sql.=" from chartTbl";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
//echo $sql;
//return;
###########################
}

Look at the very top of that script in bold, if isset, the reason I am using this is because, if we call the page using the querystring again, then I want to do the query, not before, be aware of the things in your app or page, you don't want unnecessary queries or widgets running without needing them.

This line here (from the script)

$row = mysql_fetch_assoc($result);

Is where we assign the value to the variable $row

This is little tricky, but I can explain in another tutorial in a little more detail, we are using subsqueries in this code snippet.

Take this:

$sql="select distinct (select count(chartVal) from chartTbl where chartVal = 1) as yes,";
$sql.="(select count(chartVal) from chartTbl where chartVal = 2) as no,";
$sql.="(select count(chartVal) from chartTbl where chartVal = 3) as maybe";

Notice they are using the same column name chartVal, in our insert no matter what value came from the form we put it in the db, since these are different values we need to separate by type:

• 1 = yes
• 2 = no
• 3 = maybe

Simple, the sub query takes care of that along with giving them an alias (as yes for 1) so that the query won't have problems finishing, otherwise it would stop and kick off an error; we don't want that, we just want our flash chart.

Anyway after that query runs, it fills in the variables that are being called inside the yahoo script, painless.

Now alot of other charts can be used in the same manner as this, you have to read through the yahoo dev page, and see what works for your situation.

Also, this is just an example and by far not the ony way you can go about assembling or managing some app or page, there are more elegant ways to do it.

Take it and get comfortable and looking forward to having you read my articles again.