Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoid getting twice the same entry in database + question

    Hello all,

    First off, I'm fairly new to PHP, but I do know a little about databases.

    I have started building a website for a friend, even though my experience in the area is light at best. But I have plenty of time.
    I don't want to go into too much detail

    Anyway, the website will need to have an admin access (but only this one admin account, no subscribers, moderators, etc.), where he will be able to add news, add events linked to certain clients, events which would have some images linked to it. He will also be able to add clients, images and change the banner as well.

    I've created a database with phpmyadmin, called wwww, and a table called client, which had an aid (primary key), a fullname (varchar) and a description (text).

    I've tried making a very basic page where one can add a client to the table. However, the code I wrote to avoid entries with the same fullname does not seem to work as I wish it to.

    Here is test.php, which is where user inputs some data. Note that some common checks are not made (empty input, etc.)

    PHP Code:
    <!DOCTYPE html>
    <html>
        <head>
            <meta charset="utf-8" />
            <title>My blog</title>
        <link href="style.css" rel="stylesheet" /> 
        </head>
            
        <body> 
        <form action="testpost.php" method="post">
            <p>
                <label for="fullname">Full Client Name</label> : <input type="text" name="fullname" id="fullname" /><br />
                <label for="description">Biography</label> :  <input type="text" name="description" id="description" /><br />
                
            <input type="submit" value="Envoyer" />
            </p>
        </form>

    <?php
    // Connect to database
        
    try
        {
            
    $db = new PDO('mysql:host=localhost;dbname=wwww''root''');
        }
        
        catch(
    Exception $e)
        {
            die(
    'Error : '.$e->getMessage());
        }

        
    $answer $db->query('SELECT * FROM client');

        while (
    $data $answer->fetch())
        {
            echo 
    '<p><strong>' htmlspecialchars($data['fullname']) . '</strong> : ' htmlspecialchars($data['description']) . '</p>';
        }

        
    $reponse->closeCursor();

    ?>
    </body>
    </html>
    Here is testpost.php, which checks if entry isn't duplicate and then refers back to test.php.

    PHP Code:
    <?php
    // Connect to database
        
    try
        {
            
    $db = new PDO('mysql:host=localhost;dbname=wwww''root''');
        }
        catch(
    Exception $e)
        {
            die(
    'Error : '.$e->getMessage());
        }

        if(isset(
    $_POST['fullname']) AND isset($_POST['description']))
        {

            
    $_check $db->prepare('SELECT fullname FROM client WHERE fullname = ?');
            
    $_check->execute(array($_POST['fullname']));

            if(!
    $_check)
            {
                die(
    'Query failed for some reason');
            }

            if(
    mysql_num_rows($_check)>0)
            {
                echo 
    "Client already in database";
            }
            else{

                
    $req $db->prepare('Insert INTO client(fullname , description) VALUES(?,?)');
                
    $req->execute(array($_POST['fullname'],$_POST['description']));

                echo 
    "Successfully added client to database.";

                
    $req->closeCursor();
            }
        }
        
    header('Location: test.php');
    ?>
    A couple of questions more or less directly linked to this code. Some of them might be lack of understanding of some basic PHP, but most of it is linked to databases. I hope this is ok, and that it is the right place to post this.

    -The echo commands don't seem to do anything when I test this, even when I don't return to test.php. What is the reason ?

    -Is this good PHP coding practice ? Essentially, the previous version of the website I studied had some mysql issues. It made use of deprecated mysql commands. The thing is, all those commands were spread out through the entire source, so updating that would be really slow-going. To avoid this kind of problem with the website I'm going to make, I intend to write all those backend admin adding/editing/deleting commands on the same file. All the test files would point to the same testpost.php file, basically. Then I would have some separate files doing the displaying. I actually don't really feel the need to do much OOP. Is all this good coding practice ?


    -Does one have to type some SQL commands in PHPmyadmin in order to create foreign keys, or unique primary keys made from tuples ? I haven't found anything that enabled me to do it without SQL, and I would like a table that links clients to events using their respective ids (a client can take part in multiple events, and events can have multiple clients taking part in them).

    I'm pretty sure the questions I'm asking are fairly common issues, I hope that isn't too much of a bother.

    Anyway, thanks a lot,
    ogcoom

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,874
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Looks okay to me. Have you used a MySQL query tool to test it out? That is, eliminate the PHP code from the question until you are sure the SQL code is correct and doing what you expect.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Looks okay to me. Have you used a MySQL query tool to test it out? That is, eliminate the PHP code from the question until you are sure the SQL code is correct and doing what you expect.
    I've tested it in PHPMyAdmin using

    SELECT `fullname` FROM `client` WHERE `fullname` = 'test';

    It worked correctly.

  • #4
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also, for some reason I never see the text that is echoed.

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,066
    Thanks
    2
    Thanked 319 Times in 311 Posts
    When you are stating things are not being output/echoed, you need to be specific about what output you did and didn't get, since we are not standing right next to you, and knowing exactly which things are and are not being output tells us the execution path the code took or even if the code is running at all.

    You need to have php's error_reporting set to E_ALL and display_errors set to ON in your php.ini on your development system to get php to help you. While you are making that change to the php.ini, set output_buffering to OFF so that you don't fight output being discarded when you do a header() redirect on a page.

    You currently have a mysql_num_rows() statement in your code that won't work as expected and should be throwing a php error since you are using PDO commands.

    You should configure PDO to throw exceptions for prepare/query/execute errors and extend the try/catch block to be around all your database code. You can configure PDO to do this by adding $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); right after the line that creates the database connection. This will eliminate the need to have inline code testing if each database command resulted in an error or not.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your reply.

    I have had a looked at any php.ini files on my computer, all of them have the settings you mentioned. I've also added the set attributes line you mentioned and changed the try/catch to encompass the whole PDO code.

    I simply don't understand why they still are being added. Oh, and by echo command, I meant the echo commands in the above code. They are never printed anywhere on the generated webpages, as far as I can see. What I do see is the fact that my list of entries increases by one every time I send the form, regardless of whether it is in the entry already or not.

    I suppose another way would be to go into phpmyadmin and create a rule to avoid entries with the same name. I'd rather do it in the PHP code though.

    I do realise my setup isn't helping me out for debugging. I'm only using notepad++ at the moment. I think I'll configure Eclipse for PHP and use a debugger as I am used to that environment.

    Oh, and changing the mysql_num_rows to

    PHP Code:
    $rows $_check->fetch(PDO::FETCH_NUM);

    if(
    $rows[0]>0){
    ... 
    doesn't seem to solve my problem.

    I'm sorry if this is a really obvious problem, I don't seem to be able to find it. But thanks again for the help.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,874
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    When in doubt: DEBUG DEBUG DEBUG

    If you don't have a debugging environment, just use more echo's.

    Example:
    Code:
        $fname = $_POST["fullname"];
        $descr = $_POST["description"];
        echo "DEBUG: isset? fullname : " . isset($fname] . "; description: " . isset($descr) . "<br/>"
        echo "DEBUG: values? fullname : $fname; description: $descr<br/>"
    
        if(isset($fname) AND isset($descr)) 
        {
    And so on. One step at a time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,066
    Thanks
    2
    Thanked 319 Times in 311 Posts
    If you weren't getting a php error message at the previous use of mysql_num_rows(), that would indicate that the php.ini that is being used by php doesn't have the error_reporting/display_errors settings in it (you can check which php.ini is being used by php by checking the output from a phpinfo() statement as the Loaded Configuration File value) or your code/server is doing something to hide both the output from your echo statements and any php errors.

    Your statement that the code is inserting (duplicate) rows suggests that the code itself is running, assuming that the rows you are seeing in the database table are not left over from previous testing.

    The code you posted in reply #6 won't test for the existence of a row. It's fetching, if any, the row and testing if the zero'th element, when treated as a number, is greater than a zero. For both no row and a row, this comparison will not be true. For no row, $row will be a false, $row[0] won't exist and a null value is not greater than zero. For a row, $row[0] will be the firstname value and when treated as a number, unless it starts with numerical characters, it will be a zero, which is not greater than zero.

    You need to actually test if the query matched a row or not. If you are only ever going to use a mysql database with this code, the PDOStatement ->rowCount() method will tell you how many rows a SELECT query matched.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot, the rowCount worked like a charm, thanks a bunch !

    The phpinfo() statement returned the apache php.ini file, which is thusly configured :


    ; display_errors
    ; Default Value: On
    ; Development Value: On
    ; Production Value: Off

    ; display_startup_errors
    ; Default Value: Off
    ; Development Value: On
    ; Production Value: Off

    ; error_reporting
    ; Default Value: E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED
    ; Development Value: E_ALL
    ; Production Value: E_ALL & ~E_DEPRECATED & ~E_STRICT

    ; html_errors
    ; Default Value: On
    ; Development Value: On
    ; Production value: On

    ; log_errors
    ; Default Value: Off
    ; Development Value: On
    ; Production Value: On


    ; output_buffering
    ; Default Value: Off
    ; Development Value: 4096
    ; Production Value: 4096


    Only thing I haven't figured out is why those echo statements don't show up, but that is a minor detail with a likely simple solution.

    Thanks again !


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •