How to select username from table when post method pass the email and insert into another table that username?

Question

I want to get email address,hotel name and the rate value from the form and insert into rating table. when inserting data to rate table it should insert username of the email address from the user table.

here is the php code i tried.

if (!empty($_POST)) {
    $userName = "";
    $sql = "select name from user where email='$_POST[user]'";

    $query1 = mysql_query($sql,$con);

    while ($row = mysql_fetch_array($query1)) 
    {
        $userName = $row;
        echo $userName;
    }

    $rest_name = $_POST['name'];
    $user_name = $_POST['user'];
    $rate = $_POST['Rate'];

    $query = "INSERT INTO rates(rest_name,user,rate) values ('$_POST[name]','".$userName"','$_POST[Rate]')";
    $query2 = mysql_query($query,$con);

    $response["success"] = 1;
    $response["message"] = "Rate Successfully Added!";
    echo json_encode($response);
}

But it's not works. Can anyone help me?

You are using this format sometimes to insert variables into your query ```'".$yourVariable."'```, and you are using this sometimes: ```'$yourVariable'```. The latter is wrong and should give you a (syntax) error. What error messages do you get if any at all? And you are not even executing the 2nd ```INSERT``` query in this code snippet. — chrki, Jan 26 '14 at 07:34
Is RATE an integer (int) in the dbtable? If so, you cannot have it in the single quotes. — Andrew Allen West, Jan 26 '14 at 07:40
@user1781026 i added the execute query.It's insert the data to table but not the user name, it insert email address. — anuruddhika, Jan 26 '14 at 07:42
@AndrewAllenWest rate is successfully added.The problem with the user name — anuruddhika, Jan 26 '14 at 07:43
You should print_r($_POST) and see if the username is even being posted. ;) — Andrew Allen West, Jan 26 '14 at 07:48
You are selecting the name where email = user. I fixed it in my code example. — SyntaxLAMP, Jan 26 '14 at 08:04

score 1 · Answer 1 · edited Jan 26 '14 at 14:08

You need to concatenate the string, as arrays can't be auto concatenated. You need to do this for both of your mysql statements. You also should be using mysqli_query instead of mysql_query, as mysql_query has been deprecated.

You also have to execute the 2nd mysql statement. It's also a great idea to sanitize your user inputs to avoid SQL injection attacks. I have included the mysql_real_escape_string function for this purpose.

Your query also needs to have $row["name"], which I added.

if (!empty($_POST)) {
    $userName = "";
    $sql = "select name from user where email='".mysql_real_escape_string($_POST['email'])."'";

    $query1 = mysql_query($sql,$con);

    while ($row = mysql_fetch_array($query1)) 
    {
        $userName = $row["name"];
        echo $userName;
    }

    $rest_name = $_POST['name'];
    $user_name = $_POST['user'];
    $rate = $_POST['Rate'];

    $query = "INSERT INTO rates (rest_name, user, rate) values ('".mysql_real_escape_string($_POST['name'])."','".mysql_real_escape_string($userName)."','".mysql_real_escape_string($_POST['Rate'])."')";
    $rs = mysql_query($query, $con);

    $response["success"] = 1;
    $response["message"] = "Rate Successfully Added!";
    echo json_encode($response);

}

its inset data.but not the user name.it's insert email address. it not select the user related to email address. — anuruddhika, Jan 26 '14 at 07:48
I edited my code so that the query uses $_POST["email"] instead. Your code was using the username. — SyntaxLAMP, Jan 26 '14 at 08:02

score 1 · Accepted Answer · answered Jan 26 '14 at 07:44

At here you didn't passed the data to the $userName. Here is how you do it:

    while ($row = mysql_fetch_array($query1)) 
                {
                $userName = $row['name'];
                echo $userName;
                 }

And at the insert you have a few syntax error too. It should look like this:

$query = "INSERT INTO rates(rest_name,user,rate) values ('".$_POST['name']."','".$userName"','".$_POST['Rate']."')";

Also you forgot to use

mysql_query($query);

score 0 · Answer 3 · edited May 23 '17 at 12:21

I have put all POST parameters you put in your queries in mysql_real_escape_string() functions. You should however switch to mysqli or PDO if you can. Read here why this is a good idea: How can I prevent SQL injection in PHP?

I added $query2 = mysql_query($query, $con) so that your second query is executed.

<?php
if (!empty($_POST)) {
    $userName = "";
    $sql = "SELECT name FROM user WHERE email='".mysql_real_escape_string($_POST[user])."'";

    $query1 = mysql_query($sql, $con);

    while ($row = mysql_fetch_array($query1)) {
        $userName = $row["name"];
        echo $userName;
    }

    $rest_name = $_POST['name'];
    $user_name = $_POST['user'];
    $rate = $_POST['Rate'];

    $query = "INSERT INTO rates(rest_name, user, rate) values ('".mysql_real_escape_string($rest_name)."','".mysql_real_escape_string($userName)"','".mysql_real_escape_string($rate)."')";

    if($query2 = mysql_query($query, $con)) {
        $response["success"] = 1;
        $response["message"] = "Rate Successfully Added!";
        echo json_encode($response);    
    } else {
        $response["success"] = 0;
        $response["message"] = "MySQL Error";
    }
}

How to select username from table when post method pass the email and insert into another table that username?

3 Answers3