Jump to content

Fatal error: Uncaught mysqli_sql_exception:


phpsane

Recommended Posts

Folks,

 

I am trying to create a Login sytem where the user can login to his account by either typing his Username or Email and Password. Like you do with your Youtube account.

I get this error:

 

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM users WHERE usernames = ? OR emails = ? AND passwords = ?' at line 1 in C:\xampp\htdocs\id\login_2.php:32 Stack trace: #0 C:\xampp\htdocs\id\login_2.php(32): mysqli_prepare(Object(mysqli), 'SELECT accounts...') #1 {main} thrown in C:\xampp\htdocs\id\login_2.php on line 32

 

I coded like this:


//Select Username or Email to check against Mysql DB if they are already registered or not.
$stmt = mysqli_stmt_init($conn);
if($stmt = mysqli_prepare($conn, "SELECT accounts_activations, usernames, emails, FROM users WHERE usernames = ? OR emails = ? AND passwords = ?"))
{
mysqli_stmt_bind_param($stmt, 'sss', $username_or_email, $username_or_email, $password);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt); //Use either this line, or ...
//$result = mysqli_stmt_bind_result($stmt, $db_account_activation_state, $db_username, $db_email, $db_password); // ... this line. But not both.

This forum is breaking my indentations.

login_2.php

Link to comment
Share on other sites

This not working either:

 

 

$stmt = mysqli_stmt_init($conn);
if($stmt = mysqli_prepare($conn, "SELECT accounts_activations, usernames, emails, FROM users WHERE usernames = ? OR emails = ? AND passwords = ?"))
{
mysqli_stmt_bind_param($stmt, 'sss', $username_or_email, $username_or_email, $password);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt); //Use either this line, or ...
//$result = mysqli_stmt_bind_result($stmt, $db_account_activation_state, $db_username, $db_email, $db_password); // ... this line. But not both.
 
$row = mysqli_fetch_array($result, MYSQLI_ASSOC); 
 
// Check if inputted Username or Email is registered or not.
//Either type following paragraph or the next one but not both. Ask in forum which one is best.
 
// PARAGRAPH 1 
if ($username_or_email !== $row['usernames'] && $username_or_email !== $row['emails']) && password_verify($password, $row['passwords']) // either this paragraph or ...
{
echo "Paragraph 1: That Username or Email is not registered!"; 
exit;
}
else
{
if($row['accounts_activations'] == '0')
{
echo "Paragraph 1: You have not activated your account yet! Check your email for instructions on how to activate it. Check your spam folder if you don't find an email from us.";
exit;
} 
} 
Link to comment
Share on other sites

Pro Tip: Place a line break between logical sections of your query. It helps to "see" the query better and you would have likely found the error yourself

SELECT accounts_activations, usernames, emails,
FROM users
WHERE usernames = ?
   OR emails = ?
   AND passwords = ?

Do you see it now? Take a look at the end of the SELECT section.

 

Also, you need to read up on order of precedents with regard to AND/OR conditions. They are read Left-to-Right. The first check is for a match on username. Then there is an 'OR" condition. If that first check is true, the rest of the conditions are not evaluated. In other words, as long as the  user enters a matching username it doesn't matter what the password is

 

In fact, the logic (as I alluded to in another post) is all fouled up. You are using the password as passed form the user in your query. But, you are storing a hashed password - so it could never match. Plus, since you are looking for a match on the username OR the email address, it is possible to get TWO results back. I really suggest that you need to get out a pencil and paper and "walk through" what the logic should be.

Link to comment
Share on other sites

Pro Tip: Place a line break between logical sections of your query. It helps to "see" the query better and you would have likely found the error yourself

SELECT accounts_activations, usernames, emails,
FROM users
WHERE usernames = ?
   OR emails = ?
   AND passwords = ?

Do you see it now? Take a look at the end of the SELECT section.

 

Also, you need to read up on order of precedents with regard to AND/OR conditions. They are read Left-to-Right. The first check is for a match on username. Then there is an 'OR" condition. If that first check is true, the rest of the conditions are not evaluated. In other words, as long as the  user enters a matching username it doesn't matter what the password is

 

In fact, the logic (as I alluded to in another post) is all fouled up. You are using the password as passed form the user in your query. But, you are storing a hashed password - so it could never match. Plus, since you are looking for a match on the username OR the email address, it is possible to get TWO results back. I really suggest that you need to get out a pencil and paper and "walk through" what the logic should be.

 

