Get data from a single record.

[marsheng]

I'm a bit stuck here. In Delphi, when finding a unique record in an SQL, the code puts you on that record and you can access the data directly.

 

From what I gather in SQL language,

"SELECT * FROM members_cams WHERE LicenceNo = ".$_POST["LicenceNo"]

returns a data set.

 

To get the results, you can run

while($row = $result->fetch_assoc()) {
  echo "MemName: ".$row["MemName"]." MNZId: ".$row["MNZID"]."<br>";  
}

If the License number is unique and there is only record, what is the easiest way to get the data into variables.

ie $MemName = $row["MemName"]

 

From what I gather SQL does not use record pointers as Dbase etc did in the past so I'm not sure how to get the data out of the set.

[Jacques1]

If you want only one record, then call fetch_assoc() only once.

 

However, your query is vulnerable to injection attacks, and the mysqli library you're currently using isn't very good, especially for beginners. Use PDO instead.

<?php

$database_connection = new PDO(...);

// create a prepared statement to safely pass the dynamic license number to the database system
$member_cam_stmt = $database_connection->prepare('
    SELECT
        MemName,    -- select *specific* columns, not just everything which happens to be there
        MNZID
    FROM
        members_cams
    WHERE
        LicenceNo = :licence_number   -- a placeholder for the licence number
');

// execute the prepared statement with the licence number from the POST parameters
$member_cam_stmt->execute([
   'licence_number' => $_POST['LicenceNo']
]);
C
// fetch a single row; make sure to check if $member_cam is null in case of an invalid number
$member_cam = $member_cam_stmt->fetch();

Your naming conventions are quite the mess with this random switching between PascalCase, snake_case, singular, plural. Choose one style and stick to it.

[marsheng]

Yes the case is a mess - a result of copying and pasting.

 

I'm working from an old book (3-4 years old) so I guess it is old style. 

 

I have to say getting the Web pages correct, learning languages, ensuring securities, interfacing with a Web server is quite a tall order. 

 

I'll look through PDO bits.

 

Thanks for your time.

[cyberRobot]

Just to clarify, injection attacks can also be prevented with MySQLi. PDO will likely be the better option though.

Edited July 7, 2017 by cyberRobot

[marsheng]

This probably all wrong, as I still battling with the concepts rather than actual code. I'm not worried about security at the moment, just want to get the basic code running.

 

Does the line below, $row,  have the all the fields information ?

<?php $row = $result->fetch_assoc();

Next, is the $row a 'global variable' or do I need to execute the above statement in each <?php> block?

 

Lastly, it does not like ($_POST['Password']) Probably because I'm thinking proceduraly.

       <?php if ($status == 1 ): ?>
            <?php $row = $result->fetch_assoc();
                echo "Hi ".$row["MemName"]."<br>";  
            ?>            
            Password: <input type="text" name="Password" />    
            <input type="submit" value="Enter">      
            <br>
            <?php if ($_POST['Password'] == $row['Password']): ?> 
                <p> Match</p>';     
            <?php endif; ?>                   
       <?php endif; ?>             

Edited July 7, 2017 by marsheng

[Jacques1]

And “doesn't like” means ... what?

 

I understant that you just want to get the code running, but you make this a lot harder when you're going back to spaghetti code and practices which you already know are wrong. It's a common misconception that prepared statements and HTML-escaping are “just security features” and somehow irrelevant during development. To the contrary. Their primary purpose is to make your code work. Security is just a nice side effect.

 

So instead of doing it wrong and then spending your time on bugfixes, start with a proper structure. As I already explained, all the business logic must be on top of the script, not scattered across your HTML markup. This also makes it a lot easier to handle different events which happen at different times -- like in your case.

 

When users visit the page for the first time, they make a plain GET request. You cannot access $_POST items, because there wasn't even a POST request. This happens after the page has been submitted. So in the business logic, you should first check what kind of request you're dealing with and then take the appropriate actions.

<?php

/* the business logic goes here */

// did we receive a POST request? then go through the log-in procedure
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    // this is where you do your database queries, check the credentials etc.
}

?>
<!-- at this point, you *only* generate HTML markup; do not include any complex code -->
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Title</title>
    </head>
    <body>

    </body>
</html>

[marsheng]

I think I have answered the first question by some trials. All the data is there.

            Name: <input type="text" name="Name" value=<?php echo $row["MemName"]?>/> 

The above works partially. The "value" gets the data up to any space and no more.

MemName = Joe Boggs

Value displays only Joe.

