PHP Mysql Data Insert

Question

I'm learning PHP from reading the php manual and studying different tutorials. I hit a snag with the mysql_query. I'm trying to insert user data into a database from a form using PHP. The mysql_query should return false because the username doesn't exist in the database yet but according to the result I am getting it is returning true and nothing is being entered into the database. Am I using mysql_query wrong or is using !result incorrect?

$sql = "SELECT * FROM users WHERE username='".$_POST["name"]."'";
$result = mysql_query($sql)

if (!$result) {
    $sql = "INSERT INTO USERS (username, email, password) VALUES
            ('".$_POST["name"]."', '".$_POST["email"]."', '".$passwords[0]."')";
    $result = mysql_query($sql);

    if ($result) {
        echo "It's entered!";
    }  else {
        echo "There's been a problem: " . mysql_error();
    }
} else {
    echo "There's already a user with that name: <br />";

    $sqlAll = "SELECT * FROM users";
    $resultsAll = mysql_query($sqlAll);
    $row = mysql_fetch_array($resultsAll);

    while ($row) {
        echo $row["username"]." -- ".$row["email"]."<br />";
        $row = mysql_fetch_array($result);
    }
}

Since you're in the process of learning, now is the perfect time to instill good coding practices. As suggested in some of the below answers, read up on SQL Injection and consider using an abstraction layer like PDO for your data access. While it's good to know how to use mysql_query() and its brethren, any public-facing code should be made as safe as possible. — Justin ᚅᚔᚈᚄᚒᚔ
– Justin ᚅᚔᚈᚄᚒᚔ, Commented Nov 1, 2011 at 16:18
@Justin: I wouldn't suggest an abstractions over SQL whilst he's learning. Once he's got SQL knuckled down, then it's time to start learning about ORMs, etc. — user542603
– user542603, Commented Nov 1, 2011 at 16:58
@Muu: While it's true that an abstraction layer adds some complexity, in the end you are still writing SQL queries (whether you pass them to mysql_query() or $dbh->prepare()). I don't consider "learning how to write SQL queries" and "using a database abstraction layer" to be mutually exclusive. — Justin ᚅᚔᚈᚄᚒᚔ
– Justin ᚅᚔᚈᚄᚒᚔ, Commented Nov 1, 2011 at 18:14
@Justin: Sure. It depends on what abstraction you're using really. — user542603
– user542603, Commented Nov 1, 2011 at 18:45

user542603 · Accepted Answer · 2011-11-01 16:11:54Z

5

Jason, you're checking to see if the query has failed or not - not whether it has returned the value 'false' or 'true'. You need to call mysql_fetch_row or similar, then compare the result.

Alternatively you could use the following:

if (mysql_num_rows($result) == 0) {
    /* User doesn't exist */
} else {
    /* User exists */
}

This will detect if any users have been chosen by your query and - if they have - your user exists already.

Also, you should learn about input sanitisation and SQL Injection. It's a very critical security issue and your script is vulnerable to it. More info here.

answered Nov 1, 2011 at 16:11

user542603

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2011-11-01 16:12:24Z

4

A select query which has no result rows STILL returns a result handle. msyql_query() will ONLY return a 'false' value if the query fails due to a syntax error, constraint violation, etc...

Your code should be

$sql = "...";
$result = mysql_query($sql);
if ($result === false) {
   die("QUery failed: " . mysql_error());
}

if (mysql_num_rows($result) == 0) {
   ... user does not exist ...
}

And please please please read up about SQL injection vulnerabilities. Your code has holes wide enough for a truck to drive through.

answered Nov 1, 2011 at 16:12

Marc B

362k44 gold badges433 silver badges508 bronze badges

4 Comments

Jason Johnson Over a year ago

I'm following a tutorial, it's their code I'm just following along trying to learn. I can't seem to get the results they get.

Marc B Over a year ago

Most php tutorials you'll find on the net are badly outdated, written for obsolete versions of PHP, and written by people who couldn't code their way out of a paper bag. If the tutorial is saying if (!$result) would work for "no results", then I suggest you never ever visit that site again.

Jason Johnson Over a year ago

Thank you Marc B. I've been beating my head against a wall over this for days.

Justin ᚅᚔᚈᚄᚒᚔ Over a year ago

@JasonJohnson Some suggestions: Zend - This teaches the proper way to use mysql_query(); PDO - Good introduction to database abstraction

Rodrigo Ferreira · Accepted Answer · 2011-11-01 16:14:41Z

1

In this case, $result will be a resource. You should check the number of results with mysql_num_rows().
Never, really, NEVER, use $_POST or any direct user input in a query. Always escape the input, BEFORE using it in a query, with mysql_real_escape_string(), or you'll have opened a serious security issue with SQL Injection.

Ex:

$safe_name = mysql_real_escape_string($_POST["name"]);
$sql = "SELECT * FROM users WHERE username='$safe_name'";

answered Nov 1, 2011 at 16:14

Rodrigo Ferreira

3661 silver badge4 bronze badges

1 Comment

Jason Johnson Over a year ago

Thank you everyone for the input, I never knew about SQL injections until now. I am just learning and I want to learn good habits now. I'm going to go back to the drawing board and study some more.

Aurelio De Rosa · Accepted Answer · 2011-11-01 16:11:36Z

0

It's not exact.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

In your case you have the permission but the user doesn't exist. So it will return true but the result set returned is empty.

answered Nov 1, 2011 at 16:11

Aurelio De Rosa

22.2k8 gold badges51 silver badges71 bronze badges

Comments

rogerlsmith · Accepted Answer · 2011-11-01 16:11:58Z

0

mysql_query will return an empty set if the query returns no data. The query will however not fail.

answered Nov 1, 2011 at 16:11

rogerlsmith

6,7982 gold badges22 silver badges27 bronze badges

Comments

Adnan · Accepted Answer · 2013-08-10 22:58:23Z

i solve my problem :

like this

<?php 
$username = $_POST['username']; 

include('config.php');
$result = mysqli_query($con,"SELECT * FROM persons WHERE username='$username'");

while($row = mysqli_fetch_array($result)){

echo $row['username'];
echo "</br>";
echo "</br>";
echo "<p><b>Secret Question</b></p>";
echo $row['secret'];

 }  
?>


</br>
</br>
<form action="forgetaction.php" method="POST">
<p><b>Answer is :</b><p>
<input type="hidden" name="username" value="<?php echo $username; ?>">
<input type="text" name="answer">
</br>
</br>
<input type="Submit" value="Submit">
</form>


and forget action.php like this :

<?php 
include('config.php');

$username = $_POST['username']; 
echo $username;

$result = mysqli_query($con,"SELECT * FROM persons WHERE username='$username'");
$row = mysqli_fetch_array($result);

       if($row['answer'] == $_POST['answer']) {
            echo $row['password'];
        } else {
            echo 'wrong!';
             }

?>

thank you all for help .

Collectives™ on Stack Overflow

PHP Mysql Data Insert

6 Answers 6

Comments

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related