Thanks. I had the "passwords" column mentioned but then deleted it. Have put in back in again.

The logic is this:

 

Username & Password

Or

Email & Password

 

You are right. For some reason I am getting logged in without typing the password.

Even this not working:

if (($username_or_email == $row['usernames']  || $username_or_email == $row['emails']) && password_verify($password, $row['passwords']))
Link to comment
Share on other sites

Psycho,

 

I reckon this would work:

($username_or_email == $row['usernames']  || $username_or_email == $row['emails'] && password_verify($password, $row['passwords']))

For this logic:

 

Username & Password

Or

Email & Password

 

I get error that there are no match and I guess it is due to what you said about me mixing both the hashed and non-hashed password. Agree ? So, how to fix it then ?

Tried this too with no success:

 

if ($username_or_email == $row['usernames']  || $username_or_email == $row['emails'] && $password = $row['passwords'])
Link to comment
Share on other sites

Psyco,

 

Atleast any of these should have worked:

 

 

 

if (($username_or_email == $row['usernames']  || $username_or_email == $row['emails']) && $hashed_password = $row['passwords'])
$hashed_password = password_hash($password, PASSWORD_DEFAULT);
if ($username_or_email == $row['usernames']  || $username_or_email == $row['emails'] && $hashed_password = $row['passwords'])
Link to comment
Share on other sites

Pycho,

 

Atleast either these should have worked:

 

 

if ($username_or_email == $row['usernames']  && password_verify($hashed_password, $row['passwords']) || $username_or_email == $row['emails'] && password_verify($hashed_password, $row['passwords']))

 

 

if ($username_or_email == $row['usernames']  && password_verify($password, $row['passwords']) || $username_or_email == $row['emails'] && password_verify($password, $row['passwords']))

 

Right ?

Link to comment
Share on other sites

Psycho,

 

This probably not working due to  2 sets of results getting pulled. Just like you said. And so how to rid this new problem ? How to check ho wmany sets of result is getting pulled ? I've forgotten it now how to check.

if ($username_or_email == $row['usernames']  && password_verify($hashed_password, $row['passwords']) || $username_or_email == $row['emails'] && password_verify($hashed_password, $row['passwords']))
Link to comment
Share on other sites

S L O W   D O W N. Take a breath.

 

As I've stated previously, you need to think through the logic before you write the code. When you see a problem don't just jump to a conclusion on how to fix it and start throwing things at the wall to see what works. Once you have decided on the logic and have implemented what you have already planned, if you see an error in the results you need to review the planned logic and determine what the actual flaw is.

 

So, let's start at the beginning. You want users to log in via their username OR their email address. So, first you need to modify the query to pull any records where there is a match on the username or email address (the password should NOT be part of the query).

 

From there, you can have three outcomes:

 

1) No records are returned. In this scenario you can run the failed to authenticate logic.

 

2) Only ONE record is returned. In this scenario you should run the logic to continue the authentication process (i.e. validating the password) and go fro there.

 

3) Two (or more) records are returned. One matched on username and one matched on email address (do you restrict the user of usernames that look like email addresses?). Not knowing if you put unique constraints in the database for those two fields you could get more than two records. For this scenario, you need to decide what you want to do. I guess you could see if the password is correct for one of the records returned. But, depending on how you have set it up, this may not be even possible.

 

This code makes no sense

 

if ($username_or_email == $row['usernames']  && password_verify($hashed_password, $row['passwords']) || $username_or_email == $row['emails'] && password_verify($hashed_password, $row['passwords']))

 

Once the query has returned a record you only need to validate that the user supplied password matches the password returned in the query.

 

if(password_verify($hashed_password, $row['passwords']) ) { 

 

Including the username/email address in that validation is unnecessary because you know the record already matches on one of those because it was returned in the result set.

Link to comment
Share on other sites

S L O W   D O W N. Take a breath.

 

As I've stated previously, you need to think through the logic before you write the code. When you see a problem don't just jump to a conclusion on how to fix it and start throwing things at the wall to see what works. Once you have decided on the logic and have implemented what you have already planned, if you see an error in the results you need to review the planned logic and determine what the actual flaw is.

 

So, let's start at the beginning. You want users to log in via their username OR their email address. So, first you need to modify the query to pull any records where there is a match on the username or email address (the password should NOT be part of the query).

 

From there, you can have three outcomes:

 

1) No records are returned. In this scenario you can run the failed to authenticate logic.

 

