We closed this forum 18 June 2010. It has served us well since 2005 as the ALPHA forum did before it from 2002 to 2005. New discussions are ongoing at the new URL http://forum.processing.org. You'll need to sign up and get a new user account. We're sorry about that inconvenience, but we think it's better in the long run. The content on this forum will remain online.
Index › Programming Questions & Help › Syntax Questions › mysql strings and text
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1
mysql strings and text (Read 765 times)
ilikebagels
mysql strings and text
Mar 9th, 2009, 3:03pm  
Hello another attempt at getting the answer im looking for :S

what i'm currently doing is connecting to a database reading info from a table and then its displaying onscreen via the good old text function

thats all working but what i need to do is get it to check wether to see if the database table has been added to and if it has to send the new stuff to the screen without the old stuff.

i have a vague vague vague idea that it could be done with the row number of the database just not sure how i would put it together.

Quote:
import de.bezier.mysql.*;
PFont f;
int linePos = 100;

// created 2005-05-10 by fjenett
// updated 2005-11-16 by fjenett


MySQL msql;


void setup()
{
    size( 400, 400 );
  
    f = createFont("Arial",16,true);
    
    // this example assumes that you are running the
    // mysql server locally (on "localhost").
    //
    // replace --username--, --password-- with your mysql-account.
    //
    String user     = "daver";
    String pass     = "superhero";

    // name of the database to use
    //
    String database = "daver";

    // name of the table that will be created
    //
    String table    = "inbound";

    // connect to database of server "localhost"
    //
    msql = new MySQL( "*************", database, user, pass, this );
    
    if ( msql.connect() )
    {   
       
        msql.query( "SELECT * FROM " + table );
        
        while (msql.next())
        {
          
          String s = msql.getString("message");
            int n = msql.getInt("id");
            println(s + "   " + n);
            fill(0);
            textFont(f);
            textAlign(CENTER);
            if ((linePos<= 100) || (linePos>=100)){
              linePos = linePos + 20;
            }
            //rotate(PI/1.0);
            text(s,150,linePos);
            
          
           
            
        }
        
        // need to find out how many rows there are in table?
        //
        msql.query( "SELECT COUNT(*) FROM " + table );
        msql.next();
        println( "number of rows: " + msql.getInt(1) );
        
        
    }
    else
    {
        // connection failed !
    } 
 
}

void draw()
{
   
}
void query()
{
  //i will put the databse stuff here but its in setup at the moment
}
Back to top
 
WWW  
PhiLho 
Re: mysql strings and text
Reply #1 - Mar 9th, 2009, 3:49pm  
Do you have control on how information is added to your database? If you add a time column to the table, setting it to the date/time of added data, you can check it to see what is new. Actually, you should ask the database to return only data added after a given time, that's the most efficient.
Back to top
 
WWW  
ilikebagels
Re: mysql strings and text
Reply #2 - Mar 9th, 2009, 3:57pm  
the information is added to the database via a php script im actually sending text (sms) messages to the php script thats how the database is being populated.

the idea is that you send a text message and it will appear all processed up

asking the databse to only return stuff after a certain time is a good thought but not really sure that would help with my current problem of getting processing to spurt out that live population of the databse
Back to top
 
WWW  
seltar
Re: mysql strings and text
Reply #3 - Mar 9th, 2009, 4:45pm  
if you have an incrementing id on the table, just save the highest id when you do a load, and then do a sql "select * from table where id > last_id", and if the result is an empty array, no posts have been added.
check that every 15th or 30th second, and you'll have semi-live applet

but beware. i can download your jar-file, decompile it, and pull out the username and password to your mysqldatabase, and then delete all the tables.
mysql + processing is just not very safe
it's actually better if you make a php file, where you can send different queries, (only allow select, insert etc in that php file), and return something you can parse with processing.
Back to top
 
I'm bored, therefore I program
http://seltar.org  > seltar's special place
http://snippet.seltar.org > seltar's snippet palace
http://libraries.seltar.org > seltar's libraries
WWW  
ilikebagels
Re: mysql strings and text
Reply #4 - Mar 9th, 2009, 5:05pm  
im having trouble construct said if statement at the moment im using the row numbers  but it isnt working properly tbh

created a new variable called num_rows_old = num_rows

and then saying

if (num_rows_old < num_rows) {

   mysql.query(

   etc etc

}else{
do nothing

}
all it seems to do is just update the row numbers and not actually print on screen

Back to top
 
WWW  
PhiLho 
Re: mysql strings and text
Reply #5 - Mar 9th, 2009, 5:15pm  
Show us your exact logic. Sometime we do stupid errors we are not able to see while fresh eyes can catch them.
Back to top
 
WWW  
ilikebagels
Re: mysql strings and text
Reply #6 - Mar 9th, 2009, 5:23pm  
i'm probably doing something really stupid but here it goes
Quote:
import de.bezier.mysql.*;
PFont f;

int linePos = 100;

MySQL msql;


void setup()
{
    size( 400, 400 );
 
    f = createFont("Arial",16,true);
 
    // this example assumes that you are running the
    // mysql server locally (on "localhost").
    //
    // replace --username--, --password-- with your mysql-account.
    //
    String user     = "daver";
    String pass     = "superhero";

    // name of the database to use
    //
    String database = "daver";

    // name of the table that will be created
    //
    String table    = "inbound";

    // connect to database of server "localhost"
    //
    msql = new MySQL( "*********", database, user, pass, this );
    
    if ( msql.connect() )
    {   
       
    
        // need to find out how many rows there are in table?
        //
        msql.query( "SELECT COUNT(*) FROM " + table );
        msql.next();
        int num_rows = msql.getInt(1);
        println( "number of rows: " + num_rows );
        println(num_rows);
        int num_rows_old=num_rows;
        
        
    }
    else
    {
        // connection failed !
    }

}
void draw()
{
    query();
    delay(10000);
}
Back to top
 
WWW  
ilikebagels
Re: mysql strings and text
Reply #7 - Mar 9th, 2009, 5:23pm  
Quote:
void query(){
  
  
    // this example assumes that you are running the
    // mysql server locally (on "localhost").
    //
    // replace --username--, --password-- with your mysql-account.
    //
    String user     = "daver";
    String pass     = "superhero";

    // name of the database to use
    //
    String database = "daver";

    // name of the table that will be created
    //
    String table    = "inbound";

    // connect to database of server "localhost"
    //
    msql = new MySQL( "***********", database, user, pass, this );
    
    if ( msql.connect() )
    {   
              // need to find out how many rows there are in table?
        //
        msql.query( "SELECT COUNT(*) FROM " + table );
        msql.next();
        int num_rows = msql.getInt(1);
        println( "number of rows: " + num_rows );
        println(num_rows);
        int num_rows_old=num_rows;
        
        if (num_rows_old < num_rows){
         
        
        
      msql.query( "SELECT * FROM " + table );
        
        while (msql.next())
        {
          
          String s = msql.getString("message");
            int n = msql.getInt("id");
            println(s + "   " + n);
            fill(0);
            textFont(f);
            textAlign(CENTER);
            if ((linePos<= 100) || (linePos>=100)){
              linePos = linePos + 20;
            }
            //rotate(PI/1.0);
            text(s,150,linePos);
            
          
           
            
        }
        }else{
          println("nothing");
        }
//        // need to find out how many rows there are in table?
//        //
//        msql.query( "SELECT COUNT(*) FROM " + table );
//        msql.next();
//        int num_rows = msql.getInt(1);
//        println( "number of rows: " + num_rows );
//        println(num_rows);
//        int num_rows_old=num_rows;
        
        
    }
    else
    {
        // connection failed !
    }
}
  
Back to top
 
WWW  
PhiLho 
Re: mysql strings and text
Reply #8 - Mar 9th, 2009, 11:43pm  
Mmm, as I suspected, seeing
 int num_rows_old=num_rows;
 if (num_rows_old < num_rows)
just one after the other make little sense: they are equals, so the test will always be false.

num_rows_old should be global and initially set to 0. You set it to new value after having read the rows.
Back to top
 
WWW  
ilikebagels
Re: mysql strings and text
Reply #9 - Mar 10th, 2009, 11:10am  
could you give me a hint as to how to code that it just keeps doing more of the same i cant get it to work :S
Back to top
 
WWW  
ilikebagels
Re: mysql strings and text
Reply #10 - Mar 10th, 2009, 11:49am  
what i also need to ask is how do i stop it from just displaying the entire database again, i only want the whatever has been added since the last time i checked.

would i also be better putting the info into an array as i want to be able to check through the strings for certain words to trigger different functions

is that all possible ??
Back to top
 
WWW  
PhiLho 
Re: mysql strings and text
Reply #11 - Mar 10th, 2009, 1:36pm  
It is "hard" to test unless one have a SQL database with proper table, etc.

Anyway, in pseudo-code, I think it would look like:

int oldRowNb = 0;

while (true)
 int newRowNb = current SQL COUNT
 if (newRowNb > oldRowNb)
   get new data where index >= oldRowNb
   add new data to previous one
   for (int i = oldRowNb; i < newRowNb; i++)
     display row i
   oldRowNb = newRowNb

If you want to store the data as it comes, I recommend using an ArrayList which will grow as needed.
Back to top
« Last Edit: Mar 16th, 2009, 5:16pm by PhiLho  »  
WWW  
ilikebagels
Re: mysql strings and text
Reply #12 - Mar 16th, 2009, 5:31pm  
so to try and put this into the actual code i have above would it look something like this ?
while(what goes here? num_rows_old <= num_rows){

int num_rows = mysql.getInt(1)
if(num_rows > num_rows_old)


i dont know what to do, would this be where i populate and arraylist

for(int i = num_rows_old; i<num_rows; i++){
text(???,x,y)
num_rows_old= num_rows
}
}
Back to top
 
WWW  
fjen
Re: mysql strings and text
Reply #13 - Mar 17th, 2009, 8:54am  
continues here
Back to top
 
WWW  
Page Index Toggle Pages: 1
‹ Previous Topic | Next Topic ›