77653

Get values from database using PHP PDO and update input to checked

Question:

I am having the most difficult time getting the results that I want. I have done a ton of research and I am just not getting it. I am very new to this, but did my research before posting this question.

Ok, so I have a table with these columns: user_id, my_music, my_movies, my_weather, my_maps, and my_news

Each columns except user_id will have either a 1 or 0. What I need to do is find out the value stored in the database for each column for a specific user.

Here is what I have so far - This is my config.php:

// These variables define the connection information for your MySQL database $username = "dbo12345"; $password = "xxxxxx"; $host = "db12345.db.123.com"; $dbname = "db12345"; $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); try { $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); } catch(PDOException $ex){ die("Failed to connect to the database: " . $ex->getMessage());} $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); header('Content-Type: text/html; charset=utf-8'); session_start();

Here is my admin.php file:

require("config.php"); if(empty($_SESSION['user'])) { header("Location: index.php"); die("Redirecting to index.php"); } $userid = $_SESSION['user']['id']; $sql = "SELECT my_music, my_movies, my_weather, my_maps, my_news FROM user_preferences WHERE user_id = :userID"; //Note the removed semi-colon that was probably causing your error $stmt = $db->prepare($sql); $stmt = $db->bindParam(":userID", $userid); $userid = $_SESSION['user']['id']; $sql = "SELECT my_music, my_movies, my_weather, my_maps, my_news FROM user_preferences WHERE user_id = :userID"; //Note the removed semi-colon that was probably causing your error $stmt = $db->prepare($sql); $stmt->bindParam(":userID", $userid, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetch(); if ($result['my_music']) { $musicChecked = 'checked="checked"'; } else { $musicChecked = ''; } if ($result['my_movies']) { $checked = 'checked="checked"'; } else { $checked = ''; }

How can I write the above code differently? I know there is a way and I am having trouble finding it.

Based on the results above I need to update some checkboxes, for example if my_music is 1 in the database then set the checkbox to checked. So I am doing this:

<input type="checkbox" name="mymusic" id="mymusic" <? echo $musicChecked;?> /> <input type="checkbox" name="mymovies" id="mymovies" <? echo $moviesChecked;?> />

If I need to add more info please let me know. Any and all help is greatly appreciated.

Answer1:

You were really close, you did not fetch properly:

require("config.php"); if(empty($_SESSION['user']['id'])) { header("Location: index.php"); die("Redirecting to index.php"); } $userid = $_SESSION['user']['id']; $sql = "SELECT my_music, my_movies, my_weather, my_maps, my_news FROM user_preferences WHERE user_id = :userID"; $stmt = $db->prepare($sql); $stmt->bindParam(":userID", $userid); $stmt->execute(); $result = $stmt->fetch(); <ul><li>You bind Params on the statement object not the connection</li> <li>You fetch on the statement as well not the connection</li> <li>fetchAll returns a 2 dimensional array if you want to see the content use var_dump not echo</li> </ul><hr /><input id="mymusic" type="checkbox" name="mymusic" <?php echo ($result['my_music']==1 ? 'checked' : '');?> /> <input id="mymovies" type="checkbox" name="mymovies" <?php echo ($result['mymovies']==1 ? 'checked' : '');?> />

Answer2:

user_id is not guaranteed to be a number, is it? So put it between '. And you have a semicolon on the end of your query. The new query becomes:

$query = "SELECT my_music, my_movies, my_weather, my_maps, my_news FROM user_preferences WHERE user_id = '".$userid."'";

Answer3:

It could be the semi-colon at the end of $userid in the query. Shouldn't be needed.

Can I also recommend looking into PDO as it's much more secure, and the mysql_* functions are becoming outdated?

Edit: Just noticed you asked about PDO, so here's a quick tutorial.

$user = "root"; $pass = ""; $conn = new PDO('mysql:host=localhost;dbname=SCHEMA;', $user, $pass); $sql = "SELECT my_music, my_movies, my_weather, my_maps, my_news FROM user_preferences WHERE user_id = :userID"; //Note the removed semi-colon that was probably causing your error $stmt = $conn->prepare($sql); $stmt->bindParam(":userID", $userid); $result = $stmt->fetchAll(); //or $stmt->fetch(); if one line echo $result['my_music']; //replace my_music with the column name result you need

Recommend

  • pgsql 42601 error with PDO::execute
  • Can I improve my PDO method (just started)
  • SphinxQL doesn't work with prepared statements?
  • Call to a member function prepare() on null - PDO
  • What is wrong with the SQL?
  • isset() not working properly on form
  • Error when creating MySQL Table with PDO
  • PDO retrieve table data
  • How to use CASE WHEN in the WHERE clause?
  • How to use CASE WHEN in the WHERE clause?
  • error updating record in database
  • Connecting to MSSQL server via php-pdo?
  • Something wrong with Singleton class, no idea what
  • Java Mail: Unable to send email via Yahoo
  • Automatically Catch Exceptions for PDO in PHP
  • Xampp PHP server GET works Fine POST isn't working
  • Populating Spinner with JSON
  • Android: save file to downloads that can be viewed later
  • Turning String textfile into object array
  • Spring bean validation messages resolution
  • Disable add to cart redirection
  • Passing parameter through “window.location.href”
  • JSON - slashes not escaping
  • PDO error when wrong host name
  • Randomizing -and remembering that randomisation- multiple choice questions in php
  • Why does it draw lines in the wrong place?
  • pip in virtualenv gets ConnectTimeoutError
  • Dynamically switching connect in Modelica
  • Check for zero lines output from command over SSH
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • Redux Form - Not able to type anything in input
  • Problems installing Yesod for Haskell
  • Unable to install Git-core+svn by MacPorts
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Converting a WriteableBitmap image ToArray in UWP
  • VSO Build — Response status code does not indicate success: 404 (Not Found)
  • Reading JSON from a file using C++ REST SDK (Casablanca)
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • javascript inside java/jsp code