2) Only ONE record is returned. In this scenario you should run the logic to continue the authentication process (i.e. validating the password) and go fro there.

 

3) Two (or more) records are returned. One matched on username and one matched on email address (do you restrict the user of usernames that look like email addresses?). Not knowing if you put unique constraints in the database for those two fields you could get more than two records. For this scenario, you need to decide what you want to do. I guess you could see if the password is correct for one of the records returned. But, depending on how you have set it up, this may not be even possible.

 

This code makes no sense

if ($username_or_email == $row['usernames']  && password_verify($hashed_password, $row['passwords']) || $username_or_email == $row['emails'] && password_verify($hashed_password, $row['passwords']))

Once the query has returned a record you only need to validate that the user supplied password matches the password returned in the query.

if(password_verify($hashed_password, $row['passwords']) ) { 

Including the username/email address in that validation is unnecessary because you know the record already matches on one of those because it was returned in the result set.

 

Thanks. I was going mad last-night trying to sort this mess before I go to sleep so I can sleep satisfied that the problem is sorted. Hence was trying all possibilities and in the process driving you crazy. Sorry!

Now taking a different route, which I will mention at the end. Another logic. Logic Number 2.

Anyway, you gave 3 out-comes but I reckon the way I am trying to code it the 3rd out-come not a possibility. This is why ...

 

Old Logic (Logic Number 1):

Username & Password matches;

Or

Email & Password matches;

 

Since the "Username or Email" field will only take-in one entry (either Username or Email) then there is no possibility of 2 matches (both Username & Email). And so, 2 records should not be returned ? Do you agree this makes sense that only one result can be returned ? Yes or no ? Ok, So now we have weeded-out your 3rd out-come. (Not your fault to think there are 3 outcomes as I drove you crazy to the all, lastnight. My panic's fault.

Anyway, since I am a little relaxed now, a thought came into my mind. Instead of getting php to query to find either the Username match or the Password match, why don't we just tell it which one to check like this ....

 

New Logic (2nd Logic)

Option 1

IF "Username or Email" field contains an email then only check for email match;

Else check for Username match.

 

This narrows things down, right ?

 

Or, I can provide 2 fields instead of 1. One for Username and another for Email. And, give the user a choice to fill-in either.

Logic 3:

IF "Username field contains an input then only check for Username match;

Elseif Email field contains an input then only check for Email match;

Else, Check for Username match.

 

Which one would you go for ? Maybe not the 3rd Logic as 3 fields (let's not forget the password field) looks cluttered.

So now we have a choice between the old logic (Logic Number 1) or the new one (Logic Number 2).

Now, before I change my code to Logic Number 2, I made another attempt at Logic Number 1. Look:

 

 

if ($username_or_email == $row['usernames']  && password_verify($hashed_password, $row['passwords'])) // either this paragraph or ...
{
    if($row['accounts_activations'] == '0')
    {
        echo "Paragraph 1: You have not activated your account yet! Check your email for instructions on how to activate it. Check your spam folder if you don't find an email from us.";
        exit;
    } 
}
elseif ($username_or_email == $row['emails'] && password_verify($hashed_password, $row['passwords']))
{
    if($row['accounts_activations'] == '0')
    {
        echo "Paragraph 1: You have not activated your account yet! Check your email for instructions on how to activate it. Check your spam folder if you don't find an email from us.";
        exit;
    }
}
else
{
    echo "Paragraph 1: That Username or Email is not registered!";
    echo "Password $password<br>"; //echo for debugging purpose
    echo "Hash $hashed_password"; //echo for debugging purpose
    exit; 
} 
Link to comment
Share on other sites

Again, you are not addressing the source of the problem, but rather the symptom. Have you fixed the issue with the Query? Have you decided whether you are going to check the password as part of the query or not? If not, then the query should ONLY look for matches on the username or email address. Then check the password in the code as you have above. Or, you can check the password as part of the query (by hashing the user supplied password and comparing to the records). If you go that route you only need to check if a record was returned or not. You do not (and should not) include a password check in BOTH the query and in the PHP code. It is unnecessary and will cause problems.

 

But, I can tell you right now that the logic you posted above is far from ideal. In fact, it has a major flaw. The else condition assumes that there is no matching username or email address. But the first two conditions check for a (username AND a matching password) OR (email AND a matching password). So, there could have been a record that matched the username or email address - but the password is wrong. Yet, your else condition assumes there was no matching record.

 

In retrospect why are you even including the email address and username in the condition checks? That is completely illogical since you had to do a DB query to return records that matched on username/email address so why in the world do you need to check them in the PHP code? Stop and think about the code you are writing and ask yourself what you are trying to accomplish and why. You only need to check the password returned in the record (assuming there was one).

 

Also, by having a message such as "That Username or Email is not registered!" means your application will "leak" data and could be used for malicious purposes. E.g. a malicious user could spam login attempts with different,random email addresses to find ones that are valid (based on the error message returned) and then use those for spamming purposes. When a user fails authentication you should NOT tell them why authentication failed (e.g. no username match vs. wrong password).

 

Here is what you should do:

 

1. Run a query looking for matches ONLY on the username or email address. You could include the password, but there is a good reason not to do that. In order to include logic to prevent brute force attacks (an automated attack against a known userid and random passwords looking for a match) you will want to track failed login attempts. So, you want the user returned even if the password doesn't match so you can update some data to track the failed login count and take action when appropriate (e.g. lock the user out for some period of time after X number of failed attempts - be sure to reset the count after a successful attempt)

 

2. If no records are returned, call a function/process to provide the response for a failed login attempt. Having a separate process is important - see below. It should be a generic message such as "We are unable to verify the credentials provided". It should not say we are unable to find a matching user record or that the password is wrong.

 

NOTE: The problem I referred to above about more than one record will be a problem unless you can ensure that one user's username could not match another user's email address. I.e. email addresses much contain a validly formatted email address and usernames cannot be in an email address format. If that is the case, then you would only ever have 0 or 1 records returned. But, if you don't ensure that then you DO have to solve the problem of when there are multiple records returned. Also, do you have a uniqueness constraint on your table to ensure the same username or email address doesn't exist in the table?

 

3. Assuming one records was returned. Verify the password supplied by the user to the password hash returned in the query results. If the password was incorrect: update the failed login attempts (if you are doing that) then call the same process as in #2 to tell the user that the login failed. That way you know that you will always be giving the same response regardless of whether the user could not be found or if their password is incorrect.

 

4. If the password matches then perform a check to see if the user has completed registration. If not, give them the appropriate response. Otherwise, perform the login functions.

Link to comment
Share on other sites

Psycho,

 

ISSUE 1: DO NOT REVEAL IN ECHOS WHETHER USERNAME OR PASSWORD WAS WRONG

You forgot. You had actually told me before not to give-out info to the user whether the username or Password was wrong and I did fix this on another version of this script. But when it started malfunctioning on another issue I deleted it and continued from this old version and here I forgot to fix this part where I give away details to what is incorrect. Username or Password.

Look at your post (entry 7):

https://forums.phpfreaks.com/topic/304939-why-serverrequest-method-post-fails-to-trigger/?do=findComment&comment=1551130

 

Anyway, now working on one version only and fixed it.

 

HISTORY OF THE PROJECT & SCRIPT

Infact, look at post 13 here of another programmer's (mlukac89) advice (which I was already aware of but forgot):

https://www.sitepoint.com/community/t/improvements-to-member-registration-site-reg-php/260491/13

He, mlukac89, did the PREP STMTS on the registration.php in that forum. This script comes from that forum and particularly that thread.

Post 16 (Pdo version):

https://www.sitepoint.com/community/t/improvements-to-member-registration-site-reg-php/260491/16

Post 52 (Mysqli version):

https://www.sitepoint.com/community/t/improvements-to-member-registration-site-reg-php/260491/52

 

Kicken did the PREP STMTS on the account_activation.php on devshed.com. Your partner forum (I gathered this from Requinix's growlings at me that devshed.com is the sister forum of this forum).

http://forums.devshed.com/php-development-5/improvements-regsitration-site-reg-php-977810.html

Look at post 12 to see Kicken's post. Look at post 13 to see what errors Kicken's code was producing. He did say there that, he did not test his code (PREP STMTS).

Anyway, I fixed Kicken's code in this forum and the account_activation.php was working on the PREP STMTs.

 

Anyway, both registration.php (originated at sitepoint.com and PREP STMT added by mlukac89) and account_activation.php (originated at sitepoint.com but continued at devshed.com here Kicken added PREP STMT) and login.php (originated at sitepoint.com but continued on this forum by me to add PREP STMT) are the same script and project: Member Registration Site.

 

So, Psycho (Mod), Requinix (Admin), Mac_Guyver (Mod), GinerJm and Sepodati, if you are wondering where my codes are coming from then now you know the FULL history.

Look how they complain why my code is like this or like that:

https://forums.phpfreaks.com/topic/304963-fatal-error-uncaught-typeerror-sha1-expects-parameter-1-to-be-string-integer-given/

Therefore, complainers, check these links for PREP STMTs work done by others. If you find any flaws then you know why my code is flawed. If you don't find any flaws in their codes then you know the flaws are the result of my own additions and/or modifications.

 

ISSUE 2: LOGIN AUTHENTICATION LOGIC

Anyway Psycho (Mod), I have understood your hints and would go by your hinted suggestion. You can feel relieved now. Sigh! ;)

I will use this logic:

 

Check "Username or Email" field. If "@" exists then take the input as an email and check for email match. Else, take the input as a "Username" and check for Username match.

Finally, check for Password match.

That way, there would be one IF for Username or Email match. And, another IF for Password match. Therefore, each IF i likely to get 1 results and not risk getting 2 if I were to check for Username-Password or Email-Password combination checks.

You see Requinix, I do learn and act upon your staffs' suggestions. And you tell me they complain that I don't take their uggestions and ask the same questions over and over again boring them all! Lol!

 

 

ISSUE 4: UNIQUE COLUMNS IN TBL: USERNAME & EMAILS

Anyway, looking at my Usernames and Emails fields, it seems on this project, I forgot to make them UNIQUE. On my previous project I made them UNIQUE.

So, I just deleted those 2 columns and tried creating each of them UNIQUE. But look at the imgs. When I try creating them UNIQUE, I get a prompt and I do not know what to type there.

Only 1 column is PRIMARY: id. And it is AI (Auto Increment). What should I type in the prompt you see in the imgs ?

 

 

post-204793-0-65386100-1505252482_thumb.png

post-204793-0-33527000-1505252509_thumb.png

 

 

ISSUE 5: QUERYING

>>You say: In retrospect why are you even including the email address and username in the condition checks? That is completely illogical since you had to do a DB query to return records that matched on username/email address so why in the world do you need to check them in the PHP code?<<

 

Do you mean, I hold not have them both but one of these 2 ? I believe you prefer the first one and feel I should weed-out the 2nd ? Right ?

 

1. 

if($stmt = mysqli_prepare($conn, "SELECT usernames, emails, passwords FROM users WHERE usernames = ? OR emails = ? OR passwords = ?"))

.

2.

if ($username_or_email == $row['usernames']  && password_verify($password, $row['passwords']))
Link to comment
Share on other sites

So, Psycho (Mod), Requinix (Admin), Mac_Guyver (Mod), GinerJm and Sepodati, if you are wondering where my codes are coming from then now you know the FULL history.

Accurate that you left yourself off that list, since all you do is take what we write and fuck it up.

Link to comment
Share on other sites

phpsane,
 
This is a "help" forum not a "do it for you" forum. When you post a problem, people will look at the specific problem you present them with and provide a possible solution. Most responders are not typically going to take three steps back and look at your overall project to determine if what you are asking for is the right question. Just because someone provides you a solution to exactly what you ask for does not mean what you are trying to accomplish is the right thing. If someone asks how to take the lug nuts off a tire and I tell them how, but later find out that they are trying to change the oil, it doesn't mean my response is bad. It means they are not asking the right question.
 
Everyone has to start somewhere. but, what can be rally dangerous is someone who has a little knowledge who overestimates their abilities. Sometimes it seems (to me) that you think you understand what we are saying and fail at actually putting it into practice. n other cases it seems like you completely ignore advice. Understand that none of us are paid to respond. We do it out of our own volition to help those who want to learn and contribute. So, it is very frustrating when it appears someone is not listening.
 

Anyway, looking at my Usernames and Emails fields, it seems on this project, I forgot to make them UNIQUE. On my previous project I made them UNIQUE.

So, I just deleted those 2 columns and tried creating each of them UNIQUE. But look at the imgs. When I try creating them UNIQUE, I get a prompt and I do not know what to type there.

Only 1 column is PRIMARY: id. And it is AI (Auto Increment). What should I type in the prompt you see in the imgs ?

 

And why did you delete the columns instead of just adding a unique constraint to the existing columns? This is another example of just going off on a tangent without knowing how to perform the task at hand. I will say it again "Slow Down". Give those indexes any name you want. For single fields unique constraints I just name then the same as the field.

 

 

 

ISSUE 5: QUERYING

>>You say: In retrospect why are you even including the email address and username in the condition checks? That is completely illogical since you had to do a DB query to return records that matched on username/email address so why in the world do you need to check them in the PHP code?<<

 

Do you mean, I hold not have them both but one of these 2 ? I believe you prefer the first one and feel I should weed-out the 2nd ? Right ?

 

I don't understand your response. So, let me provide a more direct example.

 

You first ran a query such as 

 

SELEDCT * FROM table_name WHERE username = ? OR email_address = ?

So you KNOW that any record returned MUST match the username or email address, correct? Therefore, when you want complete the validation you ONLY need to do this:

 

if(password_verify($password, $row['passwords']))

Complicating the condition to check with something like (mock code)

 

if( (username=username AND password=password) OR (email=email AND password=password) )

is completely asinine and only complicates your code to create errors that have plagued you this whole time. As I've stated before, think about what you are checking and, most importantly, "why". Take your time and plan out the logic before hand.

Link to comment
Share on other sites

Psycho,

 

I was reading on Arrays again as I need to learn how to dump column data to array. Will read on some code samples then edit them to my needs. If I get stuck, then will open a new thread. If I get no replies I'll PM Sedopati and bug him till he drops dead. Only joking. I'll try some new forums and give this one a break from time to time.

 

>>This is a "help" forum not a "do it for you" forum. <<

Yeah, I know. That is why I never asked you to build the whole script for me. Just asked for feed-back on this forum on my work so I (and not you or anybody) get a chance to fix the flaws which I am failing to see but others see before I offering for Gpl.

 

>>And why did you delete the columns instead of just adding a unique constraint to the existing columns? This is another example of just going off on a tangent without knowing how to perform the task at hand.<<

 

I tried editing it but the editing screen did not give the option to make the column UNIQUE. Therefore, deleted to start-over. Using Xampp.

 

 

>>Sometimes it seems (to me) that you think you understand what we are saying and fail at actually putting it into practice. n other cases it seems like you completely ignore advice.<<

 

Where does it seem that I ignored anyone's advice ? Sometimes, I think I understand but understand it wrong. That is probably where you see me not making any progress to where you expect me to make amends. But, I do not ignore. So tell me where I ignored and I will double check if I overlooked anything. Don't forget to reply to this.

My code now looks like this on the IF/ELSE/ELSEIF nesting.

if (!$result) // either this paragraph or ...
{
    echo "Paragraph 1: Incorrect User Credentials!";
    echo "Username/Email did not match!<br>"; //echo for debugging purpose. Remove from release version
    echo "Username/Email $username_or_email<br>"; //echo for debugging purpose. Remove from release version
    exit; 
}
elseif (password_verify($password, $row['passwords']))
{
    if($row['accounts_activations'] == '0')
    {
        echo "Paragraph 1: You have not activated your account yet! Check your email for instructions on how to activate it. Check your spam folder if you don't find an email from us.";
        exit;
    }
}
else
{
    echo "Paragraph 1: Incorrect User Credentials!";
    echo "Password $password did not match!<br>"; //echo for debugging purpose. Remove from release version
    echo "Hashed-Password $hashed_password not present on db!"; //echo for debugging purpose. Remove from release version
    exit; 
}
Link to comment
Share on other sites

Psycho,

 

 

>>Give those indexes any name you want. For single fields unique constraints I just name then the same as the field.<<

 

I copied you and gave the INDEX the same name as the field. But I get duplicate error nonsense, like the img you see attached. Strange! Just another hindrance out of the blue. Sigh!

 

 

post-204793-0-09462200-1505266289_thumb.png

Link to comment
Share on other sites

It's not nonsense, it's error reporting. It's MySQL telling you the mistake you made so you can correct it. You don't even make an attempt at it and just run back here to shit out the latest thing you saw.

 

You can't make a field UNIQUE if the values are not UNIQUE. Funny how that works, eh? MySQL is telling you that you have more than one row with the same username value, an empty string, so how is it supposed to make that field unique?

 

Try DELETE TABLE 'users' and that should fix everything.

Link to comment
Share on other sites

I tried editing it but the editing screen did not give the option to make the column UNIQUE. Therefore, deleted to start-over. Using Xampp.

 

I use XAMPP with PHPMyAdmin and have no problems adding a unique constraint. Plus, you can do it by running an SQL query. All you would have to do is a little googling to figure out how to do either.

 

 

I copied you and gave the INDEX the same name as the field. But I get duplicate error nonsense, like the img you see attached. Strange! Just another hindrance out of the blue. Sigh!

 

That is because you already have duplicates in that field. You cannot already have duplicates in a field when you change it to unique. Run this query to see which names have duplicates

 

SELECT usernames, count(usernames) as usercount
FROM users
GROUP BY usernames
HAVING usercount > 1
Link to comment
Share on other sites

It's not nonsense, it's error reporting. It's MySQL telling you the mistake you made so you can correct it. You don't even make an attempt at it and just run back here to shit out the latest thing you saw.

 

You can't make a field UNIQUE if the values are not UNIQUE. Funny how that works, eh? MySQL is telling you that you have more than one row with the same username value, an empty string, so how is it supposed to make that field unique?

 

Try DELETE TABLE 'users' and that should fix everything.

 

Is Mysql telling me I got more than one row with the same values ? I thought it was telling me I got duplicate column name. kept getting that error about 5-6 time in those attempts. And so, I did not jut happen to "run here" just like that!

Anyway, that was bad avice telling em to delete/drop the whole tbl as it had about 50 columns!

I just deleted all the entries and it worked. Problem solved. Anyway, thanks for atleast trying to help.

Link to comment
Share on other sites

 

I use XAMPP with PHPMyAdmin and have no problems adding a unique constraint. Plus, you can do it by running an SQL query. All you would have to do is a little googling to figure out how to do either.

 

 

 

That is because you already have duplicates in that field. You cannot already have duplicates in a field when you change it to unique. Run this query to see which names have duplicates

SELECT usernames, count(usernames) as usercount
FROM users
GROUP BY usernames
HAVING usercount > 1

 

Ooops! I already deleted all the entries.

Thanks for that line of sql query, though!

You know, I saw you can query mysql in one lang (sql lang) and do the same query with php in php lang. And, sql lang seems harder to remember and so I quit. Since learning php then why bother wasting brain cells learning the same thing in 2 langs ? So sticking to php code to query mysql. :)

You guys are either really good memorizing all that sql lang commands or you use the php myadmin's mysql tool to generate the sql query. How did you do it , from the top of your head ?

Anyway, starting php tutorial for today. When I have questions or get stuck, I "will run here" as Sedopati put it. And he can provide me with the answers as much as he can take. Lol!

Gonna flood this forum with precious questions. :)

Link to comment
Share on other sites

Queries are done using the SQL language.  Period.  Yes - there are some subtle differences between various database implementations of SQL but basically they are all the same.

 

PHP does not talk to databases.  The selected database interface (MySqlI or PDO) does the talking for PHP by submitting an SQL statement.  PHP uses functions to reach the db and those functions are issuing sql commands, not php code.  You need to understand this.

 

And we don't all write code from the tops of our heads.  Those of us that are unfamiliar with certain aspects of coding RELY ON THE MANUAL to refresh our memories or to clarify certain uses of various parts of the PHP language.  Those that write code full-time probably, over time, do have the entire syllabus memorized.  Those that don't do it full time (like this retired guy) have managed to memorize the parts that are used the most but then have to quickly leaf thru the manual for the proper syntax of those functions that don't come up that often.   A newbie like you will need to keep the manual close by but gradually can rely on it less as you (hopefully) pick up the necessary language skills required in whatever project you are working on.

 

I'll probably regret saying this, but I'd love to see some code of yours that queries a db without using SQL.  You could win a prize with it!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.