Updating database row is updating the whole table

[Murciano]

 

This .php code fetche's data from MySQL and displays it in a texteditor (CKeditor) where it can be updated and returned to its same row, it fetches it ok,  However after updating and clicking submit to return it to its row it just goes bananas and overwrites the whole table with the same content in each row, what do i need to add to the UPDATE query to get it to function properly?

 

<?php 
// If submitted, check the value of "select". If its not blank value, get the value and put it into $select.
if(isset($select)&&$select!=""){
$select=$_GET['select'];
}
?>
<form id="form1" align="center" name="form1" method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<select name="select">
<option value="">Select Template For Editing</option>
<?php
// Get records from database (table "listings").
$q=mysql_query("SELECT * FROM listings ORDER BY id");

// Show records by while loop.
while($row_list=mysql_fetch_assoc($q)){
?>
<p align="center">
<option value="<?php echo $row_list['id']; ?>"><?php echo $row_list['company_name']; ?></option>
<?php
// End while loop.
}
?>
</select>
<input type="submit" name="Submit" value="Select" />
</form>

<?php
// If you have selected from list box.
if(isset($select)&&$select!=""){

// Get records from database (table "name_list").
$result=mysql_query("SELECT * FROM listings WHERE id='$select'");
$row=mysql_fetch_assoc($result);
?>
<div id="tempdetails">The listing that you have chosen to edit from is:  <font color="red"><u><?php echo $row['company_name']; ?></u></font></div><!-- /tempdetails --> 
<p>
<!-- ##### THIS FORM SHOWS (or should show) THE INFORMATION FROM EDITOR1 FIELD IN db ##### -->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="test" method="post">
<p align="center"><font color="red">All fields are required.</font></p>
            <p align="center">Location: <input type="text" name="location" size="40" value="<?php echo $row['location']; ?>" />  
             Company Name: <input type="text" name="company_name" size="40" value="<?php echo $row['company_name']; ?>" /></p>
            <textarea id="editor1" name="editor1"><?php echo $row['editor1']; ?></textarea>
            <script type="text/javascript">
                CKEDITOR.replace( 'editor1',
{
     enterMode : Number(2),
        filebrowserBrowseUrl : 'ckfinder/ckfinder.html',
	filebrowserImageBrowseUrl : 'ckfinder/ckfinder.html?type=Images',
	filebrowserImageUploadUrl : 'ckfinder/core/connector/php/connector.php?command=QuickUpload&type=Images',

     
}
   );
            </script>
           </p>
           <p>
            <input type="submit" name="submit" />
           </p>
</form>
<?php
// End if statement.
}
?>
<?php 
// this part updates the database with the newly created business listing
    
    if (isset($_POST['submit']))
    {
     $location = $_POST['location'];   
     $company_name = $_POST['company_name'];
     $editor1 = $_POST['editor1'];
     
     // lets populate the database with this new information
     
     $q = ("UPDATE listings SET location='$location', company_name='$company_name', editor1='$editor1'");
     
     $result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $q . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

     
     // success or failure ?
     if ($result) echo "<p align=\"center\"><u>The newly edited listing was successfully inserted into the Listing's database</u></p>";
     else echo "<p align=\"center\"><font color=\"red\">There was a problem, the data got lost on the way.</font></p>";
    }
    
}else {
        /* Link back to main */
header("Location: index.php");
    }
?>

     <!-- ##### END OF EDITING AND SENDING THE EDITED HTML TO THE "LISTINGS" db TABLE ##### --> 
</div><!-- /editor -->
</div><!-- /editor_wrapper -->
<!-- START OF FOOTER -->
                                  
     </div><!-- /wrapper -->
</body>
</html>

[kenrbnsn]

You need to tell MySQL which record it's supposed to update with the where clause, if you don't it will update all the records.

 

Ken

[Asheeown]

 

<?php
     $q = ("UPDATE listings SET location='$location', company_name='$company_name', editor1='$editor1' WHERE id = '$id'");
     
     $result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $q . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
?>

 

As long as you populate $id with the correct listing id before this statement now it will update the specific record.

[Murciano]

