Checkbox issue

genista

I am hoping this is simple, but it is keeping me awake late on a Saturday night here. Very simple premise:

Either update or insert checkbox values into mysql table. The queries for update or insert (depending on whether the user has already inserted some data or not. Both queries run fine, however I cannot get any of the checkbox values into the query. When I echo out the query (as you will see from the code below) the values are empty, except for the userid.

What am I missing?

    $sql2="SELECT userid, category, faith, headlines, family, politics, film, music FROM content_selection WHERE '".$userid."'='".$_SESSION['userid']."'";
$result = mysql_query($sql2) or die('Request 2 failed: ' . mysql_error());
$row = mysql_fetch_assoc($result);

$userid2 = $row['userid'];
$media = $row['category'];
$faith = $row['faith'];
$headlines = $row['headlines'];
$family = $row['family'];
$politics = $row['politics'];
$film = $row['film'];
$music = $row['music'];

if(mysql_num_rows($result) == 1)
{ 
  //$link next to affected rows and mysql query
    $exists = 'true';
    echo "<p>found stuff in db</p>";
    echo "<p>headlines = $headlines</p>";
} 

else 
{ 
    $exists = 'false';
    echo "<p>oh dear nothing in db</p>";
}


//echo "<p>sql2 = $sql2</p>";


if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form
?>
<html>
<head>
<title></title>
</head>
<body>
<form method="post" action="#">
    <?php
  if (isset($_POST['faith'])){
    $faith = "true";
}
    else{ 
    $faith = "";
}
   ////////HEADLINES/////////////////////////  

if (isset($_POST['headlines'])){
    $headlines = "true";
}
    else{ 
    $headlines = "";
}
//////////FAMILY/////////////////////////////
if (isset($_POST['family'])){
    $family = "true";
}
    else{ 
    $family = "";
}
//////////POLITICS//////////////////////////
if (isset($_POST['politics'])){
    $politics = "true";
}
    else{ 
    $politics = "";
}
///////////BUSINESS//////////////////////////
if (isset($_POST['business'])){
    $business = "true";
}
    else{ 
    $business = "";
}
//////////FILM///////////////////////////////
if (isset($_POST['film'])){
    $film = "true";
}
    else{ 
    $film = "";
}
//////////MUSIC//////////////////////////////
if (isset($_POST['music'])){
    $music = "true";
}
    else{ 
    $music = "";
}
        ?>
<input type="checkbox" name="Faith" value="true" <?php if($_POST['faith']=="true") echo "checked=checked"; ?>/>Faith<br />
<input type="checkbox" name="Headlines" value="true" <?php if($_POST['headlines']=="true") echo "checked=checked"; ?> /> Headlines<br />
<input type="checkbox" name="Family" value="true" <?php if($_POST['family']=="true") echo "checked=checked"; ?> /> Family<br />
<input type="checkbox" name="Politics" value="true" <?php if($_POST['politics']=="true") echo "checked=checked"; ?> /> Politics<br />
<input type="checkbox" name="Film" value="true" <?php if($_POST['film']=="true") echo "checked=checked"; ?> /> Film<br />
<input type="checkbox" name="Music" value="true" <?php if($_POST['music']=="true") echo "checked=checked"; ?> /> Music<br />
<input type="submit" value="submit" name="submit">
</form>
<?php
} else {


if ($exists === 'true'){
    //$query = "UPDATE content_selection SET category='$media' where 'userid'='$userid'"; 
    $query = "UPDATE content_selection SET faith='$faith', headlines='$headlines', family='$family', politics='$politics', film='$film', music='$music' where 'userid'='$userid'";
        //
$result = mysql_query($query) or die('Update failed: ' . mysql_error());

}
else{
    $query = "INSERT INTO content_selection (faith, headlines, family, politics, film, music, userid) VALUES( '$faith', '$headlines', '$family', '$politics', '$film', '$music', '$userid')";
$result = mysql_query($query) or die('Insert failed: ' . mysql_error());

}

echo "<p>$query</p>";
echo "<p>headlines=$headlines</p>";
}
?>
</body>
</html>

Thanks!

Weedpacket

Your checkboxes are named [font=monospace]Faith[/font], [font=monospace]Headlines[/font], and so on, but you're looking in the [font=monospace]$_POST[/font] array for elements named [font=monospace]faith[/font], [font=monospace]health[/font], and so on.

genista

Thanks for that, I have changed the checkbox values to lower case to match but no success, the queries are still empty.

Regards,

NogDog

You only set the relevant variables from the $POST values when this evaluates as true:

