[SOLVED] Using two tables with a common row

[breakthecasing]

Hi all, I'm new to SQL and I have a problem I can't seem to figure out.

 

I have a users table and another table.  The users table keeps track of users/passwords/email/etc. The other table is supposed to remember the values of a checklist for each user and show the user what they checked off when they return to the page at a later time while logged in.

 

I want both tables to keep the same list of users (both have the field 'username'), but I can't seem to figure it out.

 

This is the code I used at the top of the page that does the processing (the "form action=" page)

 

$q = "SELECT users.username, checklist.username FROM users, checklist WHERE users.username = checklist.username";
mysql_query($q);

 

I then declare the variables and set them equal to the checkbox names I setup on

the checklist page.

 

I then put this at the bottom of the processing page, after the variables are all declared:

 

$q = "INSERT INTO checklist VALUES ('','$check_1','$check_2', ... '$check_32')";
mysql_query($q);

 

I know I must be missing the bigger picture here, so any help getting closer to the goal would be very appreciated.

[kickstart]

Hi

 

That you have so many columns which appear to be markers for different checks suggests a not great table design.

 

I would suggest having multiple rows on the checklist table for each user, one per check. That way you can join them easily. And have a 3rd table that just lists all the possible checks. Makes it easy to report everything in SQL and also means if you add another check you do not need to alter the structure of your tables.

 

All the best

 

Keith

[breakthecasing]

Thank you for your reply, Keith.

 

Unfortunately, I am new to all of this, so linking 3 tables seems daunting when I can't even link 2 right now, haha.

 

So what you're saying is I should have a separate table called "checks" with a row called "box" or something and the values check_1 - check_32 as values within that row? And then a column for true and a column for false?

 

I still cannot figure out how to successfully get the two tables to read off of the usernames so that it will show up when someone is logged in.  I tried changing from the INSERT format to the UPDATE format, but still no go.

 

Thank you again for your help.

[kickstart]

So what you're saying is I should have a separate table called "checks" with a row called "box" or something and the values check_1 - check_32 as values within that row? And then a column for true and a column for false?

 

Yes. The list of checks a user had done would contain all the checks that they had completed but not those they had not yet completed.

 

Say a tables of Users:-

 

Users

UserName, SomeOtherCols

 

Checklist

UserName, CheckId, CheckValue

 

PossibleChecks

CheckId, CheckDescription

 

You could link them like this:-

 

SELECT *
FROM Users
JOIN PossibleChecks
LEFT OUTER JOIN Checklist 
ON Users.UserName = Checklist.UserName
AND PossibleChecks.CheckId = Checklist.CheckId
WHERE UserName = 'fred'

 

That would give you a list of all the possible checks for a user called fred (whether they had been completed or not), and with the value of the checks (which would be NULL if not completed).

 

All the best

 

Keith

[breakthecasing]

I am somewhat understanding a little better.

 

However, when I try to join two tables together I get the error "Column 'username' in where clause is ambiguous".

 

For my WHERE clause, instead of using 'fred', I used $session->username because I want whoever is logged in's row of data to be used. I changed username = $session->username into users.username AND checklist.username = $session->username  Now, this gives me the error "Unknown column 'breakthecasing' in 'where clause' "

 

I'm not sure where to go from here. Am I at least getting warmer? haha

 

Thank you again for all your help, it is greatly appreciated.

[kickstart]

However, when I try to join two tables together I get the error "Column 'username' in where clause is ambiguous".

 

Sorry, that's my fault.

 

SELECT *
FROM Users
JOIN PossibleChecks
LEFT OUTER JOIN Checklist 
ON Users.UserName = Checklist.UserName
AND PossibleChecks.CheckId = Checklist.CheckId
WHERE Users.UserName = 'fred'

 

The ON clause is specifying the columns to use to join the 2 tables together, and as this is the same column as the one in the WHERE clause you do not need to specify both in the WHERE clause.

 

All the best

 

Keith

[breakthecasing]

Oh, I understand that now!  Thank you, I got the tables to join up.

 

I'm still using two tables right now as I don't want to get too "complex" and figure out the three tables until I get my bad table setup working the way I wanted it to.

 

I switched INSERT for REPLACE, so when the same user saves again, I don't get a duplicate error.

 

The usernames are joined into the second table just fine. Unfortunately, I can't get the values for the check_1, check_2, etc. to show up on the php page or even save to the database at all.

 

I'm using a submit form method POST and have set check_1, etc. as tinyint. Is it safe to say that me assuming the boxes would update themselves just because they have the same name as the variables (i.e., $check_1=$_POST['check_1'] was incorrect? Do I need to make an if/then for each and every checkbox?

