Storing multiple MySQL queries in different variables for use throughout script

General PHP Help
#1

So I am looking to pull contact names and phone numbers from a MySQL database and strip any extra formatting and then write them to XML that our VoIP phones can read. I have figured out how to clean the data up and how to write everything to a XML. What I amstruggling with is piping the MySQL queries into my code that cleans up the numbers and then writes the XML. Below is sampling of the code I have for cleaning and writing to XML, instead of manually assigning the variables as I have done for testing of my current code, I would like to pipe in data from a MySQL query. I know how to echo MySQL out but I am struggling with this…
[php]
$name = ‘TestUser1’;
$dirtynumber = ‘(520) 555-2179’;

$databasequeryresult = $dirtynumber;

$number = $databasequeryresult;
$remove = array("(", “)”, “-”, " “);
$new = array(”", “”, “”, “”);

$cleannumber = str_replace($remove, $new, $number);

$file = ‘store1mgrs.xml’;

$entry = “\n $name”;

file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);

$file = ‘store1mgrs.xml’;

$entry = “\n $cleannumber”;

file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);

$file = ‘store1mgrs.xml’;

$entry = “\n $cleannumber1”;

file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);

$file = ‘store1mgrs.xml’;

$entry = “\n $cleannumber2”;

file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);
[/php]

#2

A few comments.

  1. The XML serialization can be handled with XML_Serializer

  2. You can make your string replace a single line:
    [php]
    $remove = array("(", “)”, “-”, " “);
    $new = array(”", “”, “”, “”);
    $cleannumber = str_replace($remove, $new, $number);
    [/php]

Compared to:
[php]$cleannumber = preg_replace(’/[^0-9{9,10}]/’, ‘’, $number);[/php]

For your actual issue, use a PDO query and select what you need. Then you just pass the data to have it cleaned and append the file. It is easier to demonstrate than type about, honestly.

#3

Thanks for the pointers, this is what I came up with that works, any recommendations for improvement?
[php]
include ‘dbauth.php’;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = “Select CellPhone from person where JobTitle=1 and PrimaryLocation=2”;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {

    $number = $row["CellPhone"];
    
    $cleannumber = preg_replace('/[^0-9{9,10}]/', '', $number);
    
    $file = 'test2.xml';

$entry = “\n $cleannumber”;

file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);

}

} else {
echo “0 results”;
}
[/php]

#4

[php]
include ‘dbauth.php’;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = “Select CellPhone from person where JobTitle=1 and PrimaryLocation=2”;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
$file = ‘test2.xml’;
// output data of each row
while($row = $result->fetch_assoc()) {
$cleannumber = preg_replace(’/[^0-9{9,10}]/’, ‘’, $row[“CellPhone”]);
$entry = “\n $cleannumber”;

    file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);
}

} else {
echo “0 results”;
}
[/php]

#5

I would also build the entire string, rather than repeatedly hitting the file.

#6

Can you please explain this? So I am pretty sure I know why the 0-9 and ‘’ are there, but why the {9,10}? I am looking write something similar that will filter out everything except A-Z, a-z, and commas.
[php]
$cleannumber = preg_replace(’/[^0-9{9,10}]/’, ‘’, $number);
[/php]

#7

That portion is for length. Minimum of 9 digits, max of 10. That was to account for leading coumtry codes.

Sponsor our Newsletter | Privacy Policy | Terms of Service