[Jacques1]

When you're ready for code that does more than kinda work from time to time, read my reply. Especially the part about escaping (and quoting the attributes, I should add).

 

Not trying to be mean, but you have a surprisingly lax attitude for somebody who normally uses super-unforgiving low-level languages like C or C++.

[gizmola]

I think I have answered the first question by some trials. All the data is there.

            Name: <input type="text" name="Name" value=<?php echo $row["MemName"]?>/> 

The above works partially. The "value" gets the data up to any space and no more.

MemName = Joe Boggs

Value displays only Joe.

 

Yes, because you are outputting an html string without quotes around it.

 

If you use a web developer tool or just view source in your browser of choice you should see the malformed html.

 

Should be:

 

Name: <input type="text" name="Name" value="<?php echo $row["MemName"]?>"> 

Notice I left out the "/" from the end of the input tag, with the assumption you are using HTML5. Self closing aka "Void tags" are not part of the HTML5 spec, and you should stop using them. They are a remnant left over from the days when people were still using xhtml, and that specification is dead and buried now. You should be using HTML5 now.

[marsheng]

Please bear with me for a bit longer. I know the code is not 'correct' but I'm still trying to think in HTML and not Delphi.

 

I am using the status variable to see what is happening.

status = 0 first time on the page

status = 1 valid entry so now require password.

status = 2 number not found

status = 3 invalid entry

 

So the issue is, once I have a valid License Number, how do I reuse the same form to input a password or anything else ?

 

Once I have fully understood the flow of the whole HTML system, I'll be in a better framework to look at the individual bits.

 

PS My brain works by doing and only a bit by reading.

<html>
<head>
    <title>CAMS Index Page</title>
    <link rel="stylesheet" href="Style.css">  
