Getting an update query to work

[dazzathedrummer]

Hi,

 

I've been creating an update form with the help of this forum.

 

I'm almost there but i've run into what I think is a data type problem.

 

here's my code:

<?php 
// Connects to your Database 
mysql_connect("database.lcn.com", "LCN_7792", "PASSWORD") or die(mysql_error()); 
mysql_select_db("the_guards_org_uk_users") or die(mysql_error()); 

$id =  $_POST['gl_id'];
$text = $_POST['gl_text'];
echo $id;

//This code runs if the form has been submitted
if (isset($_POST['submit'])) { 


//This makes sure they did not leave any fields blank
if (!$_POST['gl_date']) {
die('You did not enter a valid date');
}	


// now we insert it into the database

mysql_query("update tg_gig_list set gl_text='$text' where gl_id='$id'");
?>


<h1>Gig entered succesfully</h1>
<p>back to <a href="guard_admin.php">admin area</a>.</p>
<?php 
} 
else 
{	
?>


<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<table border="0">
<tr>
<td>Date (YYYY-MM-DD)</td>
<td><input type="date" name="gl_date"  maxlength="20"value="<?php echo $_POST['gl_date']; ?>"></td></tr>
<tr>
<td>Venue</td>
<td><input type="text" name="gl_venue" maxlength="20"value="<?php echo $_POST['gl_venue']; ?>"></td></tr>
<tr>
<td>City</td>
<td><input type="text" name="gl_city" maxlength="20"value="<?php echo $_POST['gl_city']; ?>"></td></tr>
<tr>
<td>Postcode</td>
<td><input type="text" name="gl_postcode" maxlength="10" value="<?php echo $_POST['gl_postcode']; ?>"></td></tr>
<tr>
<td>Phone</td>
<td><input type="text" name="gl_phone" maxlength="20"value="<?php echo $_POST['gl_phone']; ?>"></td></tr>
<tr>
<td>Contact</td>
<td><input type="text" name="gl_contact" maxlength="20"value="<?php echo $_POST['gl_contact']; ?>"></td></tr>
<tr>
<td>Fee</td>
<td><input type="text" name="gl_net" maxlength="6" value="<?php echo $_POST['gl_net']; ?>"></td></tr>
<tr>
<td>Comments (internal)</td>
<td><textarea rows="5" cols="17" name="gl_comments"><?php echo $_POST['gl_comments']; ?></textarea></td></tr>
<tr>
<td>Web text</td>
<td><textarea rows="15" cols="17" name="gl_text"><?php echo $_POST['gl_text']; ?></textarea></td></tr>
<tr><td>Private Function</td>
<td><input type="checkbox" name="gl_pf" value="<?php echo $_POST['gl_pf']; ?>"><td></tr>
<tr><td>Publish</td>
<td><input type="checkbox" name="gl_publish" value="<?php echo $_POST['gl_publish']; ?>"/></td></tr>
<tr><td>Unavailable</td>
<td><input type="checkbox" name="gl_unavailable" value="<?php echo $_POST['gl_unavailable']; ?>"/></td></tr>
<tr><td align="right" rowspan="2"><input type="submit" name="submit" value="update"></td></tr>

</table>


</form>

<?php
}
?>

 

The problem is with the $id variable not being taken in the query in the right format (I think), where I have "...where gl_id = '$id'" im not getting a match - but if I hardcode "...where gl_id = '67'" I get the desired result.

 

I'm echoing out $id at the top and that gives me '67' so i'm assuming that its the type that the query doesn't like. The db feild 'gl_id' is a 5 char Int.

 

How can I match these up?

 

[beingalex]

It doesn't look as if you're actually posting gl_id.

 

You're asking for $_POST['gl_id'] but you haven't supllied it from the form.

[dazzathedrummer]

It doesn't look as if you're actually posting gl_id.

 

You're asking for $_POST['gl_id'] but you haven't supllied it from the form.

 

Hi,

 

its coming from another forms 'post' - its being sent from an 'edit' button on a list - so $id = '67' when the page starts, presumably this is why it echos out correctly.

I'm probably not seeing something here - i'm fairly new to this.

 

Is this why the sql query isnt recognising '67'?

 

[beingalex]

Are you seeing "Gig entered succesfully" when you submit ?

[dazzathedrummer]

Yes, I get the success message, so the query works, its just not getting an ID to update - either because the type is wrong (but that would error wouldn't it?) or $id isn't set at that point.

 

?

[beingalex]

Very strange ... where you have your query try this line instead..

 

mysql_query(sprintf("update tg_gig_list set gl_text='%s' where gl_id=%d", $text, $id));

 

This will tell you if there is a type problem.

[dazzathedrummer]

I put this in the "gig entered..." section and echoed it - it returns 1, what does that mean?

[beingalex]

It means it returned true - which means it worked. Are you definitely checking the right database table for changes ?

 

Did you replace :

mysql_query("update tg_gig_list set gl_text='$text' where gl_id='$id'");

 

with:

mysql_query(sprintf("update tg_gig_list set gl_text='%s' where gl_id=%d", $text, $id));

 

?

[dazzathedrummer]

...yes, I did.

 

 

ahhh - if I do $id = 67, then

 

mysql_query("update tg_gig_list set gl_text='$text' where gl_id='$id'");

 

..will update row 67.

 

so, does this suggest that when the variable is set using $id = $_POST['gl_id'], it will take '67' from the previous form, but when the 'update' button on this form is pressed, it looses the value??

[beingalex]

Yes it will lose it's value unless you put a hidden form field in like:

 

<input type="hidden" value="$_POST['id']" name="id" />

[dazzathedrummer]

thats it!!

 

Problem solved!

 

I guess that's what you said in your first reply "variable called but not set" doh!

 

...now I understand I can set this up for all fields.

 

Thanks very much for your help!!

[beingalex]

no problem.

 

Yes, you have to pass the post variables again each time.