John Chase John Chase - 5 months ago 12
SQL Question

PHP Keep Executing Against Database

So I'm trying to write a script where I can populate a database with phone numbers and then have the script go down the list and text them all. The service I'm using "Nexmo" has a test function where you can input the number into a URL and then execute the URL to send the text. I got my database setup and I got the URL to except information from the database but I have two issues.


  1. My script only grabs the first database entry, I need it to go down a list by ID.

  2. I need tp script to repeat itself so that it will grab the next entry in the database and send the next text until it has exhaustes all possible DB entries.



As an example..script runs...URL is populated with DB entry 1 and executes. Text is sent. Process repeats until there is no more entries in the database but it to fill in the link with.

I believe I have most of the code and am just missing one small link somewhere, I'm posting it below with the sensitive information removed.

Thanks for taking the time to read this, or help me out.

<?php
ini_set('max_execution_time', 1300);

$dbhost = "localhost";
$dbname = "data_base";
$dbuser = "dbusername";
$dbpass = "passy";



$conn = mysql_connect($dbhost, $dbuser, $dbpass) or trigger_error(mysql_error(),E_USER_ERROR);


mysql_select_db($dbname);

$sql = "SELECT * FROM ha_n ORDER BY `id` ASC";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res);


$phone_fill = $row['phone_numbers'];



$url = 'https://rest.nexmo.com/sms/json?api_key=5555555555&api_secret=555555555555&from=5555555555&to='.$phone_fill.'&text=Welcome+to+Nexmo';


$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/6.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.7) Gecko/20050414 Firefox/1.0.3");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
$result = curl_exec ($ch);
curl_close ($ch);

Answer

Like this, but I would suggest using PDO or MySqli instead of plain mysql because that ( mysql_* functions ) are depreciated and set to be removed as of PHP 7

while( false !== ( $row = mysql_fetch_assoc($res) ) ){


        $phone_fill = $row['phone_numbers'];



    $url = 'https://rest.nexmo.com/sms/json?api_key=5555555555&api_secret=555555555555&from=5555555555&to='.$phone_fill.'&text=Welcome+to+Nexmo';


    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url); 
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
    curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/6.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.7) Gecko/20050414 Firefox/1.0.3");
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER,1); 
    $result = curl_exec ($ch); 
    curl_close ($ch);
}

essentially to get all the results you need to iterate through them. This basically says loop until $row == false, with each row being the next record, I would probably suggest adding something like sleep(1) between the calls, wait one second, so you don't hammer their API but it depends on what they allow you to do, you may also want to do set_time_limit(300) inside the loop that will give PHP 300 seconds to execute each part and not timeout if you have a ton of numbers.

If you want it to run on like a nightly basis, you'd use something like CRON for that, which if you have C-Panel will be in the dashboard there. I'd research that word a bit first though. But basically it allows you to execute a php file on a specific time, or interval. Like a scheduler.

http://php.net/manual/en/book.pdo.php