How to "hide" field on update in MySQL

[Helen_W_Lee]

I’ve scrolled through all the posts and can’t see a similar question, so hopefully this isn’t a repeat.

I have a MySQL DB system set up which works well but now we need to tweak it a bit. It works in conjunction with a Mal’s E-comm shopping cart so that people can donate to sponsor trophies for a dog show.

Currently, the UPDATE record command just turns the “Show_Hide” field from “Y” to “N” so that a second person can’t try to pay for the same trophy.

Now, however, the powers that be want the trophy to stay visible but without the shopping cart fields visible. I know I can just removed the instruction to change Y to N but I’m stumped on how to tell it to NOT display two specific fields.

This is my current bit of code:

    $sql="UPDATE trophies 
		SET Show_Hide = 'N', dedication = '$dedication', donor ='$name
		WHERE ID = $id
		";

It turns off the Show_Hide field and also adds the dedication the purchaser wants as well as their name.

This is the coding for each individual trophy:

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<p class='left'>
		<input type='checkbox' name='qty". $row["itemnumb"]. "' value='1'>". $row["name"]. " - $". $row["price"]. 
			"<input type='hidden' name='product". $row["itemnumb"]. "[]' value='". $row["name"]. "'>
			<input type='hidden' name='price". $row["itemnumb"]. "' value='". $row["price"]. "'>
			- Dedication: ". $row["dedication"]. 
			"<input size='40' name='product". $row["itemnumb"]. "[]'>
			<input type='hidden' name='scode". $row["itemnumb"]. "' value='". $row["ID"]. "'>
			<input type='hidden' name='units". $row["itemnumb"]. "' value='1'>
			<input type='hidden' name='noqty". $row["itemnumb"]. "' value='1'>
        </p>";
    }
} else {
    echo "0 results";
}

I can’t figure out how to get it to NOT display the input type=‘checkbox’. I’m thinking that it probably isn’t done in the UPDATE instruction for the DB itself but I’m drawing a complete blank on how to do it!

Could someone kindly point me in the right direction on how to do this? Thanks!

[DarthGuido]

Can you show the select query for the trophies?

[SamA74]

A simple condition should do it:-

if($row['Show_Hide'] === 'Y') { 
    // Show a checkbox 
}

[Helen_W_Lee]

Hopefully this is the line you mean (I am SO rusty doing this!):

$sql = "SELECT ID, name, price, dedication, itemnumb FROM trophies WHERE ID BETWEEN 9900 and 9999 AND Show_Hide='Y'";
$result = $conn->query($sql);

[Helen_W_Lee]

Thanks SamA74 but I have that already. The problem is that hides the WHOLE line, not just the pertinent field(s).

The first line is one which has not been paid yet. The second line HAS been paid for and is displaying the dedication. I need to get rid of the checkbox at left and the blank “dedication” box at right but have drawn a blank on how to do that.

[DarthGuido]

Yes it is.
Ao right now the script only selects the trophies it has to show.

You could remove the check on show_hide from the select query, and add a check in the display loop.

[SamA74]

The idea is you don’t put everthing within the condition, only the bits that you don’t want to always show.
You will need to alter the query to SELECT the Show_Hide column and remove it from the WHERE clause.

[Helen_W_Lee]

Thanks DarthGuido. I would certainly do that except I can’t figure out just what you’re saying. Sorry - my brain is not what it was 40 years ago! It doesn’t help that it is EXTREMELY rigid/black-and-white.

Let me give it a shot: You’re saying I should NOT change the Show_hide in the UPDATE part (which I did already figure out) but rather do it somehow in the “if … while … echo” part? Is that correct?

[SamA74]

The update can be left*, but the select must change.

SELECT ID, name, price, dedication, itemnumb, Show_Hide FROM trophies WHERE ID BETWEEN 9900 and 9999

Then put the condition into the PHP.

*It actually should use a prepared statement, but that’s another topic.

[Helen_W_Lee]

Sorry SamA74 - my black-and-white brain is being really pig-headed today. By “condition” do you mean the “if … while … echo” part? I’ve tried to figure out just what you’re saying but am drawing a complete blank.

[SamA74]

Don’t echo everything all at once.
Divide it into what will show always, and what will only show if the condition is true.

[Helen_W_Lee]

SamA74 - I did try to figure out prepared statements but keep getting interrupted.

Is this perhaps what you mean:

echo “<input type=‘checkbox’ name='qty” WHERE dedication =“” … or something along those lines?

[Helen_W_Lee]

Oh! That makes sense … assuming I can figure out the correct coding. Thanks! I’ll give my brain a rest for a while and give that a try later. Thanks so much to you, SamA74 and to DarthGuido for replying so quickly!! Much appreciated.

[Helen_W_Lee]

OK. Thanks to SamA74 I’m getting there but am stumped by one thing.

As you can see from the above, I DID get it to remove the check box and text entry box for the trophies which have been paid for but for the LIFE of me I cannot figure out why it will not display the Donor name!

This is the code I have:

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		if ($row["dedication"] == "") {
        echo "<p class='left'>
		<input type='checkbox' name='qty" . $row["itemnumb"]. "' value='1'>". $row["name"]. " - $". $row["price"]. 
			"<input type='hidden' name='product". $row["itemnumb"]. "[]' value='". $row["name"]. "'>
			<input type='hidden' name='price". $row["itemnumb"]. "' value='". $row["price"]. "'>
			- Dedication: ". $row["dedication"]. 
			"<input size='40' name='product". $row["itemnumb"]. "[]'>
			<input type='hidden' name='scode". $row["itemnumb"]. "' value='". $row["ID"]. "'>
			<input type='hidden' name='units". $row["itemnumb"]. "' value='1'>
			<input type='hidden' name='noqty". $row["itemnumb"]. "' value='1'>
        </p>";
			} else {
             echo "<p class='left'>" . $row["name"] . " - Sponsored by " . $row["donor"]. " - " . $row["dedication"] .
        "</p>";
      }
    }

No matter what I change, it will NOT display the donor name! This is what the DB looks like:

As far as I can see the “donor” and “dedication” fields are identical - what on earth am I missing? I think there must be something I’m missing about the donor field because if I change the - if ($row[“dedication”] == “”) - part to “donor” instead of “dedication” (which I’d prefer) then the whole thing doesn’t work - it displays ALL the checkboxes and box to enter the dedication.

Thoughts?

[Helen_W_Lee]

I figured it out!!! It had nothing to do with the field itself.

$sql = "SELECT ID, name, price, dedication, donor, itemnumb FROM trophies WHERE ID BETWEEN 9900 and 9999";

I finally noticed that the donor field was NOT in that SELECT line. Once I added it - it worked!

Thanks for the original help folks. I must admit I’m feeling pretty chuffed that I managed to solve the problem myself.