[British Summer Time]

21 minutes ago, requinix said:

1. Strip out the part of the date string that contains the timezone

Sorry - misread what you were saying - I thought the above was your instruction on what to to.

[British Summer Time]

If he removes the 'Z' timezone indicator it will assume the timestring is his default timeszone (BST) and not UTC, therefore he will need

$tstr = '2019-08-13T13:30:00.000';
$dt = new DateTime($tstr, new DateTimeZone('UTC'));          // specify UTC timezone

to specify it is a UTC time. Otherwise he's back to where he started as his default is Europe/London already.

[British Summer Time]

13 minutes ago, knutsford said:

I had understood that Europe/London took care of BST at at the right time of the year but obviously not

It does...

$tstr = '2019-08-13T13:30:00.000Z';
$dt = new DateTime($tstr);                                // create UTC datetime object
$dt->setTimezone(new DateTimeZone("Europe/London"));      // convert to BST

echo $dt->format('Y-m-d H:i:s P');  //--> 2019-08-13 14:30:00 +01:00

 

[While loop to isolate array items help]

Given that xml extract

$required = ['Feasibility', 'Measure Up', 'Model Drawing', 'Concept Design', 'Developed Design', 'Resource Consent', 'Construction Documentation' ];
$str = <<<XML
<Jobs>
  <Job>
    <ID></ID>
    <Name>Job 1</Name>
    <State>Concept Design</State>
    <StartDate>2019-02-01</StartDate>
    <DueDate>2019-10-01</DueDate>
    <Tasks>
      <Task>
        <ID></ID>
        <Name>Measure Up</Name>
        <StartDate>2019-07-01</StartDate>
        <DueDate>2019-07-30</DueDate>
      </Task>
      <Task>
        <ID></ID>
        <Name>Concept Design</Name>
        <StartDate>2019-08-01</StartDate>
        <DueDate>2019-08-31</DueDate>
      </Task>
    </Tasks>
  </Job>
</Jobs>
XML;
$xml = simplexml_load_string($str);
// echo '<pre>', print_r($xml, 1), '</pre>';
foreach ($xml->Job as $job) {
    $state = (string)$job->State;
    if (!in_array($state, $required)) continue;

    echo "{$job->Name} — $state — ";
    
    foreach ($job->Tasks->Task as $task) {
        if ($task->Name == $state) {
            echo $task->DueDate . '<br>';
        }
    }
    
}

Output

Job 1 — Concept Design — 2019-08-31

EDIT - Alternative method

$xml = simplexml_load_string($str);

foreach ($xml->xpath("//Job") as $job) {
    $state = (string)$job->State;
    if (!in_array($state, $required)) continue;
    
    echo "{$job->Name} — $state — ";
    
    $task = $job->xpath("//Task[Name='$state']") ;
    echo $task[0]->DueDate . '<br>';
}

 

[While loop to isolate array items help]

Any chance of the full XML?

That doesn't seem to match up with your processing.

[htmlspecialchars vs mysqli_real_escape_string]

Don't use form method = GET if you don't want the values to be in the query string.

[htmlspecialchars vs mysqli_real_escape_string]

5 minutes ago, mahenda said:

which is better for securing search input

That has already been answered in the previous post...

On 8/2/2019 at 10:31 AM, gizmola said:

Forget about mysqli_real_escape_string or any attempt to escape anything, and use parameters.

 

The query string has been automatically url_encoded prior to submission.

[How do I count rows from multiple products?]

A single query will do the job without any looping.

SELECT COUNT(*) as likes
FROM products p
     JOIN product_likes pl USING (product_id)
WHERE p.user_id = ?

 

[How to get the name of the triggered event?]

Perhaps you can make use of the get_class() function

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

Now you've had time to digest the manual, you should have something like this...

$sql = "INSERT INTO `customer` (`FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `SubscribeMailings`) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssss', $Firstname, $Lastname, $Address, $Telephone, $email, $SubscribeMailings);

if ($stmt->execute()) {
    $_SESSION['Customer_ID'] = $conn->insert_id;
    header('location: report.html'); // if result is true bypasses to the page.
}
else {
    $_SESSION['Customer_ID'] = null;
    echo "details could not be inserted.<br />";
}

 

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

