PHP Database Search

[andylpsx]

So I am still learning PHP and PDO and I am trying to compare a name that was wrote in a text box to what I have in my database. I tried with a query but the problem was when it found a matching first name in the database it would print it out regardless of if the last name and first name were together. Now I searched around and found prepare and using WHERE ... and that may be working but I can't figure out how to print the value from the database, with the query it was

foreach(db->query('SELECT........') as $cert) { }

and I could use the $cert['xxxxx']; to see the value from the database but with this prepare I can't figure out how to display what is in the database. my php currently looks like:

    $query = $db->prepare('SELECT UID, fname, lname, Date_Reg FROM users WHERE fname = :fnameForm && lname = :lnameForm');
    $query->bindValue(':fnameForm', $fnameForm);
    $query->bindValue(':lnameForm', $lnameForm);

The entire purpose is to build an image, a certificate, that users get when they pay their fees, I have the generating down and all that working but now I want to make sure that it reads from the database and knows who has paid and no. Basically if they are in the database they have paid so I have a

$fnameForm $_GET['fname'];

which shows the result from the form on the html page, that is supposed to be compared to the fname from the database, I think that is happening now but I need something to store in my $Name variable which is what is put onto the certificate, I can't use the $fnameForm because if they put in an incorrect name it would generate it anyway, I need to use the fname from the database but I don't know how to use that. Also will that code work for making sure that the name entered in the form is the same as the one stored in the database, also I know I could use strtolower($lnameform/$fnameForm) to make it lower case to check to make it more accurate but how can I do that with the fname and the lname of the database. I am still in the learning stages of PHP and I have been using my resources as best as I can, the PHP PDO manual and websites like this and StackOverflow, please help a beginner out.

 

[descalzo]

Sorry, I cannot get a clear picture of what you are doing.

1. What is the structure of the database table?
2. When do put an entry in there?
3. What is the form you are using? What is the purpose? Input fields transmitted?
4. What are you doing?

 

[essellar]

You will want to get the "canonical" values from the database, most likely. So you can lowercase both the submitted value and the stored value, returning the canonical (stored) value with a query like this:

$query = $db->prepare('SELECT UID, fname, lname, Date_Reg FROM users WHERE LOWER(fname) = LOWER(:fnameForm) && LOWER(lname) = LOWER(:lnameForm)');

Because you are using LOWER() in your SQL, there is no reason to lowercase the submitted values in your own code. The returned result will include the UID value, fname, lname and Date_Reg as they were recorded in the database. That may be as an object or as an associative array, depending on the rest of your code. In either case, you will be able to pull the first and last names from the database query results.

The real problem here is that there is a high likelihood of duplicate names and no way to figure out which is which. The user's login ID should at least be theoretically unique, like an enforced-unique user name (which, often as not, results in a very unprofessional-looking site with childish "handles" instead of grown-up users, but there are people who like pseudonymity) or an email address (which is by definition unique). There may be a thousands John Smiths, but there's only one john.smith@example.com.

 

[andylpsx]

You will want to get the "canonical" values from the database, most likely. So you can lowercase both the submitted value and the stored value, returning the canonical (stored) value with a query like this:

$query = $db->prepare('SELECT UID, fname, lname, Date_Reg FROM users WHERE LOWER(fname) = LOWER(:fnameForm) && LOWER(lname) = LOWER(:lnameForm)');

Because you are using LOWER() in your SQL, there is no reason to lowercase the submitted values in your own code. The returned result will include the UID value, fname, lname and Date_Reg as they were recorded in the database. That may be as an object or as an associative array, depending on the rest of your code. In either case, you will be able to pull the first and last names from the database query results.

The real problem here is that there is a high likelihood of duplicate names and no way to figure out which is which. The user's login ID should at least be theoretically unique, like an enforced-unique user name (which, often as not, results in a very unprofessional-looking site with childish "handles" instead of grown-up users, but there are people who like pseudonymity) or an email address (which is by definition unique). There may be a thousands John Smiths, but there's only one john.smith@example.com.

So I am jumping off the whole fname/lname area and switching to emails like you said, now I only have one question remaining.

I have my code to pull from the database:

    $db = new PDO('mysql:host=localhost;dbname=XXXXXXXX_cert14;charset=utf8', 'petrzilk_dbAdmin', 'XXXXXXX'); // Connecting to Database
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Error statement
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $query = $db->prepare('SELECT * FROM User_Data WHERE LOWER(email) = LOWER(:email)');
    $query->bindValue(':email', $email);

I also have a variable $name which is the value of fname and lname from the database, how could I save $name with the variables fname and lname. When I was doing a query I had

 (SELECT * FROM .....) As $cert);

and I could do

$cert['fname']

to grab my value for fname, now do I do that as my code sits.

Extra Info:

Now instead of a user entering their name, first and last, they enter their email which is checked and then their name is displayed because it is saved in the table with their email. Also will LOWER(email) work when an email looks like test123@example.com where there is a numerical value along with the string? I assume it does, just changing the case of the characters that are letters and ignoring the numbers.

My database is set up like:

UID
fname
lname
email
Date_Reg
Date_Exp

 

[essellar]

Yes. LOWER() will only affect the alphabetical characters, leaving numerals, special characters (like @) and punctuation alone.

 

[andylpsx]

Yes. LOWER() will only affect the alphabetical characters, leaving numerals, special characters (like @) and punctuation alone.

Alright but how do I use the information in the fields as variables as asked in the first part?

 

[essellar]

Um, howzabout using $query->fetch(PDO::FETCH_ASSOC) or $query->fetchObject() and assigning the result to a variable? Whether you decide to use an associative array or an object is up to you (objects are probably handier when dealing with more complex results and actions, where the stored data need to be transformed/translated and/or have "behaviours").