about database select method

roger5089

i wish some one can test my program and tell me what problem is

how can i delete data from database one by one .....
how can i set up variable in $sql_sendout
what should i change in this bit if i want to delete id from webreserve one by one...

$sql_drop = "delete from webreserve where (reserve < current_date);";
pg_exec($sql_drop);

<?
$SQL_SERVER_USER = "roger";
$SQL_SERVER_PASSWORD = "19740910";
$SQL_SERVER_TABLE = "agent";
$SQL_SERVER_HOST = "localhost";
$SQL_SERVER_PORT ="5432";

$service_port = $SENDER_SERVER_PORT;
$service_addr = $SENDER_SERVER_IP;

// First.. Trying connect to SQL server
// If connecting failed sleep 5 seconds, and try again.
// until the connecting finished, break the while loop, go ahead to send out sms.
// connect to SQL server loop.

while (1)
{
$conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

if ($conn_checker == false)
{
    print "connect to SQL server fail..\n";
    pg_close($conn_checker);
}
else
{
    break;
}
sleep (5);

}

// sendout sms loop

while (1)
{
// get current time
$thesec = time ();

// select reserved messages from CallME reserve TABLE

$sql_sendout =  "SELECT deliver_date,target,message,id,reserve FROM b WHERE (reserve < $thesec);";

$result_sendout = pg_query ($conn_checker,$sql_sendout);

// if could not read anything from reserve TABLE,
// there should be some error happened,
// the simplest way to solve the problem is shut down the SQL connect
// and re-establish it..

if ($result_sendout == false)
{
    printf ("Counld not query from SQL server.. try again latter\n");
    // break the connection
    pg_close ($conn_checker);

    // the same as the above main while loop.
    while (1)
    {
        $conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

        if ($conn_checker == false)
        {
            print "connect to SQL server fail..\n";
            pg_close($conn_checker);
        }
        else
        {
            break;
        }
        sleep (5);
    }
    continue;
}

// fingure out how many sms was reversed in reseve TABLE
$result_count = pg_numrows ($result_sendout);

// for loop to send out the sms message one per connection to remote sms gateway
for ($result_ptr = 0; $result_ptr < $result_count; $result_ptr++)
{
    // fetch message informations from SQL querying results.
    $qr = pg_fetch_object($result_sendout, $result_ptr);

    // setup the request URL.

    $agentid      = 2123;
    $target       = $qr->target;
    $message      = $qr->message;
    $deliver_date = $qr->deliver_date;

    $response_url = "http://61.218.68.20/~roger/testresponse.php";

    $url          = "http://211.21.147.30:7744/sendmessage.php?" .
                                             "agentid=$agentid&" .
                                             "target=$target&" .
                                             "message=\"$message\"&" .
                                             "deliver_date=$deliver_date&" .
                                             "response_url=$response_url";

    // create new socket descriptor for connecting to remote HTTP server

    $fp_sender = fsockopen ($SENDER_SERVER_IP,$SENDER_SERVER_PORT, $errno_sender, $errstr_sender, 30);

    // if error happened, skip this message a ahead to next one.
    if (!$fp_sender)
    {
        echo ("CONNECT ERROR: $errstr_sender ($errno_sender)<br>\n");
        continue;
    }

    $outgoing_msg = sprintf ("GET %s\n\n", $url);
    fputs ($fp_sender, $outgoing_msg);

    // read remote response messages and print it out.
    while (1)
    {
        $incoming_msg = fgets ($fp_sender);
        if ($incoming_msg == NULL)
            break;
        printf ("RECEIVE: %s", $incoming_msg);
    }

    fclose ($fp_sender);

    // how to insert data

    $qr = $sql_send;

    $sql_send = "INSERT INTO delive09(acount,sendout,target,content,result,series,fileidx,finaldate,delflag)
                                  VALUES(444,2222,'$target','$message',2,3,'$deliver_date',0,0);";


    pg_exec($sql_send);

    // how to remove & store record??

    <?php

$SQL_SERVER_USER = "roger";
$SQL_SERVER_PASSWORD = "19740910";
$SQL_SERVER_TABLE = "agent";
$SQL_SERVER_HOST = "localhost";
$SQL_SERVER_PORT ="5432";

$service_port = $SENDER_SERVER_PORT;
$service_addr = $SENDER_SERVER_IP;

while (1)
{
$conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

if ($conn_checker == false)
{
    print "connect to SQL server fail..\n";
    pg_close($conn_checker);
}
else
{
    break;
}
sleep (5);

}

// sendout sms loop

while (1)
{
// get current time
$thesec = time ();

// select reserved messages from CallME reserve TABLE

$sql_sendout =  "SELECT deliver_date,target,message,id,reserve FROM b WHERE (reserve < $thesec);";

$result_sendout = pg_query ($conn_checker,$sql_sendout);

// if could not read anything from reserve TABLE,
// there should be some error happened,
// the simplest way to solve the problem is shut down the SQL connect
// and re-establish it..

if ($result_sendout == false)
{
    printf ("Counld not query from SQL server.. try again latter\n");
    // break the connection
    pg_close ($conn_checker);

    // the same as the above main while loop.
    while (1)
    {
        $conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

        if ($conn_checker == false)
        {
            print "connect to SQL server fail..\n";
            pg_close($conn_checker);
        }
        else
        {
            break;
        }
        sleep (5);
    }
    continue;
}

// fingure out how many sms was reversed in reseve TABLE
$result_count = pg_numrows ($result_sendout);

// for loop to send out the sms message one per connection to remote sms gateway
for ($result_ptr = 0; $result_ptr < $result_count; $result_ptr++)
{
    // fetch message informations from SQL querying results.
    $qr = pg_fetch_object($result_sendout, $result_ptr);

    // setup the request URL.

    $agentid      = 2123;
    $target       = $qr->target;
    $message      = $qr->message;
    $deliver_date = $qr->deliver_date;

    $response_url = "http://61.218.68.20/~roger/testresponse.php";

    $url          = "http://211.21.147.30:7744/sendmessage.php?" .
                                             "agentid=$agentid&" .
                                             "target=$target&" .
                                             "message=\"$message\"&" .
                                             "deliver_date=$deliver_date&" .
                                             "response_url=$response_url";

    // create new socket descriptor for connecting to remote HTTP server

    $fp_sender = fsockopen ($SENDER_SERVER_IP,$SENDER_SERVER_PORT, $errno_sender, $errstr_sender, 30);

    // if error happened, skip this message a ahead to next one.
    if (!$fp_sender)
    {
        echo ("CONNECT ERROR: $errstr_sender ($errno_sender)<br>\n");
        continue;
    }

    $outgoing_msg = sprintf ("GET %s\n\n", $url);
    fputs ($fp_sender, $outgoing_msg);

    // read remote response messages and print it out.
    while (1)
    {
        $incoming_msg = fgets ($fp_sender);
        if ($incoming_msg == NULL)
            break;
        printf ("RECEIVE: %s", $incoming_msg);
    }

    fclose ($fp_sender);

    // how to insert data

    $qr = $sql_send;

    $sql_send = "INSERT INTO delive09(acount,sendout,target,content,result,series,fileidx,finaldate,delflag)
                                  VALUES(444,2222,'$target','$message',2,3,'$deliver_date',0,0);";


    pg_exec($sql_send);

    // how to remove & store record??

    $sql_drop = "delete from webreserve where (reserve < current_date);";
    pg_exec($sql_drop);



}//end for

}//end while

pg_close ($conn_checker);

volleytotal_ch

use the mysql's "now()"-funciton:
```
$sql_sendout = "SELECT tid,tname,taddress,tdate FROM b WHERE tdate < now()"; 
```
BTW: have you really named your table "b"? try to use table names that sort of describes what is in it.

don't know if there's a better solution. I think you have to use a function for this problem:

class my_class
{
  // make your database connection accessible for the class
  global sql_conn;
  // function to delete an entry:
  function del_entry($id);
  {
    $query = "DELETE FROM table_name WHERE id='".$id."'";
    $res = mysql_query($query,$sql_conn);
    if($res)
    {
      if(mysql_affected_rows()==1)
        return true;
      else
        return false;
    }
    else return false;
  }
}

// then call the function:
$my_object = new my_class();

if($my_object->del_entry("the_number_to_delete"))
{
  // row was deleted successfully!
}
else
{
  // Couldn't delete the desired entry!
}

roger5089

<?php

$SQL_SERVER_USER = "roger";
$SQL_SERVER_PASSWORD = "19740910";
$SQL_SERVER_TABLE = "agent";
$SQL_SERVER_HOST = "localhost";
$SQL_SERVER_PORT ="5432";

$service_port = $SENDER_SERVER_PORT;
$service_addr = $SENDER_SERVER_IP;

while (1)
{
$conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

if ($conn_checker == false)
{
    print "connect to SQL server fail..\n";
    pg_close($conn_checker);
}
else
{
    break;
}
sleep (5);

}

// sendout sms loop

while (1)
{
// get current time
$thesec = time ();

// select reserved messages from CallME reserve TABLE

$sql_sendout =  "SELECT deliver_date,target,message,id,reserve FROM b WHERE (reserve < $thesec);";

$result_sendout = pg_query ($conn_checker,$sql_sendout);

// if could not read anything from reserve TABLE,
// there should be some error happened,
// the simplest way to solve the problem is shut down the SQL connect
// and re-establish it..

if ($result_sendout == false)
{
    printf ("Counld not query from SQL server.. try again latter\n");
    // break the connection
    pg_close ($conn_checker);

    // the same as the above main while loop.
    while (1)
    {
        $conn_checker = pg_connect("host=$SQL_SERVER_HOST port=$SQL_SERVER_PORT dbname=$SQL_SERVER_TABLE user=$SQL_SERVER_USER password=$SQL_SERVER_PASSWORD");

        if ($conn_checker == false)
        {
            print "connect to SQL server fail..\n";
            pg_close($conn_checker);
        }
        else
        {
            break;
        }
        sleep (5);
    }
    continue;
}

// fingure out how many sms was reversed in reseve TABLE
$result_count = pg_numrows ($result_sendout);

// for loop to send out the sms message one per connection to remote sms gateway
for ($result_ptr = 0; $result_ptr < $result_count; $result_ptr++)
{
    // fetch message informations from SQL querying results.
    $qr = pg_fetch_object($result_sendout, $result_ptr);

    // setup the request URL.

    $agentid      = 2123;
    $target       = $qr->target;
    $message      = $qr->message;
    $deliver_date = $qr->deliver_date;

    $response_url = "http://61.218.68.20/~roger/testresponse.php";

    $url          = "http://211.21.147.30/sendmessage.php?" .
                                             "agentid=$agentid&" .
                                             "target=$target&" .
                                             "message=\"$message\"&" .
                                             "deliver_date=$deliver_date&" .
                                             "response_url=$response_url";

    // create new socket descriptor for connecting to remote HTTP server

    $fp_sender = fsockopen ($SENDER_SERVER_IP,$SENDER_SERVER_PORT, $errno_sender, $errstr_sender, 30);

    // if error happened, skip this message a ahead to next one.
    if (!$fp_sender)
    {
        echo ("CONNECT ERROR: $errstr_sender ($errno_sender)<br>\n");
        continue;
    }

    $outgoing_msg = sprintf ("GET %s\n\n", $url);
    fputs ($fp_sender, $outgoing_msg);

    // read remote response messages and print it out.
    while (1)
    {
        $incoming_msg = fgets ($fp_sender);
        if ($incoming_msg == NULL)
            break;
        printf ("RECEIVE: %s", $incoming_msg);
    }

    fclose ($fp_sender);

    // how to insert data

    $qr = $sql_send;

    $sql_send = "INSERT INTO delive09(acount,sendout,target,content,result,series,fileidx,finaldate,delflag)
                                  VALUES(444,2222,'$target','$message',2,3,'$deliver_date',0,0);";


    pg_exec($sql_send);

    // how to remove & store record??

    $sql_drop = "delete from webreserve where (reserve < current_date);";
    pg_exec($sql_drop);



}//end for

}//end while

pg_close ($conn_checker);

please help me up this script ..........
how can i delete data from database?
1.the condition is which i send out already one by one
2.how can i set up variable for $sql_drop .......