13228

MySql PHP Update Error

Question:

I've been messing about with this code for a few hours now and can't work out why it's not working. It's a profile update php page that is passed through JQuery and all seems to be fine except for it actually updating into the table. Here is the code I'm using:

session_start(); include("db-connect.php");//Contains $con $get_user_sql = "SELECT * FROM members WHERE username = '$user_username'"; $get_user_res = mysqli_query($con, $get_user_sql); while($user = mysqli_fetch_array($get_user_res)){ $user_id = $user['id']; } $name = mysqli_real_escape_string($con, $_REQUEST["name"]); $location = mysqli_real_escape_string($con, $_REQUEST["location"]); $about = mysqli_real_escape_string($con, $_REQUEST["about"]); $insert_member_sql = "UPDATE profile_members SET id = '$user_id', names = '$name', location = '$location', about = '$about' WHERE id = '$user_id'"; $insert_member_res = mysqli_query($con, $insert_member_sql) or die(mysqli_error($con)); if(mysqli_affected_rows($con)>0){ echo "1"; }else{ echo "0"; }

All I get as the return value is 0, can anybody spot any potential mistakes? Thanks

Answer1:

To begin with, use

require("db-connect.php");

instead of

include("db-connect.php");

And now, consider using prepared statements, your code is vulnerable to sql injections.

Consider using PDO instead of the mysql syntax, in the long run I find it much better to use and it avoids a lot of non-sense-making problems, you can do it like this (You can keep it in the db-connect file if you want, and even make the database conncetion become global):

// Usage: $db = connectToDatabase($dbHost, $dbName, $dbUsername, $dbPassword); // Pre: $dbHost is the database hostname, // $dbName is the name of the database itself, // $dbUsername is the username to access the database, // $dbPassword is the password for the user of the database. // Post: $db is an PDO connection to the database, based on the input parameters. function connectToDatabase($dbHost, $dbName, $dbUsername, $dbPassword) { try { return new PDO("mysql:host=$dbHost;dbname=$dbName;charset=UTF-8", $dbUsername, $dbPassword); } catch(PDOException $PDOexception) { exit("

An error ocurred: Can't connect to database.

More preciesly: ". $PDOexception->getMessage(). "

"); } }

And then init the variables:

$host = 'localhost'; $user = 'root'; $databaseName = 'databaseName'; $pass = '';

Now you can access your database via

$db = connectToDatabase($host, $databaseName, $user, $pass);

Now, here's how you can solve your problem (Using prepared statements, avoiding sql injection):

function userId($db, $user_username) { $query = "SELECT * FROM members WHERE username = :username;"; $statement = $db->prepare($query); // Prepare the query. $statement->execute(array( ':username' => $user_username )); $result = $statement->fetch(PDO::FETCH_ASSOC); if($result) { return $result['user_id']; } return false } function updateProfile($db, $userId, $name, $location, $about) { $query = "UPDATE profile_members SET name = :name, location = :location, about = :about WHERE id = :userId;"; $statement = $db->prepare($query); // Prepare the query. $result = $statement->execute(array( ':userId' => $userId, ':name' => $name, ':location' => $location, ':about' => $about )); if($result) { return true; } return false } $userId = userId($db, $user_username); // Consider if it is not false. $name = $_REQUEST["name"]; $location = $_REQUEST["location"]; $about = $_REQUEST["about"]; $updated = updateProfile($db, $userId, $name, $location, $about);

You should check the queries though, I fixed them a little bit but not 100% sure if they work.

You can easily make another function which inserts into tha database, instead of updating it, or keeping it in the same function; if you find an existance of the entry, then you insert it, otherwise you update it.

Recommend

  • sql data type bit
  • Sum each column for a table with over 20 columns without writing out each sum
  • Veracode throws “Technology-Specific Input Validation Problems (CWE ID 100)” for a public string pro
  • Bootstrap fixed navigation jumps when scrolling
  • Restrict method filter with multiple date condition
  • drop duplicates pandas dataframe
  • dm-script catch error with “Analyze Particles”
  • jBCrypt 0.3 C# Port (BCrypt.net)
  • Number of threads being used during Parallel.ForEach
  • New dataframe with difference between first and last values of repeated measurements?
  • Highcharts - Column chart with empty columns for date in x-axis
  • Access the state of control in Winforms from another application
  • RegistryKey.GetSubKeyNames returns names that are not in that subkey!
  • How to create a Unix-domain socket with specific access permissions
  • Get used tables from sql query [duplicate]
  • Deploying a CodeRush plugin from the Community Site
  • ggplot2: make the points on the line a darker color than the line color
  • How to access meteor package name inside package?
  • How do I remove all but some records based on a threshold?
  • Getting the scrolling offset when storing coordinates
  • Stop Bash Script if Hive Fails
  • xtable package: Skipping some rows in the output
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Installed module is empty
  • Display Images one by one with next and previous functionality
  • Upload files with Ajax and Jquery
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Django query for large number of relationships
  • reshape alternating columns in less time and using less memory
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • Conditional In-Line CSS for IE and Others?
  • How to push additional view controllers onto NavigationController but keep the TabBar?