updating a table using PHP and MySQL

I am working my way through an update page for a simple MySQL database and have run into a couple of issues.  I am a newbie when it comes to PHP and MySQL - I have tried to pull the pertinent procedures together, but can't seem to finish it up.

Here is the pertinent section of the code:

********

<table width="90%"><tbody><tr>

<td valign="top" align="left">
<p>Edit Existing Menu items</p>

<?php

include("dbinfo.inc.php");

// Connect to server and select databse.
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
?>


<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="800" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Menu Id</strong></td>
<td align="center"><strong>Visible</strong></td>
<td align="center"><strong>Menu Item</strong></td>
<td align="center"><strong>Course</strong></td>
<td align="center"><strong>Price</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $menu_id[]=$rows['menu_id']; ?><? echo $rows['menu_id']; ?></td>
<td align="center"><input name="isVisible[]" type="checkbox" id="isVisible" value"1" checked="<? echo $rows['isVisible']; ?>"></td>
<td align="center"><input name="menu_item[]" type="text" id="menu_item" value="<? echo $rows['menu_item']; ?>"></td>
<td align="center"><input name="course[]" type="text" id="course" value="<? echo $rows['course']; ?>"></td>
<td align="center"><input name="price[]" type="text" id="price" value="<? echo $rows['price']; ?>"></td>
</tr>


<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>

**********

Here is the test page:  http://www.whalecovecottages.ca/admin/menuedit.php


********

I am trying to figure out a couple of things:

1.  Dynamically populate the isVisible checkbox to reflect 1 = checked or 0 = unchecked, depending on what is in the table field isVisible.  Currently, the page shows them all checked, but only items 1-9 should be checked.

2.  Ignoring that small issue, I have tested the submit function, and it doesn't submit.  What am I missing?  Is it because I have the submit query firing on the submit button even though there is no action on the form tag?

I am sure these are simple issues and I am just not getting it at this stage - my apologies.  Thank you in advance!

chipsterva69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chipsterva69Author Commented:
dang it - I missed the submit piece of the code - Sorry.  Here is the rest, picking up after the </table> tag above:

</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET isVisible='$isVisible[$i]', menu_item='$menu_item[$i]', course='$course[$i]', price='$price[$i]' WHERE menu_id='$menu_id[$i]'";
$result1=mysql_query($sql1);
}
}

mysql_close();
?>


</td></tr></tbody></table>
0
john-formbyCommented:
Hi,

1.  Change this line:

<td align="center"><input name="isVisible[]" type="checkbox" id="isVisible" value"1" checked="<? echo $rows['isVisible']; ?>"></td>

to:

<td align="center"><input name="isVisible[]" type="checkbox" id="isVisible" value="1" <?php echo ($rows['isVisible']=="1" ? 'checked' : '') ?>></td>


2.  You need to set the page you are submitting the form to in your form tag.  This line:

<form name="form1" method="post" action="">

Should be something like:

<form name="form1" method="post" action="PAGE_TO_SEND_TO">

Hope this helps,

John
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gamebitsCommented:
replace this

<td align="center"><input name="isVisible[]" type="checkbox" id="isVisible" value"1" checked="<? echo $rows['isVisible']; ?>"></td>

by that

<td align="center">

<?php if($rows['isVisible'] == '1'){echo "<input name=\"isVisible[]\" type=\"checkbox\" id=\"isVisible\" value\"1\" checked=checked"></td>";}else{echo "<input name=\"isVisible[]\" type=\"checkbox\" id=\"isVisible\" value\"1\" checked=checked"></td>";} ?>

This should take care of the checked unchecked issue
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chipsterva69Author Commented:
john-formby - the checkbox worked like a charm.  thanks!

regarding the submit - so I need to move the submit code to a page like menuupdate.submit.php have the form action point to that, right?  how do I pass over only the changed values/records?  i was hoping that the submit code that is fired on the submit would take care of having to pass all that along...  or is it just the act of the page moving on to another/different page that makes the submit fire?
0
john-formbyCommented:
Is the update code on the same page as the form?  If it is, then you need to make you form tag like this:

<form name="form1" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
0
john-formbyCommented:
Also, make this line:

if($Submit){

like this:

if(isset($_POST['Submit'])) {
0
chipsterva69Author Commented:
john-formby - thanks again!

the submit function works, but unfortunately, it just blanked the database!  no worries, it is a tester...  it looks like none of the variables in the submit statement had values.

this is what I currently have -

if(isset($_POST['Submit'])) {
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET isVisible='$isVisible[$i]', menu_item='$menu_item[$i]', course='$course[$i]', price='$price[$i]' WHERE menu_id='$menu_id[$i]'";
$result1=mysql_query($sql1);
0
chipsterva69Author Commented:
sorry - submitted it too quickly.

the submit statement is this -


if(isset($_POST['Submit'])) {
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET isVisible='$isVisible[$i]', menu_item='$menu_item[$i]', course='$course[$i]', price='$price[$i]' WHERE menu_id='$menu_id[$i]'";
$result1=mysql_query($sql1);
}
}

Thanks for your help with the original request - absolutely on the nose with you advice.  Thanks!!
0
chipsterva69Author Commented:
excellent recommendations and dead on.  still working on the final part of the submit - apparently my code is leaving the values blank.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.