16 minutes ago, PHPnoobster said:

is there a better way to write this?

As you have a mysqli connection then using the mysqli functions (and not the PDO versions) would be a start. I gave you the link to the manual.

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

Now you have decided to show the query execution code I can see you are using mysqli and not PDO. The functions are similar but not the same

You can find mysqli examples here

[connect via wpdb to another database :: how to combine two wp-pages - a approach that works]

1 hour ago, dil_bert said:

If the sites are on different servers then we would need to build an API

Not necessarily. The sites can be spread all over the world. It is the location of the databases that is significant here. If the databases are on the same database server then only a single connection would be required. As I said in my earlier reply, the connection is to the server, not to the database.

[phpspreadsheet create a new worksheet on salesperson name change]

1 hour ago, Psycho said:

Why not just create each spreadsheet when requested by a user.

I agree with Psycho.

Instead of creating all the spreadsheets and emailing them to each salesperson, let them request their data for the month and download the spreadsheet.

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

Apologies for my my lazy copying of your values from your query when creating the execute statement.

The single quotes should, of course, not be there

$stmt->execute( [ $Firstname, $Lastname, $Address, $Telephone, $email, $HRD, $SubscribeMailings ] );

 

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

Sorry, I thought a name like "$pdo" would indicate it is a PDO connection.

Use whatever your connection is in conn.php.

(If you are not using PDO then I strongly recommend you change to it)

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

13 minutes ago, PHPnoobster said:

$_SESSION["Customer_ID"] = "??";

Have you read the edited version of my last post?

[Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK]

When you insert the registration and create a new customer save the new id (last_insert_id) in $_SESSION.

Then, when you insert the report, insert the customer id saved in $_SESSION.

Same goes for existing customers logging in - save their id in $_SESSION.