Thanks for the heads up, i've added the id as you can see beneath the if(isset and also to the WHERE clause, now its giving me a fatal error of

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=''' at line 1

im looking at what the error says but it seems to be pointing at charset issues which isnt the case here (not that i am aware of anyway)

 

if (isset($_POST['submit']))
    {
     
     $id= $_POST['id'];
     $location = $_POST['location'];   
     $company_name = $_POST['company_name'];
     $editor1 = $_POST['editor1'];
     
     // lets populate the database with this new information
     
     $q = ("UPDATE listings SET location='$location', company_name='$company_name', editor1='$editor1' WHERE $id='id'");
     
     $result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $q . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

     
     // success or failure ?
     if ($result) echo "<p align=\"center\"><u>The newly edited listing was successfully inserted into the Listing's database</u></p>";
     else echo "<p align=\"center\"><font color=\"red\">There was a problem, the data got lost on the way.</font></p>";
    }

[KevinM1]

Syntax error.  The following:

 

WHERE $id='id'

 

needs to be

 

WHERE id='$id'

[Murciano]

Thanks, but that still gives me the same error

 

Syntax error.  The following:

 

WHERE $id='id'

 

needs to be

 

WHERE id='$id'

[kenrbnsn]

Please post the entire error message

[Murciano]

This is the entire message

 

Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=''' at line 1

 

what i have noticed is that the fatal error query seems to break just here during the update for the editor1 field

 

A fatal MySQL error occured.

Query: UPDATE listings SET location='El Pareton', company_name='Scotties Bar', editor1='\r\n\r\n

 

[kenrbnsn]

You're not sanitizing the values passes from the form so something in the value is messing you up. Try:

<?php
(isset($_POST['submit']))
    {
     //
     // debug
     //
     echo '<pre>' . print_r($_POST,true) . '</pre>';
     //
     $id= mysql_real_escape_string($_POST['id']);
     $location = mysql_real_escape_string($_POST['location']);   
     $company_name = mysql_real_escape_string($_POST['company_name']);
     $editor1 = mysql_real_escape_string($_POST['editor1']);
     
     // lets populate the database with this new information
     
     $q = "UPDATE listings SET location='$location', company_name='$company_name', editor1='$editor1' WHERE id='$id'";
     
     $result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $q . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
?>

 

Ken

[yaricks]

You can also use LIMIT 1 at the end to make sure you only update one.

 

WHERE id=$ID LIMIT 1

 

[Murciano]

Hi Ken, thanks for taking the time, using the complete block of code just returned a white page when refreshed, adding the mysql_real_escape_string into my code (same place as yours) returned the sam error with an extra slash  example:"\\r\\n"

[kenrbnsn]

What does

<?php
echo '<pre>' . print_r($_POST,true) . '</pre>';
?>

produce?

 

Ken

[Murciano]

On clicking submit it shows this

 

Array

(

    [location] => Camposol

    [company_name] => Lizs Cafe

    [editor1] => \r\n\r\n

\r\n

\r\n

\r\n

\r\n

 

theres lots more about 1000 characters or so, but i guess the info you want is the first couple of lines

[kenrbnsn]

That's the problem. Somehow the value of the editor1 field contains multiple end-of-line characters. You have to figure out why that is happening before we can fix the current problem (which may go away once the field contains a valid string).

 

Ken

[Murciano]

Ok Ken, thanks for all your help, i will look into what is causing it, once again many thanks.

[Murciano]

Well today managed to get somewhere at last (almost) this is the block of code now for the UPDATE query

<?php 
// this part updates the database with the newly created business listing
    
    if (isset($_POST['submit']))
    {
     
     $id= $_POST['id'];
     $location = $_POST['location'];   
     $company_name = $_POST['company_name'];
     $editor1 = str_replace("\n\r","<br />",$_POST['editor1']);
     
     // lets populate the database with this new information
     $q = ("UPDATE listings SET location='$location', company_name='$company_name', editor1='$editor1' WHERE id='$id'");
     
     $result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $q . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

     
     // success or failure ?
     if ($result) echo "<p align=\"center\"><u>The newly edited listing was successfully inserted into the Listing's database</u></p>";
     else echo "<p align=\"center\"><font color=\"red\">There was a problem, the data got lost on the way.</font></p>";
    }
    
}else {
        /* Link back to main */
header("Location: index.php");
    }
?>

 

Now there are no errors, no updating all the rows in the table, just now it will not update its own row, i click submit, it returns success but the Db row has not been altered, no errors or anything to go on