[kickstart]

Hi

 

Think you might need to post your code.

 

However are you expecting a variable called $check_1 to be created just because there was a form field called check_1? If so this is not the case, although it used to be the case. You can make this the case again by setting register globals on, but this opens up security holes and is not advised.

 

All the best

 

Keith

[breakthecasing]

This is the page that collects the data from the user:

 

<? include("session.php"); ?>

<?php include("../../../header.php"); ?>

<table border="0" cellspacing="0" cellpadding="0" width="600">
  <tr>
    <td class="main">

      <? echo "Welcome <b>$session->username</b>! <br><br>"; ?>
      
      <form action="process_checks.php" method="POST">

       <input type="checkbox" name="check_1" />One<br />
       <input type="checkbox" name="check_2" />Two<br />
       <input type="checkbox" name="check_3" />Three<br />
       <input type="checkbox" name="check_4" />Four<br />
       <input type="checkbox" name="check_5" />Five<br />
       <input type="checkbox" name="check_6" />Six<br />
       <input type="checkbox" name="check_7" />Seven<br />
       <input type="checkbox" name="check_8" />Eight<br />
       <input type="checkbox" name="check_9" />Nine<br />

      <br /><br /><input type="Submit" value="Save Progress" />
      </form><br /><br />

    </td>
  </tr>
</table>

<?php include("../../../footer.php"); ?>

 

And this is the processing page (process_checks.php)

 

<? include("session.php");

//variables

$check_1=$_POST['check_1'];
$check_2=$_POST['check_2'];
$check_3=$_POST['check_3'];
$check_4=$_POST['check_4'];
$check_5=$_POST['check_5'];
$check_6=$_POST['check_6'];
$check_7=$_POST['check_7'];
$check_8=$_POST['check_8'];
$check_9=$_POST['check_9'];

$query = "SELECT * FROM users RIGHT OUTER JOIN checklist ON users.username = checklist.username WHERE users.username = '$session->username'";

mysql_query($query) or die(mysql_error());

$query2 = "REPLACE checklist(username,check_1,check_2,check_3,check_4,check_5,check_6,check_7,check_8,check_9) VALUES('$session->username','$check_1','$check_2','$check_3','$check_4','$check_5','$check_6','$check_7','$check_8','$check_9')";

mysql_query($query2)  or die(mysql_error());

header("Location: ".$session->referrer);

mysql_close();

?>

 

