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.

Answer 1

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();

• You bind Params on the statement object not the connection
• You fetch on the statement as well not the connection
• fetchAll returns a 2 dimensional array if you want to see the content use var_dump not echo

---

<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' : '');?>
/>

Answer 2

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

Answer 3

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."'";