</head>
<body>
    <div id="container">
        <div id="content">   
            <?php
            if ($_SERVER['REQUEST_METHOD'] == 'POST') { 	
                if (!empty($_POST['LicenceNo'])) {
                    require ('sql_con.php');         //connect to database                    
                    $sql = "SELECT * FROM members_cams WHERE LicenceNo = ".$_POST["LicenceNo"];
                    $result = $dbcon->query($sql);
                    if ($result->num_rows > 0 ) {  
                        $status = 1;                        
                     }else {
                        $status = 2;                        
                    }                       
                    $e = mysqli_real_escape_string($dbcon, $_POST['LicenceNo']);
                  } else {
                    $e = FALSE;
                    $status = 3;
                }  
              }else {
                $status = 0; 
              }
            ?> 
        </div> 
    </div> ` 
    <form action=<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>  method="post">
        <?php if ($status == 0 ): ?>
            LicenceNo: <input type="text" name="LicenceNo" />     
            <input type="submit" value="Enter">
       <?php endif; ?>    
       <?php if ($status == 3 ): ?>
            LicenceNo: <input type="text" name="LicenceNo" />     
            <input type="submit" value="Enter">
            <p> Enter your MNZ ID number.</p>';
       <?php endif; ?>             
       <?php if ($status == 2 ): ?>
            LicenceNo: <input type="text" name="LicenceNo" />     
            <input type="submit" value="Enter">
            <p> Number not Found</p>';
       <?php endif; ?>    
       <?php if ($status == 1 ): ?>
            <?php $row = $result->fetch_assoc();
                echo "Hi ".$row["MemName"].$row["Password"]."<br>";  
            ?>            
            Password: <input type="text" name="Password" />    
            <input type="submit" value="Enter">      
            <br>
            <?php if ($_POST['Password'] == $row['Password']): ?> 
                <p> Match</p>';     
            <?php endif; ?>                   
       <?php endif; ?>             
    </form>     
</body>
</html>

[Jacques1]

PS My brain works by doing and only a bit by reading.

 

And my brain is starting to hurt.

 

Really, how hard can it be to put the PHP code on top of the script and the HTML markup to the bottom? Why do you keep making things more complicated with this tangled mess of spaghetti code? Programming with PHP isn't hard. You're making it hard for yourself.

 

Actually, none of this is PHP-specific. If you have done any serious Delphi projects, then I'm sure you've heard of concepts like Separation of Concerns or even the Model-view-controller Pattern. It's the exact same thing in PHP: You separate the business logic from the output (i. e. the HTML markup).

 

If you're truly unable to let go of the spaghetti code, then I suggest you skip the PHP-embedded-into-HTML part and jump straight to Twig. This forces you to use a clean structure, and maybe that's exactly what you need to overcome the mental block.

[marsheng]

I'm missing the fundamental concepts of client server relationships. (I think) I have an understanding of HTML and can do a bit of PHP, but I am missing how they work together.  I think once I get this, it will make it easier. It's a bit like being in Japan. I can read a map, I have a translation dictionary, but I will not be able to put a sentience together to ask a question until I understand the grammar.  I think for you, it is so obvious to you, you cant see why I'm stuck.  No doubt that if I read this post in 3 months time, I too will say its obvious.

 

I've read a lot and watched a heap of Youtube and they all seem to talk about the dictionary or map, not the bit that links them. If you have any suggestions, please let me know.  

[Jacques1]

Right now, all you have to do is follow simple rules. You don't need a deep understanding of PHP to realize that a clean code structure is a good idea. Like I said, this isn't even a PHP-specific topic. You (hopefully) do this is every language you're using.

 

PHP and HTML don't work together. PHP has no idea what HTML is. The parser simply reads the entire script and treats everything between <?php ?> tags as code and everything else as raw output. That's it. What the output means is irrelevant. It doesn't even have to be HTML markup, it might as well be plaintext or an Excel spreadsheet or anything you like. PHP merely takes the stuff outside of code tags and prints it as is.

 

I think you should start with more basic exercises before you jump to advanced topics like database queries. Writing a simple but proper form script will teach you much more than fumbling with a buggy mess of spaghetti code which does half of a log-in.

[marsheng]

I think I have found my issue.

 

HTML is a markup "language". The submit is part of this language. From the outset submit looks like a "function" as it executes a procedure, however, html, (from what I see) has no way of actually using the POST or GET data. The results are always used by another "language".  It is basically a pipeline between HTML and another language.

 

In essence the HTML page is just dumb doc type form with the results of functions/code run on the server embedded in the text.

 

The problem is that when you look at sample code, it is all on the same form and it looks like it is one piece of code.

[Jacques1]

Maybe the real problem is that you don't fully understand the HTTP workflow and what the client and server do when.

 

Web applications are very different from desktop application. There is no direct user interaction in the sense that the code is executed from top to bottom and immediately processes user input (this is also possible, but it's not how classical PHP scripts work). The client and server are working independently from each other and only communicate through HTTP messages.

 

The initial step is that the client asks the server for a particular page, say https://yoursite.com/contact.php. To retrieve the page content, it sends a GET request:

client ------ HTTP request: GET /contact.php -----> server

Note that the “.php” extension is just a (stupid) tradition of naming pages which are backed by PHP scripts. The client itself doesn't know anything about PHP.

 

Once the server has received the GET request, it executes the script to produce an HTML document. It's completely up to the script how it does that. It may execute a single block of PHP code with echo statements, it may execute multiple blocks of PHP code embedded into HTML markup, it may delegate the task to an external template engine, it may even read a static HTML document from a file or a database. This document is then sent in the client in the HTTP response:

client <----- HTTP response: OK; body contains HTML document ------ server

Now the HTML document happens to contain a form. The user can fill out this form and click the submit button which causes the browser to send the data in another request (say a POST request):

client ------> HTTP request: POST /contact.php; body contains form data -----> server

Upon receiving the request, the server again executes the script. But this time there's data from the POST request available, so the script is going to process that. In any case, the server has to send a response which may again contain an HTML document (like a success message) or instruct the client to go to another page.

client <----- HTTP response: OK; optional content in body ------ server

And that's it until the client makes another request for whatever it needs.

 

 

 

The problem is that when you look at sample code, it is all on the same form and it looks like it is one piece of code.

 

Well, only because you wrote it that way.

[marsheng]

Thanks, the penny has dropped, at least for a while. Changing POST to GET and seeing the url details has helped in my understanding. 

 

Thanks for your help.  Now I'll "fix" my code.

 

Is it a worthwhile exercise to use GETs while testing and then change to POST once it is up and running ?

[Jacques1]

I have no idea why you think the request method is relevant for any of this.

 

The workflow has nothing whatsoever to do with whether you use GET or POST for the form. It's always the same. The method only defines the semantics of the request when it is interpreted by the application. GET is for getting data and should not have any side effects, POST is for changing or adding data. So a search form would use GET (since it only retrieves data), but a registration form would use POST (since it's meant to add a new user).

 

None of this makes any relevant difference for your HTML form or the PHP code. Except that using GET for both the initial request and the submission forces you to distinguish between the two and is therefore slightly harder to process.

Edited July 9, 2017 by Jacques1