How to connect mySQL to SVG using RaphaelJS?


Problem :

I am trying to link a MySQL DB to an SVG image to dynamically change the SVG elements with Raphael JS.

I have a MySQL DB where I query using PHP and display the results in table form to an html page: (The script below works and displays the username and a picture only when the condition of the timestamp is met.)

    <?php
    mysql_connect("","","");
    mysql_select_db("");
    $res=mysql_query("select username, picture from 'table' WHERE     status > UNIX_TIMESTAMP(NOW()) - 300");
    echo "<table>";
    if (!$res) {
        die("Query to show fields failed");
    }

$fields_num = mysql_num_fields($res);

echo "<h1>Table:Status</h1>";
echo "<table border='1'><tr>";


for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($res);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row=mysql_fetch_array($res))
{
echo "<tr>";

echo "<td>"; echo $row["username"]; echo "</td>";

echo "<td>"; ?> <img src=" <?php echo $row["picture"]; ?>" height="50">  

<?php 

How can I take the similar concept above of displaying the results in table form to an SVG image where the SVG elements will change/update only when the query condition is met?

Here is my sample SVG image with 5 elements:

<polygon fill="#B2B2B2" points="150.3,8.8 203.8,31.7 169.8,91.4       133.4,75.8 "/>
<circle id="circleT3" circle fill="#FFFFFF" cx="163.1" cy="53.6" r="7.3"/>
<circle id="circle3_1" circle fill="#CCCCCC" cx="184.5" cy="82.4" r="7.3"/>
<circle id="circle3_5" circle fill="#CCCCCC" cx="136.6" cy="27.2" r="7.3"/>
<circle id="circle3_4" circle fill="#CCCCCC" cx="166.4" cy="7.3" r="7.3"/>

Can someone point me to some sample code or tutorial? Or is there a better way to do this? Thanks.

EDIT:

In MySQL DB I have a column for username, password and timestamp. When a user logs into webpage the timestamp updates. The PHP code above is used to query who has logged within 5 minutes ago from current time.

What I would like to do with this information with SVGs is create a graphical representation of the login.

So each username will have their own SVG element (a circle) associated with them and when they log in/out, that SVG element (code above) will change color.

Right now I do not know how to link the username with my SVG elements so the SVG element will dynamically update like my table I query from MySQL when the timestamp changes.



Solution :

The answer will depend on further information that isn't really available until the rest is written.

You could combine Snap (to modify existing inline SVG or create it) or Raphael (to create new SVG only, you can't use it to modify inline SVG), or another SVG library of choice (eg svg.js or jquery.svg maybe).

Assuming you already have something to use on the page, that is showing the logged in user, you could do something like in pseudocode...

loop user; 
if( document.getElementById( userId ) ) Snap('#' + userId + '_image').attr({ fill: 'green' });

(The svg reference may be the same as the circles, but somewhere you would need some type of lookup to know which circle is which userid)

This assumes the svg is on the page. If its not, you could create it with

paper.circle(x,y,r).attr({ fill: 'green' });

If you want it dynamic (so status changes without a refresh), you may need to tie ajax calls to get status from the mysql db, but if you already have a user name displaying on the page, I'm assuming that is already taken care of.


Mysql Tutorials

Mysql Howto..

How to make one MySQL User to access only one schema

How do I prevent MySQL Database Injection Attacks using vb.net?

MySQL - how can I avoid high load when I switch schemas (databases)?

php / mysql - how to get rankings of users?

How to do 'grading' in pure (i.e. ANSI) SQL

How do I change the mysql server date using SQL

How to model optional self dependence in mySQL?

How to insert HTML select value as text in MySQL via PHP

How can I fetch “partial matches” with mysql?

How to output a variable on a MySQL query string?

My posted date via php is off 1 hour… How to fix?

How do I insert the auto-increment ID into a MySQL table using MySQL ODBC with ASP.Net/C#?

How to skip blank data in MySQL?

How to check after update on maximum constraints?

How to connect MySQl using php on IBM Bluemix?

How to return more than a string from a webservice?

How to convert a MS SQL Merge Query to work with MySQL DB? [closed]

ElasticSearch - how to get the auto generated id from an insert query

How to set up a database and API web page to accept POST updates from iPhone?

How to setup a JDBC connection to MySQL in Spring3 MVC?

How do i select distinct values from multiple columns in SQL?

How to efficiently group, sum and sort this mysql result?

Sql Self join query? How to get categories sub categories?

How to delete more than one files from database in MySQL [duplicate]

Inserting TEXT in mysql database showing error

How to speed up a query?

How do you reference multiple lookups from the same table?

How to get mysql data from table into php date()?

How to compare mysql timestamp with jquery timestamp?

How to know how many mysql queries a php script has done?