$sql = "INSERT INTO `customer` (`FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `HRD`, `SubscribeMailings`) 
                        VALUES (?, ?, ?, ?, ?, ?, ?')
                        "; 
$stmt = $pdo->prepare($sql);

// then, assuming you have validated your input data,
// execute the statement passing the data as parameters

$stmt->execute( [ '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$HRD', '$SubscribeMailings' ] );

// save the new customer id
$_SESSION['customer_id'] = $pdo->LastInsertId();

 

EDIT: my 0.02 worth...

• Don't create variables for the sake of it. $_POST['Location'] as already a variable so why move its content, unchanged, into yet another one
• Do not put variables containing user-provided data directly into your queries. Instead use prepared statements and provide the data as parameters.
• No need to insert those NULLs. Just exclude those fields from the insert and let them default to NULL.

[connect via wpdb to another database :: how to combine two wp-pages - a approach that works]

You only need two connections if the databases you want to access are on separate servers. When you make a connection you connect to a server, the database specified is just the default database.

Suppose you have a server with two databases

When you connect you specify DB1

$conn = mysqli_connect(HOST, USER, PWD, 'DB1');

Now when you want to query tableA you would have

$sql = "SELECT cola, colb FROM TableA"

This works fine because TableA is in DB1, the default. If, however you want to select something from TableB then all you need to do is specify that it is in DB2

$sql = "SELECT cola, colb FROM DB2.TableB";

You can even mix them in the same query

$sql = "SELECT a.cola
             , b.colb
        FROM tableA as a
             INNER JOIN
             DB2.TableB as b USING (cola)
        ";

To summarize - to access multiple databases on the same server prefix the table names with the database names.

[Validation Question]

7 minutes ago, SkyRanger said:

person leaves account field empty should not give error. When enters data into field check field to ensure they are only numbers.

In that case

if (isset($_POST['account']) && trim($_POST['account']) != '' && !ctype_digit($_POST['account'])) {
    $accountErr = "Only numbers are permitted or leave blank";
}
else {
    $accountErr = "";
}

 

[Creating text file for user]

You could use output buffering when generating the page. Save the buffered content to a uniquely named file then output to the screen. Provide a download link to the file.

[While loop to isolate array items help]

If, for other purposes, you require all records to be in the array then you can filter the records when you process them to gat those with a required status value EG

$status_filter = function ($v) use ($required) { 
                    return in_array($v['job_status'], $required); 
                 };
                
foreach (array_filter($projects, $status_filter) as $proj) {
    // process $proj
}

 

[Validation Question]

Simpler...

if (isset($_POST['account']) && ctype_digit($_POST['account'])) {
    // OK
}
else {
    // ERROR
}

 

[While loop to isolate array items help]

Use an array to get the ones you want...

$projects = array();

$required = ['Feasibility', 'Measure Up', 'Model Drawing', 'Concept Design', 'Developed Design', 'Resource Consent', 'Construction Documentation' ];

$xml=simplexml_load_string($response) or die("Error: Cannot create object");
 
foreach($xml->Jobs->Job as $item) {
    if (in_array((string)$item->State, $required)) {
        $projects[] = array(
                         'job_no'          => (string)$item->ID,
                         'job_name'        => (string)$item->Name,
                         'job_due'         => (string)$item->DueDate,
                         'job_status'      => (string)$item->State,
                         'job_staff'       => (string)$item->Assigned->Staff->Name,
                        );
    }
}
     
 usort($projects, function($a,$b) {return $b['job_due'] <=> $a['job_due']; } );       // reverse $a and $b for desc sort

// $projects = array_reverse($projects);          // no longer required

This code is just plain wrong...

while ($projects['job_status'] == ('Feasibility') 
          && ('Measure Up') 
          && ('Model Drawing') 
          && ('Concept Design'                ) 
          && ('Developed Design'               ) 
          && ('Resource Consent'               ) 
          && ('Construction Documentation')) {

and this is a waste of time and effort. Why just move a value from one variable to another and why put a single string variable inside quotes?

$formatted = date('d/m/Y', strtotime($proj['job_due'])); 
    $job_no ="{$proj['job_no']}";
    $job_name ="{$proj['job_name']}";
    $job_due ="$formatted";
    $job_status ="{$proj['job_status']}";
    $job_staff ="{$proj['job_staff']}";

 

[Populate array with recursive function]

The main thing when populating an array recursively is to pass the result array by reference. If you don't you are just passing a copy of the array to the next level and the copy is lost when the function returns.

Here's an example which scans a directory and adds the files to the results array.

If the file added is itself a directory then its subfiles are added to its subfiles array, and so on.

$results = [];
$path = "c:/inetpub/wwwroot/test/mytest";                            // start path

add_file($path, $results);

echo '<pre>', print_r($results, 1), '</pre>';                        // show results 


function add_file($fname, &$farray)                                  // NOTE : results array passed by reference
{
    $farray[$fname] = [];
    if (is_dir($fname)) {
        $files = glob("$fname/*");
        foreach ($files as $f) {
            add_file($f, $farray[$fname]);
        }
    }
}

Path structure

Results

Array
(
    [c:/inetpub/wwwroot/test/mytest] => Array
        (
            [c:/inetpub/wwwroot/test/mytest/dir_1] => Array
                (
                    [c:/inetpub/wwwroot/test/mytest/dir_1/dir_1_A] => Array
                        (
                            [c:/inetpub/wwwroot/test/mytest/dir_1/dir_1_A/dir_1_B] => Array
                                (
                                    [c:/inetpub/wwwroot/test/mytest/dir_1/dir_1_A/dir_1_B/fileE.txt] => Array
                                        (
                                        )

                                )

                            [c:/inetpub/wwwroot/test/mytest/dir_1/dir_1_A/fileD.txt] => Array
                                (
                                )

                        )

                    [c:/inetpub/wwwroot/test/mytest/dir_1/fileC.txt] => Array
                        (
                        )

                )

            [c:/inetpub/wwwroot/test/mytest/dir_2] => Array
                (
                    [c:/inetpub/wwwroot/test/mytest/dir_2/fileF.txt] => Array
                        (
                        )

                )

            [c:/inetpub/wwwroot/test/mytest/fileA.txt] => Array
                (
                )

            [c:/inetpub/wwwroot/test/mytest/fileB.txt] => Array
                (
                )

        )

)