(Yes, I know it's terrible and I need to switch over to arrays, but I just want to understand the mechanics behind it first.)

 

What I want to do is have the users check off the objects that apply to them, then click the Submit button. This will update their database row and return them to the same page as the form they used to submit, but whichever checkboxes they checked before they hit Submit are still checked off, and continue to be checked off upon subsequent returns to that page.

[kickstart]

Hi

 

I assume username is set as a primary key or unique index on the checklist table.

 

What type of columns are check_1, check_2, etc? Would probably make most sense for them to be bool, in which case set the field up with something like:-

 

$check_1=(($_POST['check_1']) ? 1 : 0);

 

All the best

 

Keith

[breakthecasing]

That did the trick! Thank you so much.

 

Do you think that I would be better off using arrays to save the values of the checks, or should I use the table format you originally spoke of?

 

Now I think I just need to figure out how to print all the checked/unchecked boxes to the first page and I'll be all set.

 

Thank you again, Keith.  You've been a big help.

[breakthecasing]

I changed my code and put all of it into a single page that handles itself. I can get the checks to check off after checking them off and clicking "Save Progress" twice, but then when I leave the page and come back, it rewrites the value of check_1 (It's the only variable I've coded to work with showing the checkbox) to 0, but keeps the checkbox checked off.

 

<? include("session.php"); ?>

<?php include("../../../header.php"); ?>

<table border="0" cellspacing="0" cellpadding="0" width="600">
  <tr>
    <td class="main">

      <? echo "Welcome <b>$session->username</b>! <br><br>"; ?>
      
      <form action="checks.php" method="POST">

       <input type="checkbox" name="check_1" />One<br />
       <input type="checkbox" name="check_2" />Two<br />
       <input type="checkbox" name="check_3" />Three<br />
       <input type="checkbox" name="check_4" />Four<br />
       <input type="checkbox" name="check_5" />Five<br />
       <input type="checkbox" name="check_6" />Six<br />
       <input type="checkbox" name="check_7" />Seven<br />
       <input type="checkbox" name="check_8" />Eight<br />
       <input type="checkbox" name="check_9" />Nine<br />

      <br /><br /><input type="Submit" value="Save Progress" />
      </form><br /><br />

    </td>
  </tr>
</table>

<?php include("../../../footer.php"); ?>


<? 

//variables

$check_1=(($_POST['check_1']) ? 1:0);
$check_2=(($_POST['check_2']) ? 1:0);
$check_3=(($_POST['check_3']) ? 1:0);
$check_4=(($_POST['check_4']) ? 1:0);
$check_5=(($_POST['check_5']) ? 1:0);
$check_6=(($_POST['check_6']) ? 1:0);
$check_7=(($_POST['check_7']) ? 1:0);
$check_8=(($_POST['check_8']) ? 1:0);
$check_9=(($_POST['check_9']) ? 1:0);

?>

      <form action="quickguide3.php" method="POST">

<? echo "$check_1"; ?>  //check if data is being collected.

<?

$chk = "SELECT * FROM checklist WHERE username = '$session->username'";
$result = mysql_query($chk) or die(mysql_error());
$row = mysql_fetch_array($result);

if ($row['check_1'] == 1) {
  $check = " checked";
} else {
  $check = "";
} 

?>

<br />
       <? echo "<input type=\"checkbox\" name=\"check_1\"$check />One<br />\n"; ?>
       <input type="checkbox" name="check_2" />Two<br />
       <input type="checkbox" name="check_3" />Three<br />
       <input type="checkbox" name="check_4" />Four<br />
       <input type="checkbox" name="check_5" />Five<br />
       <input type="checkbox" name="check_6" />Six<br />
       <input type="checkbox" name="check_7" />Seven<br />
       <input type="checkbox" name="check_8" />Eight<br />
       <input type="checkbox" name="check_9" />Nine<br />
      
      <br /><br /><input type="Submit" value="Save Progress" />
      </form><br /><br />

<?

$query = "SELECT * FROM users RIGHT OUTER JOIN checklist ON users.username = checklist.username WHERE users.username = '$session->username'";

mysql_query($query) or die(mysql_error());

$query2 = "REPLACE checklist(username,check_1,check_2,check_3,check_4,check_5,check_6,check_7,check_8,check_9) VALUES('$session->username','$check_1','$check_2','$check_3','$check_4','$check_5','$check_6','$check_7','$check_8','$check_9')";

mysql_query($query2)  or die(mysql_error());

mysql_close();

?>

    </td>
  </tr>
</table>

<?php include("../../../footer.php"); ?>

 

 

[kickstart]

Hi

 

Bit confused as you appear to have 2 (and possibly 3) pages in that listing above, the first of which doesn't set any to "checked".

 

As such not sure how much you have removed and how it hangs together.

 

All the best

 

Keit

[breakthecasing]

Oh, sorry about that. I must have pasted twice by accident.

 

<? include("session.php"); ?>

<?php include("../../../header.php"); ?>

<table border="0" cellspacing="0" cellpadding="0" width="600">
  <tr>
    <td class="main">

      <? echo "Welcome <b>$session->username</b>! <br><br>"; ?>

      <? 

        //variables

        $check_1=(($_POST['check_1']) ? 1:0);
        $check_2=(($_POST['check_2']) ? 1:0);
        $check_3=(($_POST['check_3']) ? 1:0);
        $check_4=(($_POST['check_4']) ? 1:0);
        $check_5=(($_POST['check_5']) ? 1:0);
        $check_6=(($_POST['check_6']) ? 1:0);
        $check_7=(($_POST['check_7']) ? 1:0);
        $check_8=(($_POST['check_8']) ? 1:0);
        $check_9=(($_POST['check_9']) ? 1:0);

      ?>

      <form action="checks.php" method="POST">

      <? echo "$check_1"; ?>  //check if data is being collected.

      <?

        $sel = "SELECT * FROM checklist WHERE username = '$session->username'";
        $res = mysql_query($sel) or die(mysql_error());
        $row = mysql_fetch_array($res);

        if ($row['check_1'] == 1) {
          $check = " checked";
        } else {
          $check = "";
        } 

      ?>

        <br />

        <? echo "<input type=\"checkbox\" name=\"check_1\"$check />One<br />\n"; ?>
        <input type="checkbox" name="check_2" />Two<br />
        <input type="checkbox" name="check_3" />Three<br />
        <input type="checkbox" name="check_4" />Four<br />
        <input type="checkbox" name="check_5" />Five<br />
        <input type="checkbox" name="check_6" />Six<br />
        <input type="checkbox" name="check_7" />Seven<br />
        <input type="checkbox" name="check_8" />Eight<br />
        <input type="checkbox" name="check_9" />Nine<br />
      
        <br /><br /><input type="Submit" value="Save Progress" />

      </form><br /><br />

    <?

      $query = "SELECT * FROM users RIGHT OUTER JOIN checklist ON users.username = checklist.username WHERE users.username = '$session->username'";

      mysql_query($query) or die(mysql_error());

      $query2 = "REPLACE checklist(username,check_1,check_2,check_3,check_4,check_5,check_6,check_7,check_8,check_9) VALUES('$session->username','$check_1','$check_2','$check_3','$check_4','$check_5','$check_6','$check_7','$check_8','$check_9')";

      mysql_query($query2)  or die(mysql_error());

      mysql_close();

    ?>

    </td>
  </tr>
</table>

<?php include("../../../footer.php"); ?>

 

This code is all on just one page.  The echo "check_1" is only there temporarily so I can see what the database is showing without going back and forth between the php page and phpMyAdmin.

 

I apologize for the confusion.

[kickstart]

Hi

 

Right, think it is a basic logic problem.

 

When you are on the screen and submitting it the form is being returned, hence $_POST['check_1'] (etc) is set.

 

However come to the page fresh and none of those are set, hence you update the table back to them all not being set.

 

Probably best to put the code to check those and do the REPLACE near the top and within an IF statement checking if the submit button on the form has been pressed (you will need to give it a name).

 

All the best

 

Keith

[breakthecasing]

I turned it back into two separate pages, with the variables in a separate file that both pages included.  The checkboxes stay ticked now!

 

Thank you so much for your help with this matter, it's helped me learn a bit more about MySQL/PHP that I didn't know earlier.

 

Do you have any suggestions on optimizing the whole coding? Right now I have 32 if statements for 32 checkboxes.  I'm going to try and set an array for them all, but an array has to be big enough to hold all the unserialized data, correct? So for 32 checkbox values, I would probably have to make a varchar (500) I imagine.

 

I tried to think about how the relations between the three tables you spoke of back in your first post.  If I am understanding correctly, the 2nd table, the one that has username, checkid and checkvalue.. there would be no primary key? Because wouldn't each username show up multiple times in the username column, once for each checkid?  And then, multiple usernames would mean each checkid is used more than once... Or am I understanding it incorrectly?

[kickstart]

Do you have any suggestions on optimizing the whole coding? Right now I have 32 if statements for 32 checkboxes.  I'm going to try and set an array for them all, but an array has to be big enough to hold all the unserialized data, correct? So for 32 checkbox values, I would probably have to make a varchar (500) I imagine.

 

Depends how you want to do it. Best idea is to split them off into a different table. Keeping seperate columns would be my 2nd choice. Putting them all into a varchar field would be my last choice unless space was critical.

 

If you did put them all into a single field then just have it as a 32 character long string and use substr to split it up, or a 63 character long string with a "," between each value and use explode to convert it to an array. But neither would be my first choice.

 

I tried to think about how the relations between the three tables you spoke of back in your first post.  If I am understanding correctly, the 2nd table, the one that has username, checkid and checkvalue.. there would be no primary key? Because wouldn't each username show up multiple times in the username column, once for each checkid?  And then, multiple usernames would mean each checkid is used more than once... Or am I understanding it incorrectly?

 

You understand correctly, but nothing to stop you creating a meaningless primary key with an autonumber integer. Have a unique index on username and checkid combined.

 

All the bet

 

Keith

[breakthecasing]

I've been working on using the three tables as you suggested, however, I'm running into some problems.

 

      <form action="process" method="POST">

<?

mysql_query("SELECT * FROM users JOIN Checks
LEFT OUTER JOIN Checklist ON users.username = Checklist.username
AND Checks.id = Checklist.id WHERE users.username = '$session->username'");

$id = 1;

  for($id=1; $id<=32; $id++)
  {

      $sel = "SELECT * FROM Checklist WHERE username='$session->username' AND id='$id'";
      $res = mysql_query($sel) or die(mysql_error());
      $row = mysql_fetch_array($res);

        if($row['value'] == 1)
        {
          $check = " checked";
        } else {
          $check = "";
        }

      mysql_query("SELECT * FROM Checks WHERE id='$id') or die(mysql_error());
      echo "<input type=\"checkbox\" name=\"'$id'\" value=\"'$id'\"$check />$description<br />\n"; 

  }

?>

      <br /><br /><input type="Submit" name="btn" value="Save Progress" />
      </form><br /><br />

<? mysql_close(); ?>

 

I get an unexpected T_STRING error for       echo "<input type=\"checkbox\" name=\"'$id'\" value=\"'$id'\"$check />$description<br />\n"; , so I am not sure of the correct way to have the value = to a variable, if it is even possible.

 

Also, I have been trying to figure out how to do an UPDATE (since as far as I can figure, a REPLACE wouldn't be possible since the information would be in the same column), and I don't know what I would put, if anything, on the process.php page.  What I'm currently thinking is that each FOR loop, an update would be made to that specific row, but then I am unsure how to link this to a submit button action.

[kickstart]

Hi

 

The problem causing that is the missing " in the following:-

 

      mysql_query("SELECT * FROM Checks WHERE id='$id'") or die(mysql_error());

 

However not sure on your SQL. I would assume that the 3 tables would contain the users details, another table of checks (independent of users) and a third table that lists the check results for each user.

 

Making a few assumptions about your table layouts:-

 

<?

mysql_query("SELECT Checks.Id, Checklist.value, Checks.description 
FROM users 
JOIN Checks
LEFT OUTER JOIN Checklist 
ON users.username = Checklist.username
AND Checks.id = Checklist.id 
WHERE users.username = '$session->username'");

while($row = mysql_fetch_array($res))
{
if($row['value'] == 1)
{
  $check = " checked";
} else {
  $check = "";
}
echo "<input type='checkbox' name='$id' value='$id' $check />".$row['description']."<br />\n"; 
}

?>

      <br /><br /><input type="Submit" name="btn" value="Save Progress" />
      </form><br /><br />

<? mysql_close(); ?>

 

You could probably leave the users table out of the SQL (just check the username against the one of the checklist table) but I have left it in for now as there might be other things you want to check from it in this code.

 

All the best

 

Keith

[breakthecasing]

Oh, sorry about that.

 

- users

username, password, email, etc.

 

-Checklist

username, id, value

 

-Checks

id, description

 

Those are the tables. users has a primary key of username, checklist has a combined primary key of username and id.

 

That coding worked except I had to add in something about the ids to keep them updating.  The coding and descriptions print out fine now.

 

<? include("include/session.php"); ?>

      <? echo "Welcome <b>$session->username</b>, you are logged in. <br><br>"; ?>

      <form action="process.php" method="POST">

<?

$sel = "SELECT Checks.id, Checklist.value, Checks.description FROM users JOIN Checks
LEFT OUTER JOIN Checklist ON users.username = Checklist.username
AND Checks.id = Checklist.id WHERE users.username = '$session->username'";

$res = mysql_query($sel) or die(mysql_error());

$id = 1;

while($row = mysql_fetch_array($res))
{

        if($row['value'] == 1)
        {
          $check = " checked";
        } else {
          $check = "";
        }

      echo "<input type='checkbox' name='$id' value='$id' $check />".$row['description']."<br />\n"; 
      $id++;
  }

?>

      <br /><br /><input type="Submit" name="btn" value="Save Progress" />
      </form><br /><br />

<? mysql_close(); ?>

 

But I can't get any data sent to the database. My guess is that my variables aren't working, or the WHERE in my UPDATE query is incorrect.

 

<?

include("include/session.php");

//Get data in local variable

$value["1"]=(($_POST['1']) ? 1:0);
$value["2"]=(($_POST['2']) ? 1:0);
$value["3"]=(($_POST['3']) ? 1:0);
$value["4"]=(($_POST['4']) ? 1:0);
$value["5"]=(($_POST['5']) ? 1:0);
$value["6"]=(($_POST['6']) ? 1:0);
$value["7"]=(($_POST['7']) ? 1:0);
$value["8"]=(($_POST['8']) ? 1:0);
$value["9"]=(($_POST['9']) ? 1:0);

foreach($value as $check => $mark)
{

$query = "UPDATE Checklist SET value='$mark' WHERE username = '$session->username' AND id='$check' ";

mysql_query($query)  or die(mysql_error());

}

header("Location: ".$session->referrer);

mysql_close();

?>

[kickstart]

Hi

 

Nothing obvious. I assume it isn't DIEing.

 

Echo out the SQL and then try it in phpmyadmin.

 

All the best

 

Keith

[breakthecasing]

No, it wasn't die-ing.  The problem was that I got ahead of myself and never inserted any data before I was updating. haha

 

So now it works.  I just wanted to make sure I thanked you again for all your help before I closed the topic.  I really appreciate it.