Get values from database using PHP PDO and update input for validation
I am having the hardest time getting the results I want. I've done a lot of research and I just don't get it. I'm very new to this, but did my research before posting this question.
So, I have a table with these columns: user_id, my_music, my_movies, my_weather, my_maps and my_news
Each column except user_id will be either 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's 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 a hard time finding it.
From the results above, I need to update some checkboxes, for example, if my_music is 1 in the database, check the checkbox to check. So I do 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 information, please let me know. Any help is appreciated.
source to share
You were very close, you didn't get it 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 are associating Params with an operator object, not a connection
- You also get an offer, not a connection
- fetchAll returns a 2-dimensional array if you want to use
var_dump
not contentecho
<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' : '');?>
/>
source to share
The request may have a semicolon at the end of the $ userid. Not necessary.
May I also have a look at PDO as it is much more secure and the mysql_ * functions are being deprecated?
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
source to share