[SOLVED] What's wrong with this PHP/MYSQL code?

jb60606 · July 1, 2007

A very simple MYSQL table contains four fields: ID, sym, e_price & e_date

ID

sym

e_price

e_date

1A1.002007-07-01

2B2.002007-07-02

3C3.002007-07-03

4D4.002007-07-04

I have an edit script that populates a simple HTML web form, with those fields, so that the user can edit records in the database.

I can populate the form just fine, though editing is another story. Let's say that the contents of the table above are the contents of the database. If I attempt to update record #1 (A, 1.00, 2007-07-01) to reflect a new date (2007-07-15, for example), the record is removed and replaced with a duplicate of the last record in the DB. In other words, record A becomes record D.

I think this may have something to do with the checkboxes, but I am not sure. Can you spot anything?

Code:


<form name="form1" method="post" action="<?PHP $_SERVER['PHP_SELF'] ?>">
<table width="665" cellpadding="0" cellspacing="0" class="main"><tr bordercolor="#909090" bgcolor="#909090">


<?php
include '../db.php';

$query  = "SELECT DISTINCT sym, id, e_date, e_price FROM tablename ORDER by sym";
$result = mysql_query($query);
$count = mysql_num_rows($result);

while($row = mysql_fetch_array($result)) {  
?>	

<tr>
<td><div align="center"><input name="checkbox[]" id="checkbox[]" type="checkbox" value="<? echo $row['id']; ?>"></div></td>
<td><input type="text" name="new_sym" id="new_sym" value="<? echo $row['sym']; ?>"></td>
<td><input type="text" name="new_e_date" id="new_e_date"  value="<? echo $row['e_date']; ?>"</td>
<td><input type="text" name="new_e_price" id="new_e_price" value="<? echo $row['e_price']; ?>"></td>
<td><A href="notes.php">View/Edit Notes on this Record</A></td>
<td> </td>
<td><input type="submit" name="update" value="Edit Selected"></td>
</tr>

<?PHP
}


if($_POST['update'])
{

$new_sym=$_POST['new_sym'];
$new_e_date=$_POST['new_e_date'];
$new_e_price=$_POST['new_e_price'];

for($i=0;$i<$count;$i++){
$id = $checkbox[$i];
$sql = "UPDATE symbols SET sym='$new_sym', e_date='$new_e_date', e_price='$new_e_price' WHERE id='$id'";
$result = mysql_query($sql);
}

// if successful redirect to records.php

if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=records.php\">";
}
}
mysql_close();
?>

Let me know if you need a link to the site.

Thanks in advance.

GingerRobot · July 1, 2007

I think you're almost there. However, you'll need to make some other changed to be able to edit more than one row at a time. You'll need all of your form inputs as arrays, and its usually best to make them have a key which is the ID from the database:

<form name="form1" method="post" action="<?PHP $_SERVER['PHP_SELF'] ?>">
<table width="665" cellpadding="0" cellspacing="0" class="main"><tr bordercolor="#909090" bgcolor="#909090">


<?php
include '../db.php';

$query  = "SELECT DISTINCT sym, id, e_date, e_price FROM tablename ORDER by sym";
$result = mysql_query($query);
$count = mysql_num_rows($result);

while($row = mysql_fetch_array($result)) {  
?>	

<tr>
<td><div align="center"><input name="checkbox[<?php echo $row['id'];?>]" id="checkbox[<?php echo $row['id'];?>]" type="checkbox" value="<? echo $row['id']; ?>"></div></td>
<td><input type="text" name="new_sym[<?php echo $row['id'];?>]" id="new_sym" value="<? echo $row['sym']; ?>"></td>
<td><input type="text" name="new_e_date[<?php echo $row['id'];?>]" id="new_e_date"  value="<? echo $row['e_date']; ?>"</td>
<td><input type="text" name="new_e_price[<?php echo $row['id'];?>]" id="new_e_price" value="<? echo $row['e_price']; ?>"></td>
<td><A href="notes.php">View/Edit Notes on this Record</A></td>
<td> </td>
<td><input type="submit" name="update" value="Edit Selected"></td>
</tr>

<?php
}


if($_POST['update'])
{

$new_sym=$_POST['new_sym'];
$new_e_date=$_POST['new_e_date'];
$new_e_price=$_POST['new_e_price'];
$checked = $_POST['checked'];

foreach($checked as $key => $value){
$id= $key;	
$sql = "UPDATE symbols SET sym='$new_sym[$id]', e_date='$new_e_date[$id]', e_price='$new_e_price[$id]' WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error()."<br />SQL: $sql");

}

// if successful redirect to records.php

if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=records.php\">";
}
}
mysql_close();
?>

Ive also changed your loop to a foreach - that way it'll only loop through if the checkbox was selected

jb60606 · July 2, 2007

Thanks for your help GinerRobot

For some reason I still can't get it to work though.

Also, I understand what the "checked" variable is for, though how does it get posted to the PHP code?

Thanks again.

jb60606 · July 2, 2007

UPDATE:

I removed the auto-refresh/redirect at the bottom of the code and found this error:

"Warning: Invalid argument supplied for foreach() in (filename) on line 415

Is there something in the HTML that I am supposed to pass the script? Where does $checked get it's values from? Is it something built into PHP?

teng84 · July 2, 2007

foreach($checked as $key => $value){ are refering to this??

then i think that was wrong

foreach($checked as $key ){ remove the $value because it the array from post right

and that is not multidimensional

jb60606 · July 2, 2007

Thanks for your help.

Unfortunately I'm still getting the same error.

If it's any help to anyone, I had PHP print out everything that was posted to the script and I didn't see anything regarding the status of the checkbox anywhere. I'm very new to PHP/MYSQL, but doesn't it need to be in there for us to use it as a variable?

Thanks again.

teng84 · July 2, 2007

;)I had PHP print out everything that was posted to the script and I didn't see anything regarding the status of the checkbox anywhere

ok can you tell us what do you realy mean

jb60606 · July 2, 2007

;)I had PHP print out everything that was posted to the script and I didn't see anything regarding the status of the checkbox anywhere

ok can you tell us what do you realy mean

well, the variable "$checked" is declared in the PHP code:

$checked = $_POST['checked'];

And is then used in the foreach loop:

foreach($checked as $key){
             $id=$key;

Though I don't see anything about the state of the checkbox (and by "state" I mean checked or unchecked) being posted from the form to the PHP code. As a matter of fact, I thought that only checked check boxes were posted in PHP and the unchecked were ignored. That's my guess as to why PHP is saying that the foreach statement is invalid, but, again, I'm rather new to all of this.

Thanks again.

jb60606 · July 2, 2007

I just figured out the problem, and I'm almost too embarrassed to say what it was.

In the HTML, "checkbox[]" is posted to the script. In the PHP code, it was looking for $_POST['checked'];. I thought they were two different things before but it hit me when I was writing out that reply to you.

Thank you GingerRobot and Teng, for all your help. I don't know what I'd do without this website.

JP128 · July 2, 2007

what was it?

GingerRobot · July 2, 2007

Whoops, that was partly my fault. Glad you sorted it out

jb60606 · July 2, 2007

Whoops, that was partly my fault. Glad you sorted it out

not a problem. Without your code to point me in the right direction, I probably would have given up and gone another route, probably altering it so that the user would have to tediously update each record one at a time.

Thanks again.

Sign In

[SOLVED] What's wrong with this PHP/MYSQL code?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information