Preserved Topic: Updating data in MySQL database via PHP

I can get data into a db via a php form.
I can get the data from the db into <input> and <textarea> fields via php.

But when I edit those fields, and try to use an UPDATE back to MySQL, I'm not getting the updates.

My method is that a php page grabs the info and populates the form fields. I can edit them, and when SUBMITted, they are sent to another page which contains nothing but the following code:

<?PHP
$db = mysql_connect("localhost", "root");
mysql_select_db("widgetsRW", $db);
$sql = "update news set heading='$new_heading' where news_id='4'";
mysql_query($sql);

echo("<b>Heading:</b> $new_heading\n");
echo("<br><b>Email:</b> $new_email\n");
echo("<br><b>Author:</b> $new_name\n");
echo("<br><b>Date:</b> $new_date\n");
echo("<br><b>Body:</b> $new_body\n");
?>

Now - all of the echo statements are working fine, so I know that the data is being sent from the other page. But the db is not being updated. The $sql= line originally was attempting to update all of the fields at one time, but troubleshooting has made me remove all but one field. This new page DOES show the updated entry in the echo statement, just not in the db.

If I manually update the record at a telnet prompt using:
update news set heading='this is my new header' where news_id='4'";
this works fine.

Can someone give me some ideas on what I'm doing wrong? People at the GurusNetwork have corrected the syntax, but I'm still having a problem. Everything I've read online says that my syntax should be correct.

Help!

Never mind, I think I got it. Once I get it straightened out, I'll post the answer.

Okay. I give up.

I have a page that brings up db info into <input> and <textarea> fields for editing. Source can be seen here. This works fine. If you browse that page and edit info, it sends it to another page, update.php, whose source can be seen here. This page also echoes the variables sent from the edit page. I know that the values are getting to the page, but they're not getting into the db. I've looked at a zillion pages of info, and I don't see what's wrong. I'm not getting any DIE statements.

HELP!

You forgot one very important thing. You are not passing news_id to the update script. You have hardcoded number 4 as news_id, but there's no news at id 4 (your edit script fetches news from id 1) and that's why update won't work (insert command should be used instead of update if there're no news defined).

So, you should define one hidden input field in your edit page, which will hold news id:

<input type=hidden name=news_id value="<?=$edit?>">

and modify your query in update script to look like:

$sql = "update news set heading='$new_heading' where news_id='$news_id'";

BTW You should run variables in update script through addslashes() function before inserting them in the query, like this:

$new_heading = addslashes($new_heading);

Max, my first born is on it's way!

Instead of
<input type=hidden name=news_id value="<?=$edit?>">
I used
<input type=hidden name=news_id value=$edit">
since that was already within the original php tags.

I added the addslashes features (I'll have to read about those a little), and it appears to be working.

I can't thank you enough. I was really starting to pull my hair out on that one.

Pugz,

Just a tip. You can always drop out of PHP and go back in even in loops. This way you don't need to use echo to write out forms.

You can even do this in the middle of loops

<?

while (var < $num) {
?>
I am cool
<?
$var++;
}
?>

This would loop through printing I am cool however many times the loop runs.


Walking the Earth like Kane

I would also like to add a few more suggestions:

1. Use mysql_pconnect() instead of mysql_connect() in order to establish persistent connections.
2. Create a new user in MySQL, which will have access only to database that you're using (don't access MySQL as root and while we are at it, you should really define a password for root user).
3. Don't use alternative syntax for control structures in PHP (if () : blah endif , use regular version of the syntax.



[This message has been edited by mr.maX (edited 07-23-2001).]

I planned on setting up users and stuff. I was just trying to learn a few quick tips before I commence to building something.

Actually, the end result will probably be one page that does all the admin, including adding, deleting, and editing the content. I'm about as green as you can get, but I'm learning fairly quickly.

Thanks to all for the help!

Mr. Max - can you be more specific about #3 in your suggestions?

He means use syntax like:

if (condition) {

   ...statements...

}