if (!isset($_POST['submit'])) {

...which means they're not set by the time you're past the end of that block and trying to process your queries in the following else block.

genista

Perfect, your solution has got me most of the way there. The issue I now have with the same code is on the update. Currently if there is no data in the table I run an insert. If the user has selections in the table I want to update this table. So currently I have an update query, the query runs fine, but the table is not getting updated with the new selection.

My code is below, but having spent some time reading over the last few days is whether I use an insert with a duplicate key update or to use REPLACE instead. Either way the code below is not updating the table which is what I need it to do so would be great to get the right solution - thanks!

$sql2="SELECT userid, category, faith, headlines, family, politics, film, music FROM content_selection WHERE '".$userid."'='".$_SESSION['userid']."'";
$result = mysql_query($sql2) or die('Request 2 failed: ' . mysql_error());
$row = mysql_fetch_assoc($result);

$userid2 = $row['userid'];
$media = $row['category'];
$faith = $row['faith'];
$headlines = $row['headlines'];
$family = $row['family'];
$politics = $row['politics'];
$film = $row['film'];
$music = $row['music'];

if(mysql_num_rows($result) == 1)
{ 
  //$link next to affected rows and mysql query
    $exists = 'true';
    echo "<p>found stuff in db</p>";
    echo "<p>headlines = $headlines</p>";
} 

else 
{ 
    $exists = 'false';
    echo "<p>oh dear nothing in db</p>";
}


//echo "<p>sql2 = $sql2</p>";
  if (isset($_POST['faith'])){
    $faith = "true";
}
    else{ 
    $faith = "";
}
   ////////HEADLINES/////////////////////////  

if (isset($_POST['headlines'])){
    $headlines = "true";
}
    else{ 
    $headlines = "";
}
//////////FAMILY/////////////////////////////
if (isset($_POST['family'])){
    $family = "true";
}
    else{ 
    $family = "";
}
//////////POLITICS//////////////////////////
if (isset($_POST['politics'])){
    $politics = "true";
}
    else{ 
    $politics = "";
}
///////////BUSINESS//////////////////////////
if (isset($_POST['business'])){
    $business = "true";
}
    else{ 
    $business = "";
}
//////////FILM///////////////////////////////
if (isset($_POST['film'])){
    $film = "true";
}
    else{ 
    $film = "";
}
//////////MUSIC//////////////////////////////
if (isset($_POST['music'])){
    $music = "true";
}
    else{ 
    $music = "";
}


?>
<html>
<head>
<title></title>
</head>
<body>
<form method="post" action="#">
<input type="checkbox" name="faith" value="true" <?php if($_POST['faith']=="true") echo "checked=checked"; ?>/>Faith<br />
<input type="checkbox" name="headlines" value="true" <?php if($_POST['headlines']=="true") echo "checked=checked"; ?> /> Headlines<br />
<input type="checkbox" name="family" value="true" <?php if($_POST['family']=="true") echo "checked=checked"; ?> /> Family<br />
<input type="checkbox" name="politics" value="true" <?php if($_POST['politics']=="true") echo "checked=checked"; ?> /> Politics<br />
<input type="checkbox" name="film" value="true" <?php if($_POST['film']=="true") echo "checked=checked"; ?> /> Film<br />
<input type="checkbox" name="music" value="true" <?php if($_POST['music']=="true") echo "checked=checked"; ?> /> Music<br />
<input type="submit" value="submit" name="submit">
</form>
<?php
if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form
} else {


if ($exists === 'true'){

$query = "UPDATE content_selection SET faith='$faith', headlines='$headlines', family='$family', politics='$politics', film='$film', music='$music' where 'userid'='$userid'";
  // $query = "INSERT INTO content_selection (faith, headlines, family, politics, film, music) VALUES('$faith', '$headlines', '$family', '$politics', '$film', '$music') ON DUPLICATE KEY UPDATE category = '$media'";

$result = mysql_query($query) or die('Update failed: ' . mysql_error());

}
else{
    $query = "INSERT INTO content_selection (faith, headlines, family, politics, film, music, userid) VALUES( '$faith', '$headlines', '$family', '$politics', '$film', '$music', '$userid')";
$result = mysql_query($query) or die('Insert failed: ' . mysql_error());

}

echo "<p>$query</p>";
echo "<p>headlines=$headlines</p>";
}
?>
</body>
</html>

bradgrafelman

Some issues/comments I see...

Personally, I would get rid of your three SQL queries - SELECT, INSERT/UPDATE - and instead use a single INSERT ... ON DUPLICATE KEY UPDATE query. It makes the code more efficient and, even better, simpler to manage.
Your SELECT query uses a variable called $userid in the WHERE clause. Where is that variable defined?

Based on the way you later use that variable, it probably doesn't make any sense to use it as you are in the SELECT query. For example, assuming $userid and $_SESSION['userid'] both contain numeric values (e.g. integer "ID" values), your SELECT query would have a where clause that looks like:
```
WHERE 123 = 321
```
where 123 and 321 correspond to the two integer values. In other words, you're always going to select all of the rows or none of the rows.
If you don't follow my suggestion in #1 and you do resolve the issue in #2, you should try checking to see how many rows were affected by your UPDATE query in addition to outputting the UPDATE query so that you can visually inspect it to make sure it contains the data you expect it to contain.
Your update query doesn't make sense much like your SELECT query above, since your WHERE clause will look something like:
```
where 'userid'='123'
```
Unless $userid contains the exact string "userid" (which makes no sense - it's not a very identifying value in that case), the two strings will never be equal, thus you'll never update any rows.
The entire [man]mysql[/man] extension is severely outdated and has been deprecated. Why continue to use it?

genista

Thanks for all of your points, I have tidied up the select and now run the 'ON DUPLICATE KEY UPDATE' and everything is working great. I am also looking at joining the modern world and not using deprecated stuff.

I think I am all set so thanks again for all of your work - to all of you who answered your efforts on a site like this help make a real difference!