I want to make a unique value column in my table,and use php to check if the value is already Inserted into the table or not. For example, one username cannot be used twice. I tried to use SELECT to get value from database,but it does not work. When I tried to echo the value from table like that:
echo $conn->query("SELECT DISTINCT Username
FROM UserDetail;");
It says: Catchable fatal error: Object of class mysqli_result could not be converted to string
This problem should not be solved in PHP because it's a database design issue. Instead, you want to alter your UserDetail table so that the DB will reject any query that tries to insert a duplicate. This is the query you run:
$sql = 'ALTER TABLE UserDetail ADD UNIQUE INDEX (`Username`)';
$result = $conn->query($sql);
if($result) echo 'Success!';
else echo $conn->error;
Once you've done this successfully, it will not be possible to enter a duplicate Username value in this table; The query will not execute and will produce an error.
Now to avoid errors when your script tries to insert a username that already exists, change your insert query from INSERT to INSERT IGNORE:
$sql = "INSERT IGNORE INTO UserDetail SET Username='$escaped_new_username'";
$result = $conn->query($sql);
By the way your code fails because you're trying to echo the result of $conn->query, but it's not a string. The steps to showing DB results are:
Run the query (you did)
Verify that there was no error by checking that the result is not false
Fetch results by executing fetch fetch_assoc, fetch_array or some such method on the result
Use or show what you fetched.
Look at the documentation for the DB driver you're using. The most common are MySQLi and PDO_MySQL
