[RESOLVED] Displaying existing checked data so it can be updated

[Bilbozilla]

I have a database/php app set up that allows a user to input and record data by way of checkboxes. The entry for the HTML code is as follows:

<td width=142><p><input type="checkbox" name="model[]" value="1">Ford</td>
<td width=142><p><input type="checkbox" name="model[]" value="2">Ferrari</td>
<td width=142><p><input type="checkbox" name="model[]" value="3">Dodge</tr>
<td width=142><p><input type="checkbox" name="model[]" value="4">Porsche</td>
<td width=142><p><input type="checkbox" name="model[]" value="5">BMW</td>
<td width=142><p><input type="checkbox" name="model[]" value="6">GM</tr>
<td width=142><p><input type="checkbox" name="model[]" value="7">Fiat</td>
<td width=142><p><input type="checkbox" name="model[]" value="12">VW</td>
<td width=142><p><input type="checkbox" name="model[]" value="8">Lambo</tr>
<td width=142><p><input type="checkbox" name="model[]" value="9">Seat</td>
<td width=142><p><input type="checkbox" name="model[]" value="10">Saab</td>

If the user were to have checked Ford and BMW, the values 1 and 5 would have been input into a database (2 separate records in the db) containing the user's ID and the values 1 & 5, indicating this user likes Ford and BMW.

The problem I am facing is that the user changed his mind and now likes Ferraris also. How can I pull the existing tagged data (1, 5) and provide an update page with Ford & BMW selected, but the other options also available, so that the records can be brought current? Does this involve two separate queries? How does this get merged, so that after doing an "update" command, the database now has (5, 2) (the guy changed his mind about Ford).

[Krik]

I think I would just delete all previous records when they click submit and then just add the changes as new records. Because what if he changed from Ford and BMW to Dodge, Fiat and Saab that could make any other method of doing the update much more complicated.

EDIT: after rereading your question not sure if that was the answer you were looking for? It seems like you may be asking how mark the checkbox as checked when you display the form.

[Bilbozilla]

Krik;10960221 wrote:

EDIT: after rereading your question not sure if that was the answer you were looking for? It seems like you may be asking how mark the checkbox as checked when you display the form.

Bingo. This is exactly what I'm looking for. In the event there are 1,000 different options to choose from, and the user has already gone through and selected the 5 that fit his criteria, if he changes his mind, those 5 should already be checked. I'm trying to make the experience as easy as possible on the user.

[Bilbozilla]

Here are some additional thoughts I have on it -

[B]Search #1 RESULTS[/B]
[B]ID[/B]         [B]MODEL[/B]
1           FORD
2           FERRARI
3           GM
4           PORSCHE
5           SAAB

[B]Search #2 Results[/b]
[B]USERID[/B]        [B]MODEL[/B]       [B]CHECKED[/B]
41334a4      FORD         TRUE
41334a4      PORSCHE   TRUE

I want to merge them so the results are as follows in preparation to display the checked boxes.

[B]ID[/B]         [B]MODEL[/B]    [B]CHECKED[/B]
1           FORD       TRUE
2           FERRARI    

3           GM
4           PORSCHE    TRUE
5           SAAB

[Crowly]

Something like this, untested

$user_id=1;

// you need two tables, one which store each car model
// and which stores which models that user has selected
// not a 100% sure about the join
$sql="SELECT c.id, c.model, u.model umodel
          FROM cars c
          LEFT OUTER JOIN users u ON c.model=u.model
          WHERE u.id=".$user_id;

$result=mysql_query($sql);

while ($row=mysql_fetch_assoc($result))
{
  echo '<td width=142><p><input type="checkbox" name="model[]" value="'.$row['id'].'"';

  if (!empty($row['umodel'])) // $row['umodel'] should be NULL for all models the users hasn't selected
    echo ' checked="checked" ';

  echo '>'.$row['model'].'</td>';
}

mysql_free_result($result);

Basically you need to check if the current value/model you are printing is equal to a value/model the user has selected, then echo the checked="checked" part.

About the updating i think you might need to queries, the first deletes all the records from the current user, and the 2nd insert the new/current values/selections. But I'm not sure if this is the best approach, if it isn't hopefully someone else has a better way 🙂

[Bilbozilla]

I'm still having some trouble wrapping my brain around this.

Here's what I have, and the last one is what I'm trying to do. The solutions thus far are definitely in the right direction, and the if/then routine for placing the checked boxes is definitely the way to go. The problem I keep running up against is that I cannot pull out one complete set of model names.

Here's what I have:

tbl_vehicle
------------------------------
|   id   |    name           |
------------------------------
|    1  | Ford               |
|    2  | Ferrari            |
|    3  | GM                 |
|    4  | Dodge              |
|    5  | Porsche            |
|    6  | VW                 |
------------------------------

lu_vehicle
------------------------------
|   id   | userid    | vid   |
------------------------------
|   1   | bccd       | 1     |
|    2  | bccd       | 2     |
|    3  | bccd       | 6     |
|    4  | abbb       | 5     |
|    5  | cdef       | 6     |
|    6  | bbbb       | 2     |
------------------------------