I wanted also to add that if your mysql might not affect any rows even if it doesn't cause an error (and hence die()). The only possible problem I can see is some kind of mysql error because the PHP looks solid.

-jiblet

Pugzly, in your edit page, you have the following code:

if(isset($delete)):
// <-- some mysql stuff here
endif;

That's the alternative syntax, which should be avoided...

I don't think he mentioned this because of your code and you probably are not using the alternative syntax.

The alt syntax kinda looks like something from VBScript where you can do an if statement like this

<?
if ($var == "whatever"):
echo "whatever";
elseif ($var == "whatever2"):
echo "whatever2";
else:
echo "It's not whatever or whatever2";
endif;
?>

It's old and deprecated and I doubt in any current books on the subject. Like I said it's not in any of the code you use here.

Oh and one other thing, I like to create a constants file that has my mysql pass, db and server info (It also connects to the db and has a function that handles my query's a little more elegantly)

I put this file outside of my root htdocs dir and access it from it's complete path.
It makes things both easier and a bit mroe secure.

~ heh fon't everyone speak at once now ~


Walking the Earth like Kane

[This message has been edited by bitdamaged (edited 07-23-2001).]

Thanks for all the info guys! I do have one more question -

If I want to display just one row of info, what's the best way of doing it? Currently, I use:
$sql = "select * from table_name where news_id = '$news_id'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
printf("<a href=\"/news.php?news_id=%s\">%s</a>\n", $row["news_id"], $row["heading"]);
}

This means I am using a loop for just one row of data. Is it the best way to do it?

[This message has been edited by Pugzly (edited 07-23-2001).]

mysql_fetch_row() is your friend.

There's a couple of different functions you can use most without any damage.

I may be wrong but I'm pretty sure you can do this without using the loop at all

ie
<?
$row = mysql_fetch_array($result);
printf("<a href=\"/news.php?news_id=%s\">%s</a>\n", $row["news_id"], $row["heading"]);
?>

Since it's just one row this should grab the first row.



:[ Computers let you make more mistakes faster than any other
invention in human history, with the possible exceptions of
handguns and tequila. ]:

The loop will prevent it from printing something if your query returns 0 rows for some reason. I don't see why you would need the miniscule performance enhancement from losing the loop (which in the case of 0 rows would arguably be more inefficient since it would print the empty variables).

Max cites mysql_fetch_row which does the same thing as fetch_array, but it returns ONLY an indexed array rather than both an indexed array and associative array. If you put any null values for anything other than the last field from the mysql selection you make, you run the risk of using the wrong values, because it won't include the empty space in the indexed array. The PHP manual states that the performance overhead for using fetch_array over fetch_row is not significant.

-jiblet

I'm a little confused on this alt syntax issue. Given bitdamaged's example:

if ($var == "whatever"):

echo "whatever";

elseif ($var == "whatever2"):

echo "whatever2";

else:

echo "It's not whatever or whatever2";

endif;

What would be an acceptable way of doing something like this? Does not using alt syntax mean I can't do if/else statements, or does it mean I need to use a different method? I've been using:

if (whatever):

// do my "true" stuff

else:

// do my "false" stuff

endif;

Nope the syntax to use for PHP is pretty much the same as JavaScript

if ($var == "whatever"){
echo "whatever";
}
elseif ($var == "whatever2") {
echo "whatever2";
}
else {
echo "It's not whatever or whatever2";
}


I sometimes get lazy and use the shorthand without the curly brackets

if ($var == "whatever") echo "whatever";






:[ Computers let you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila. ]:

I see. So the alt syntax refers to the curly bracket usage? That's not a problem. I just thought I was missing something really big.

Thanks.

Nope, it's the other way around. Alternative syntax is what you were using (this thing with ':' and "endif;"). The proper syntax uses curly brackes, as in C...

That's actually what I meant. My mistake. In alt syntax you don't use the curly brackets, and in standard syntax, you do use curly brackets.

Thanks!