tbl_user
------------------------------
|   id   | userid    | name   |
------------------------------
|   1  | bccd       | Bob     |
|   2  | abbb       | Jake     |
|   3  | bbbb       | Jim    |
------------------------------

Here's the output that I want from a search for bob's cars.

------------------------------
|   id   |    name | ????
------------------------------
|    1  | Ford     | TRUE
|    2  | Ferrari  | TRUE
|    3  | GM       | NULL
|    4  | Dodge    | NULL
|    5  | Porsche  | NULL
|    6  | VW       | TRUE
------------------------------

[Crowly]

Edit: Not sure if this is gonna work, but perhaps it can spark some ideas :p Stuff like this happens when you click post a bit to soon :o

You just need to tweak the sql a little bit. I have rarely used outer joins so a bit unsure about this since its untested
The inner join part might not be needed

SELECT tv.name car, tu.name uname, lv.userid user
FROM tbl_vehicle tv
LEFT OUTER JOIN lu_vehicle lv ON tv.id=lv.vid
INNER JOIN tbl_user tu ON tu.userid=lv.userid
WHERE lv.userid='username'
ORDER BY tv.name

// or perhaps like this

SELECT tv.name car, tu.name uname, lv.userid user
FROM tbl_user tu 
INNER JOIN lu_vehicle lv ON tu.userid=lv.userid
RIGHT OUTER JOIN tbl_vehicle tv ON tv.id=lv.vid
WHERE lv.userid='username'
ORDER BY tv.name

You don't really need the id field in lu_vehicle, since you can use userid + vid as primary key. Also instead of userid you could use uid and just reference the id from tbl_user as you have done with tbl_vehicle.

[Bilbozilla]

SELECT tv.name car, tu.name uname, lv.userid user 
FROM tbl_vehicle tv 
LEFT OUTER JOIN lu_vehicle lv ON tv.id=lv.vid 
INNER JOIN tbl_user tu ON tu.userid=lv.userid 
WHERE lv.userid='username' 
ORDER BY tv.name 

That's very cool about naming the output columns. I just learned something new (the car, uname, & user values in the first line.)

I'm trying to work with this untested code, and now I'm lost even further. You can do two joins?

Let's simplify and make it a SELECT *.

I get "tbl_vehicle" is not a unique table errors.

[Crowly]

Bilbozilla;10960414 wrote:

In this sql code I see "car", "uname", and "user" in the first line. Those don't even exist. Can you explain why these are being used? I'm trying to understand the conventions here.

They are aliases, i have dropped the as keyword. Ex if you want to get field name from table 1 and field name from table 2, aliases are very useful (and needed) to tell them apart. Aliases are also useful to shorten down long field names and such.
Aliases are also used on the table names, instead of SELECT tbl_vehicle.name its shorter to put an alias tv on tbl_vehicle and reference the field as tv.name (table_alias.field)

I'm trying to work with this untested code, and now I'm lost even further. You can do two joins?

Yes, you can join many tables together, not sure if there is a upper limit. And if there are, i guess its db dependant.

Let's simplify and make it a SELECT .

Only use SELECT when you need to fetch all the fields.

I get "tbl_vehicle" is not a unique table errors.

Not sure about this one, sounds like there are more than one table named tbl_vehicle, but that shouldnt be possible. You sure the table names are correct?

[Crowly]

Replicated your example data and did a little tweaking and testing, went with a subquery instead of joins, this works

$sql=sprintf("SELECT tv.name car, (SELECT lv.userid 
                                   FROM lu_vehicle lv 
                                   WHERE tv.id=lv.vid AND lv.userid='%s') selected
              FROM tbl_vehicle tv
              ORDER BY tv.name",
              mysql_real_escape_string($user_id));

$result=mysql_query($sql);

Returns, when $user_id='bccd'

car 	selected 
------------------
Dogde	NULL
Ferrari	bccd
Ford	bccd
GM	NULL
Porsche	NULL
VW	bccd

[Bilbozilla]

What an elegant solution!!

I had no idea you could do a sub-select like you presented here.

This was similar to the process I was thinking through, but I didn't realize you could implement it such as this.

[Crowly]

Depending on the database you can have multiple subqueries, atleast up to 255 in one. Subqueries can be put almost every part of a sql (if i remember correctly)

SELECT something, (SELECT .....)
FROM (SELECT field1,field2 ...)
WHERE fieldx IN (SELECT alsdjs FROM tableN WHERE fieldY NOT IN (SELECT ....))
HAVING fieldA = (SELECT ....)
ORDER BY fieldA, fieldB, (SELECT ....)

I can recommend Learning SQL from O'Reilly (reading it atm). Parts of the book can be read at their web page (the link goes to that page)

[Bilbozilla]

Crowly;10960431 wrote:

I can recommend Learning SQL from O'Reilly (reading it atm). Parts of the book can be read at their web page (the link goes to that page)

Thank you for the link and all your assistance. I've got a book on mysql, but it wasn't nearly as in depth as the issues described in here. Google was helpful, but as I've always known, it's not the end-all.